B-Tree Index

Prathap G
A free video tutorial from Prathap G
Technical Enterprise Architect
4.2 instructor rating • 3 courses • 10,924 students

Learn more from the full course

Oracle SQL : Mastering Oracle SQL Performance Tuning

Oracle SQL Query Tuning and Architecture | Solve critical Oracle SQL query performance issues | Advanced SQL Tuning

06:26:10 of on-demand video • Updated October 2019

  • Step by Step approach on troubleshooting badly performing SQL queries.
  • Identify how internal Oracle components affects the performance of the SQL query.
  • Identify potential performance bottlenecks by reading the Explain plan of the queries.
  • Tips and Tricks to make the Oracle SQL query run faster.
  • Compare BAD vs GOOD Queries with Examples.
  • Understand how STATISTICS and CARDINALITY affects the performance of the SQL Query.
  • Implications of JOINS and ACCESS Methods on the performance of the Query.
  • Oracle Performance Tuning on both Oracle11g and Oracle12c Environment.
  • Advanced SQL Query Tuning Steps.
English [Auto] Hey guys. Welcome back. In this lesson we will look at beetroot index which is also quite as balanced tree index. Bee trees are the most common type of cannabis index a bee tree index is an ardent list of values divided into ranges by associating a key with the row or range of Rose beetles provide an excellent retrieval performance for a wide range of queries including exact match and rain. So just look at this example as the name indicate that the bee trees to the data in the form of a tree where it has a root node which is also called the Spirit node and then child nodes also called branch nodes and then the lowest level within the tree. That doesn't have any children under it. Often colonies live or die each green color rectangle is a database block which does index related data the entire picture illustrates the structure of the Beazley index. The example shows an index on the department 80 column within which there are two hundred and fifty one distinct department ideas ranging from zero to 250. OK let me now explain the blocks in detail. A B tree index has two types of blocks branch blocks for searching and leaf blocks. That's stores actual index data. In this case the purple locks are branch blocks and lower blocks of palm leaf blocks the upper level branch blocks of a beautifully index contain data ranges that points to a lower level index blocks. So in this case there are six entries containing the eighty range of Department data. That is it has a range value starting from zero to forty forty one data and so on to department 80 to 50. Note that data here is just a range of the department items and it doesn't add to the actual data for the entire range each range. We then have some internal information containing the link to the next blocks in the tree that contain some more information on the range it is associated to. In this example the root branch block has an entry 0 2 40 which points to the left most block in the next branch level. This branch would again break the period plate 0 2 40 into multiple ranges like zero to 10 12 20 21 to 30 and even to 40 and to study the note. Just like buried in the data here is just a range of department ideas and it doesn't store the actual data for the entire range. This branch block contains entries such as Cedar Ben and eleven to 19 it points to a leaf block that contains key values that fall in the range. In the leaf level you see the actual data. We see all the index data for department 80 0 2 10 and at the same time its tools to row 80 information which is actually an accurate to say a table to retrieve the full information or recording a table each row in the leaf level corresponds to one drawing a table as you could see here that there are two ideas with department ideas equal to zero. What this means is that it is pointing to three rows in the actual table which department either you value as zero the same logic applies for all the ranges namely forty one to eighty eight. You do want to a B and so on. Below reaches the last strange name. You do have to do 250 has shown here the top most block in the Crisco route though as it contains the high level grain shape now for all the entries of the index as we can see here it is only the leaf level block that contains the actual index data that are growing. So generally the number of entries in the leaf limit should equate to a number of red card the table on which they index is created a weekly index is balanced because all leaf blocks automatically stay at the same depth from the top thus retrieval of any records from any within the index speaks approximately the same number of levels. In this example if you had to read data with the Department data is equal to fight that left most part selected. So in total three levels are red and at the same time in order to read department I'd use to do forty nine. That right. The worst part was selected. So even in this case three levels are selected and hence be three indexes card balanced the height of the index is that number of blocks are numbers required to go from block the leaf block to branch level is the height of the index minus one. In this slide the index has a height of three and a branch level of two branch blocks towards the minimum keep the fixed needed to make a branching decision which would do this. This technique enables the database to fit as much data as possible. Each batch block the branch blocks have been appointed to the changed law obtaining the key a number of keys and pointers is limited by the properties the lead blocks contains and the index gaining value and a corresponding row i.e. used to locate the actual. As you can see here that it is only the leaf block that actually contains the data helping ex and also stores the row any data that points to the location of the ball in a table. Let's now take up an example and see how the b tree index works. What would happen if you provide the following query that is select start from employee with department 80 between 5 and what. In this case we are selecting all the records from the employee whose department a b is between five and twelve. As we know that department I.T. has an index and a data in the index would be started as mentioned in the slide. It first checks the root node and ticks for the value file. So in this case it would find the value that it is searching in the range 0 to 40. It then traverses to the branch nodes did it find the leaf. So in this case it goes to the next level and searches for the range. This time it finds that the value 5 would be the rate 0. It would then check the live blog that has the first possible entry that matches the predicate values. It starts reading data from zero since it doesn't match the index criteria. That record is rejected as soon as it finds the index with ideas is equal to 5 it matches the index who then selects index value 5 and the royalty but since in the Senate statement reasserting all the records from the table employee then a royalty from the mix will be used to find the red card in the table and then it will select all the other columns this process would continue before it reaches the last record in the blog namely 10. So what would happen after really the last record in the first block the oracle knows that the next block in the index would have the value either from an outlier because that data in the index is always sorted. So instead of again crowdsourcing from root nor to branch toward it directly jumps to the next leave and performs to seem operation since the live blog data is sorted it continues to traverse to the remaining leaf blogs till it reaches the last block that matches the predicate values. In this case it will lead data to one and after that Oracle stops reading the data and returns the reserve to the user. In this case the number of index blocks are read by the optimizer is for a friend to branch blocks and two leaf blocks. Let us now look at a few examples. Let me first log into asset schema and copy all the queries related to this particular listen. Let me first check the index properties on table as too much. We have an index by the name customer interest could be key on the table. It would check the properties of the index from the user index table. Receipt at the branch level is just one and the height of the index is two and in total each towards fifty five thousand five hundred records. But this table does not show other details of the index. In order to see them we have to analyze the structure of the index first and below is the command to do that. This statement will validate the structure of the index and towards the result in the index and this Stats table that we now execute the query. Let me know extract the data from index and those good stats. Let me run this query here. I see that there is only one branch block and there are in total more than a hundred and ten leaf blocks. So this table provides more information regarding the true structure of the index. It will not log into secret place and execute few queries on a table. Customers let me first enable author Chris I will first select the record with custody value as food 9 6 7 1. He will then check how many blocks did that optimizer read to instead eat up since the depth of the index is just to optimize a should read only two blocks received from the consistent guest that he does right. Only two blocks to get that information. Let me know. Execute the same query with different custody value that is 3 2 2 8 again. The optimism reads only two blocks. Which makes perfect sense. Let me know execute the same query but only differences instead of using count. And where do you start that is. I want all the column data for this but variety. What do you think would be the consistent get read column think A. Let me know run the query. This time it is reading three blocks. There is two blocks from the index but it has to read the data from the table to fetch all the other columns that did not reside in index blocks and optimize it uses royally stored in the index to read the corresponding record from the people. And that is the reason why we have one extrapolated block of guys. I would urge you to understand between X really well as there are some other topics that you will learn in the next few chapters which requires you to understand the internal workings of between X. I would recommend to go through this lesson multiple times in order to understand the concept of be treated X with that background. Let me write up this lesson and see you in the next lesson.