Index indexes are generally divided into two types:
1) b-tree (balance-tree), typically used for OLTP (online transaction processing)
2) bitmap, generally used in OLAP (online analysis processing)
The following example is used to learn the structure of index:
1. First set up a test table, the index on the test table
Sql> CREATE TABLE Test_index as SELECT * from Dba_objects;
Table created.
Sql> CREATE index T_idx on Test_index (object_id);
Index created.
2. Indexed data_object_id
Sql> Select data_object_id from dba_objects where object_name= ' t_idx ';
data_object_id
--------------
52517
3. Treedump the index hierarchy
Sql> alter session SET events ' immediate trace name Treedump level
52517';
Session altered.
4. Find the Udump directory:
Sql> Show Parameter User_dump_dest
NAME TYPE VALUE
---------------- --------- -----------
User_dump_dest String/u01/app/oracle/admim/jiagulun/udump
5. Locate the name of the dump file:
Sql> select distinct SID from V$mystat;
Sid
----------
137
Sql> Select Paddr from v$session where sid=137;
Paddr
--------
2ce1f110
Sql> select spid from v$process where addr= ' 2ce1f110 ';
SPID
------------
31964
6. View the contents of the dump file:
[Root@redhat4 udump]# VI jiagulun_ora_31964.trc
[Root@redhat4 udump]# Cat Jiagulun_ora_
31964. TRC
/u01/app/oracle/admin/jiagulun/udump/jiagulun_ora_31964.trc
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0-production
With the partitioning, OLAP and Data Mining options
Oracle_home =/u01/app/oracle/product/10.2.0/db_1
System Name:linux
Node NAME:REDHAT4
Release:2.6.9-89.elsmp
Version: #1 SMP Mon Apr 10:34:33 EDT 2009
machine:i686
Instance Name:jiagulun
Redo thread mounted by this instance:1
Oracle Process number:26
Unix Process PID:
31964, IMAGE:ORACLE@REDHAT4 (TNS v1-v3)
Note: Leaf: 0x40ef5b 4255579 ( -1:nrow:485 rrow:485)
Leaf: Indicates that the data block is a leaf node (leaf blocks)
0X40EF5B: The 16-carry address of the corresponding index data block, encoding the file number and block numbering information;
4255579: The decimal address of the corresponding index data block, encoding the file number and block numbering information;
We can verify that 0x40ef5b is equal to 4255579:
Sql> Select To_number (' 40ef5b ', ' xxxxxx ') from dual;
To_number (' 40ef5b ', ' XXXXXX ')
----------------------------
4255579
-1: The number of the index data block, numbering starts with-1
NROW:485: Represents the total number of rows in the indexed data block, containing the rows that were deleted
rrow:485: Indicates that there is actually a valid number of rows in the index data block
We can use the stored procedures provided by Oracle to compute the meaning of address 4255579:
Sql> Select Dbms_utility.data_block_address_file (4255579) from dual;
Dbms_utility. Data_block_address_file (4255579)
---------------------------------------------
1
Sql> Select Dbms_utility.data_block_address_block (4255579) from dual;
Dbms_utility. Data_block_address_block (4255579)
----------------------------------------------
61275
This result indicates that the first leaf node of the index is located at file_id 1
61275Blocks.
Let's verify that the index T_IDX has one such block:
Sql> Select File_id,block_id,blocks from
dba_extentswhere segment_name= ' t_idx ';
Now that we know: the first leaf node of the index is located at file_id 1
61275block, I'll dump the block and study its contents:
Sql>
alter system dump DATAFILE 1 Block 61275;
System altered.
Then use VI to open just dump out of the file, which has the following content:
......
Leaf Block Dump
===============
Header Address 214086748=0xcc2b45c
Kdxcolev 0
Kdxcolev Flags =--
Kdxcolok 0
KDXCOOPC 0x80:opcode=0:iot flags=---is converted=y
Kdxconco 2
KDXCOSDC 0
Kdxconro 485
KDXCOFBO 1006=0x3ee
Kdxcofeo 1830=0x726
Kdxcoavs 824
KDXLESPL 0
Kdxlende 0
KDXLENXT 4255580=0x40ef5c
KDXLEPRV 0=0x0
KDXLEDSZ 0
Kdxlebksz 8032
ROW#0[8020] Flag:------, lock:0, len=12
Col 0; Len 2; (2): C1 03
Col 1; Len 6; (6): Ed da 2d
ROW#1[8008] Flag:------, lock:0, len=12
Col 0; Len 2; (2): C1 04
Col 1; Len 6; (6): Ed da 00 05
ROW#2[7996] Flag:------, lock:0, len=12
Col 0; Len 2; (2): C1 05
Col 1; Len 6; (6): Ed da 2e
......
Let's look at the first row of row#0[8020 in the index, and it's clear that the first line of the index should hold information about the first row of data in the table. The first two columns are:
Col 0; Len 2; (2):
C1 Col 1; Len 6; (6): Ed da 2d
What is the meaning of the index t_idx these two columns?
Because the index is based on the OBJECT_ID column of the table Test_index, let's take a bold guess: the first column represents the value of the column object_id of the table Test_index, and the second column represents the Test_index value of the first row of the table rowID. Now let's check our guesses:
Sql> Select Object_id,rowid from Test_index where object_id=1;
No rows selected
Sql> Select Object_id,rowid from Test_index where object_id=2;
OBJECT_ID ROWID
---------- ------------------
2 Aaam0kaabaaao3aaat
Let's take a look at object_id=2, how this 2 is expressed in Oracle:
Sql> Select Dump (2,16) from dual;
DUMP (2,16)
-----------------
typ=2 len=2:
c1,3
Obviously this result proves that the first column of the first row of index T_IDX represents the value of the object_id of the first row of the table Test_index!!!
Let's look at the relationship between the second list of Ed da 2d and rowID Aaam0kaabaaao3aaat, we first convert the 16 into binary:
Ed da 2d <<==16 System
00000000 01000000 11101101 11011010 00000000 00101101 <<== binary system
Value of rowID:
aaam0k AAB aaao3a AAt
。
160890 the
Oracle uses the 10 bits on the left to represent the relative file ID (why 10 bits). Because each tablespace can only have up to 1024 data files, and 2 of 10 times equals 1024):
00000000 01 =
1 and AAB =
1 Oracle uses the next 22 digits to represent the block number:
000000 11101101 11011010 = 0xedda = Select To_number (' Edda ', ' xxxx ') from dual =
60890 and aaao3a = 14*64*64 + 55*64 + = Select 14*64*64 + 55*64 + from dual =
60890 Oracle represents the line number with the last 8 for 00101101来:
00101101 = 0x2d =
the;
and AAt =
the
Here we get to the point: The second column of the index represents the information in the next three parts of rowID, the relative file ID, block ID, and line number.
To sum up：
1 The first column of the index indicates the value of the column in which the index was built;
2 The second column of the index represents information for the following three sections of the ROWID.
This allows us to see why indexes can be quickly indexed to data rows.
The index is physical, and the rowid is logically existent, physically nonexistent, and indexing is the logical rowID materialization.
The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion;
products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the
content of the page makes you feel confusing, please write us an email, we will handle the problem
within 5 days after receiving your email.
If you find any instances of plagiarism from the community, please send an email to:
info-contact@alibabacloud.com
and provide relevant evidence. A staff member will contact you within 5 working days.
