创建索引组织表
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>
二级索引
在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更少。