Dump Analysis of B-tree indexes

In oracle, the default index type is B-tree index. Bitmap indexes, reverse key indexes, hash indexes, and function-based indexes. This article mainly introduces the B-tree index through dump analysis. For the index scan type, the basic operations of the index are not described in detail. System Information: [oracle @ localhost ~] $ CatetcissueEnterpriseLinuxEnt

In oracle, the default index type is B-tree index. Bitmap indexes, reverse key indexes, hash indexes, and function-based indexes. This article mainly introduces the B-tree index through dump analysis. For the index scan type, the basic operations of the index are not described in detail. System Information: [oracle @ localhost ~] $ Cat/etc/issue Enterprise Linux Ent

In oracle, the default index type is B-tree index. Bitmap indexes, reverse key indexes, hash indexes, and function-based indexes.
This article mainly introduces the B-tree index through dump analysis. For the index scan type, the basic operations of the index are not described in detail.
System Information:
[Oracle @ localhost ~] $ Cat/etc/issue
Enterprise Linux Server release 5.5 (Carthage)
Kernel \ r on an \ m

Database Version:
SQL> select * from v $ version where rownum = 1;

BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0-Production

SQL> show user;
The USER is "HR"
SQL> desc tt;
Is the name empty? Type
-----------------------------------------------------------------------------
ID NUMBER
NAME VARCHAR2 (10)
SQL> select count (rownum) from tt;
COUNT (ROWNUM)
-------------
3670016
Create index index_t Based on ID

SQL> create index index_t on tt (id) tablespace users;

The index has been created.

SQL> select object_id from dba_objects where object_name =
2 'index _ T ';
In the database, segment has data segments, index segments, and undo segments. They are not the same as table names, but the index names are the same.

OBJECT_ID
----------
76332
Dump index:
SQL> alter session set events 'immediate trace name treedump level 100 ';
The session has been changed.

----- Begin tree dumpbranch: 0x10038ab 16791723 (0: nrow: 15, level: 2) branch: 0x100540b 16798731 (-1: nrow: 503, level: 1) leaf: 0x10038ac 16791724 (-1: nrow: 512 rrow: 512) leaf: 0x10038ad 16791725 (0: nrow: 512 rrow: 512) leaf: 0x10038ae 16791726 (1: nrow: 512 rrow: 512) leaf: 0x10038af 16791727 (2: nrow: 512 rrow: 512) leaf: 0x10038b0 16791728 (3: nrow: 512 rrow: 512) leaf: 0x10038b1 16791729 (4: nrow: 512 rrow: 512) leaf: 0x10038b2 16791730 (5: nrow: 512 rrow: 512) leaf: 0x10038b3 16791731 (6: nrow: 512 rrow: 512) leaf: 0x10038b4 16791732 (7: nrow: 512 rrow: 512) leaf: 0x10038b5 16791733 (8: nrow: 512 rrow: 512) leaf: 0x10038b6 16791734 (9: nrow: 512 rrow: 512) leaf: 0x10038b7 16791735 (10: nrow: 512 rrow: 512) leaf: 0x10038b9 16791737 (11: nrow: 512 rrow: 512) leaf: 0x10038ba 16791738 (12: nrow: 512 rrow: 512) leaf: 0x10038bb 16791739 (13: nrow: 512 rrow: 512) leaf: 0x10038bc 16791740 (14: nrow: 512 rrow: 512) leaf: 0x10038bd 16791741 (15: nrow: 512 rrow: 512) leaf: 0x10038be 16791742 (16: nrow: 512 rrow: 512) leaf: 0x10038bf 16791743 (17: nrow: 512 rrow: 512) leaf: 0x10038c0 16791744 (18: nrow: 512 rrow: 512) leaf: 0x10038c1 16791745 (19: nrow: 512 rrow: 512) leaf: 0x10038c2 16791746 (20: nrow: 512 rrow: 512) leaf: 0x10038c3 16791747 (21: nrow: 512 rrow: 512) leaf: 0x10038c4 16791748 (22: nrow: 512 rrow: 512) leaf: 0x10038c5 16791749 (23: nrow: 512 rrow: 512) leaf: 0x10038c6 16791750 (24: nrow: 512 rrow: 512) leaf: 0x10038c7 16791751 (25: nrow: 512 rrow: 512) leaf: 0x10038c9 16791753 (26: nrow: 512 rrow: 512) leaf: 0x10038ca 16791754 (27: nrow: 512 rrow: 512) leaf: 0x10038cb 16791755 (28: nrow: 512 rrow: 512) leaf: 0x10038cc 16791756 (29: nrow: 512 rrow: 512) leaf: 0x10038cd 16791757 (30: nrow: 512 rrow: 512) leaf: 0x10038ce 16791758 (31: nrow: 512 rrow: 512) leaf: 0x10038cf 16791759 (32: nrow: 512 rrow: 512) leaf: 0x10038d0 16791760 (33: nrow: 512 rrow: 512) leaf: 0x10038d1 16791761 (34: nrow: 512 rrow: 512) leaf: 0x10038d2 16791762 (35: nrow: 512 rrow: 512) leaf: 0x10038d3 16791763 (36: nrow: 512 rrow: 512) leaf: 0x10038d4 16791764 (37: nrow: 512 rrow: 512) leaf: 0x10038d5 16791765 (38: nrow: 512 rrow: 512) leaf: 0x10038d6 16791766 (39: nrow: 512 rrow: 512) leaf: 0x10038d7 16791767 (40: nrow: 512 rrow: 512) leaf: 0x10038d9 16791769 (41: nrow: 512 rrow: 512) leaf: 0x10038da 16791 ( http://www.68idc.cn )] 770 (42: nrow: 512 rrow: 512) leaf: 0x10038db 16791771 (43: nrow: 512 rrow: 512) leaf: 0x10038dc 16791772 (44: nrow: 512 rrow: 512) leaf: 0x10038dd 16791773 (45: nrow: 512 rrow: 512) leaf: 0x10038de 16791774 (46: nrow: 512 rrow: 512 )...................................... ........................................ ......... ----- end tree dump

This is a Balance Tree, because the efficiency of the Balance Tree is very high, and the height from the root node to all the leaf nodes is the same. Branch indicates the root node. The above part is selected, and the order has been sorted from left to right.
Leaf: 0x10038ac 16791724 (-1: nrow: 512 rrow: 512) We select this column:

Convert the hexadecimal format to the decimal number:

SQL> select to_number('10038ac','xxxxxxxxxxxxxxxx') from dual;TO_NUMBER('10038AC','XXXXXXXXXXXXXXXX')---------------------------------------16791724SQL> select to_char('16791724','xxxxxxxxxxxxxxxx') from dual;TO_CHAR('16791724','XXXXXXXXXXXXXX----------------------------------10038ac

We can use a package provided in oracle to obtain the file number where the index is located, block number:

SQL> select dbms_utility.data_block_address_file(16791724) from dual;DBMS_UTILITY.DATA_BLOCK_ADDRESS_FILE(16791724)----------------------------------------------4SQL> select dbms_utility.data_block_address_block(16791724) from dual;DBMS_UTILITY.DATA_BLOCK_ADDRESS_BLOCK(16791724)-----------------------------------------------14508

By checking the view dba_extends, the index is stored in data file 4, and block 14508 is within the range of the starting block.

In this case, dump data file 4, block 14508:

SQL> alter system dump datafile 4 block 14508;


The system has been changed.

row#0[8020] flag: ------, lock: 0, len=12col 0; len 2; (2): c1 02col 1; len 6; (6): 01 00 1b ab 00 01row#1[8008] flag: ------, lock: 0, len=12col 0; len 2; (2): c1 02col 1; len 6; (6): 01 00 1b ab 00 03row#2[7996] flag: ------, lock: 0, len=12col 0; len 2; (2): c1 02col 1; len 6; (6): 01 00 1b ab 00 08row#3[7984] flag: ------, lock: 0, len=12col 0; len 2; (2): c1 02col 1; len 6; (6): 01 00 1b ab 00 0arow#4[7972] flag: ------, lock: 0, len=12col 0; len 2; (2): c1 02col 1; len 6; (6): 01 00 1b ab 00 0frow#5[7960] flag: ------, lock: 0, len=12col 0; len 2; (2): c1 02col 1; len 6; (6): 01 00 1b ab 00 11row#6[7948] flag: ------, lock: 0, len=12col 0; len 2; (2): c1 02col 1; len 6; (6): 01 00 1b ab 00 16row#7[7936] flag: ------, lock: 0, len=12col 0; len 2; (2): c1 02col 1; len 6; (6): 01 00 1b ab 00 18row#8[7924] flag: ------, lock: 0, len=12col 0; len 2; (2): c1 02col 1; len 6; (6): 01 00 1b ab 00 1drow#9[7912] flag: ------, lock: 0, len=12col 0; len 2; (2): c1 02col 1; len 6; (6): 01 00 1b ab 00 1frow#10[7900] flag: ------, lock: 0, len=12col 0; len 2; (2): c1 02col 1; len 6; (6): 01 00 1b ab 00 24row#11[7888] flag: ------, lock: 0, len=12col 0; len 2; (2): c1 02col 1; len 6; (6): 01 00 1b ab 00 26row#12[7876] flag: ------, lock: 0, len=12col 0; len 2; (2): c1 02col 1; len 6; (6): 01 00 1b ab 00 2brow#13[7864] flag: ------, lock: 0, len=12col 0; len 2; (2): c1 02col 1; len 6; (6): 01 00 1b ab 00 2drow#14[7852] flag: ------, lock: 0, len=12col 0; len 2; (2): c1 02col 1; len 6; (6): 01 00 1b ab 00 32row#15[7840] flag: ------, lock: 0, len=12col 0; len 2; (2): c1 02col 1; len 6; (6): 01 00 1b ab 00 34row#16[7828] flag: ------, lock: 0, len=12col 0; len 2; (2): c1 02col 1; len 6; (6): 01 00 1b ab 00 39row#17[7816] flag: ------, lock: 0, len=12col 0; len 2; (2): c1 02col 1; len 6; (6): 01 00 1b ab 00 3brow#18[7804] flag: ------, lock: 0, len=12col 0; len 2; (2): c1 02col 1; len 6; (6): 01 00 1b ab 00 40row#19[7792] flag: ------, lock: 0, len=12col 0; len 2; (2): c1 02col 1; len 6; (6): 01 00 1b ab 00 42row#20[7780] flag: ------, lock: 0, len=12...............................................................

.......
Take this line as an example:
Row #0 [8020] flag: ------, lock: 0, len = 12
Col 0; len 2; (2): c1 02
Col 1; len 6; (6): 01 00 1b AB 00 01
Col 0 indicates the first column. The length is 2. What is c1 02?

SQL> select * from tt where rownum<4 order by id;ID NAME---------- --------------------1 wO2 wang6 hongSQL> select dump(1,16) from dual;DUMP(1,16)----------------------------------

Typ = 2 Len = 2: c1, 2 => here c1 is omitted

This is clear. The first column in the first row stores 1, and the orale Method for storing data is complicated.

Col 1 indicates the second column. The length is 6. 01 00 1b AB 00 01 indicates the index value. It is a hexadecimal number. We can convert it to a binary number:
00000001 00000000 00011011 10101011 00000000 00000001
Certificate -----------------------------------------------------------------------------------------------------------------------------------------------------------
00000001 00 => 1x2x2 = 4 the first 10 digits indicate the data file number.
000000 00011011 10101011 => 4096 + 2048 + 512 + 256 + 128 + 32 + 8 + 2 + 1 = 7083 the 22 digits here indicate the block number.
00000000 00000001 => 1 16 digits represent the row number
Sort and query rowid:

The rowid of the first column is used,
You can find the object number, file number, and block number through a package provided by oracle:
Example:

The above section analyzes the index storage segments and data segments.
We know that indexes do not necessarily increase the query efficiency. index creation may seriously affect the query efficiency. if the system does not use indexes, we cannot intervene (but DBAs can change the query efficiency manually ), is the result of oracle CBO selection.
Below we can do a small experiment:

SQL> select count(rowid) from t;COUNT(ROWID)------------4718644SQL> select count(rowid) from t where id=1;COUNT(ROWID)------------4718592SQL> select count(rowid) from t where id=2;COUNT(ROWID)------------26SQL> select count(rowid) from t where id=3;COUNT(ROWID)------------26SQL> set autotrace traceonly;SQL> select * from tt;

Execution Plan

----------------------------------------------------------Plan hash value: 264906180--------------------------------------------------------------------------| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |--------------------------------------------------------------------------| 0 | SELECT STATEMENT | | 3556K| 67M| 1742 (2)| 00:00:21 || 1 | TABLE ACCESS FULL| TT | 3556K| 67M| 1742 (2)| 00:00:21 |--------------------------------------------------------------------------Note------ dynamic sampling used for this statement (level=2)

In this case, the full table scan reads multiple data blocks, which is faster. If indexes are used at this time, the efficiency will be lower.
SQL> select * from tt where id = 5;


Execution Plan

----------------------------------------------------------Plan hash value: 3103123359---------------------------------------------------------------------------------------| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |---------------------------------------------------------------------------------------| 0 | SELECT STATEMENT | | 5 | 100 | 4 (0)| 00:00:01 || 1 | TABLE ACCESS BY INDEX ROWID| TT | 5 | 100 | 4 (0)| 00:00:01 ||* 2 | INDEX RANGE SCAN | INDEX_T | 5 | | 3 (0)| 00:00:01 |---------------------------------------------------------------------------------------Predicate Information (identified by operation id):---------------------------------------------------2 - access("ID"=5)Note------ dynamic sampling used for this statement (level=2)

In this case, the index is read.
SQL> select * from tt where id = 1;


Execution Plan
----------------------------------------------------------

Plan hash value: 264906180--------------------------------------------------------------------------| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |--------------------------------------------------------------------------| 0 | SELECT STATEMENT | | 2121K| 40M| 3644 (2)| 00:00:44 ||* 1 | TABLE ACCESS FULL| TT | 2121K| 40M| 3644 (2)| 00:00:44 |--------------------------------------------------------------------------Predicate Information (identified by operation id):---------------------------------------------------1 - filter("ID"=1)Note------ dynamic sampling used for this statement (level=2)

You can see that the whole table is scanned and read. The database is very clever!