ITPub博客
首页 博客 专栏 学院 社区 IT新媒体评选
发博

首页 > 数据库 > Oracle > Oracle 优化之SQL【Composite Indexes（最佳前导列的选择）】

Oracle 优化之SQL【Composite Indexes（最佳前导列的选择）】

原创 Oracle 作者：lovehewenyu 时间：2012-12-21 16:08:05 0 删除 编辑

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

 

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

| Id  | Operation         | Name      | Rows  | Bytes | Cost (%CPU)| Time     |

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

|   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

 

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

| Id  | Operation         | Name      | Rows  | Bytes | Cost (%CPU)| Time     |

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

|   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

 

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

| Id  | Operation         | Name      | Rows  | Bytes | Cost (%CPU)| Time     |

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

|   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

 

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

| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |

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

|   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

 

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

| Id  | Operation         | Name      | Rows  | Bytes | Cost (%CPU)| Time     |

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

|   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

 

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

| Id  | Operation         | Name      | Rows  | Bytes | Cost (%CPU)| Time     |

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

|   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

 

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

| Id  | Operation         | Name      | Rows  | Bytes | Cost (%CPU)| Time     |

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

|   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

 

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

| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |

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

|   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

 

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

| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |

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

|   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

 

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

| Id  | Operation         | Name      | Rows  | Bytes | Cost (%CPU)| Time     |

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

|   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]--> 1          <!--[endif]--> rows 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博客 ” ，链接：https://blog.itpub.net/26442936/viewspace-751497/ ，如需转载，请注明出处，否则将追究法律责任。

0
0
上一篇： Oracle 优化之SQL优化（二）（联合索引的使用）
下一篇： Oracle 优化之SQL（index skip scan使用条件）
请登录后发表评论 登录
全部评论
lovehewenyu
专家姓名：何文玉
10年金融行业老鸟。Oracle，Mysql，PostgreSQL，EMC 存储，NBU备份。微信／电话 18211103995

注册时间：2012-02-03

  • 博文量
    307
  • 访问量
    1528515

最新文章

支持我们 作者招募 用户协议 FAQ Contact Us

北京盛拓优讯信息技术有限公司. 版权所有  京ICP备16024965号-8 北京市公安局海淀分局网监中心备案编号：11010802021510 niuxiaotong@pcpop.com 17352615567 未成年人举报专区

广播电视节目制作经营许可证(京) 字第1234号 中国互联网协会会员

违法和不良信息举报电话:18101376593