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 :)

2 thoughts on “HOT”

Leave a Reply

Your email address will not be published.


*

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>