Composite Partitioning in Oracle

[@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

[@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.

[@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

[@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?

[@nagaswathichaluvadi3523]

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

[@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.