Composite Partitioning in Oracle

This video is the 6th Video in the series partitioning in oracle.
It explains the different composite partitioning approaches and in what scenario they should be used with real project use case explanations.
#OraclePartitioning #CompositePartitioning #TechCoach

[@TechCoach]

List of all partitioning videos Part 1 : Partitioning in Oracle : kzbin.info/www/bejne/o2TUZZ-oeptqZ7s Part 2 : Partitioning in Oracle - Performance Basics :kzbin.info/www/bejne/hYKbfYuYrbF8a8k Part 3 : List Partitioning in Oracle : kzbin.info/www/bejne/rWfOc2BvZdV5l5Y Part 4 : Range partitioning in Oracle : kzbin.info/www/bejne/jHPOoXxqgM5nb7M Part 5 : Hash Partitioning in Oracle : kzbin.info/www/bejne/aIrbgHmVqK-Agqs Part 6 : Composite Partitioning in Oracle :kzbin.info/www/bejne/p5DIanyhg9mGjcU Part 7 : Reference Partitioning in Oracle :kzbin.info/www/bejne/pmnKf6qoa7enmtE Part 8 : Local Vs Global Partitioned Index in Oracle 11g : kzbin.info/www/bejne/qmial6yLqZ2Lhtk Part 9 : Partitioning enhancement in 12 c :kzbin.info/www/bejne/pZC6m5Smmb2ch8k

[@MoinullaBaig]

Thank you for your very informative videos 🙏. I have a problem statement and I would like to hear your opinions on it. Problem Statement: I have a transaction table with 1 Billion Rows per Month (Approx. 50 GB/month). I will have aggregation of transactions for a specific user_id and a date range. The aggregation can be like 1. Aggregate per Day for a Week 2. Aggregate per Day for a Month 3. Aggregate per Month for an year. So my query will always a "user_id" and timestamp > start_date and timestamp < end_date. Also, I need to retain the data for 3 years. So, I'm exploring which partitioning strategy to opt for and the below is my understanding. 1. Range Partitioning (INTERVAL= EVERY 1 DAY): Pros: - Easy maintenance as Data purging is easier Cons: - Since the query can have a date range for a week(7 partitions), a month(30 partitions) or an year(365 partitions), will this impact the query performance as I need to aggregate over multiple partitions? 2. Hash Partitioning (partition key by user_id, NUM Partitions = 128 partitions): Pros: - All data for a specific user will be in the same partition and hence can query performance would be better Cons: - Might result in Skewed data as the partitioning is based on user_id and not transaction_id - Difficulty in purging as the old data is spread across multiple partitions. 3. Range-Hash Partitioning (INTERVAL= EVERY 1 MONTH, Sub-partition by user id with 32 partitions) Pros: - Benefits of Range Partitioning (Easier Maintenance) - Most of my queries will be for weekly or monthly and hence most of the time the query should find the data in the same segment Cons: - Will it have any impact if I have to query for an year and aggregate monthly? 4. Hash-Range Partitioning (Partition by user with 32 partitions, sub-partition by Range(INTERVAL= EVERY 1 MONTH)) -- I have no clue how this can be different from Range-Hash Partitioning. Kindly share your opinion and help me in taking this decision. Thanks, Moin

[@andishez5359]

teaching in very proper manner to visualize deeply , Thanks again and again , God Bless You

[@TechCoach]

Thanks Andishe for the kind words, Can I ask you for a small favor.I am workinh on this new channel please subscribe to it and share it among your friends as well. kzbin.info/www/bejne/oGi1o6t7o7-Sbqc

[@andishez5359]

@@TechCoach Yes of course , It is the least I can do to say thank you to your effort and valuable time spending to prepare wonderful and very useful videos for all over the world.

[@sandipjena]

A very good video on subpartition. It will be helpful for us/your subscribers if you can post more videos on subpartiton focusing on hash - hash , has-range and has-list subpartition. Thanks.

[@TechCoach]

Hey Sandip, Thanks for being so responsive. I am making some new videos and I will post them soon.

[@nagaswathichaluvadi3523]

could you please provide examples for hash-hash,hash-list and hash-range as you did for others

[@FaisalFaizz]

Can you tell me how to delete duplicate records in the partitioned table.i got this question in my interview.

[@rgrepo]

Did you find your answer for this?

[@sireeshaas7680]

@Faisal Faizz Pls let me know if know the answer?