高效的SQL【Composite Indexes（最佳前導列的選擇）】

Composite Indexes（最佳前導列的選擇）

一、前導列是否有序對composite indexes的影響大

1、建立表t1(x,y) x有序列，y隨意列

doudou@TEST> begin

2 for i in 1.. 1000000 loop

3 insert into t1 values (i,to_char(dbms_random.random,'999999999999') );

4 if mod(i,100000)=0

5 then

6 commit;

7 end if;

8 end loop;

9 end;

10 /

PL/SQL procedure successfully completed.

2、組合索引：前導列是否有序的列對clustering_factor影響大。

doudou@TEST> create index idx_t1_01 on t1(x,y) ;

Index created.

doudou@TEST> create index idx_t1_02 on t1(y,x);

Index created.

doudou@TEST> select index_name,table_name,clustering_factor from user_indexes where table_name='T1';

INDEX_NAME TABLE_NAME CLUSTERING_FACTOR

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

IDX_T1_02 T1 999562

IDX_T1_01 T1 2402

doudou@TEST> drop index idx_t1_02;

Index dropped.

3、資料相同的2個表，不同前導列的組合索引，檢視效率

doudou@TEST> create table t2 as select * from t1;

Table created.

doudou@TEST> create index idx_t2_01 on t2(y,x);

Index created.

doudou@TEST> select index_name,table_name,clustering_factor from user_indexes where table_name='T2';

INDEX_NAME TABLE_NAME CLUSTERING_FACTOR

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

IDX_T2_01 T2 999591

doudou@TEST> select index_name,table_name,clustering_factor from user_indexes where table_name='T1';

INDEX_NAME TABLE_NAME CLUSTERING_FACTOR

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

IDX_T1_01 T1 2402

sys@TEST> alter system flush shared_pool; --（生產庫慎用，會造成大量物理讀）

System altered.

doudou@TEST> exec dbms_stats.gather_table_stats('DOUDOU','T1',cascade=>true); --（cascade=>true收集統計索引資訊,預設為flase）

PL/SQL procedure successfully completed.

doudou@TEST> exec dbms_stats.gather_table_stats('DOUDOU','T2',cascade=>true);

PL/SQL procedure successfully completed.

3-1、等值查詢

doudou@TEST> select count(*) from t1 where x=1 and y=-1481404810;

COUNT(*)

----------

1

Execution Plan

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

Plan hash value: 1645197104

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

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

| 0 | SELECT STATEMENT | | 1 | 12 | 3 (0)| 00:00:01 |

| 1 | SORT AGGREGATE | | 1 | 12 | | |

|* 2 | INDEX RANGE SCAN| IDX_T1_01 | 1 | 12 | 3 (0)| 00:00:01 |

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

Predicate Information (identified by operation id):

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

2 - access("X"=1 AND "Y"=(-1481404810))

Statistics

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

0 recursive calls

0 db block gets

3 consistent gets

0 physical reads

0 redo size

411 bytes sent via SQL*Net to client

400 bytes received via SQL*Net from client

2 SQL*Net roundtrips to/from client

0 sorts (memory)

0 sorts (disk)

1 rows processed

doudou@TEST> select count(*) from t2 where x=1 and y=-1481404810;

COUNT(*)

----------

1

Execution Plan

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

Plan hash value: 3050965404

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

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

| 0 | SELECT STATEMENT | | 1 | 12 | 3 (0)| 00:00:01 |

| 1 | SORT AGGREGATE | | 1 | 12 | | |

|* 2 | INDEX RANGE SCAN| IDX_T2_01 | 1 | 12 | 3 (0)| 00:00:01 |

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

Predicate Information (identified by operation id):

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

2 - access("Y"=(-1481404810) AND "X"=1)

Statistics

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

1 recursive calls

0 db block gets

3 consistent gets

0 physical reads

0 redo size

411 bytes sent via SQL*Net to client

400 bytes received via SQL*Net from client

2 SQL*Net roundtrips to/from client

0 sorts (memory)

0 sorts (disk)

1 rows processed

【clustering_factor高低，對index rang scan 模式。等值查詢影響不大。】

3-2、where 謂語使用“<和>”

doudou@TEST> select count(*) from t1 where x<10 and y>0;

COUNT(*)

----------

4

Execution Plan

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

Plan hash value: 1645197104

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

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

| 0 | SELECT STATEMENT | | 1 | 12 | 3 (0)| 00:00:01 |

| 1 | SORT AGGREGATE | | 1 | 12 | | |

|* 2 | INDEX RANGE SCAN| IDX_T1_01 | 1 | 12 | 3 (0)| 00:00:01 |

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

Predicate Information (identified by operation id):

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

2 - access("Y">0 AND "X"<10)

filter("Y">0)

Statistics

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

1 recursive calls

0 db block gets

3 consistent gets

0 physical reads

0 redo size

411 bytes sent via SQL*Net to client

400 bytes received via SQL*Net from client

2 SQL*Net roundtrips to/from client

0 sorts (memory)

0 sorts (disk)

1 rows processed

doudou@TEST> select count(*) from t2 where x<10 and y>0;

COUNT(*)

----------

4

Execution Plan

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

Plan hash value: 3321871023

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

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

| 0 | SELECT STATEMENT | | 1 | 12 | 542 (1)| 00:00:07 |

| 1 | SORT AGGREGATE | | 1 | 12 | | |

|* 2 | TABLE ACCESS FULL| T2 | 1 | 12 | 542 (1)| 00:00:07 |

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

Predicate Information (identified by operation id):

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

2 - filter("X"<10 AND "Y">0)

Statistics

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

0 recursive calls

0 db block gets

2417 consistent gets

0 physical reads

0 redo size

411 bytes sent via SQL*Net to client

400 bytes received via SQL*Net from client

2 SQL*Net roundtrips to/from client

0 sorts (memory)

0 sorts (disk)

1 rows processed

【clustering_factor高低，對index rang scan 模式。where 謂語使用“<和>”，clustering_factor對索引影響大】

二、前導列選擇度高低對composite indexes的影響小

1、t3,t4資料相同 x列選擇度高，y列選擇低

doudou@TEST> create index idx_t3_01 on t3(x,y);

Index created.

doudou@TEST> create index idx_t4_01 on t4(y,x);

Index created.

doudou@TEST> select index_name,table_name,clustering_factor from user_indexes where table_name='T3';

INDEX_NAME TABLE_NAME CLUSTERING_FACTOR

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

IDX_T3_01 T3 995325

doudou@TEST> select index_name,table_name,clustering_factor from user_indexes where table_name='T4';

INDEX_NAME TABLE_NAME CLUSTERING_FACTOR

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

IDX_T4_01 T4 999539

【composite indexes 的 clustering_factor差別小】

doudou@TEST> select count(*) from t3 where x=1 and y=765571731;

COUNT(*)

----------

1

Execution Plan

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

Plan hash value: 30972477

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

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

| 0 | SELECT STATEMENT | | 1 | 26 | 3 (0)| 00:00:01 |

| 1 | SORT AGGREGATE | | 1 | 26 | | |

|* 2 | INDEX RANGE SCAN| IDX_T3_01 | 1 | 26 | 3 (0)| 00:00:01 |

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

Predicate Information (identified by operation id):

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

2 - access("X"=1 AND "Y"=765571731)

Note

-----

- dynamic sampling used for this statement

Statistics

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

9 recursive calls

0 db block gets

67 consistent gets

0 physical reads

0 redo size

411 bytes sent via SQL*Net to client

400 bytes received via SQL*Net from client

2 SQL*Net roundtrips to/from client

0 sorts (memory)

0 sorts (disk)

1 rows processed

doudou@TEST> select count(*) from t4 where x=1 and y=765571731;

COUNT(*)

----------

1

Execution Plan

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

Plan hash value: 2556691066

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

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

| 0 | SELECT STATEMENT | | 1 | 26 | 3 (0)| 00:00:01 |

| 1 | SORT AGGREGATE | | 1 | 26 | | |

|* 2 | INDEX RANGE SCAN| IDX_T4_01 | 1 | 26 | 3 (0)| 00:00:01 |

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

Predicate Information (identified by operation id):

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

2 - access("Y"=765571731 AND "X"=1)

Note

-----

- dynamic sampling used for this statement

Statistics

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

9 recursive calls

0 db block gets

76 consistent gets

2 physical reads

0 redo size

411 bytes sent via SQL*Net to client

400 bytes received via SQL*Net from client

2 SQL*Net roundtrips to/from client

0 sorts (memory)

0 sorts (disk)

1 rows processed

doudou@TEST> select count(*) from t3 where x=1 and y>0;

COUNT(*)

----------

50037

Execution Plan

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

Plan hash value: 30972477

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

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

| 0 | SELECT STATEMENT | | 1 | 26 | 58 (0)| 00:00:01 |

| 1 | SORT AGGREGATE | | 1 | 26 | | |

|* 2 | INDEX RANGE SCAN| IDX_T3_01 | 15446 | 392K| 58 (0)| 00:00:01 |

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

Predicate Information (identified by operation id):

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

2 - access("X"=1 AND "Y">0 AND "Y" IS NOT NULL)

Note

-----

- dynamic sampling used for this statement

Statistics

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

9 recursive calls

0 db block gets

220 consistent gets

148 physical reads

0 redo size

413 bytes sent via SQL*Net to client

400 bytes received via SQL*Net from client

2 SQL*Net roundtrips to/from client

0 sorts (memory)

0 sorts (disk)

1 rows processed

doudou@TEST> select count(*) from t4 where x=1 and y>0;

COUNT(*)

----------

50037

Execution Plan

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

Plan hash value: 405148644

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

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

| 0 | SELECT STATEMENT | | 1 | 26 | 481 (1)| 00:00:06 |

| 1 | SORT AGGREGATE | | 1 | 26 | | |

|* 2 | TABLE ACCESS FULL| T4 | 34100 | 865K| 481 (1)| 00:00:06 |

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

Predicate Information (identified by operation id):

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

2 - filter("X"=1 AND "Y">0)

Note

-----

- dynamic sampling used for this statement

Statistics

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

28 recursive calls

0 db block gets

2215 consistent gets

0 physical reads

0 redo size

413 bytes sent via SQL*Net to client

400 bytes received via SQL*Net from client

2 SQL*Net roundtrips to/from client

0 sorts (memory)

0 sorts (disk)

doudou@TEST> select count(*) from t3 where y=765571731;

COUNT(*)

----------

1

Execution Plan

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

Plan hash value: 463314188

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

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

| 0 | SELECT STATEMENT | | 1 | 13 | 470 (1)| 00:00:06 |

| 1 | SORT AGGREGATE | | 1 | 13 | | |

|* 2 | TABLE ACCESS FULL| T3 | 23 | 299 | 470 (1)| 00:00:06 |

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

Predicate Information (identified by operation id):

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

2 - filter("Y"=765571731)

Note

-----

- dynamic sampling used for this statement

Statistics

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

5 recursive calls

0 db block gets

2201 consistent gets

0 physical reads

0 redo size

411 bytes sent via SQL*Net to client

400 bytes received via SQL*Net from client

2 SQL*Net roundtrips to/from client

0 sorts (memory)

0 sorts (disk)

1 rows processed

doudou@TEST> select count(*) from t4 where y=765571731;

COUNT(*)

----------

1

Execution Plan

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

Plan hash value: 2556691066

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

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

| 0 | SELECT STATEMENT | | 1 | 13 | 3 (0)| 00:00:01 |

| 1 | SORT AGGREGATE | | 1 | 13 | | |

|* 2 | INDEX RANGE SCAN| IDX_T4_01 | 1 | 13 | 3 (0)| 00:00:01 |

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

Predicate Information (identified by operation id):

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

2 - access("Y"=765571731)

Note

-----

- dynamic sampling used for this statement

Statistics

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

9 recursive calls

0 db block gets

76 consistent gets

0 physical reads

0 redo size

411 bytes sent via SQL*Net to client

400 bytes received via SQL*Net from client

2 SQL*Net roundtrips to/from client

0 sorts (memory)

0 sorts (disk)

<!--[if !supportLists]--&gt1 <!--[endif]--&gtrows processed

【選擇度高低對composite indexes影響很小】

總結：

1、 前導列是否有序對clustering_factor影響大。前導列有序的composite indexes ， clustering_factor 低（效能高）。【反之則效能低】

2、 前導列選擇度高低對clustering_factor影響小。進而對composite indexes影響小。

3、 Clustering_factor高低對 index range scan : ①等值查詢影響小 ②< and >影響大。

建議：

Composite indexes 前導列最好是有序的，這樣clustering_factor低，效能會高。

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/26442936/viewspace-751497/，如需轉載，請註明出處，否則將追究法律責任。

高效的SQL【Composite Indexes（最佳前導列的選擇）】

相關文章