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

This article introduces you how to carry out the daily maintenance and application of Oracle IOT, the content is very detailed, interested friends can refer to, hope to be helpful to you.

IOT routine maintenance

Compared with the heap structure of the heap table, the most important feature of the index organization table is that the whole contents of the data row are saved in the index structure as leaf nodes. IOT only includes index segment (Index Segment) structure, and there is no corresponding data table segment (Table Segment) structure.

In the daily operation and maintenance work, we often need to reconstruct the index structure regularly rebuild operations to eliminate index invalid nodes (Dead Node). So, what problems should we pay attention to in our maintenance work in the IOT structure?

We still use the IOT data table T_IOT and the heap table T_HEAP in the previous article for comparison experiments.

SQL > select index_name from user_indexes where table_name='T_IOT'

INDEX_NAME

-

SYS_IOT_TOP_75124

The name of the primary key index for the data table T_IOT is SYS_IOT_TOP_75124. The index segment is roughly 2m in space.

SQL > desc t_iot

Name Type Nullable Default Comments

--

OBJECT_ID NUMBER (10)

OBJECT_NAME VARCHAR2 (100) Y

SQL > select count (*) from t_iot

COUNT (*)

-

72638

SQL > select segment_name, bytes/1024/1024 from user_segments where segment_name='SYS_IOT_TOP_75124'

SEGMENT_NAME BYTES/1024/1024

SYS_IOT_TOP_75124 2

We delete a batch of data to form dead leaf nodes.

SQL > delete t_iot where rownum commit

Commit complete

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

PL/SQL procedure successfully completed

SQL > select segment_name, bytes/1024/1024 from user_segments where segment_name='SYS_IOT_TOP_75124'

SEGMENT_NAME BYTES/1024/1024

SYS_IOT_TOP_75124 2

The data row is deleted and the index segment HWM is not shrunk. We can use the analyze index command to check the index health.

SQL > analyze index SYS_IOT_TOP_75124 validate structure

Index analyzed

QL > select height, blocks, name, lf_rows, DEL_LF_ROWS, pct_used from index_stats

HEIGHT BLOCKS NAME LF_ROWS DEL_LF_ROWS PCT_USED

-

2 256 SYS_IOT_TOP_75124 72638 39999 90

From the index_stats view, we can clearly see that nearly 40, 000 leaf nodes are in the Dead state, and the index tree height is 2. We do index rebuild, which is a common index operation.

SQL > alter index SYS_IOT_TOP_75124 rebuild

Alter index SYS_IOT_TOP_75124 rebuild

ORA-28650: the primary index in IOT cannot be rebuilt

SQL > alter table t_iot disable constraint SYS_IOT_TOP_75124

Alter table t_iot disable constraint SYS_IOT_TOP_75124

ORA-25188: cannot delete / disable / delay primary key constraints for index tables or sorted hash clusters

Common rebuild operations cannot be used in IOT primary key indexes, and disable indexes cannot be implemented. To sort out the IOT, you can choose the move method of the data table.

SQL > alter table t_iot move

Table altered

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

PL/SQL procedure successfully completed

SQL > select segment_name, bytes/1024/1024 from user_segments where segment_name='SYS_IOT_TOP_75124'

SEGMENT_NAME BYTES/1024/1024

SYS_IOT_TOP_75124 0.6875

After sorting out the data table t_iot move operation, the index high water mark dropped.

SQL > analyze index SYS_IOT_TOP_75124 validate structure

Index analyzed

SQL > select height, blocks, name, lf_rows, DEL_LF_ROWS, pct_used from index_stats

HEIGHT BLOCKS NAME LF_ROWS DEL_LF_ROWS PCT_USED

-

2 88 SYS_IOT_TOP_75124 32639 0 89

From the analysis results, we eliminate the dead leaf nodes. So, can we turn on row movement on the datasheet? This operation is a substitute for the move operation.

SQL > alter table t_iot enable row movement

Alter table t_iot enable row movement

ORA-14066: illegal option for unpartitioned index table

SQL > alter table t_heap enable row movement

Table altered

From the experimental results, row movement can not be applied to IOT.

5 、 IOT Index Overflow Segment

For IOT tables, we need to consider the problem of Overflow Segment. There is a controversial problem in the leaf node of B-tree index for a long time, that is, the splitting and merging of leaf blocks.

The process of index structure tree formation and maintenance is a process in which the index tree constantly splits leaf nodes and copies data. When a new leaf node value is added to the index tree, the index structure needs to assign it sequentially to a specific leaf "location". This is quite different from the random save strategy of the heap table heap table. If the data block in this location has been written "full", you need to split the data block (5Accord 5 algorithm or 9Unip 1 algorithm), find a new blank block, and write the overflow data leaf node information to the new block. This process is also accompanied by the adjustment of branch nodes.

Maintaining the balance of B-tree is a very complex process. General data tables usually lose an order of magnitude of DML operation efficiency in order to maintain the synchronous structure of the corresponding index.

For IOT, this B-tree balancing process represents a more complex consumption. Because all the data rows of the IOT table are saved in the leaf block, the copy and split operations during the maintenance of the tree are more intense. To alleviate this situation, Oracle introduced the concept of IOT Overflow Segment.

Generally speaking, we need to consider when we use IOT tables. We tend to choose a data table with relatively large primary key columns and relatively few columns as the IOT table. At the same time, reading more and writing less is also an important qualitative index of IOT.

The idea of Overflow Segment (overflow segment) is simple, by setting a threshold (PCTThreshold) to specify that the data rows are transferred to the storage location. If we set the PCTThreshold value to 10, then if the proportion of data row space exceeds 10% of the block size, non-primary key columns will be "overflowed" out of the IOT index for storage. This overflow space is called "Overflow Segment", or we can specify a separate table space for the overflow segment to save.

The tablespace where Overflow Segment exists, which we call Overflow Segment Tablespace. Let's create a brand new IOT and set the special PCTThresold value.

SQL > create table t_iotbig

2 (object_id number primary key

3 object_name varchar2

4 object_type varchar2

5 EDITION_NAME varchar2

6 last_ddl_time date)

7 organization index tablespace users

8 pctthreshold 5

9 overflow tablespace example

Table created

SQL > insert into t_iotbig select object_id, object_name, object_type, edition_name, last_ddl_time from dba_objects

72604 rows inserted

SQL > commit

Commit complete

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

PL/SQL procedure successfully completed

The table space in which the data table segment (essentially an index segment) resides is specified by organization index tablespace. The Pctthreshold parameter is used to specify the overflow segment threshold, which is set to 5% in our experiment. The overflow segment overflow segment is specified by overflow tablespace.

After loading about 70,000 pieces of data, we check the condition of the data segment.

SQL > col tablespace_name for A10

SQL > col iot_name for A10

SQL > select table_name, tablespace_name, num_rows, iot_type, iot_name from dba_tables where wner='SYS' and table_name='T_IOTBIG'

TABLE_NAME TABLESPACE NUM_ROWS IOT_TYPE IOT_NAME

T_IOTBIG 72604 IOT

SSQL > select index_name, index_type, PCT_THRESHOLD, tablespace_name from dba_indexes where table_name='T_IOTBIG' and wner='SYS'

INDEX_NAME INDEX_TYPE PCT_THRESHOLD TABLESPACE

-

SYS_IOT_TOP_75137 IOT-TOP 5 USERS

The index has the property pct_threshold=5. At the same time, we can see the overflow segment in dba_tables.

SQL > select table_name, tablespace_name, iot_name, iot_type from dba_tables where wner='SYS' and iot_name='T_IOTBIG'

TABLE_NAME TABLESPACE IOT_NAME IOT_TYPE

SYS_IOT_OVER_75137 EXAMPLE T_IOTBIG IOT_OVERFLOW

In the Datasheet view, we find that there is a special implicit datasheet named system automatic naming among the objects corresponding to the name of the IOT datasheet in IOT_NAME. This data table is different from IOT in that it clearly exists in the tablespace EXAMPLE, and IOT_TYPE also explicitly marks the IOT_OVERFLOW type.

Let's look at the IOT table T_IOTBIG from the point of view of segment space allocation.

SQL > select segment_name, segment_type, tablespace_name, extents, blocks from dba_segments where wner='SYS' and segment_name in ('SYS_IOT_OVER_75137','SYS_IOT_TOP_75137','T_IOTBIG')

SEGMENT_NAME SEGMENT_TYPE TABLESPACE EXTENTS BLOCKS

--

SYS_IOT_TOP_75137 INDEX USERS 20 640

SYS_IOT_OVER_75137 TABLE EXAMPLE 1 8

From dba_segments, you can clearly see the space usage of the IOT table: there is space allocation for index segments and space allocation for overflow segments. And the two can be in different table spaces.

The parameter pctthreshold is that you can specify this overflow segment threshold. If not specified, Oracle selects a default value of 50%. This is how our IOT table t_iot works.

SQL > select index_name, index_type, PCT_THRESHOLD, tablespace_name from dba_indexes where table_name='T_IOT' and wner='SYS'

INDEX_NAME INDEX_TYPE PCT_THRESHOLD TABLESPACE

-

SYS_IOT_TOP_75124 IOT-TOP 50 SYSTEM

SQL > select count (*) from dba_tables where wner='SYS' and iot_name='T_IOT'

COUNT (*)

-

0

On how to carry out the daily maintenance and application of Oracle IOT to share here, I hope that the above content can be of some help to you, can learn more knowledge. If you think the article is good, you can share it for more people to see.

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.