oracle 索引组织表IOT_bc

oracle 索引组织表IOT_执行计划_02



oracle 索引组织表IOT_执行计划_03



创建索引组织表


SQL> create table tb_index_org
  2  (
  3      id integer not null,
  4      user_name varchar2(20) not null,
  5      address varchar2(20),
  6      primary key(id, user_name)
  7  )
  8  organization index;

表已创建。

SQL>



插入数据:

BEGIN
  FOR I IN 1..5000 LOOP
    INSERT INTO tb_index_org (id, user_name) VALUES (I, I || 'abc');
  END LOOP;
  FOR I IN 5001..10000 LOOP
    INSERT INTO tb_index_org (id, user_name) VALUES (I, I || 'def');
  END LOOP;
  COMMIT;
END;



SQL:select * from tb_index_org where id = 1000;

执行计划:

SQL> select * from tb_index_org where id = 1000;


执行计划
----------------------------------------------------------
Plan hash value: 158518905

--------------------------------------------------------------------------------------
| Id  | Operation        | Name              | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT |                   |     1 |    37 |     2   (0)| 00:00:01 |
|*  1 |  INDEX RANGE SCAN| SYS_IOT_TOP_53362 |     1 |    37 |     2   (0)| 00:00:01 |
--------------------------------------------------------------------------------------

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

   1 - access("ID"=1000)

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


统计信息
----------------------------------------------------------
          0  recursive calls
          0  db block gets
          3  consistent gets
          0  physical reads
          0  redo size
        527  bytes sent via SQL*Net to client
        385  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed

SQL>



SQL:select * from tb_index_org where id = 1000 and user_name = '1000abc';

执行计划:

SQL> select * from tb_index_org where id = 1000 and user_name = '1000abc';


执行计划
----------------------------------------------------------
Plan hash value: 480838416

---------------------------------------------------------------------------------------
| Id  | Operation         | Name              | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |                   |     1 |    37 |     1   (0)| 00:00:01 |
|*  1 |  INDEX UNIQUE SCAN| SYS_IOT_TOP_53362 |     1 |    37 |     1   (0)| 00:00:01 |
---------------------------------------------------------------------------------------

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

   1 - access("ID"=1000 AND "USER_NAME"='1000abc')


统计信息
----------------------------------------------------------
          1  recursive calls
          0  db block gets
          2  consistent gets
          0  physical reads
          0  redo size
        527  bytes sent via SQL*Net to client
        385  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed

SQL>



SQL:select * from tb_index_org where user_name = '1000abc';

执行计划:

SQL> select * from tb_index_org where user_name = '1000abc';


执行计划
----------------------------------------------------------
Plan hash value: 2012919451

------------------------------------------------------------------------------------------
| Id  | Operation            | Name              | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT     |                   |     1 |    37 |     8   (0)| 00:00:01 |
|*  1 |  INDEX FAST FULL SCAN| SYS_IOT_TOP_53362 |     1 |    37 |     8   (0)| 00:00:01 |
------------------------------------------------------------------------------------------

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

   1 - filter("USER_NAME"='1000abc')

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


统计信息
----------------------------------------------------------
          5  recursive calls
          0  db block gets
         67  consistent gets
          0  physical reads
          0  redo size
        527  bytes sent via SQL*Net to client
        385  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed

SQL>



二级索引

oracle 索引组织表IOT_bc_04



在user_name上创建二级索引:

SQL> create index idx_index_org_name on tb_index_org(user_name);

索引已创建。

SQL>



SQL:select * from tb_index_org where user_name = '1000abc';

执行计划:

SQL> select * from tb_index_org where user_name = '1000abc';


执行计划
----------------------------------------------------------
Plan hash value: 746095946

----------------------------------------------------------------------------------------
| Id  | Operation         | Name               | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |                    |     1 |    37 |     1   (0)| 00:00:01 |
|*  1 |  INDEX UNIQUE SCAN| SYS_IOT_TOP_53362  |     1 |    37 |     1   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN| IDX_INDEX_ORG_NAME |     1 |       |     1   (0)| 00:00:01 |
----------------------------------------------------------------------------------------

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

   1 - access("USER_NAME"='1000abc')
   2 - access("USER_NAME"='1000abc')

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


统计信息
----------------------------------------------------------
          9  recursive calls
          0  db block gets
         41  consistent gets
          4  physical reads
          0  redo size
        527  bytes sent via SQL*Net to client
        385  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed

SQL>



在id上创建二级索引:

SQL> create index idx_index_org_id on tb_index_org(id);

索引已创建。



SQL:select * from tb_index_org where id = 1000;

执行计划:

SQL> select * from tb_index_org where id = 1000;


执行计划
----------------------------------------------------------
Plan hash value: 3933225530

---------------------------------------------------------------------------------------
| Id  | Operation         | Name              | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |                   |     1 |    37 |     1   (0)| 00:00:01 |
|*  1 |  INDEX UNIQUE SCAN| SYS_IOT_TOP_53362 |     1 |    37 |     1   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN| IDX_INDEX_ORG_ID  |     1 |       |     1   (0)| 00:00:01 |
---------------------------------------------------------------------------------------

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

   1 - access("ID"=1000)
   2 - access("ID"=1000)

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


统计信息
----------------------------------------------------------
          9  recursive calls
          0  db block gets
         41  consistent gets
          4  physical reads
          0  redo size
        527  bytes sent via SQL*Net to client
        385  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed

SQL>



跟B树索引相比


SQL> drop table tb_index_org;

表已删除。

SQL> create table tb_index_org
  2  (
  3      id integer not null,
  4      user_name varchar2(20) not null,
  5      address varchar2(20),
  6      primary key(id, user_name)
  7  );

表已创建。

SQL> BEGIN
  2    FOR I IN 1..5000 LOOP
  3      INSERT INTO tb_index_org (id, user_name) VALUES (I, I || 'abc');
  4    END LOOP;
  5    FOR I IN 5001..10000 LOOP
  6      INSERT INTO tb_index_org (id, user_name) VALUES (I, I || 'def');
  7    END LOOP;
  8    COMMIT;
  9  END;
 10  /

PL/SQL 过程已成功完成。

SQL>



创建B树索引:

SQL> create index idx_index_org_id on tb_index_org(id);

索引已创建。



SQL:select * from tb_index_org where id = 1000;

执行计划:

SQL> select * from tb_index_org where id = 1000;


执行计划
----------------------------------------------------------
Plan hash value: 2510892312

------------------------------------------------------------------------------------------------
| Id  | Operation                   | Name             | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |                  |     1 |    37 |     2   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| TB_INDEX_ORG     |     1 |    37 |     2   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN          | IDX_INDEX_ORG_ID |     1 |       |     1   (0)| 00:00:01 |
------------------------------------------------------------------------------------------------

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

   2 - access("ID"=1000)

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


统计信息
----------------------------------------------------------
         28  recursive calls
          0  db block gets
         40  consistent gets
          4  physical reads
          0  redo size
        530  bytes sent via SQL*Net to client
        385  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed

SQL>



或者只有一个主键:

SQL> drop table tb_index_org;

表已删除。

SQL> create table tb_index_org
  2  (
  3      id integer not null,
  4      user_name varchar2(20) not null,
  5      address varchar2(20),
  6      primary key(id)
  7  );

表已创建。

SQL> BEGIN
  2    FOR I IN 1..5000 LOOP
  3      INSERT INTO tb_index_org (id, user_name) VALUES (I, I || 'abc');
  4    END LOOP;
  5    FOR I IN 5001..10000 LOOP
  6      INSERT INTO tb_index_org (id, user_name) VALUES (I, I || 'def');
  7    END LOOP;
  8    COMMIT;
  9  END;
 10  /

PL/SQL 过程已成功完成。

SQL> select * from tb_index_org where id = 1000;


执行计划
----------------------------------------------------------
Plan hash value: 309417691

--------------------------------------------------------------------------------------------
| Id  | Operation                   | Name         | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |              |     1 |    37 |     1   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| TB_INDEX_ORG |     1 |    37 |     1   (0)| 00:00:01 |
|*  2 |   INDEX UNIQUE SCAN         | SYS_C005713  |     1 |       |     1   (0)| 00:00:01 |
--------------------------------------------------------------------------------------------

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

   2 - access("ID"=1000)


统计信息
----------------------------------------------------------
         44  recursive calls
          0  db block gets
          7  consistent gets
          0  physical reads
          0  redo size
        434  bytes sent via SQL*Net to client
        374  bytes received via SQL*Net from client
          1  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed

SQL>




总结



适合使用IOT的几种情况:
1、表完全由主键组成或者只通过主键来访问一个表。使用IOT,表就是索引,可以节约空间,提高效率。
2、通过外键访问子表,子表使用IOT。通过IOT将相同外键的子表数据物理的存储在同一个位置,查询所需要的物理I/O更少,因为数据都在同一个(几个)块上。
3、经常在主键或者或惟一键上使用BETWEEN查询。数据以某种特定的顺序物理存储,所以获取这些数据时所需的物理I/O更少。