Shulou(Shulou.com)05/31 Report--

This article mainly explains "what is the operation flow of Oracle index range scanning". Interested friends may wish to have a look at it. The method introduced in this paper is simple, fast and practical. Next, let the editor take you to learn "what is the operation flow of Oracle index range scanning"?

Index range scanning is to read in the order of roots, branches and leaves, and then go back to the table to read the data according to the ROWID of the data that meets the conditions. If the data column to be queried is included in the index, then the step of returning to the table is avoided. The address of the leaf block is in the branch block and the address of the branch block is in the root block. If you find the branch, you can find the leaf block, and if you find the root block, you can find the branch block. So, how to find the root block?

In fact, it is very simple, in Oracle, the root block is always at the next block in the head of the index segment. Therefore, the index scan does not have to read the index segment header. First find the segment header location in the data dictionary table, and the block number plus 1 is the root block location.

Let's test it next.

-create a test table

SQL > create table T11 as select * from dba_objects;Table created.

-create an index

SQL > create index ind_t11 on T11 (object_id); Index created.

-collect statistics

SQL > exec dbms_stats.gather_table_stats (ownname= > 'SCOTT',tabname= >' T11 cascade1) > 100 true,method_opt= > true,method_opt= > 'for all columns size auto',no_invalidate= > false); PL/SQL procedure successfully completed.

-View index information

SQL > select table_name,index_name,blevel,index_type,leaf_blocks from dba_indexes where index_name='IND_T11' and table_name='T11' TABLE_NAME INDEX_NAME BLEVEL INDEX_TYPE LEAF_BLOCKS -T11 IND_T11 1 NORMAL 161

-execute a simple query to view the execution plan

SQL > select * from table (dbms_xplan.display_cursor (',', 'allstats last')) PLAN_TABLE_OUTPUT- -SQL_ID g7411gwcvppnd Child number 0--select * from T11 where object_id=11Plan hash value: 469757982 Murray- -| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers |- -- | 0 | SELECT STATEMENT | | 1 | 1 | 00 SELECT STATEMENT 00.01 | 4 | | 1 | TABLE ACCESS BY INDEX ROWID | T11 | 1 | 1 | 1 | 0 0001 | 4 | | * | 2 | INDEX RANGE SCAN | IND_T11 | 1 | 1 | 1 | 00Rank 00.01 | 3 | -Predicate Information (identified by operation id):-- 2-access ("OBJECT_ID" = 11) 19 rows selected.

From the execution plan, you can see that the scan in the index range consumes three logical reads, while the layer height of the index is 1, indicating that there are two layers.

The observed logic is read as 4. The four logical reads are: Root block once, leaf block twice, back to the table to read the data block once.

Leaf blocks are needed twice because the index is not unique. The first time to read the leaf block is to extract the target row ROWID, and the second time to read the leaf block to determine whether there are any rows in the leaf block that meet the conditions.

If a unique index is built, there is no need to determine whether the leaf block has rows that meet the conditions, the leaf block only needs to be read once, and a total of three logical reads are needed.

Drop index ind_t11;SQL > drop index ind_t11;Index dropped.create unique index ind_t11_1 on T11 (object_id); SQL > create unique index ind_t11_1 on T11 (object_id); Index created.select * from T11 where object_id=11;SQL > select * from table (dbms_xplan.display_cursor (',', 'allstats last')) PLAN_TABLE_OUTPUT- -SQL_ID g7411gwcvppnd Child number 0--select * from T11 where object_id=11Plan hash value: 645999193 Murray- -| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | Reads |- -| 0 | SELECT STATEMENT | | 1 | 1 | 000.00 00.01 | 3 | 4 | 1 | TABLE ACCESS BY INDEX ROWID | T11 | 1 | 1 | 0 00.01 | 3 | 4 | | * 2 | INDEX UNIQUE SCAN | IND_T11_1 | 1 | 1 | 1 | 00 | 0 00.01 | 2 | 4 |- -Predicate Information (identified by operation id):- -2-access ("OBJECT_ID" = 11) 19 rows selected. At this point, I believe that everyone on the "Oracle index range scanning operation process is what" have a deeper understanding, might as well to the actual operation of it! Here is the website, more related content can enter the relevant channels to inquire, follow us, continue to learn!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.