The Index organization table (IOT) can store not only the data, but also the indexes established for the table. The data in the Index organization table is arranged according to the order of the primary key, which increases the speed of the access. However, this is done at the expense of insert and update performance (reordering after each write and update).
The Index organization table is created in the following format:
Copy Code code as follows:
CREATE TABLE Indextable (
ID VARCHAR2 (10),
NAME varchar2 (20),
Constraint pk_id primary key (ID)
)
Organization Index;
Note two points:
When you create a IoT, you must set the primary key, otherwise the error occurs.
The Index organization table actually puts all the data into the index.
Index Organization Table Properties
1, overflow clause (row overflow)
Because all the data is indexed, the query performance of the Index organization table is reduced when the table's data volume is large. The set overflow segment now stores the primary key and overflow data separately to increase efficiency. The settings for the overflow segment are in two forms:
pctthreshold N:Create a percentage of a block of data that, when the row data occupies more than one size, puts the other column data of the row into the overflow segment
including column_name:Columns before the specified column are placed in the index block, and the columns that follow are placed in the overflow segment
use Pctthreshold when the amount of data in a field in a row is not determined.
Consider using including if all rows exceed the pctthreshold specified size.
Copy Code code as follows:
CREATE TABLE T88 (
ID VARCHAR2 (10),
NAME varchar2 (20),
Constraint t88_pk_id primary key (ID)
)
Organization Index
Pctthreshold 20
Overflow tablespace users
including name;
As the example above shows, name and subsequent columns must be placed in overflow columns, while others are based on the Pctthreshold rule.
2, compress clause (key compression)
As with normal indexes, the index organization table can also use the COMPRESS clause for key compression to eliminate duplicate values.
The specific operation is to add the Compress n clause after organization index
Used to compress indexed columns, extract common factors at block level, and avoid duplicate values.
Such as:
Copy Code code as follows:
CREATE TABLE IoT (
Owner VARCHAR2 (8),
Object_type VARCHAR2 (8),
object_name VARCHAR2 (8),
Constraint IOT_PK primary KEY (owner, Object_type,object_name))
Organization Index
nocompress;
Represents physical storage for each primary key combination. If you use Compress N, you are no longer physically storing the duplicate columns.
The meaning of n is: Specify the number of columns to compress. The default is infinity.
For example, for data (1,2,3), (1,2,4), (1,2,5), (1,3,4), (1,3,5)
If you use compress, you compress the recurring (1,2), (1,3)
If the Compress 1 o'clock is used, only the data (1) is compressed
maintenance of index Organization tables
(1), the Index organization table can be the same as the normal heap table for INSERT, UPDATE, DELETE, select operation.
(2), you can use alter TABLE ... Overflow statement to change the properties of an overflow segment.
Copy Code code as follows:
Alter table indextable Add overflow;
(3), to alter any overview properties, you must first define the overflow, if the table is not to add
Copy Code code as follows:
ALTER TABLE T88 pctthreshold including name; --Adjust the overflow parameters
ALTER TABLE T88 Initrans 2 overflow Initrans 4; --Modifying the initrans characteristics of data block and overflow segment
Application of Index Organization table
The Heap table is a general form, and getting the data in the table is based on the hit rate. There is no clear sequence of points, in the full table scan, not the first data to be inserted first acquisition. Data storage is also random, of course, according to the available free space to decide.
IOT is like a table that is all indexed, with all the fields in the table placed on the index. So it is agreed that the data is stored in strict accordance with the rules, in fact, before the data is inserted has been determined its location, so regardless of the sequence of insertion, it in that physical location and the insertion of the order of precedence is irrelevant. This makes it easier to access a lot of blocks when you're querying, but when you insert it, it's slower than a normal table. The Index organization table is mainly used for information retrieval, space and OLAP programs.
The application of the Index organization table:
1, Code Lookup table.
2, often through the main code access to the table.
3, to build their own index structure.
4, to strengthen the common positioning of data, to the data in a specific order physical storage.
5, often with between...and ... Queries the main code or the unique code.
Frequently updated tables are of course not IoT, because Oracle needs to maintain indexes continuously, and because of the high cost of field multiple indexing, do not use IoT if you are not frequently using primary key access tables.
IoT offers the following benefits:
• Increase buffer cache efficiency, because a given query requires fewer blocks in the cache.
• Reduce buffer cache access, which can improve scalability.
• The amount of work that gets the data is less, because getting the data is faster.
• Fewer physical I/O completed per query.
