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

Index Organization tables (index organized table, IOT) by default all tables are heap organized tables and the records in the table are not sorted. The heap organization table accesses (locates) records in the table through rowid. IOT uses the structure of b-tree index to store records. It is logically sorted by primary key, and unlike a normal primary key index, a primary key index simply stores the values of defined columns. IOT index stores all the columns in all IOT tables and sorts them by primary key. The index and table are merged into one and stored in the same database object. The access recorded in the table is not realized through the traditional rowid, but through the primary key. The primary key needs to exist in the creation of the IOTIOT, and the organization index clause is used in the statement that creates the IOT. Create a heap organization table, and the name of the given primary key constraint is easy to find and identify later. SQL > create table test_iot 2 (id number (3), name varchar2 (12), 3 constraints test_iot_id#_pk primary key (id)) 4 organization index;Table created. Look at the index in the IOT you just created. SQL > select index_name,index_type,table_name,table_type 2 from user_indexes 3 where table_name = 'TEST_IOT' The index in the INDEX_NAME INDEX_TYPE TABLE_NAME TABLE_TYPE--TEST_IOT_ID#_PK IOT-TOP TEST_IOT TABLE heap organization table has the same name as the primary key constraint. The following query further illustrates the column in which the index in the heap organization table resides. SQL > select index_name,table_name,column_name 2 from user_ind_columns 3 where table_name = 'TEST_IOT';INDEX_NAME TABLE_NAME COLUMN_NAME- TEST_IOT_ID#_PK TEST_IOT ID view the database objects generated because of the creation of the heap organization table. QL > select object_id,object_name,object_type 2 from user_objects 3 order by object_id desc; OBJECT_ID OBJECT_NAME OBJECT_TYPE- 69350 TEST_IOT_ID#_PK INDEX 69349 TEST_IOT TABLE view the segment assigned by the database to the heap organization table. If we give the primary key constraint a name, then the segment name of the heap organization table is the same as the primary key constraint, otherwise the system default segment name SYS_IOT_TOP_SQL > select segment_name,segment_type 2 from user_segments 3 where segment_name like'T% 'will be used. There is no table segment in the SEGMENT_NAME SEGMENT_TYPE--TEST_IOT_ID#_PK INDEX heap organization table. The advantage of IOT is that the primary key in the table is often needed in the query conditions of the SQL statement. In this case, using IOT can achieve better performance and faster access speed. In addition, the index and table are combined into one, using only one segment and no need to store rowid, only storing primary key values once can save storage overhead. About overflow area if some columns in the heap organization table are not accessed frequently, or if the record is long, you should consider using overflow area. Store this infrequent part of the data in overflow segment. You can use a smaller index segment to perform better when retrieving data in index segment. Note: primary key values is always stored in index segment, and no-key values can be stored in index segment or overflow segment. Row in index segment connects to row in overflow segment through rowid. Therefore, select and DML statements cannot directly access the data in overflow. The type of overflow segment is table not index. To create an IOT with overflow area, first get to know two important overflow clauses: the overflow pctthreshold clause: specify the percentage of space reserved in the index block. This percentage needs to be set reasonably so that there is enough space to store primary key values. Other columns, if the specified threshold is manipulated in index block, will not be stored in index block, but in overflow segment. The syntax format is: pctthreshold threshold threshold in (1.. 50), and the default value is the 50.overflow including clause: specify which columns should be stored in index block. The syntax format is: including column_name where the column_name can be the last primary key column or the no primary key column. Create a heap organization table with overflow area, where id,first_name,last_name is stored in index block and other columns are stored in overflow segment's block. SQL > create table test_iot_info 2 (id number (5), 3 first_name varchar2 (20), 4 last_name varchar2 (20), 5 major varchar2 (30), 6 current_credits number (3), 7 grade varchar2 (2), 8 constraints test_iot_info_id#_pk primary key (id)) 9 organization index 10 overflow including last_name;Table created. Check the index information of the newly created IOT SQL > select index_name,index_type,table_name 2 from user_indexes 3 where table_name = 'TEST_IOT_INFO' INDEX_NAME INDEX_TYPE TABLE_NAME--TEST_IOT_INFO_ID#_PK IOT-TOP TEST_IOT_INFOSQL > select index_name,table_name,column_name 2 from user_ind_columns 3 where table_name = 'TEST_IOT_INFO' INDEX_NAME TABLE_NAME COLUMN_NAME- TEST_IOT_INFO_ID#_PK TEST_IOT_INFO ID looks at the objects generated by creating IOT. Here there is an extra SYS_IOT_OVER_69353 because of the use of overflow. The naming format of overflow is SYS_IOT_OVER_.SQL > select object_id,object_name,object_type 2 from user_objects 3 order by object_id desc OBJECT_ID OBJECT_NAME OBJECT_TYPE--69355 TEST_IOT_INFO_ID#_PK INDEX 69354 SYS_IOT_OVER_69353 TABLE 69353 TEST_IOT_INFO TABLE view the segment generated by creating the IOT table. SQL > select segment_name,segment_type 2 from user_segments 3 order by segment_name;SEGMENT_NAME SEGMENT_TYPE--SYS_IOT_OVER_69353 TABLETEST_IOT_INFO_ID#_PK INDEX notice that the overflow segment name of the IOT table is the same as the object name.

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.