Thursday, April 14, 2011

Index Organized Tables (IOT) - Part 3

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

---------------------------------------------------------------------------------------

---------------------------------------------------------------------------------------

| 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

---------------------------------------------------------------------------------------------

---------------------------------------------------------------------------------------------

| 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

-------------------------------------------------------------------------------------------

-------------------------------------------------------------------------------------------

| 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

-----------------------------------------------------------------------------------

-----------------------------------------------------------------------------------

| 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

Index Organized Tables (IOT) - Part 2

Now, comes an important decision on from which column or value of the column should be moved on to the overflow segment. This is optionally specified by INCLUDING clause. All the columns specified after the name in this clause will be moved to OVERFLOW segment. In this example, AGE_PHOBIA and AGE_IDEAS will be stored in a separate overflow segment.

Another interesting factor when we include OVERFLOW clause is as follows:

SQL> SELECT DBMS_METADATA.GET_DDL('TABLE','AGEING_DEFECTS') FROM DUAL;

DBMS_METADATA.GET_DDL('TABLE','AGEING_DEFECTS')

--------------------------------------------------------------------------------

CREATE TABLE "INXQA"."AGEING_DEFECTS"

("NORMAL_HUMAN_AGE" VARCHAR2(10),

"AGE_ISSUES" VARCHAR2(30),

"AGE_PHOBIA" VARCHAR2(30),

"AGE_IDEAS" VARCHAR2(10),

PRIMARY KEY ("NORMAL_HUMAN_AGE") ENABLE

) ORGANIZATION INDEX NOCOMPRESS PCTFREE 10 INITRANS 2 MAXTRANS 255 NOLOGGING

STORAGE (INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645

PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)

TABLESPACE "PORT_DATA"

PCTTHRESHOLD 20 INCLUDING "AGE_ISSUES" OVERFLOW

PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 LOGGING

STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645

PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT) TABLESPACE "PORT_DATA"

Please note that the above options include PCTUSED with 40% as its value. This is because OVERFLOW segment is a separate segment, which has all the features and options of regular segments and hence we have PCTUSED.

NOCOMPRESS and COMPRESS options

When we uncompress the data, the primary key (or key columns) are repeated in the block along with the non-key columns. When we compress the index, based on the number of columns we specify, the indexes will be compressed (i.e.) the key columns will not be repeated. Please note that for the compress option to work, you need to have composite primary key. Otherwise, you will get the following error:

SQL> ALTER TABLE IOT_TABLE MOVE COMPRESS;

ALTER TABLE IOT_TABLE MOVE COMPRESS

ERROR AT LINE 1:

ORA-25193: CANNOT USE COMPRESS OPTION FOR A SINGLE COLUMN KEY

In order to show the COMPRESS option, let us create another table as follows:

CREATE TABLE IOT_TABLES

(IOT1 VARCHAR2(10),

IOT2 VARCHAR2(10),

IOT3 VARCHAR2(10),

IOT4 VARCHAR2(10),

IOT5 VARCHAR2(10),

PRIMARY KEY(IOT1,IOT2,IOT3))

ORGANIZATION INDEX;

Assume that we have a primary key on three columns with the following distribution of data in NOCOMPRESS option:

9,4,1,5,6

9,4,2,5,7

9,4,3,5,8

9,4,0,5,9

9,4,9,6,0

9,5,4,1,2

9,5,5,1,3

9,5,6,1,4

9,5,7,1,5

9,5,8,1,6

If we compress as follows:

ALTER TABLE IOT_TABLES MOVE COMPRESS 2;

9,4 and 9,5 are stored only once along with the data.

The main advantage of using COMPRESS technique is to save usage space.

Comparing the performance between Regular tables and IOT

Now, we will compare the results obtained from regular tables and IOT. Let us create two tables viz NON_IOT_TABLE and IOT_TABLE as follows:

CREATE TABLE NON_IOT_TABLE

(NON_IOTNUMBER NUMBER,

NON_IOTNAME VARCHAR2(20),

NON_IOTDETAILS VARCHAR2(20),

PRIMARY KEY(NON_IOTNUMBER));

Now, let us create a small procedure to insert equal number of rows into these tables and see the results.

CREATE OR REPLACE PROCEDURE IOTINSERT AS

INTERNALCOUNTER NUMBER;

BEGIN

INTERNALCOUNTER := 1;

WHILE INTERNALCOUNTER <= 100000

LOOP

INSERT INTO IOT_TABLE VALUES (INTERNALCOUNTER, 'AA' || INTERNALCOUNTER, 'AABBCC' || INTERNALCOUNTER);

INSERT INTO NON_IOT_TABLE VALUES (INTERNALCOUNTER, 'AA' || INTERNALCOUNTER, 'AABBCC' || INTERNALCOUNTER);

INTERNALCOUNTER := INTERNALCOUNTER + 1;

END LOOP;

COMMIT;

END;

SQL> SELECT * FROM IOT_TABLE WHERE IOTNUMBER < 2000;

1999 ROWS SELECTED.

EXECUTION PLAN

----------------------------------------------------------

PLAN HASH VALUE: 451903227

---------------------------------------------------------------------------------------

---------------------------------------------------------------------------------------

| 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

143 CONSISTENT GETS

0 PHYSICAL READS

124 REDO SIZE

55870 BYTES SENT VIA SQL*NET TO CLIENT

1839 BYTES RECEIVED VIA SQL*NET FROM CLIENT

135 SQL*NET ROUNDTRIPS TO/FROM CLIENT

0 SORTS (MEMORY)

0 SORTS (DISK)

1999 ROWS PROCESSED

SQL> SELECT * FROM NON_IOT_TABLE WHERE NON_IOTNUMBER < 2000;

1999 ROWS SELECTED.

EXECUTION PLAN

----------------------------------------------------------

PLAN HASH VALUE: 1269025442

---------------------------------------------------------------------------------------------

---------------------------------------------------------------------------------------------

| 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

283 CONSISTENT GETS

0 PHYSICAL READS

124 REDO SIZE

55882 BYTES SENT VIA SQL*NET TO CLIENT

1839 BYTES RECEIVED VIA SQL*NET FROM CLIENT

135 SQL*NET ROUNDTRIPS TO/FROM CLIENT

0 SORTS (MEMORY)

0 SORTS (DISK)

1999 ROWS PROCESSED

If we see the execution plan for both the select statements, we observe that "Consistent Gets" are more in NON_IOT_TABLE. This is very much true because the index will be searched first and then the data will be retrieved from the respective table. However, in case if IOT_TABLE table, the index and data resides in the same place and hence the "Consistent Gets" remains less. The same reason is provided for the 3 steps execution plan v/s 2 step execution plan, provided above.