List of all partitioning videos Part 1 : Partitioning in Oracle : ru-vid.com/video/%D0%B2%D0%B8%D0%B4%D0%B5%D0%BE-m3q4lrE671Y.html Part 2 : Partitioning in Oracle - Performance Basics :ru-vid.com/video/%D0%B2%D0%B8%D0%B4%D0%B5%D0%BE-OQ8LXbxLI5g.html Part 3 : List Partitioning in Oracle : ru-vid.com/video/%D0%B2%D0%B8%D0%B4%D0%B5%D0%BE-w6kB-90pFa4.html Part 4 : Range partitioning in Oracle : ru-vid.com/video/%D0%B2%D0%B8%D0%B4%D0%B5%D0%BE-VBkpI4Ki49Q.html Part 5 : Hash Partitioning in Oracle : ru-vid.com/video/%D0%B2%D0%B8%D0%B4%D0%B5%D0%BE-2YxOF_sJMLI.html Part 6 : Composite Partitioning in Oracle :ru-vid.com/video/%D0%B2%D0%B8%D0%B4%D0%B5%D0%BE-q_e9IkNtSWc.html Part 7 : Reference Partitioning in Oracle :ru-vid.com/video/%D0%B2%D0%B8%D0%B4%D0%B5%D0%BE-p8gNwr6Rtdo.html Part 8 : Local Vs Global Partitioned Index in Oracle 11g : ru-vid.com/video/%D0%B2%D0%B8%D0%B4%D0%B5%D0%BE-t77fyUt8XPw.html Part 9 : Partitioning enhancement in 12 c :ru-vid.com/video/%D0%B2%D0%B8%D0%B4%D0%B5%D0%BE-o_WjapdXiQg.html
When you drop/Truncate a partition from a table that has global index on it, The Global Index becomes Unusable. You have an option to rebuild the index using "UPDATE GLOBAL INDEXES", But Update is a DML command so the index data will be deleted(Instead of being dropped like in Local Partitioned index) and it will generate a lot of redo/undo logs (if your table in huge) leading to lot of DB performance issues.I wanted to clarify this as if I felt it was not 100% clear from the video that what exactly is taking time if we are dropping the partition .
Sure Ranaj I will try to make a more detailed video on the same. I would appreciate if you subscribe share and watch my new video channel. "An Indian Abroad " Your support is what keeps me going. ru-vid.com
Hi Vivek, Thanks for these wonderful videos which you have shared till date . Could you please explain one point . How the Global index becomes invalid when we drop a partition of the table having global index .
Hi Somnath, Thanks for the kind words. Let's say our order table has 300 rows and is range partitioned on order_id P1-1-100 P2-101-200 P3-200-300 and we have created a global index on the order_date column, If we drop partition p1 the index on order_date will have orphan keys with no data in order table thus making the index invalid in 11g, This has been taken care off in 12c where you can rebuild the index at a later stage and global index will still be usable.
Thanks Vivek .. You are doing such an excellent job . Please keep uploading these kind of videos . Index rebuild option is available in 11g also ? ... Also instead of using "UPDATE GLOBAL INDEXES" is it a better option to rebuild the index ....Why i am asking this because i have one OLTP db . Where i found a partition table has a global index ...And there is a HSKP package which truncates the older partition . But then it drop / create the global index . Then it performs "UPDATE GLOBAL INDEXES" option. So, will it be more beneficial if we rebuild the global index instead of drop/create .
Great great great info, clearly explained in simple ways, only person having good knowledge can explain clearly.. Please keep up the good work...can you cover all topics related to Oracle Db objects and their concepts as much as possible if not done already.....thanks again for wonderful explanation with diagrams and details👍🏻😊
Thanks for your info .In exist table data were already present in segment area , How to move exist data into particular partition ,could you please share us to crack those ..
In terms of partitoning yes, A local index shares one to one relationship with the partition, while a global index share 1 to many relationship. So if we are talking about a partiton we may use the word local index ( local to that partition) and global index since the index contains more values that don't lie in that partition, I hope this helps. I have a small request I am working on this new channel "An Indian abroad" and will really appreciate if you watch and subscribe to it. ru-vid.com/video/%D0%B2%D0%B8%D0%B4%D0%B5%D0%BE-11DK-oyRql0.html
In previous video on the local and global indexes, you said global index is one which is created on a column which is not the partition key. and in this video you showed an example for global index where you create an index on ID and the same column is used for partition, as partition key. Please clear me.
Can you explain by some example.. where we can use local index and global index and how it is different from normal ? As per my understanding local index will also create on table and normal also, but local will have one to one mapping with partitions so it is faster how it is faster,? if i create a normal index also on ID column it will be faster, am i correct ?
Sure I will share it soon, I would really appreciate if you help me in my new Initiative "An Indian Abroad" by subscribing viewing and sharing. ru-vid.com
Beautifully explained. Lot of questions I had have been answered just in this one video. My only suggestion is, if you could run those commands, show the difference in explain plans, it would've been very helpful.
Thanks for the feedback , I will come up with a video soon :).Meanwhile I have a small request, I am working on this new channel and would really appreciate if you watch and subscribe to it. ru-vid.com/video/%D0%B2%D0%B8%D0%B4%D0%B5%D0%BE-IP2Vn6jK8Hg.html
Hi Vivek, I created table as per your slide and queried "SELECT * from user_segments where segment_name = 'TRAN_PART_IDX_DEMO' ;" but I am getting result. I have not inserted any data then how segments are created as you have told no segmemt will create until and unless I insert data into table. My user_Segment table shows both the partition that I created on table TRAN_PART_IDX_DEMO. Please suggest.
Hi Sandip, I have verified in oracle 12c, The entry was made to user_segments only after I inserted a row in the table. Can you please confirm which version are you using?, I had verified this in past in oracle 11g as well. Unfortunately I don't have 11g anymore
They are very useful for performance optimization.you can think of them as a normal index but instead of table they are restricted to the individual partitions.I hope this helps
Hi Vivek, If I am using a composite partition strategy in one of my production table how should I partition my indexes using local prefix index partitioning in this case?
@@TechCoach My question is if I have a hash-range partition (let's assume this is the composite partition strategy) in one of the tables and I have a local index but not on the partition key(non prefix), then on which partition key the indexes will be partitioned (hash or range)?
@@write2subho83 I think that local index will have same range boundaries as the composite partioning of the table (i.e Index will have partitions based on both Hash & Range )
Thanks for the kind words Siddhartha I have a small request, I am working on this new channel and would really appreciate if you watch and subscribe to it. ru-vid.com/video/%D0%B2%D0%B8%D0%B4%D0%B5%D0%BE-QrpPc-WNjoU.html
@@TechCoach surely btw, in case of a local non-prefixed index, though majority of times there will be no partition pruning but there are times when partition pruning takes place in non-prefixed indexes as well. Take a look at this link asktom.oracle.com/pls/apex/asktom.search?tag=use-nonprefix-local-index
Thanks a lot for the kind words, I am working on this initiative, a new youtube channel named "an Indian Abroad" I would really appreciate if you watch, subscribe and share the video with your friends. ru-vid.com/video/%D0%B2%D0%B8%D0%B4%D0%B5%D0%BE-HBQHekM1U2c.html
Tech Coach Sure bro.. I am looking for some tutorials on oracle cloud as well... it will be really helpful if you start posting step-by-step guide videos for the same...
Thanks for the kind words. I have a small request I am working on this new channel "An Indian abroad" and will really appreciate if you watch and subscribe to it. ru-vid.com/video/%D0%B2%D0%B8%D0%B4%D0%B5%D0%BE-11DK-oyRql0.html
