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 🙂
