高效的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部落格 ” ，連結：http://blog.itpub.net/26442936/viewspace-750688/，如需轉載，請註明出處，否則將追究法律責任。

高效的SQL（Index unique scan最優化）

相關文章