Now, we will try to retrieve more records from the table and analyze the results.
SQL> SELECT * FROM IOT_TABLE WHERE IOTNUMBER > 2000;
98000 ROWS SELECTED.
EXECUTION PLAN
----------------------------------------------------------
PLAN HASH VALUE: 451903227
---------------------------------------------------------------------------------------
| ID | OPERATION | NAME | ROWS | BYTES | COST (%CPU)| TIME |
---------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 37 | 0 (0)| 00:00:01 |
|* 1 | INDEX RANGE SCAN| SYS_IOT_TOP_232322 | 1 | 37 | 0 (0)| 00:00:01 |
---------------------------------------------------------------------------------------
PREDICATE INFORMATION (IDENTIFIED BY OPERATION ID):
---------------------------------------------------
1 - ACCESS("IOTNUMBER">2000)
STATISTICS
----------------------------------------------------------
1 RECURSIVE CALLS
0 DB BLOCK GETS
6885 CONSISTENT GETS
0 PHYSICAL READS
0 REDO SIZE
3098440 BYTES SENT VIA SQL*NET TO CLIENT
72239 BYTES RECEIVED VIA SQL*NET FROM CLIENT
6535 SQL*NET ROUNDTRIPS TO/FROM CLIENT
0 SORTS (MEMORY)
0 SORTS (DISK)
98000 ROWS PROCESSED
SQL> SELECT * FROM NON_IOT_TABLE WHERE NON_IOTNUMBER > 2000;
98000 ROWS SELECTED.
EXECUTION PLAN
----------------------------------------------------------
PLAN HASH VALUE: 1269025442
---------------------------------------------------------------------------------------------
| ID | OPERATION | NAME | ROWS | BYTES | COST (%CPU)| TIME |
---------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 37 | 0 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| NON_IOT_TABLE | 1 | 37 | 0 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | SYS_C00104050 | 1 | | 0 (0)| 00:00:01 |
---------------------------------------------------------------------------------------------
PREDICATE INFORMATION (IDENTIFIED BY OPERATION ID):
---------------------------------------------------
2 - ACCESS("NON_IOTNUMBER">2000)
STATISTICS
----------------------------------------------------------
1 RECURSIVE CALLS
0 DB BLOCK GETS
13643 CONSISTENT GETS
0 PHYSICAL READS
0 REDO SIZE
3098452 BYTES SENT VIA SQL*NET TO CLIENT
72239 BYTES RECEIVED VIA SQL*NET FROM CLIENT
6535 SQL*NET ROUNDTRIPS TO/FROM CLIENT
0 SORTS (MEMORY)
0 SORTS (DISK)
98000 ROWS PROCESSED
As the number of records increased, the difference in accessing the data using IOT and regular tables are also increased. Wherever IOT is beneficial, regular tables are proved to be non-beneficial.
Effect of DMLs on IOT
I was actually very curious to check on this because everything seems to be positive for IOT and was wondering why IOT was not used in place of regular tables. In order to test the effect of DMLs on IOT, I took two cases viz. Bulk Insert and Serial Insert. By Serial Insert I mean inserting records one by one in a loop. I have used the same table, IOT_TABLE and issued the following insert statement.
SQL> INSERT INTO IOT_TABLE SELECT (IOTNUMBER+100000), IOTNAME, IOTDETAILS FROM IOT_TABLE;
100000 rows created.
Execution Plan
----------------------------------------------------------
Plan hash value: 3323075685
-------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------------
| 0 | INSERT STATEMENT | |100K| 2343K| 90 (2)| 00:00:02 |
| 1 | INDEX FAST FULL SCAN| SYS_IOT_TOP_232322 | 100K| 2343K| 90 (2)| 00:00:02 |
-------------------------------------------------------------------------------------------
Statistics
----------------------------------------------------------
1055 recursive calls
536919 db block gets
17450 consistent gets
445 physical reads
81962588 redo size
549 bytes sent via SQL*Net to client
612 bytes received via SQL*Net from client
4 SQL*Net roundtrips to/from client
9 sorts (memory)
0 sorts (disk)
100000 rows processed
What a pleasant surprise!! it takes 17,450 consistent gets and 445 physical reads to insert 100,000 records. But how much cost it would have for a regular table? Below is the next set of statement
SQL> INSERT INTO NON_IOT_TABLE SELECT (NON_IOTNUMBER+100000), NON_IOTNAME, NON_IOTDETAILS FROM NON_IOT_TABLE;
100000 rows created.
Execution Plan
----------------------------------------------------------
Plan hash value: 277682176
-----------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------------
| 0 | INSERT STATEMENT | | 100K| 2343K| 111 (1)| 00:00:02 |
| 1 | TABLE ACCESS FULL| NON_IOT_TABLE | 100K| 2343K| 111 (1)| 00:00:02 |
-----------------------------------------------------------------------------------
Statistics
----------------------------------------------------------
238 recursive calls
12235 db block gets
2963 consistent gets
42 physical reads
12218160 redo size
558 bytes sent via SQL*Net to client
632 bytes received via SQL*Net from client
4 SQL*Net roundtrips to/from client
2 sorts (memory)
0 sorts (disk)
100000 rows processed
That’s a huge difference. Almost 6x difference between the insert on regular table and IOT. Is this the same for update and delete? When I checked on update and delete, I found another set of interesting facts.
| Description | No of Records | Consistent Gets | Physical Reads |
| Bulk Update IOT | 90,978 | 27,634 | 110 |
| Bulk Update Regular | 90,978 | 2282 | 6 |
| Bulk Delete IOT | 90,978 | 977 | 7 |
| Bulk Delete Regular | 90,978 | 877 | 7 |
Instead of bulk insert, I tried single insert (record by record in a loop with a final commit outside the loop) in both IOT and regular tables and obtained the following results:
| Description | No of Records | CPU | Elapsed Time | Physical Reads | IOs |
| Insert – Regular Tables | 100,000 | 9.79 | 10.52 | 9 | 312,262 |
| Insert - IOT | 100,000 | 8.46 | 10.62 | 7 | 211,301 |
Benefits of IOT
The structure of an index-organized table provides the following benefits:
Ø Fast random access on the primary key because an index-only scan is sufficient.
Ø There is no separate table storage area, hence changes to the table data result only in updating the index structure.
Ø Fast range access on the primary key because the rows are clustered in primary key order.
Ø Lower storage requirements because duplication of primary keys is avoided.
Conclusion
From the above article, the following can be observed:
Ø IOT is not recommended to be used for bulk processing
Ø In case of OLTP transactions, IOT can be used
Ø Query retrieval is faster compared to the regular tables
Ø Using COMPRESS option, (even otherwise as well), storage can be saved phenomenally
Please do understand that these are not thumb rules to work on. Please do benchmark the usage of IOT before using the same.
| Further Reading | Please don’t forget to visit this site to read my next articles on Ø Joins and Access Methods Ø Oracle Indexes |
| References | Ø Expert Oracle Database Architecture 9i and 10g Programming Techniques and Solutions By Thomas Kyte |
1 comment:
Thе Uniqueness of Greеn TеaТodaу, gгeen teа extгact pure
gгeеn coffee еxtract in either chin оr gyаna mudга.
Hеrе is my sіte; purecoffeegreenbean.com
Post a Comment