HOT


CREATE TABLE T(X NUMBER PRIMARY KEY) ORGANIZATION HEAP;

I have read the SQL reference more than once -believe me- but I did not notice this syntax until today… an HOT table is a simple table and this clause is optional. An IOT, or INDEX ORGANIZED TABLE, is a table where you mostly select thru an index. Good candidates for IOT are tables where you mostly access rows thru a full index scan.

Compare :


SQL> CREATE TABLE LSC_SELECTED_EMPLOYEES (EMPNO NUMBER PRIMARY KEY)
2 ORGANIZATION HEAP;

Table created.

SQL> INSERT INTO LSC_SELECTED_EMPLOYEES(EMPNO) VALUES (7839);

1 row created.

SQL> INSERT INTO LSC_SELECTED_EMPLOYEES(EMPNO) VALUES (7788);

1 row created.

SQL> COMMIT;

Commit complete.

SQL> set autot on exp
SQL> select empno, ename from LSC_EMP
2 join LSC_SELECTED_EMPLOYEES using (EMPNO);

EMPNO ENAME
---------- ----------
7839 KING
7788 SCOTT

Execution Plan
----------------------------------------------------------
Plan hash value: 609992009

--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| T
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 2 | 46 | 3 (0)| 0
| 1 | NESTED LOOPS | | 2 | 46 | 3 (0)| 0
| 2 | TABLE ACCESS FULL| LSC_EMP | 14 | 140 | 3 (0)| 0
|* 3 | INDEX UNIQUE SCAN| SYS_C0010139 | 1 | 13 | 0 (0)| 0
--------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

3 - access("LSC_EMP"."EMPNO"="LSC_SELECTED_EMPLOYEES"."EMPNO")

Note
-----
- dynamic sampling used for this statement

SQL> set autot off
SQL> select segment_name,segment_type from user_segments
2 where segment_name in (
3 select object_name from user_objects t where t.CREATED >
4 sysdate-1/24 and segment_name not like 'BIN$%');

SEGMENT_NAME SEGMENT_TYPE
------------------------------ ------------------------------
LSC_SELECTED_EMPLOYEES TABLE
SYS_C0010139 INDEX

SQL> drop table LSC_SELECTED_EMPLOYEES;

Table dropped.

with

SQL> CREATE TABLE LSC_SELECTED_EMPLOYEES2 (EMPNO NUMBER PRIMARY KEY)
2 ORGANIZATION INDEX;

Table created.

SQL> INSERT INTO LSC_SELECTED_EMPLOYEES2(EMPNO) VALUES (7839);

1 row created.

SQL> INSERT INTO LSC_SELECTED_EMPLOYEES2(EMPNO) VALUES (7788);

1 row created.

SQL> COMMIT;

Commit complete.

SQL> set autot on exp
SQL> select empno, ename from LSC_EMP
2 join LSC_SELECTED_EMPLOYEES2 using (EMPNO);

EMPNO ENAME
---------- ----------
7839 KING
7788 SCOTT

Execution Plan
----------------------------------------------------------
Plan hash value: 3539129569

--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CP
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 2 | 46 | 3 (
| 1 | NESTED LOOPS | | 2 | 46 | 3 (
| 2 | TABLE ACCESS FULL| LSC_EMP | 14 | 140 | 3 (
|* 3 | INDEX UNIQUE SCAN| SYS_IOT_TOP_23608 | 1 | 13 | 0 (
--------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

3 - access("LSC_EMP"."EMPNO"="LSC_SELECTED_EMPLOYEES2"."EMPNO")

Note
-----
- dynamic sampling used for this statement

SQL> set autot off
SQL> select segment_name,segment_type from user_segments
2 where segment_name in (
3 select object_name from user_objects t where t.CREATED >
4 sysdate-1/24 and segment_name not like 'BIN$%');

SEGMENT_NAME SEGMENT_TYPE
------------------------------ ------------------------------
SYS_IOT_TOP_23608 INDEX

SQL> drop table LSC_SELECTED_EMPLOYEES2;

Table dropped.

The plan is the same, in IOT, you have only one segment, the INDEX, and in HOT, you have two segments, the INDEX and the TABLE.

Surely a good candidate for IOT 🙂

Published
Categorized as sql Tagged

By Laurent Schneider

Oracle Certified Master

2 comments

Leave a comment

Your email address will not be published.