高效的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' |
1265
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
