Partitioning in Oracle - Performance Basics

This is the 2nd video from " Partitioning in Oracle " series, It explains how oracle stores and manages data.
What is single Block IO and Multi Block IO ?
Why full table scan is better than index access in few cases.
The video is very elaborate, I have tried my level best to keep it as simple as possible
#Oraclepartitioning #PerformanceOptimzation #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

[@manjushatonape2099]

very crisp and clear explanation.. Thanks a lot

[@TechCoach]

I am Glad I could help

[@manjushatonape2099]

Can you plz make video on oracle hints

[@jinkalavamshi272]

It's very rare we will this kind of info in the youtube. Thanks so much.. It's really helpful for me..

[@kensavage7657]

I knew a lot of this, but you helped fill in some of the blanks. The best bit is that you helped me be able to explain this, in simpler terms, to our non-DBA development team. Great job with this video! As someone who is learning a foreign language, I can appreciate how silly our (English) word pronounciation can be - and I appreciate when people help me pronounce my foreign language (German), when I get it wrong. When you say "contiguous" (meaning beside/connected), in the video, you pronounce it like "contagious" (easily transmitted, like a disease) Spelled similarly, they're actually pronounced completely differently (phoenetically: 4 syllable CON-TIG-YU-US vs 3 syllable CON-TA-JUS) My wife is not a native English speaker, so makes similar mistakes when she learns a word by reading. While I can only apologise, for how erratic the English language is, this is a word that, we DBAs, use a lot - and we, often, only learn mistakes in pronounciation when a native speaker can notice and point it out. This is not a criticism of your excellent video - genuinely just trying to help! Once again, fantastic video throughout!

[@TechCoach]

Wow ken you are an artist, your team is lucky to have a polite mentor like you :)

[@andishez5359]

Thank you so much for your great tutorials, Explaining in very friendly and deep manner, really love them.

[@TechCoach]

Thanks a lot for the kind words. Your comment made my day :)

[@mansishrivastava2791]

Hi, This video is really very much informative and easy to understand.I agree that the video is lengthy but still I appreciate that each and every small part related to partition is covered with proper examples.its very rare that we get whole information related to a particular topic at one place. Thanks

[@TechCoach]

Thanks a lot mansi for the kind words, I have a small request I am working on this new youtube channel " An Indian Abroad " and will really appreciate if you watch and subscribe to it. kzbin.info/www/bejne/Z2KnfGClrrekopI

[@rameshotturu3530]

@@TechCoach bro, i am unable to see th video, seems video is corrupted. could you please check

[@TechCoach]

@@rameshotturu3530 i am able to open it, can you try again?

[@selvajananiraman4040]

Hi Vevek, It is really very nice and happy to watch your seriees of videos related to db stuff. Thank you for such a nice detailed videos with adequate explanation. Good Job.

[@samirmomin203]

Thanks a lot. It clears all small doubts. Appreciated.

[@TechCoach]

Thanks for the kind words samir, I am glad I could help

[@abhijitmohanty734]

Hello Sir, I have seen your videos for oracle partitioning and those are very informative and easy to understand the oracle data flow. please keep posting the videos on oracle performance tuning sections. A small request please upload the queries which you have been used during your explanation on video in the description. Thanks a lot.

[@parashar1505]

what do you mean when you say a random I/O to find the actual row in the block? For a table with say 4000 blocks, how does it know which block it belongs to? If it doesn't know, then does it just randomly scans a block wishing that it would be lucky to have that row in that block? If so, then it would be just impossible to determine the time of a read operation, as the data could be on the very first block itself (if you are extremely lucky) or could be in last block (if you are extremely unlucky) and that way you would end up reading all the blocks! Something is surely missing in this explanation!

[@varunagnihotri907]

What do you mean by random IO operation and being sequential ? We have the ID's address in the index we can straight away fetch that record. And if we have another ID's we again perform one more read on the table on the basis of it's address. So as per my understanding we are reading blocks one by one ( or individual access to a table each time more precisely) instead of a multi block read in case of full table scan. Kindly clarify

[@dhritibishnu]

Excellent... Wating for next...

[@TechCoach]

Hi Dhritiman , I have a complete series on partitioning why may interest you. kzbin.info/aero/PLUWdEQb_2yqX9rJ9CtahlZvo0AD3A63JA

[@srikanthgandhi7289]

Very clear explanation.. keep making videos. I really appreciate your effor.. it would be a great if you give Notes in PDF..

[@TechCoach]

Thanks Srikanth for the kind words, you made my day.I am working on new videos and I will upload them soon :) I have a small request, I am working on this new channel and will really appreciate if you watch and subscribe to it. kzbin.info/www/bejne/fZ-5n2ajhaeFsJY

[@dragonboard12]

Hi VIvek, Very good explanation. Keep up the good work.

[@TechCoach]

Thanks a lot for the kind words. I would really appreciate if you help me in my new Initiative "An Indian Abroad" by subscribing viewing and sharing. kzbin.info

[@dineshramdhn]

Wonderful explanation.

[@TechCoach]

Thanks Dinesh for the kind words, Please consider subscribing to my other channel as well it will really help me :) kzbin.info/www/bejne/bHiUqXqfYqlopdk

[@HimaliMandrekar]

Execellent video😊 Thnks vivek wonderful presentation

[@TechCoach]

Thanks himali for the kind words,I have a small request I am working on this new youtube channel, I Would love it if you watch and subscribe to it as well. kzbin.info/door/oLdaMxhfDgdgIAcRGlUvkg

[@pritammaiti8909]

Hello Vivek.. An excellent piece of demonstration.. This is the first time I am going through your videos on Partitioning in Oracle and at first ppt only I felt like giving you a full thumbs up for your effort. Excellent patience level and highly knowledgeable person demonstrating one of the vital most sections in Oracle architecture. I will definitely say about these lectures to all my friends.. Keep it up brother... :) Also, if you can share your mail id, so we can reach out to the expert for any clarification on Oracle and its features.. Waiting to hear from you more. Regards, Pritam

[@TechCoach]

Thanks a lot Pritam for the kind words, this is what keeps me going. You can always reach out to me on [e-mail] 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.If you enjoy the videos please share with your friends as well. kzbin.info/www/bejne/f4GVh6Fsn7Brfsk

[@pritammaiti8909]

@@TechCoach Done brother.. Anything for you :-) I have informed my friends about your great works too. More likes should be on the way.. Cheers

[@digvijaydamame7351]

superb bro !!! got to know some new things about DB blocks !

[@TechCoach]

digvijay damame I am happy it helped :)

[@siddhartharao8490]

Sir, Can you please answer these interview question which was asked to me. (1) How do we know that when we have to partition a table? (2) What will happen if we dont partition a table?

[@TechCoach]

Consider partition when data size is huge ( usually bigger than 2 GB ) and table will be used often in select queries . If you don't partition a big table you may face performance issues while querying your data

[@dbacorner]

Excellent explanation! Keep it up bro!

[@TechCoach]

Thanks for the kind words. I have a small request I am working on this new youtube channel "An Indian Abroad" it will really help me if you watch and subscribe to it. kzbin.info/www/bejne/fnO0eZihgpaIaMU

[@shrijeetshivdekar9076]

Blocks, extents and segments Blocks is must fundamental unit Corresponds to specific number of bus (8kb on physical disk) Any object being created, must fundamental unit of storage is block (cells - our body analogy) Extent =specific number of contagious days blocks allocated for storing done specific type of information They are predefined in number Segment . Set of extents, each allocated for specific days structure, stored in same tablespace

[@TechCoach]

Well summarized shrijeet :)

[@shrijeetshivdekar9076]

Branch blocks don't have any info, leaf blocks have all the info Blocks can have more than 1 row . Data is stored randomly. . Sequential storage is not there in blocks Single block vs multiple block io . Eg retrieving information with id 1 . Challenge : data stored randomly. . Thus data not sequential . Thus we do random io, which is slowest. . This total 4 sequential reads. . In large dw, having millions of rows, this is better What if we had to fetch 25%of data? If our data is already in same block, we have acceessed 3000 out of 4000 Full table scan: We read multiple blocks at a time. 4096 blocks, full table scan reads 128 blocks.. Along with additional overhead.

[@shrijeetshivdekar9076]

Medium level of data: partitioning would be great Different partitioning in different segments When to partition Size greater than 2gb Rolling data with only current updatable Clustered env with multiple nodes=hashed partitioning Partition on index- when we don't want to prevent rebuilding entire table.

[@pankajkharade6936]

I have a question: how the root node selected by oracle for index creation if root node selection based on the median value then in case of live system data is continuously inserted into the table so each time there will be a change in the median value which means a change in root node value.

[@amitagarwal841]

In case of the btree branch block how it find that which I'd we are looking for ... Like if we want to find id= 5555 ...and the value generated by rownum is not in branch block or child block

[@naveenp1843]

very well explained sir great tutorial indeed

[@TechCoach]

Thanks Naveen for the kind words, Your wordw made my day

[@singamramagangireddy721]

Hi Bro, U r explanation is too good.. Thanks for uploading the videos on Partition

[@TechCoach]

+Singam Ramagangireddy Thanks for the kind words :)

[@sadanb4876]

Bro pls need ur. Contact details

[@gyanbhargav1532]

Hi , I have question ,when next extent is going to allocate ,and how system decides no. of blocks in extent and in case of same type of data means data belongs to single table or single partition

[@ashwanimishra8215]

brilliant

[@TechCoach]

Thanks Ashwani :)

[@tapasgaan7371]

Hi Vivek, what is need to give another name called which is a collection of blocks...I understand that block is the finest level of data storage and segment is the one which belongs to a table space which is used to store table or index...so what does an extent signify ?

[@TechCoach]

Block(Basic unit), contagious data blocks form extent.then we have segment which is formed by set of extents.A segment is allocated gor a specific data structure like a table/index. To keep it simple in my videos I have focused on blocks and segments.I hope it clarifies.

[@sandipjena]

Hi Vivek, It's really a awesome video on partitioning. But can you please make a dedicated video on single Block I/O & Multiple Block I/O. Thanks in Advance.

[@TechCoach]

Thanks Sandip, Sure I will work on it, Thanks again for subscribing to my new channel.

[@siddeshbagali13]

WOW....!!

[@seanelucero]

AWESOME VIDEO. WOW

[@TechCoach]

Thanks Sean for the kind words, I am glad I could help.

[@mohanvilla0789]

Fantastic man.. :)

[@TechCoach]

Thanks for the kind words. I have a small request, I am working on this new youtube channel " An Indian Abroad " and will really appreciate if you watch and subscribe to it. kzbin.info/www/bejne/fnO0eZihgpaIaMU

[@Bibliophilic96]

Good Job

[@rahulgiri5645]

Please create deep performance tuning video for PLSQL

[@vivivivek420]

Hi Vivek, I have a table of size 20GB.I need to perform partition in that existing table,please give your valuable tips and suggestions.

[@TechCoach]

Sure buddy, But I may need a little more input about the kind of data you have and what kind of queries will be run in majority to decide the strategy.Please share the same and I will be able to help you out better. I have a small request I am working on this new youtube channel "An Indian Abroad" it will really help me if you watch and subscribe to it. kzbin.info/www/bejne/fnO0eZihgpaIaMU

[@vivivivek420]

sure bro...i'll share details.

[@vivivivek420]

bro plz share your mail id i'll give you the queries which we are using here.

[@TechCoach]

[e-mail]. Thanks for the kind words. I have a small request I am working on this new youtube channel "An Indian Abroad" it will really help me if you watch and subscribe to it. kzbin.info/www/bejne/fnO0eZihgpaIaMU

[@prateektripathi1158]

In the video at time 20:15,you say in order to fetch 25% data,we have already accessed 3000 blocks.I think you mean 3000 rows.Correct?

[@TechCoach]

No Prateek I mean 3000 blocks. Multiple Rows are stored in a block and to access 25% of these rows oracle has to visit these blocks frequently and sometimes the same block has to be accessed again and again. for example my block 6 contains 3 rows 90,89,78. In a full table scan oracle will read this block once while reading multiple other blocks in parallel. But when using index Oracle may have to read this block thrice in order to return these 3 rows which means reading 25% of the data using index may actually lead to reading 75-80% of the table and that too using Random I/O which is the costliest.(Because table is stored as a heap(Data is not sorted in any particular order, Exception is index organized table) reading 75-80% of the table using random I/O along with accessing the index can have real performance issues.Please let me know if this clarifies your doubt. I have a small request I am working on this new channel "An Indian abroad " and it will really help me if you watch and subscribe to it. kzbin.info/www/bejne/fnO0eZihgpaIaMU

[@prateektripathi1158]

Ok I got the point but reading and transferring the data to main memory from disk is done in units of blocks,so once a particular block has been found with a row,the whole block would be transferred to main memory.For the next search the main memory can than find the queried next row in its buffered cache and doesn't have to seek the disk.I think the block wouldn't be required to be read thrice if all the rows are in single block.The performance hit (in case of indexed search)would be in the case when the rows to be queried are in different blocks,so each time disk access a block containing the row,it would be having a seek penalty,which would multiply as the number of selected row grows.If the in this process all the blocks in buffered cache gets exhausted and we still have more rows to be retrieved,than the system would re read from blocks from disk

[@TechCoach]

Hi Prateek, Exactly You are right . Here we are talking about partitioning so the assumptions is that tables are huge and as aptly mentioned by you in the last line "Oracle can't sustain all the blocks in the memory while fetching large number of rows so there will be cases when you will have to revisit the disk to fetch the same block"

[@prateektripathi1158]

Tech Coach thanks

[@TechCoach]

Prateek it would really help me if you watch and subscribe to my new channel as well. Your support is my motivation. kzbin.info/www/bejne/fnO0eZihgpaIaMU

[@tapaswinimishra493]

Hello vivek, how can i go through all the videos you created. If you can help. Thanks in advanced

[@TechCoach]

Hi Tapaswini, here is a link from my channel that may interest you. Partitioning in Oracle: kzbin.info/aero/PLUWdEQb_2yqX9rJ9CtahlZvo0AD3A63JA

[@swathipriya1199]

good explanation

[@TechCoach]

Thanks a lot Swathi Priya for the kind words. I need a small help I am working on this new youtube channel and would really appreciate if you watch and subscribe to it. kzbin.info/www/bejne/Z2KnfGClrrekopI

[@sitaramsahoo811]

Hi, Your videos are quite informative and it's good to learn from your videos. But it will be better if you make it rather short. Professionals don't have patience to watch lengthy videos. Why I'm saying because, if one couldn't understand in a single shot, he will go and watch again and again. If you will make it long no one will be interested for second time.

[@TechCoach]

Sure Sitaram , I will try my best to reduce the timings. One thing I will recommend is watching the videos at a higher speed that will reduce the timings to an extent.

[@codetourist6996]

length is fine, as explanation is good, there are many videos with less time, but concept is not clear.

[@mohitbhandari1106]

Hi Vivek, at what basis the root node of the index is decided ?

[@TechCoach]

Mohit Bhandari Hi Mohit, Don't get confused with the word node, it"s a normal storage block on oracle disk.

[@mohitbhandari1106]

yes i understood its a normal storage block on oracle disk, but my question is when you create a index on a table say existing table of 10lakh records , so on which basis oracle will decide that which one will be the root block ? and also when we saw there are 4 branches so how it will decide , how many branches a b-tree should have?

[@TechCoach]

Mohit Bhandari Root block has no specific significance when it comes to storage, Any available location will be assigned to the index root block. Infact when the table is small.( only 1 block for the index) Root block act as the leaf block as well. As the index grows the B-Tree expands and we need Branch blocks to reavh the leaf blocks. Let me see a good link on the internet that can clear your doubt further

[@mohitbhandari1106]

ok Vivek thanks for the quick reply late night .. , well sorry to ask again i am not talking about the location of the root block, i am asking that if i have a table of 10lakh records and having a column called say issue_id all unique records from 1 to 1000000 and if i created index on the same. So here consider that the block size is of 4kb or 8kb , so on which basis oracle will decide that root block will consisting of what range (which value of column)?