IOT Table
An IOT keeps its data stored according to the primary key column values for the
table.IOTs store the data as if the entire table was stored in an index. Anormal index only stores the indexed columns in the index; an IOT stores all of the table’s columns in the index
As the table’s data is stored as an index, the rows of the table do not have
physical RowIDs. Therefore, you cannot select the RowID pseudocolumn values from an IOT.IOTs has logical RowIDs.
Logical RowIDs allow the construction of secondary indexes and improve index performance allowing the IOT performance to match the performance of regular indexes. An IOT will require less space than a regular table since there are no RowIDs.
To limit the amount of internal management with the IOT you should only create an IOT if the data is very static. If the data in your table changes frequently you should use a regular table with indexes. In general, IOTs are most effective when the primary key constitutes a large part of the tables columns. If the IOT contains many frequently accessed columns that are not part of the primary key, then the IOT will need to repeatedly access it’s overflow area.
Below will be showed performance comparison and access mode to data , between IOT tables and heap tables.
IOT TABLE
SQL> CREATE TABLE my_iot (id INTEGER CONSTRAINT my_iot_pk PRIMARY KEY, value VARCHAR2(50)) SEGMENT CREATION IMMEDIATE ORGANIZATION INDEX;
Table created.
As we can see only index segments was created on database.
SQL> SELECT table_name, iot_type, iot_name FROM user_tables;
TABLE_NAME IOT_TYPE IOT_NAME
—————————— ———— ——————————
MY_IOT IOT
SQL> SELECT index_name, index_type, table_name FROM user_indexes;
INDEX_NAME INDEX_TYPE TABLE_NAME
—————————— ————————— ——————————
MY_IOT_PK IOT – TOP MY_IOT
SQL> SELECT object_id, object_name, object_type FROM user_objects ORDER BY 1;
OBJECT_ID OBJECT_NAME OBJECT_TYPE
———- ——————– ——————-
71135 MY_IOT TABLE
71136 MY_IOT_PK INDEX
SQL> select segment_name,segment_type from user_segments ORDER BY 1;
SEGMENT_NAME SEGMENT_TYPE
——————– ——————
MY_IOT_PK INDEX
Populated IOT table with 20Milions of records
SQL> select count(*) from my_iot;
COUNT(*)
———-
20000000
IOT performance on a filtered query using PK column
SQL> set autotrace traceonly
SQL> select * from my_iot where id=45723;
Elapsed: 00:00:00.07
Execution Plan
———————————————————-
Plan hash value: 4057551070
——————————————————————————-
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
——————————————————————————-
| 0 | SELECT STATEMENT | | 1 | 40 | 1 (0)| 00:00:01 |
|* 1 | INDEX UNIQUE SCAN| MY_IOT_PK | 1 | 40 | 1 (0)| 00:00:01 |
——————————————————————————-
Predicate Information (identified by operation id):
—————————————————
1 – access(“ID”=45723)
Statistics
———————————————————-
1 recursive calls
0 db block gets
3 consistent gets
2 physical reads
0 redo size
489 bytes sent via SQL*Net to client
419 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
· IOT performance on a filtered query with between clause
SQL> select * from my_iot where id between 48374 and 1896455;
1848082 rows selected.
Elapsed: 00:00:09.60
Execution Plan
———————————————————-
Plan hash value: 1125113513
——————————————————————————
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
——————————————————————————
| 0 | SELECT STATEMENT | | 672K| 25M| 311 (0)| 00:00:04 |
|* 1 | INDEX RANGE SCAN| MY_IOT_PK | 672K| 25M| 311 (0)| 00:00:04 |
——————————————————————————
Predicate Information (identified by operation id):
—————————————————
1 – access(“ID”>=48374 AND “ID”<=1896455)
Note
—–
– dynamic sampling used for this statement (level=2)
Statistics
———————————————————-
5 recursive calls
2 db block gets
129626 consistent gets
6070 physical reads
0 redo size
52853388 bytes sent via SQL*Net to client
1355674 bytes received via SQL*Net from client
123207 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1848082 rows processed
IOT performance on filtered query with order by clause
If an ORDER BY clause only references the primary key column or a prefix of it, then the optimizer avoids the sorting overhead, as the rows are returned sorted on the primary key columns.
SQL> select * from my_iot where id between 48374 and 1896874 order by id;
1848501 rows selected.
Elapsed: 00:00:09.80
Execution Plan
———————————————————-
Plan hash value: 1125113513
——————————————————————————
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
——————————————————————————
| 0 | SELECT STATEMENT | | 672K| 25M| 311 (0)| 00:00:04 |
|* 1 | INDEX RANGE SCAN| MY_IOT_PK | 672K| 25M| 311 (0)| 00:00:04 |
——————————————————————————
Predicate Information (identified by operation id):
—————————————————
1 – access(“ID”>=48374 AND “ID”<=1896874)
Note
—–
– dynamic sampling used for this statement (level=2)
Statistics
———————————————————-
5 recursive calls
2 db block gets
129656 consistent gets
1672 physical reads
0 redo size
52865823 bytes sent via SQL*Net to client
1355982 bytes received via SQL*Net from client
123235 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1848501 rows processed
The same access method as for non order clause query on my_iot_pk is used.
From trace file
SQL ID: 0cqyd8n935h3v Plan Hash: 1125113513
select *
from
my_iot where id between 48374 and 1896455 order by id
call count cpu elapsed disk query current rows
——- —— ——– ———- ———- ———- ———- ———-
Parse 1 0.01 0.06 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 123207 6.40 6.36 6006 128827 0 1848082
——- —— ——– ———- ———- ———- ———- ———-
total 123209 6.41 6.42 6006 128827 0 1848082
Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 51
Number of plan statistics captured: 1
Rows (1st) Rows (avg) Rows (max) Row Source Operation
———- ———- ———- —————————————————
1848082 1848082 1848082 INDEX RANGE SCAN MY_IOT_PK (cr=128827 pr=6006 pw=0 time=2685243 us cost=6325 size=36991280 card=1849564)(object id 68953)
The same behavior is implemented with no order by clause as expected, and in trace file no overhead about ordering factor is showed.
SQL ID: 9ncx9uf3n0tcu Plan Hash: 1125113513
select *
from
my_iot where id between 48374 and 1896455
call count cpu elapsed disk query current rows
——- —— ——– ———- ———- ———- ———- ———-
Parse 1 0.00 0.00 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 123207 5.38 5.37 0 128827 0 1848082
——- —— ——– ———- ———- ———- ———- ———-
total 123209 5.38 5.37 0 128827 0 1848082
Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 51
Number of plan statistics captured: 1
Rows (1st) Rows (avg) Rows (max) Row Source Operation
———- ———- ———- —————————————————
1848082 1848082 1848082 INDEX RANGE SCAN MY_IOT_PK (cr=128827 pr=0 pw=0 time=2299570 us cost=6325 size=36991280 card=1849564)(object id 68953)
· IOT performance on filtered query on non indexed columns
16:39:03 SQL> select value from my_iot where value=’values1907343′;
Elapsed: 00:00:01.73
Execution Plan
———————————————————-
Plan hash value: 3577746055
———————————————————————————-
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
———————————————————————————-
| 0 | SELECT STATEMENT | | 1 | 15 | 18751 (2)| 00:03:46 |
|* 1 | INDEX FAST FULL SCAN| MY_IOT_PK | 1 | 15 | 18751 (2)| 00:03:46 |
———————————————————————————-
Predicate Information (identified by operation id):
—————————————————
1 – filter(“VALUE”=’values1907343′)
Statistics
———————————————————-
1 recursive calls
2 db block gets
69617 consistent gets
69240 physical reads
0 redo size
430 bytes sent via SQL*Net to client
419 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
Using where condition on non pk columns , full index scan is performed.
· IOT performance on a full data access
16:28:28 SQL> select * from my_iot;
20000000 rows selected.
Elapsed: 00:01:44.42
Execution Plan
———————————————————-
Plan hash value: 3577746055
———————————————————————————
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
———————————————————————————-
| 0 | SELECT STATEMENT | | 17M| 665M| 18695 (1)| 00:03:45 |
| 1 | INDEX FAST FULL SCAN| MY_IOT_PK | 17M| 665M| 18695 (1)| 00:03:45 |
———————————————————————————-
Note
—–
– dynamic sampling used for this statement (level=2)
Statistic
———————————————————-
5 recursive calls
4 db block gets
1401507 consistent gets
68874 physical reads
0 redo size
601010631 bytes sent via SQL*Net to client
14667082 bytes received via SQL*Net from client
1333335 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
20000000 rows processed
Even in case of a select * full iot index scan is better than a heap table full scan.
HEAP TABLE
SQL>CREATE TABLE my_heap (id INTEGER CONSTRAINT my_heap_pk PRIMARY KEY, value VARCHAR2(50)) SEGMENT CREATION IMMEDIATE;
Table created.
SQL>select segment_name,segment_type from user_segments where segment_name like ‘%HEAP%’;
SEGMENT_NAME SEGMENT_TYPE
——————– ——————
MY_HEAP_PK INDEX
MY_HEAP TABLE
· HEAP performance on a filtered query using PK column
SQL> select * from my_heap where id=45723;
Elapsed: 00:00:00.25
Execution Plan
———————————————————-
Plan hash value: 35804330
——————————————————————————————
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
——————————————————————————————
| 0 | SELECT STATEMENT | | 1 | 20 | 3 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| MY_HEAP | 1 | 20 | 3 (0)| 00:00:01 |
|* 2 | INDEX UNIQUE SCAN | MY_HEAP_PK | 1 | | 2 (0)| 00:00:01 |
——————————————————————————————
Predicate Information (identified by operation id):
—————————————————
2 – access(“ID”=45723)
Statistics
———————————————————-
1 recursive calls
0 db block gets
5 consistent gets
3 physical reads
116 redo size
397 bytes sent via SQL*Net to client
408 bytes recived via SQL*Net from client
1 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
· HEAP performance on a filtered query with between clause
17:15:06 SQL> select * from my_heap where id between 48374 and 1896455;
1848082 rows selected.
Elapsed: 00:00:15.59
Execution Plan
———————————————————-
Plan hash value: 4034075989
—————————————————————————–
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
—————————————————————————–
| 0 | SELECT STATEMENT | | 1847K| 35M| 19520 (2)| 00:03:55 |
|* 1 | TABLE ACCESS FULL| MY_HEAP | 1847K| 35M| 19520 (2)| 00:03:55 |
—————————————————————————–
Predicate Information (identified by operation id):
—————————————————
1 – filter(“ID”<=1896455 AND “ID”>=48374)
Statistics
———————————————————-
1 recursive calls
0 db block gets
193077 consistent gets
70281 physical reads
0 redo size
52853388 bytes sent via SQL*Net to client
1355674 bytes received via SQL*Net from client
123207 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1848082 rows processed
With a heap table , no index is used in a between search condition.
· HEAP performance on a filtered query with order by clause
17:18:51 SQL> select * from luca.my_heap where id between 48374 and 1896455 order by id;
1848082 rows selected.
Elapsed: 00:00:11.87
Execution Plan
———————————————————-
Plan hash value: 1911394047
——————————————————————————————
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
——————————————————————————————
| 0 | SELECT STATEMENT | | 1847K| 35M| 21924 (1)| 00:04:24 |
| 1 | TABLE ACCESS BY INDEX ROWID| MY_HEAP | 1847K| 35M| 21924 (1)| 00:04:24 |
|* 2 | INDEX RANGE SCAN | MY_HEAP_PK | 1847K| | 3599 (1)| 00:00:44 |
——————————————————————————————
Predicate Information (identified by operation id):
—————————————————
2 – access(“ID”>=48374 AND “ID”<=1896455)
Statistics
———————————————————-
1 recursive calls
0 db block gets
266319 consistent gets
11972 physical reads
286540 redo size
60245660 bytes sent via SQL*Net to client
1355674 bytes received via SQL*Net from client
123207 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1848082 rows processed
SQL ID: anpvrqvhq834z Plan Hash: 1911394047
select *
from
luca.my_heap where id between 48374 and 1896455 order by id
call count cpu elapsed disk query current rows
——- —— ——– ———- ———- ———- ———- ———-
Parse 1 0.01 0.12 2 52 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 123207 9.66 66.13 19565 262967 0 1848082
——- —— ——– ———- ———- ———- ———- ———-
total 123209 9.67 66.26 19567 263019 0 1848082
Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 51
Number of plan statistics captured: 1
Rows (1st) Rows (avg) Rows (max) Row Source Operation
———- ———- ———- —————————————————
1848082 1848082 1848082 TABLE ACCESS BY INDEX ROWID MY_HEAP (cr=262967 pr=19565 pw=0 time=52053332 us cost=21924 size=36963180 card=1848159)
1848082 1848082 1848082 INDEX RANGE SCAN MY_HEAP_PK (cr=126070 pr=3593 pw=0 time=1898739 us cost=3599 size=0 card=1848159)(object id 68957)
· HEAP performance on filtered query on non indexed columns
17:17:00 SQL> select value from my_heap where value=’values1907343′;
Elapsed: 00:00:00.76
Execution Plan
———————————————————-
Plan hash value: 4034075989
—————————————————————————–
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
—————————————————————————–
| 0 | SELECT STATEMENT | | 1 | 15 | 19542 (3)| 00:03:55 |
|* 1 | TABLE ACCESS FULL| MY_HEAP | 1 | 15 | 19542 (3)| 00:03:55 |
—————————————————————————–
Predicate Information (identified by operation id):
—————————————————
1 – filter(“VALUE”=’values1907343′)
Statistics
———————————————————-
1 recursive calls
0 db block gets
70286 consistent gets
70281 physical reads
0 redo size
430 bytes sent via SQL*Net to client
419 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
Below the summary about segments related to IOT and heap table+ index.
It is possible to notice that IOT tables (640MB) is less space consuming compared to heap table + traditional index segments (560MB+320MB) .
SEGMENT_NAME BYTES/1024/1024
————————————————– —————
MY_HEAP 560
MY_HEAP_PK 320
MY_IOT_PK 616