Principle Exploration from the balance tree to the oracle B-tree Index

From the principles of the Balance tree to the oracle B-tree index, let's look at it first. There are two types of oracle indexes that are widely used: 1) B-tree: OLTP (transaction-oriented) 2) bitmap: OLAP (analysis-oriented) Step into the topic, first build a test environment: [SQL] SQL> create table tt as select * from dba_objects; the table has been created. SQL> select count (*) from tt; COUNT (*) ---------- 50441 www.2cto.com SQL> insert into tt select * from tt; 50441 rows have been created. SQL>/100882 rows have been created. SQL>/201764 rows have been created. SQL>/403528 rows have been created. SQL>/807056 rows have been created. SQL> create index tt_index on tt (object_id) tablespace users; the index has been created. Dump the INDEX tt_index structure to dump: [SQL] SQL> select object_id from dba_objects where object_name = 'tt _ Index '; www.2cto.com OBJECT_ID ---------- 53042 SQL> alter session set events 'immediate trace name treedump level 100'; the session has been changed. SQL> show parameter user_dump_dest name type comment -------------------- VALUE comment user_dump_dest string G: \ ORACLE \ PRODUCT \ 10.2.0 \ ADMIN \ ORCL \ udump SQL> select distinct sid from v $ mystat; SID ---------- 147 SQL> select paddr from v $ session where sid = 147; PADDR -------- CA280DDC SQL> select spid from v $ process where addr = 'ca280ddc '; www. 2ct O.com SPID ------------------------ 5360 to udump, open the file with process number 5360, part of the content is as follows: [SQL] *** 01:21:34. 944 *** action name :() 01:21:34. 902 *** module name :( SQL * Plus) 01:21:34. 902 *** service name :( SYS $ USERS) 01:21:34. 902 *** session id: (147.92) 01:21:34. 902 ----- begin tree dump branch: 0x10001bc 16777660 (0: nrow: 7, level: 2) branch: 0x100595f 16800095 (-1: nrow: 578, level: 1) leaf: 0x10001bd 16777661 (-1: nrow: 513 rrow: 513) leaf: 0x10001be 16777662 (0: nrow: 513 rrow: 513) leaf: 0x10001bf 16777663 (1: nrow: 513 rrow: 513) leaf: 0x10001c0 16777664 (2: nrow: 513 rrow: 513) leaf: 0x10001c1 16777665 (3: nrow: 513 rrow: 513) leaf: 0x10001c2 16777666 (4: nrow: 513 rrow: 513) leaf: 0x10001c3 16777667 (5: nrow: 484 rrow: 484) leaf: 0x10001c4 16777668 (6: nrow: 478 rrow: 478) leaf: 0x10001c5 16777669 (7: nrow: 478 rrow: 478) leaf: 0x10001c6 16777670 (8: nrow: 478 rrow: 478) leaf: 0x10001c7 16777671 (9: nrow: 478 rrow: 478) leaf: 0x10001c8 16777672 (10: nrow: 478 rrow: 478) leaf: 0x10001ca 16777674 (11: nrow: 481 rrow: 481) leaf: 0x10001cb 16777675 (12: nrow: 478 rrow: 478) leaf: 0x10001cc 16777676 (13: nrow: 478 rrow: 478) leaf: 0x10001cd 1677 7677 (14: nrow: 478 rrow: 478) leaf: 0x10001ce 16777678 (15: nrow: 478 rrow: 478) leaf: 0x10001cf 16777679 (16: nrow: 478 rrow: 478) www.2cto.com proves that B in B-tree is a balance tree. Otherwise, only two Leafs under a branch are binary trees. Above: 0x10001bd (hexadecimal) and 16777661 (hexadecimal) are actually the same. [SQL] SQL> select to_number ('10001bd ', 'xxxxxxxx') from dual; TO_NUMBER ('10001bd', 'xxxxxxxx') ------------------------ 16777661 and 16777661 contains two parts: file number and data block number. Which of the following data files is the address www.2cto.com [SQL] SQL> select partition (16777661) from dual partition (16777661) limit 4 SQL> select dbms_utility.data_block_address_block (16777661) from dua DBMS_UTILITY.DATA_BLOCK_ADDRESS_BLOCK (16777661) ------------------------------------------------- 445 from top, you can get 445th pieces of file 4 to change tt_ind The content of ex is dumped to dump: [SQL] SQL> alter system dump datafile 4 block 445; the system has changed. Part of www.2cto.com is extracted as follows: [SQL] row #0 [8024] flag: ------, lock: 0, len = 12 col 0; len 2; (2 ): c1 03 col 1; len 6; (6): 01 00 01 ac 00 2d row #1 [8012] flag: ------, lock: 0, len = 12 col 0; len 2; (2): c1 03 col 1; len 6; (6): 01 00 05 36 00 40 row #2 [8000] flag: ------, lock: 0, len = 12 col 0; len 2; (2): c1 03 col 1; len 6; (6): 01 00 08 01 00 1b row #3 [7988] flag: ------, lock: 0, len = 12 col 0; len 2; (2 ): C1 03 col 1; len 6; (6): 01 00 0a a2 00 2d row #4 [7976] flag: ------, lock: 0, len = 12 col 0; len 2; (2): c1 03 col 1; len 6; (6): 01 00 0d 69 00 48 tt table minimum object_id is 2, corresponding to row #0 [8024], how is binary 2 stored in oracle? [SQL] SQL> select dump () from dual; DUMP () ------------------------------------ Typ = 2 Len = 2: c1, 3 www.2cto.com from which we can see that the storage of 2 is c1 03. that is, the value stored in the first column of row 0th is 2. this means that in the leaf node of the index, oracle stores the value of this column in the leaf node of the index. In the first row of the index, the hexadecimal number of Column 2nd: 01 00 01 what is the relationship between ac 00 2d and rowid? [SQL] SQL> select object_id, rowid from tt 2 where object_id = 2 3 order by object_id, rowid; OBJECT_ID ROWID ---------- -------------- 2 limit 2 limit 2 AAAM8xAAEAAAAgBAAb... in fact, in the first row of the index, the hexadecimal number of the 2nd Column: 01 00 01 ac 00 2d represents the last three parts of rowid, namely fno, bno, and rno. Www.2cto.com rowid: AAAM8x AAE AAAAGs AAt. Through the hexadecimal conversion, AAE AAAAGs AAt and 01 00 01 ac 00 2d are the same. Why is there only the last three parts? To put it bluntly, will the book's contents include the name of the book? The title is the object number and the directory is the index. At this point, we construct the internal structure of the index: object_id rowid (the last three parts) ...... the sketch is as follows:
Author: linwaterbin