Shulou(Shulou.com)06/02 Report--

Let's discuss the Rowid and Secondary Index problems caused by data storage for indexing.

6. Logical Rowid & Secondary Index

In the IOT environment, we cannot guarantee a fixed physical Rowid.

In the Heap Table table, a row of data is saved in a physical location (file no. After + block no.), in the normal save behavior, its rowid will not change even if the row migration occurs. New rowid assignments occur only when data table storage refactoring is performed, such as move and shrink space.

The fixed rowid of the heap table gives us the advantage that the associated data table indexing the rowid above the leaf node is always valid unless move or and shrink space operations occur (where the index is invalid).

However, there are some problems with IOT. The splitting operations of index leaf nodes are quite frequent, and it is difficult to ensure that a data row will not change much at a rowid. Of course, if we ensure that each access to the data table is through the primary key primary key, the changed rowid will not have any impact. The problem lies in indexes that are not primary keys, which are called "secondary indexes" in IOT Secondary Index.

For a general secondary index, if the rowid of the data row is retained on the leaf node, then the invalid rowid means that all the corresponding secondary indexes can easily become invalid.

Secondary Index is not supported in many database versions, including earlier versions of Oracle. Recently, the methods of Logical Rowid and Physical Guess were introduced into oracle to finally solve the Secondary Index problem.

SQL > select rowid, object_id from t_iot where rownum create index idx_t_iot_name on t_iot (object_name)

Index created

SQL > exec dbms_stats.gather_table_stats (user,'T_IOT',cascade = > true)

PL/SQL procedure successfully completed

From the point of view of the data dictionary, there is no additional difference in the index idx_t_iot_name, except that the value of clustering factor is slightly higher for the general index.

SQL > select index_Name, index_type, clustering_factor from dba_indexes where wner='SYS' and index_name='IDX_T_IOT_NAME'

INDEX_NAME INDEX_TYPE CLUSTERING_FACTOR

-

IDX_T_IOT_NAME NORMAL 55006

SQL > select count (*) from t_iot

COUNT (*)

-

72604

SQL > select sum (bytes) / 1024 plus 1024, count (*) from dba_extents where wner='SYS' and segment_name='IDX_T_IOT_NAME'

SUM (BYTES) / 1024 amp 1024 COUNT (*)

--

4 19

For a data table index of more than 70,000 records, it accounts for 19 partitions, with a total space of 4m. So, what if it's a normal heap table index? How's the space?

SQL > desc t_heap

Name Type Nullable Default Comments

--

OBJECT_ID NUMBER (10)

OBJECT_NAME VARCHAR2 (100) Y

SQL > create index idx_t_heap_name on t_heap (object_name)

Index created

SQL > select count (*) from t_heap

COUNT (*)

-

72605

SQL > select sum (bytes) / 1024 plus 1024, count (*) from dba_extents where wner='SYS' and segment_name='IDX_T_HEAP_NAME'

SUM (BYTES) / 1024 amp 1024 COUNT (*)

--

3 18

With the same value, the normal index has only 3m space, accounting for about 18 partitions. Description: Secondary Index has a lot of special information in comparison to some other indexes.

SQL > col object_name for A20

SQL > select object_id, object_name from dba_objects where object_name in ('IDX_T_HEAP_NAME','IDX_T_IOT_NAME')

OBJECT_ID OBJECT_NAME

--

75146 IDX_T_HEAP_NAME

75143 IDX_T_IOT_NAME

We try to dump the two index trees and explore their structural differences.

SQL > select value from v$diag_info where name='Default Trace File'

VALUE

/ u01/diag/rdbms/wilson/wilson/trace/wilson_ora_9101.trc

-- heap table index structure

SQL > alter system set events' immediate trace name treedump level 75146'

System altered

-- IOT table index structure

SQL > alter system set events' immediate trace name treedump level 75143'

System altered

First of all, let's analyze the index of the general heap table. Because of the space, only part of the content is intercepted.

* ACTION NAME: (Command Window-New) 2012-10-05 02PUBG 4339 561

-begin tree dump

Branch: 0x415c01 4283393 (0: nrow: 2, level: 2)

Branch: 0x415d3b 4283707 (- 1: nrow: 312, level: 1)

Leaf: 0x415c02 4283394 (- 1: nrow: 184rrow: 184)

Leaf: 0x415c03 4283395 (0: nrow: 184rrow: 184)

Leaf: 0x415c04 4283396 (1: nrow: 188 rrow: 188)

Leaf: 0x415c05 4283397 (2: nrow: 190 rrow: 190)

Leaf: 0x415c06 4283398 (3: nrow: 184rrow: 184)

Leaf: 0x415c07 4283399 (4: nrow: 186 rrow: 186)

Leaf: 0x415c08 4283400 (5: nrow: 185 rrow: 185)

From the Dump results, we can clearly see that IDX_T_HEAP_NAME is a two-tier index structure. The root node address is 0x415c01 (file=1, block=89089).

SQL > select to_number ('415c01') from dual

TO_NUMBER ("415C01century" XXXXXX')

-

4283393

One of the data blocks, 0x415c06, is tested and converted to a decimal address of 4283398 and a binary address of 10000010101110000000110. According to the rfile parsing rules, the final address is: file_no=1,block_no=89094.

SQL > alter system dump datafile 1 block 89094

System altered

The content of the leaf node in the Dump file is:

Row#0 [8000] flag: -, lock: 0, len=32

Col 0; len 22; (22):

2f 31 34 64 63 62 36 32 32 5f 53 79 6e 74 68 4c 61 62 65 6c 55 49

Col 1; len 6; (6): 00 41 55 91 00 4b

Row#1 [7968] flag: -, lock: 0, len=32

Col 0; len 22; (22):

2f 31 34 64 63 62 36 32 32 5f 53 79 6e 74 68 4c 61 62 65 6c 55 49

Col 1; len 6; (6): 00 41 55 91 00 4c

Row#2 [7928] flag: -, lock: 0, len=40

Col 0; len 30; (30):

2f 31 34 65 33 63 31 31 32 5f 50 4e 47 45 6e 63 6f 64 65 50 61 72 61 6d 50

61 6c 65 74 74

Col 1; len 6; (6): 00 41 5b 9a 00 9d

Guessing from the structure, col0 and col1 represent the value of the index column and the corresponding rowid information, respectively. What about IOT's secondary index?

* * 2012-10-05 02PUR 43MU 55.944

-begin tree dump

Branch: 0x4154b9 4281529 (0: nrow: 2, level: 2)

Branch: 0x415acd 4283085 (- 1: nrow: 330, level: 1)

Leaf: 0x4154ba 4281530 (- 1: nrow: 160rrow: 160)

Leaf: 0x4154bb 4281531 (0: nrow: 158rrow: 158)

Leaf: 0x4154bc 4281532 (1: nrow: 163rrow: 163)

Leaf: 0x4154bd 4281533 (2: nrow: 162rrow: 162)

Leaf: 0x4154be 4281534 (3: nrow: 163rrow: 163)

Leaf: 0x4154bf 4281535 (4: nrow: 160rrow: 160)

Leaf: 0x4154c0 4281536 (5: nrow: 159rrow: 159)

Leaf: 0x4154c1 4281537 (6: nrow: 161 rrow: 161)

Leaf: 0x4154c2 4281538 (7: nrow: 160rrow: 160)

Leaf node 0x4154bc, corresponding to the specific binary: 1000001010101010010111100. The position obtained by the analysis is: file_no=1,block_no=87228.

We will dump the block out.

SQL > alter system dump datafile 1 block 87228

System altered

Row#0 [7986] flag: Kmuri, lock: 0, len=46

Col 0; len 30; (30):

2f 31 32 30 66 34 37 30 38 5f 46 75 6c 6c 48 54 4d 4c 44 6f 63 75 6d 65 6e

74 61 74 69 6f

Col 1; len 4; (4): c3 04 39 24

Tl: 8 fb:-- HmurFLmuri-lb: 0x0 cc: 1

Col 0: [4] 00 41 59 3e

Row#1 [7940] flag: Kmuri, lock: 0, len=46

Col 0; len 30; (30):

2f 31 32 30 66 64 37 36 64 5f 4f 72 61 63 6c 65 44 61 74 61 62 61 73 65 4d

65 74 61 44 61

Col 1; len 4; (4): c3 02 50 1a

Tl: 8 fb:-- HmurFLmuri-lb: 0x0 cc: 1

Col 0: [4] 00 41 54 a1

Row#2 [7894] flag: Kmuri, lock: 0, len=46

Col 0; len 30; (30):

2f 31 32 30 66 64 37 36 64 5f 4f 72 61 63 6c 65 44 61 74 61 62 61 73 65 4d

65 74 61 44 61

Col 1; len 4; (4): c3 04 4c 1a

Tl: 8 fb:-- HmurFLmuri-lb: 0x0 cc: 1

Col 0: [4] 00 41 54 a2

The red part of the above is that we see the difference between Heap Index and IOT Secondary Index. Officially, due to the difference in this part of the information, the volume of IOT Secondary Index is slightly larger.

Conceptually, Secondary Index consists of three parts of leaf node content: the index key value, the logical rowid, and the primary key value of the corresponding data row. When searching, Oracle first uses logical rowid to make a preliminary probe to see if the corresponding data can be found in the corresponding location. This process is called physical guess.

If the corresponding data rows are found, then everyone will be happy. If not, oracle can only locate the IOT index tree through the primary key value of the data row. In this process, read one more segment structure over and over again.

The analysis of specific Secondary Index will be explained in more detail next time.

7. The use of IOT

We have introduced the features of IOT in detail in a series, and finally we will talk about the scenario of IOT application. Generally speaking, the author believes that IOT does not play a wide range of scenarios in the application of general systems. On the basis of no clear analysis and POC experiments, we should not make IOT decisions easily. Specifically, there are the following points:

In the ü IOT environment, there are more restrictions.

Although the heap table we often use has various problems, it is a kind of table storage structure with the widest adaptability and the most optimization strategies we can get at present. IOT is subject to many restrictions, for example: IOT must set a primary key, that is, define the core access method; if the PCTThreshold parameter is set, but does not specify overflew segment, then data rows that exceed the threshold will not be accepted and an exception will be thrown. Partitions and Lob types in IOT tables cannot be used at the same time. More work is needed to maintain IOT.

Single read, read more and write less operation type

When we define IOT, we actually specify the core access method of the data table. When we use primary key conditions, IOT can easily help us locate records. But the efficiency of applying secondary index to other query conditions is a problem. And secondary index is an Oracle feature that was supported not long ago. If our datasheet application is an operation table with multiple retrieval methods, then IOT is not an ideal choice.

The index operation itself has a performance impact on DML operations such as add, modify, and delete. In the IOT environment, this effect will only make it more intense. Therefore, if the data table is not a data table that is rarely modified, then using IOT is not the best choice.

Restriction on the number of primary key columns and columns

In the index leaf node, all the data rows and rows can be saved on the leaf node. The index leaf node is an object that changes and splits frequently. Therefore, if the number of data rows is large, or the data primary key columns are relatively small, then the effect of IOT is not good.

8. Conclusion

Heap,IOT and Cluster are the three basic storage types of data tables. In practice, the principle is to take the heap table as the core and use Heap Table by default. If you find performance problems during the architecture analysis, performance testing, and commissioning phases, consider using IOT or Cluster. However, when selecting the type, we must make clear the advantages and disadvantages and the scope of application of the two table structures.

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.