高效的 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

-------------------------------------------------------------------------------------------

-------------------------------------------------------------------------------------------

| 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

-------------------------------------------------------------------------------------------

-------------------------------------------------------------------------------------------

| 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

----------------------------------------------------------------------------------------------

----------------------------------------------------------------------------------------------

| 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

--------------------------------------------------------------------------

--------------------------------------------------------------------------

| 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

---------------------------------------------------------------------------

---------------------------------------------------------------------------

| 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

----------------------------------------------------------------------------------------------

----------------------------------------------------------------------------------------------

| 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

-------------------------------------------------------------------------------------------

-------------------------------------------------------------------------------------------

| 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'

ITPub博客

Oracle 优化之SQL（Index unique scan最优化）

最新文章