ITPub博客

首页 > Linux操作系统 > Linux操作系统 > 借助索引+非空优化distinct操作一例

借助索引+非空优化distinct操作一例

原创 Linux操作系统 作者:realkid4 时间:2011-04-19 21:16:20 0 删除 编辑

 

一个同事在做方案的时候,问我一个问题:如何快速从百万行的数据表中,快速获取到指定列的所有取定值。

 

 

这个问题的困难点在于对数据表数据列的扫描。最直接的方法无过于进行全表扫描+distinct操作。但是,因为数据量的原因,我们通常希望有更加优化的策略和方式。

 

 

Distinct+全表扫描方法

 

我们构建一个简单是实验环境,来模拟下执行计划。

 

 

SQL> create table tt as select * from dba_objects;

 

Table created

 

SQL> select count(*) from tt;

 

  COUNT(*)

----------

     51361 //减小规模

 

 

SQL> desc tt;

Name           Type          Nullable Default Comments

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

OWNER          VARCHAR2(30)  Y                         

OBJECT_NAME    VARCHAR2(128) Y                        

(篇幅原因,有省略

TEMPORARY      VARCHAR2(1)   Y                        

GENERATED      VARCHAR2(1)   Y                        

SECONDARY      VARCHAR2(1)   Y                        

 

注意,owner列此时是可空列选项。我们希望知道一共有多少个owner取值存在。

 

 

SQL> explain plan for select distinct owner from tt;

 

Explained

 

SQL> select * from table(dbms_xplan.display);

 

PLAN_TABLE_OUTPUT

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

Plan hash value: 3008180766

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

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

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

|   0 | SELECT STATEMENT   |      |    24 |   144 |   174   (6)| 00:00:03 |

|   1 |  HASH UNIQUE       |      |    24 |   144 |   174   (6)| 00:00:03 |

|   2 |   TABLE ACCESS FULL| TT   | 51361 |   300K|   167   (2)| 00:00:03 |

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

 

9 rows selected

 

 

注意这个执行计划的细节:对数据表TT的全表扫描。Oracle CBO选择对数据表TT的所有数据块扫描一遍,获取到所有的owner取值。之后,利用hash操作将相同的owner值进行合并。

 

 

优化尝试一

 

 

一种优化的思路就是针对全表扫描最大的消耗点进行优化。默认方案中,对数据表所有数据表进行扫描,是性能和资源消耗最大的部分。思考如何在尽可能少IO块读取的情况下,获取到所有owner列表。

 

 

答案是利用owner列上的索引。如果我们添加了owner列的索引,就可以将所有owner值汇集到索引的叶子节点上。这样就在少量索引块上集中了所有的owner值。

 

 

SQL> explain plan for select distinct owner from tt;

 

Explained

 

SQL> select * from table(dbms_xplan.display);

 

PLAN_TABLE_OUTPUT

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

Plan hash value: 3008180766

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

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

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

|   0 | SELECT STATEMENT   |      |    27 |   162 |   174   (6)| 00:00:03 |

|   1 |  HASH UNIQUE       |      |    27 |   162 |   174   (6)| 00:00:03 |

|   2 |   TABLE ACCESS FULL| TT   | 51361 |   300K|   167   (2)| 00:00:03 |

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

 

9 rows selected

 

出现了一些问题,CBO没有像我们预想的那样扫描索引结构,而是依然扫描数据表,之后进行Hash操作。

 

原因是什么呢?猜想是这样。Oracle在最下层的阶段,的确是选择有没有更快更高效的寻找owner所有值的方法。进行全表扫描必然是消耗资源最大,但也是最能保证全部覆盖的方法。进行索引扫描的时候,Oracle需要保证进行叶节点扫描后,可以获取到所有owner值(包括空值)。注意,空值null是不会进入索引树的。所以,Oracle在这里因为认为获取到的叶子节点上的owner值不全,才放弃了这种方式。

 

 

优化尝试二

 

那么,如何才能让Oracle相信该列全部值都在索引叶子节点上呢?答案就是对列属性进行修改。之前笔者的blog:《数据列not null对索引影响一例》(http://space.itpub.net/17203031/viewspace-682684)中,已经讲述过这种方法。

 

简单的说,not null虽然是一个约束,但是从侧面上是该列的一个属性,会影响到优化器工作的。

 

 

SQL> alter table TT modify OWNER not null;

 

Table altered

 

SQL> exec dbms_stats.gather_table_stats(user,'T',cascade => true);

 

PL/SQL procedure successfully completed

 

 

此时,进行执行计划获取。

 

 

SQL> explain plan for select distinct owner from tt;

 

Explained

 

SQL> select * from table(dbms_xplan.display);

 

PLAN_TABLE_OUTPUT

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

Plan hash value: 3631459427

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

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

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

|   0 | SELECT STATEMENT      |              |    27 |   162 |    36  (23)| 00:0

|   1 |  HASH UNIQUE          |              |    27 |   162 |    36  (23)| 00:0

|   2 |   INDEX FAST FULL SCAN| IDX_TT_OWNER | 51361 |   300K|    29   (4)| 00:0

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

 

9 rows selected

 

 

终于,我们看到了进行索引扫描的执行计划。相应的执行CPU成本也下降到原来的20%。执行方法“INDEX FAST FULL SCAN”表示的就是只进行索引叶子节点的扫描,而不进行数据块的扫描。

 

 

这个案例,我们获得如下的经验:

 

ü        对一些关键数据表(海量),无论是多小的一个操作,都存在性能问题的隐患。要在开发设计阶段就主动进行识别,之后密切关注。及时修正设计和开发方式。不要等待最后性能测试乃至投产之后才开始着手,此时大局已定,入手点较少;

ü        索引路径不是只在where条件后出现才会出现在执行计划中。CBO优化器智能程度很高,在统计量正常的情况下,一般是可以获取到正确的路径和方法的;

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/17203031/viewspace-692915/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论
求道~

注册时间:2010-11-30

  • 博文量
    545
  • 访问量
    7688287