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

This article introduces the knowledge of "how to solve the problem of multiple visits to the same leaf block during index scanning". In the operation of actual cases, many people will encounter such a dilemma. Next, let the editor lead you to learn how to deal with these situations! I hope you can read it carefully and be able to achieve something!

First create the following test environment to reproduce the relevant phenomena. Create a test table with C1 column CHAR (256) so that the column takes up more bytes so that when you create an index on the column later, you can build a two-tier index with fewer rows.

SQL > create table test0429 (id number,c1 char, v1 varchar2); Table created.

The values inserted in C1 are 254 spaces for'01 spaces and 254 spaces for'02 spaces. A value like this.

SQL > insert into test0429 select rownum id,lpad (rownum,2,'0') C1 journal rownum v1 from dual connect by rownum commit; Commit complete.

Create an index on the C1 column:

SQL > create index ind_test0429_c1 on test0429 (C1); Index created.

Query the OBJECT_ID of the index to view its tree structure.

SQL > select object_id,object_name,object_type from user_objects where object_name='IND_TEST0429_C1' OBJECT_ID OBJECT_NAME OBJECT_TYPE-97504 IND_TEST0429_C1 INDEX SQL > alter session set events' immediate trace name treedump level 97504; Session altered. SQL > select * from v$diag_info INST_ID NAME-VALUE- - -- 1 Diag Enabled TRUE 1 ADR Base / oradata/app/oracle 1 ADR Home / oradata/app/oracle/diag/rdbms/orcl/orcl 1 Diag Trace / oradata/app/oracle/diag/rdbms/orcl/orcl/trace 1 Diag Alert / oradata/app/oracle/diag/rdbms/ Orcl/orcl/alert 1 Diag Incident / oradata/app/oracle/diag/rdbms/orcl/orcl/incident 1 Diag Cdump / oradata/app/oracle/diag/rdbms/orcl/orcl/cdump 1 Health Monitor / oradata/app/oracle/diag/rdbms/orcl/orcl/hm 1 Default Trace File / oradata/app/oracle/diag/rdbms/orcl/orcl/trace/orcl_ora_2751.trc 1 Active Problem Count 51 Active Incident Count 17 11 rows selected.

In the corresponding trace file, the index structure is 1 root node and 2 leaf nodes. As follows:

-begin tree dump branch: 0x180414b 25182539 (0: nrow: 2, level: 1) leaf: 0x180414c 25182540 (- 1: nrow: 26 rrow: 26) leaf: 0x180414d 25182541 (0: nrow: 24 rrow: 24)-end tree dump

Query the file block and block number of the data block of the root node and the leftmost leaf node, and prepare DUMP its data block so that you can see its contents.

SQL > select DBMS_UTILITY.DATA_BLOCK_ADDRESS_FILE (to_number ('& & p3precision valuebook no. Xxxxxxxxx')) FILE#, DBMS_UTILITY.DATA_BLOCK_ADDRESS_BLOCK (to_number ('& & p3precision valuebook journal)) BLOCK# from dual 2 3 Enter value for p3_value: 180414b old 1: select DBMS_UTILITY.DATA_BLOCK_ADDRESS_FILE (to_number ('& & p3 gift valuebook)) FILE#, new 1: select DBMS_UTILITY.DATA_BLOCK_ADDRESS_FILE (to_number ('180414b toy journal journal xxxxxxxx') FILE#, old 2: DBMS_UTILITY.DATA_BLOCK_ADDRESS_BLOCK (to_number ('& & p3precision value') 'xxxxxxxx')) BLOCK# new 2: DBMS_UTILITY.DATA_BLOCK_ADDRESS_BLOCK (to_number (' 180414b)) BLOCK# FILE# BLOCK#-6 16715 SQL > undefine p3_value SQL > select DBMS_UTILITY.DATA_BLOCK_ADDRESS_FILE (to_number ('& & p3 valuememory)) FILE# DBMS_UTILITY.DATA_BLOCK_ADDRESS_BLOCK (to_number ('& & p3 percent valuerecording and recording xxxxxxxxx') BLOCK# from dual 2 3 Enter value for p3_value: 180414c old 1: select DBMS_UTILITY.DATA_BLOCK_ADDRESS_FILE (to_number ('& & p3 gift valuebook)) FILE#, new 1: select DBMS_UTILITY.DATA_BLOCK_ADDRESS_FILE (to_number ('180414c toy journal journal xxxxxxxx')) FILE#, old 2: DBMS_UTILITY.DATA_BLOCK_ADDRESS_BLOCK (to_number ('& & p3precision value') 'xxxxxxxx')) BLOCK# new 2: DBMS_UTILITY.DATA_BLOCK_ADDRESS_BLOCK (to_number (' 180414c)) BLOCK# FILE# BLOCK#-6 16716

The contents of the DUMP root block and the leftmost leaf block are put into the trace file.

SQL > alter system dump datafile 6 block min 16715 block max 16716; System altered.

From the trace file, you can see that the main content in the root block is as follows (to save space, only the main content related to this topic is listed below, and other similar contents below are also dealt with and will not be repeated):

Kdxcolev 1 KDXCOLEV Flags =-kdxcolok 0 kdxcoopc 0x80: opcode=0: iot flags=--- is converted=Y kdxconco 2 kdxcosdc 0 kdxconro 1 kdxcofbo 30=0x1e kdxcofeo 8048=0x1f70 kdxcoavs 8018 kdxbrlmc 25182540=0x180414c kdxbrsno 0 kdxbrbksz 8056 kdxbr2urrc 3 row#0 [8048] dba: 25182541=0x180414d col 0; len 2; (2): 32 37 col 1; TERM-end of branch block dump-

As you can see from the penultimate line above, the minimum index key value in the rightmost leaf block is'27 spaces 254 spaces.

From the trace file, you can see that the main content in the leftmost leaf block is as follows:

Kdxcolev 0 KDXCOLEV Flags =-kdxcolok 0 kdxcoopc 0x80: opcode=0: iot flags=--- is converted=Y kdxconco 2 kdxcosdc 0 kdxconro 26 kdxcofbo 88=0x58 kdxcofeo 1090=0x442 kdxcoavs 1002 kdxlespl 0 kdxlende 0 kdxlenxt 25182541=0x180414d kdxleprv 0=0x0 kdxledsz 0 kdxlebksz 8032 row#0 [7765] flag: -, lock: 0, len=267 col 0; len 256; (256): 30 31 20 20 20. 20 20 20 Row#25 [1090] flag: -, lock: 0, len=267 col 0; len 256; 32 36 20 20 20. 20 20 20 col 1; len 6; (6): 01 80 41 47 00 19-end of leaf block dump-End dump data blocks tsn: 7 file#: 6 minblk 16715 maxblk 16716

To track how index blocks are accessed, turn on the 10200 trace event.

SQL > alter session set events' 10200 trace name context forever,level 1; Session altered.

The query for the data in the leftmost leaf block returns 1 row because it is an equivalent query and there are no duplicate values on the C1 column.

SQL > set lines 200 pages 60 SQL > select C1 from test0429 where C1 pages 01' C1 -01

Since the index we created on the C1 column is not a unique index, the access method to the index at this time is index range scanning. As shown in the following figure:

SQL > select * from table (dbms_xplan.display_cursor (',', 'typical')) PLAN_TABLE_OUTPUT- -SQL_ID 3kt1uqh383qbx Child number 0-select C1 from test0429 where C1 Plan hash value: 1267036809 -| Id | Operation | Name | Rows | Bytes | Cost (% CPU) | Time |- -| 0 | SELECT STATEMENT | 1 (100) | * 1 | INDEX RANGE SCAN | IND_TEST0429_C1 | 1 | 257 | 1 (0) | 00:00:01 |-- -Predicate Information (identified by operation id):-1-access ("C1" ='01') 18 rows selected.

Looking at the output in the 10200 trace file, we can see that the index root block is accessed first, and then the leftmost leaf block is accessed. This is in line with expectations. But we can see that the leftmost leaf block was visited twice.

Ktrgtc2 (): started for block objd: 0x00017ce0 ktrget2 (): started for block objd: 0x00017ce0 ktrget2 (): started for block objd: 0x00017ce0

The reason why I was interviewed twice, I think the process is as follows:

Hongmeng official Strategic Cooperation to build HarmonyOS Technology Community

Access to the index root block, that is, access to "block"

Since the conditional value of '01' is less than the value' 27'in the index entry pointing to the second leaf block in the root block, you need to access the leftmost leaf block in the index, that is, access

The first row of qualified record ROW0 is found in the leftmost leaf block. Pause to continue scanning and return the first line

Continue to look in the leftmost leaf block for records that meet the criteria. So, the leftmost leaf block will be visited again.

When you access ROW1, you get 254 spaces with a value of '02entries, which is greater than' 01spaces, so there are no more records that meet the conditions in the whole index, so end the scan and exit

If you can continue to find records that meet the condition value in the scanning of leaf blocks, instead of pausing the scan and returning the current result every time a row is found, but according to the value in ARRAYSIZE, every time the number of rows specified by this parameter is added, the scan will be paused and the result will be returned, and then the scan will continue. When the "scan again" action occurs, the corresponding leaf blocks are accessed again.

For what is described in 6, we do the following tests. Set ARRAYSIZE to 3, that is, pause the scan for every 3 rows and return the result. The default value for this parameter is 15.

SQL > show arraysize arraysize 15 SQL > set arraysize 3 SQL > show arraysize arraysize 3

If you execute the following query, you should return 2 rows.

SQL > select C1 from test0429 where c1 select C1 from test0429 where C1 select C1 from test0429 where C1 select C1 from test0429 where C1 C1- -26

We can see that all three index blocks have been accessed, and each has been visited once, and there is no repeated access.

Ktrgtc2 (): started for block objd: 0x00017ce0 ktrget2 (): started for block objd: 0x00017ce0 ktrget2 (): started for block objd: 0x00017ce0

I think the reason for this is that when it accesses the leftmost leaf block from the pointer in the root block, it finds a row of records that meet this condition. At this point, as mentioned earlier, the scan is paused and the result is returned. Then the scan continued, but since it was known in the first scan that the index entry was the last index entry in this index block, the pointer to the next leaf block on the leftmost leaf block was accessed directly along the pointer on the leftmost leaf block, that is, the "block". Obviously, since the ROW0 in this block is already 254 spaces in the block, which is greater than the conditional value of'26 spaces, the query ends.

If the result of our query exists in two adjacent leaf blocks, the access is as follows: in the following query, two rows of records are in the leftmost leaf block, and one row of records is in the leaf block on the right.

SQL > select C1 from test0429 where C1 > = '25' and C1 select C1 from test0429 where C1 > =' 25' and C1 drop index ind_test0429_c1; Index dropped. SQL > create unique index ind_unique_test0429_c1 on test0429 (C1); Index created.

View the new unique index OBJECT_ID to see its index tree structure.

SQL > select object_id,object_name,object_type from user_objects where object_name='IND_UNIQUE_TEST0429_C1' OBJECT_ID OBJECT_NAME OBJECT_TYPE-97521 IND_UNIQUE_TEST0429_C1 INDEX SQL > alter session set events' immediate trace name treedump level 97521; Session altered.

As shown below, we can see that this structure is the same as the previous tree structure.

Branch: 0x180414b 25182539 (0: nrow: 2, level: 1) leaf: 0x180414c 25182540 (- 1: nrow: 26 rrow: 26) leaf: 0x180414d 25182541 (0: nrow: 24 rrow: 24)-end tree dump

Once again, DUMP the contents of the root block and the leftmost leaf block, as follows:

Kdxcolev 1 KDXCOLEV Flags =-kdxcolok 0 kdxcoopc 0x80: opcode=0: iot flags=--- is converted=Y kdxconco 1 kdxcosdc 0 kdxconro 1 kdxcofbo 30=0x1e kdxcofeo 8049=0x1f71 kdxcoavs 8019 kdxbrlmc 25182540=0x180414c kdxbrsno 0 kdxbrbksz 8056 kdxbr2urrc 3 row#0 [8049] dba: 25182541=0x180414d col 0; len 2; (2): 32 37

We can see that in the root block, the first two digits showing the minimum value in the second leaf block are'27 spaces, while the contents in the leftmost leaf block are as follows, and we can see that the maximum value in this block is still'26 leaves' 254 spaces.

Kdxcolev 0 KDXCOLEV Flags =-kdxcolok 0 kdxcoopc 0x80: opcode=0: iot flags=--- is converted=Y kdxconco 1 kdxcosdc 0 kdxconro 26 kdxcofbo 88=0x58 kdxcofeo 1116=0x45c kdxcoavs 1028 kdxlespl 0 kdxlende 0 kdxlenxt 25182541=0x180414d kdxleprv 0=0x0 kdxledsz 6 kdxlebksz 8032 row#0 [7766] flag: -, lock: 0, len=266, data: (6): 01 8041 47 00 col 0; len 256; (256): 30 31 20 20 20. 20 20 20 Row#25 [1116] flag: -, lock: 0, len=266, data: (6): 01 80 41 47 00 19 col 0; len 256; (256): 32 36 20 20 20. 20 20 20-end of leaf block dump-End dump data blocks tsn: 7 file#: 6 minblk 16715 maxblk 16716

Execute the query that returns only 1 row again.

SQL > select C1 from test0429 where C1 C1- -01

However, the access method to the index has become the unique scan of the index, as shown in the following execution plan:

SQL > select * from table (dbms_xplan.display_cursor (',', 'typical')) PLAN_TABLE_OUTPUT- -SQL_ID 3kt1uqh383qbx Child number 0-select C1 from test0429 where C1 Plan hash value: 3124258820 -| Id | Operation | Name | Rows | Bytes | Cost (% CPU) | Time |- -| 0 | SELECT STATEMENT | | 1 (100) | * 1 | INDEX UNIQUE SCAN | IND_UNIQUE_TEST0429_C1 | 1 | 257 | 1 (0) | 00:00:01 |- -Predicate Information (identified by operation id):- -1-access ("C1" = '01') 18 rows selected.

The observed access to the index block is as follows:

Ktrgtc2 (): started for block objd: 0x00017cf1 ktrgtc2 (): started for block objd: 0x00017cf1

As shown in the figure above, we can see that there are no two visits to the leftmost leaf block. This is due to the nature of unique indexes. Since there are no duplicate values in the unique index, when a row of records is found, it is no longer necessary to determine whether there are other records that meet the criteria. Because in the unique index, either there is no corresponding condition value, or there will be only one entry. So, when you find a line, it's over.

If we query the maximum value in the leftmost leaf block, the result is as follows:

SQL > select C1 from test0429 where C1 subscription 26' C1- -26

As shown below, we can see that the two index blocks are still accessed. Also, the second leaf block is not visited.

Ktrgtc2 (): started for block objd: 0x00017cf1 ktrgtc2 (): started for block objd: 0x00017cf1

However, the situation changes when the following query is executed.

SQL > select C1 from test0429 where C1 select * from table (dbms_xplan.display_cursor (',', 'typical')) PLAN_TABLE_OUTPUT- -SQL_ID 9g9p54332fyd4 Child number 0-- select C1 from test0429 where C1

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.