高效的SQL(Index unique scan最优化)
1、 实验表
test 唯一约束ID,test1 主键 ID 。(唯一约束与主键会自动创建索引)。另外建立一张没有约束的表test2与test1数据相同,但是没有主键约束。
Test表
doudou@TEST> desc test
Name Null? Type
----------------------------------------------------- -------- ------------------------------------
ID NUMBER
NAME VARCHAR2(20)
SQL> Select b.table_name as "表名",
2 b.column_name as "索引列",
3 b.index_name as "索引名",
4 c.constraint_type as "约束类型"
5 from
6 user_ind_columns b,
7 user_constraints c
8 where b.table_name=c.table_name
9 and b.table_name='TEST'
10 /
表名 索引列 索引名 约束类型
---------- -------------------- -------------------- --------------------
TEST ID SYS_C007383 U
【SYS_C007383,这个索引是我们创建了唯一约束后自动创建的索引。】
doudou@TEST> select count(*) from test;
COUNT(*)
----------
100
Test1表
doudou@TEST> desc test1
Name Null? Type
----------------------------------------------------- -------- ------------------------------------
ID NOT NULL NUMBER
NAME VARCHAR2(20)
SQL> Select b.table_name as "表名",
2 b.column_name as "索引列",
3 b.index_name as "索引名",
4 c.constraint_type as "约束类型"
5 from
6 user_ind_columns b,
7 user_constraints c
8 where b.table_name=c.table_name
9 and b.table_name='&table_name'
10 /
表名 索引列 索引名 约束类型
-------------------- ------------------------------ ------------------------------ ------------------------------
TEST1 ID SYS_C007384 P
【SYS_C007384,这个索引是创建主键后自动创建的索引。】
doudou@TEST> select count(*) from test1;
COUNT(*)
----------
100
Test2 表(没有唯一约束和主键)
doudou@TEST> create table test2 as select * from test1;
Table created.
2、 使用index unique scan条件
When the Optimizer Uses Index Unique Scans
The database uses this access path when the user specifies all columns of a unique (B-tree) index or an index created as a result of a primary key constraint with equality conditions.
2-1 test、test1、test2 走索引的情况(并都使用“=”)
doudou@TEST> select * from test where id=8;
ID NAME
---------- --------------------
8 兜兜+8
Execution Plan
----------------------------------------------------------
Plan hash value: 2583279465
-------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 25 | 1 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| TEST | 1 | 25 | 1 (0)| 00:00:01 |
|* 2 | INDEX UNIQUE SCAN | SYS_C007383 | 1 | | 1 (0)| 00:00:01 |
-------------------------------------------------------------------------------------------
doudou@TEST> select * from test1 where id=8;
ID NAME
---------- --------------------
8 兜兜-8
Execution Plan
----------------------------------------------------------
Plan hash value: 3681009939
-------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 25 | 1 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| TEST1 | 1 | 25 | 1 (0)| 00:00:01 |
|* 2 | INDEX UNIQUE SCAN | SYS_C007384 | 1 | | 1 (0)| 00:00:01 |
-------------------------------------------------------------------------------------------
【唯一约束和主键并使用“=”。进行index unique scan】
doudou@TEST> select * from test2 where id=8;
ID NAME
---------- --------------------
8 兜兜-8
Execution Plan
----------------------------------------------------------
Plan hash value: 965028218
----------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 25 | 2 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| TEST2 | 1 | 25 | 2 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | INDEX_TEST2_ID | 1 | | 1 (0)| 00:00:01 |
----------------------------------------------------------------------------------------------
【使用“=”,但不是唯一约束和主键。不执行index unique scan】
2-2 test、test1 不使用“=”均不执行index unique scan 及test2测试
doudou@TEST> select * from test where id<3;
ID NAME
---------- --------------------
1 兜兜+1
2 兜兜+2
Execution Plan
----------------------------------------------------------
Plan hash value: 1357081020
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 2 | 50 | 3 (0)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| TEST | 2 | 50 | 3 (0)| 00:00:01 |
--------------------------------------------------------------------------
doudou@TEST> select * from test1 where id<3;
ID NAME
---------- --------------------
1 兜兜-1
2 兜兜-2
Execution Plan
----------------------------------------------------------
Plan hash value: 4122059633
---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 2 | 50 | 3 (0)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| TEST1 | 2 | 50 | 3 (0)| 00:00:01 |
---------------------------------------------------------------------------
【test 与test1 均执行了full table scan ,并没有执行index unique scan。where中没有使用“=”的时候,索引列是唯一约束和主键也是不执行index unique scan的】
doudou@TEST> select * from test2 where id<3;
ID NAME
---------- --------------------
1 兜兜-1
2 兜兜-2
Execution Plan
----------------------------------------------------------
Plan hash value: 965028218
----------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 2 | 50 | 2 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| TEST2 | 2 | 50 | 2 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | INDEX_TEST2_ID | 2 | | 1 (0)| 00:00:01 |
----------------------------------------------------------------------------------------------
【test2没有唯一约束和主键,但在范围查询的时候,却进行了index range scan。所以在范围查询的时候用唯一约束和主键也不是明智的。因为范围查询不满足index unique scan的条件。】
3、总结
1、index unique scan 条件:索引列是唯一约束或主键、并使用“=”。
2、0 rowid asssociated with an index key 解释:查询的index key不存在相应的也就是0 rowid。
4、附表
有关11G官档中一些话0 rowid asssociated with an index key理解困难。
官档原文:
Index Unique Scan
In contrast to an index range scan, an index unique scan must have either 0 or 1 rowid associated with an index key.
经过群内朋友,老宋的点拨理解了。查询索引键值不存在,对应的ROWID就为0了。
doudou@TEST> select * from test where id=200;(id=200目前id列不存在200这个数据)
no rows selected
Execution Plan
----------------------------------------------------------
Plan hash value: 2583279465
-------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 25 | 1 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| TEST | 1 | 25 | 1 (0)| 00:00:01 |
|* 2 | INDEX UNIQUE SCAN | SYS_C007383 | 1 | | 1 (0)| 00:00:01 |
-------------------------------------------------------------------------------------------
【头脑要灵活,有行必须有rowid, 0 rowid 也可以是没有行呢(SQL语法人为操作)】
实验小工具:
脚本名称 | 功能:取索引相关信息SQL |
index_con_col.sql
取有约束的索引相关信息SQL | col 表名 for a20 col 索引列 for a30 col 索引名 for a30 col 约束类型 for a30 Select b.table_name as "表名", b.column_name as "索引列", b.index_name as "索引名", c.constraint_type as "约束类型" from user_ind_columns b, user_constraints c where b.table_name=c.table_name and b.table_name='&table_name' |
Index_col.sql
取没有约束的索引相关信息SQL | col 表名 for a20 col 索引列 for a30 col 索引名 for a30 Select b.table_name as "表名", b.column_name as "索引列", b.index_name as "索引名" from user_ind_columns b where b.table_name='&table_name' |