HOT – Laurent Schneider

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 🙂

2 thoughts on “HOT”