This article mainly introduces some features and usage methods of Oracle Index Organization table (IOT).
An index organization table (IOT) is basically a table stored in an index. He is essentially similar to a b* tree group where data is physically stored with a key value, but he has the following differences:
You have a data structure, an index structure, and the b* Tree Group has an index and a data segment.
The stored data keys are sorted, unlike the b* Tree group, where the data key values are organized in the b* Tree group, but the stored key values themselves are not sorted.
U setting IoT size is a little easier than setting the size of the group. You do not need to estimate the maximum number of keys as a hash group, and you have greater flexibility in how to set the size of the data stored by the key.
U IoT is useful in two implementations, one as a relational table, the other in a many-to-many relationship, the other is important for the physical placement of data in a table, but it is not possible to predict the order in which data is inserted, or the data cannot be reached in the order in which the data set is placed.
I. Use IOT to replace relational tables to save space
An association table is typically composed of two columns or two keys, which are used to associate two tables together. In the Oracle data dictionary, Dba_tab_privs can be imagined as an associated object between Dba_users and Dba_objects. A single user may have one or more permissions on a given object: The given object may have one or more users on which it has permissions.
For relational tables, you should generally establish the following structure:
CREATE TABLE Association
(Primary_key_table1,
Primary_key_table2,
<possibly Some columns pertaining to the relationship>);
CREATE INDEX Association_idx1
On association (PRIMARY_KEY_TABLE1,PRIMARY_KEY_TABLE2);
CREATE INDEX ASSOCIATION_IDX2
On association (PRIMARY_KEY_TABLE2,PRIMARY_KEY_TABLE1);
In this way, there will be three structures, namely a table and two indexes. Such two indexes allow traversal of all related TABLE2 rows from TABLE1, or traverse all related TABLE1 rows from the TABLE2. In most of the reality, even never accessing the table itself, he is an honorary data structure that is seen as an essential hassle because they only waste space. In some relatively few cases, he contains extra data specifically for the relationship, but the amount is generally small.
You can use IoT to get the same effect:
CREATE TABLE Association
(Primary_key_table1,
Primary_key_table2,
<possibly Some columns pertaining to the RELATIONSHIP>
Primary KEY (Primary_key_table1,primary_table2))
Organization Index;
CREATE INDEX Association_idx
On association (PRIMARY_KEY_TABLE2);
We've eliminated the need for a table, and not only that, but if you need to retrieve information about the relationship between two rows in TABLE1 and TABLE2 the table ACCESS by rowID step is canceled, note that the second index on the Assoication table does not contain two columns. This is an incidental effect of using IoT, where the logical rowid for IoT is in the index structure, and the Primary_key_table1 value already exists. You can see this in the following small example:
Sql> CREATE TABLE Test
2 (a int,
3 b int,
4 primary KEY (A,B)
5)
6 Organization Index;
Table has been created.
Sql> CREATE index test_idx on test (b);
The index has been created.
Sql> Set Autotrace traceonly explain
Sql> Select A,b from Test where b=55;
Execution plan
--------------------------------------------------------------------------------------------------------------- -----
Plan Hash value:2882402178
--------------------------------------------------------------------------------------------------------------- ----
| Id | Operation | Name | rows| bytes| Cost (%CPU) | Time |
--------------------------------------------------------------------------------------------------------------- ---
| 0 | SELECT STATEMENT | | 1 | 26 | 1 (0) | 00:00:01 |
| * 1 | INDEX RANGE SCAN | Test_idx | 1 | 26 | 1 (0) | 00:00:01 |
--------------------------------------------------------------------------------------------------------------- -----
predicate information (identified by Operation ID):
--------------------------------------------------------------------------------------------------------------- ----
1-access ("B" =55)
Note
-----
-Dynamic sampling used for this statement
There is no table ACCESS by rowID step. Even if a column a,oracle is required in a query, you know that you can get the value of column A from the logical rowid in the index structure, so you don't need to fetch him in the table. This fact makes it possible to save a lot of disk space when creating a second index on the IoT.
Two. Use IoT to place randomly inserted data
In addition to being a space-saving tool, IoT has an advantage in the ability to put relevant information physically together for fast access by eliminating the need for some redundant tables. One of the drawbacks of a group is to control access to the data in order to optimize its physical placement. IoT do not need to meet this condition because they are structured to adjust themselves to fit the inserted data.
Consider an application that frequently retrieves a document list owned by a given user. In the real world, users do not insert all the documents he owned in a single session, and the size of the document is unpredictable and will change as users add deleted documents. Thus, in traditional heap tables, the lines representing this user's literature are scattered everywhere. If you execute the following query:
SELECT * from Document table where username=:bind_variable
Oracle will use a lasso to read many blocks from the entire table. This will not happen if we use IoT to physically cluster data together. A simple simulation and autotrace can be used to observe this. For example, create two tables: one using IoT and the other using heap based implementations:
Sql> CREATE TABLE IoT
2 (username varchar2 (30),
3 Document_name varchar2 (30),
4 Other_data char (100),
5 constraint IOT_PK primary KEY (Username,document_name)
6)
7 Organization Index
8/
Table has been created.
Sql> CREATE TABLE Heap
2 (username varchar2 (30),
3 Document_name varchar2 (30),
4 Other_data char (100),
5 constraint HEAP_PK primary KEY (Username,document_name)
6)
7/
Table has been created.
The table uses char (100), which causes the average width of the rows in the table to be approximately 130 bytes. The only difference in the two tables is in the organization index clause. This clause instructs Oracle to store table data in the index segment rather than in the table segment, so the data for the corresponding table is stored in the index structure.
The following uses sample data to populate the two tables, construct a loop, and add 100 documents to each user in the All_users table. This is done here using a mock reality, where the given user literature is not added at once, but is incrementally added as other users add a lot of literature.
Sql> begin
2 for I in 1. 100
3 loop
4 for x in (select username from all_users)
5 loop
6 INSERT INTO heap
7 (Username,document_name,other_data)
8 values
9 (X.username,x.username | | '_' || I, ' x ');
INSERT INTO IoT
One (Username,document_name,other_data)
Values
X.username,x.username | | '_' || I, ' x ');
End Loop;
End Loop;
commit;
The end;
18/
The PL/SQL process has completed successfully.
This will read all the data from the table, that is to say, for USER1, all rows corresponding to him will be read, and then all rows corresponding to USER2 should be read, so down. In addition, it will be read in two ways, one using the bulk COLLECT, and the other with a single-line fetch, to see how the array processing differs in performance and scalability, and how much the IoT differs from the heap table. The corresponding benchmark routines are as follows:
Sql> alter session set Sql_trace=true;
The session has changed.
Sql> Declare
2 type array is table of VARCHAR2 (100);
3 L_array1 Array;
4 L_array2 Array;
5 L_array3 Array;
6 begin
7 for I in 1. 10
8 loop
9 for X in (select username from all_users)
Ten loop
One for y in (SELECT * from Heap Single_row
where Username=x.username)
Loop
null;
End Loop;
Y in (SELECT * from IoT Single_row
where Username=x.username)
Loop
null;
End Loop;
SELECT * Bulk Collect
Into L_array1,l_array2,l_array3
From Heap Bulk_collect
where Username=x.username;
SELECT * Bulk Collect
Into L_array1,l_array2,l_array3
IoT Bulk_collect
where Username=x.username;
End Loop;
End Loop;
to end;
32/
The PL/SQL process has completed successfully.
Sql> alter session set Sql_trace=false;
The session has changed.
