不要制造不必要的混乱把优化器给弄糊涂了

一.10.2.0.1下(其它版本没有测试过)
SQL> explain plan for SELECT PRODUCT_ID,COUNT(PRODUCT_ID) FROM RES_ARTICLE_INFO WHERE NVL(PRODUCT_ID,0)>0 GROUP BY PRODUCT_ID;
SQL> select * from table(dbms_xplan.display());
---------------------------------------------------------------------------------------
| Id  | Operation          | Name             | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |                  | 48838 |   143K| 39247   (2)| 00:07:51 |
|   1 |  HASH GROUP BY     |                  | 48838 |   143K| 39247   (2)| 00:07:51 |
|*  2 |   TABLE ACCESS FULL| RES_ARTICLE_INFO |   117K|   343K| 39236   (2)| 00:07:51 |
---------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   2 - filter(NVL("PRODUCT_ID",0)>0)

 

SQL> explain plan for SELECT /*+ index_ffs(a IND_ARTINFO_PROD_ID) */PRODUCT_ID,COUNT(PRODUCT_ID)
                            FROM RES_ARTICLE_INFO a
                            WHERE NVL(PRODUCT_ID,0)>0
                            GROUP BY PRODUCT_ID;
SQL> select * from table(dbms_xplan.display());
---------------------------------------------------------------------------------------
| Id  | Operation          | Name             | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |                  | 48838 |   143K| 39247   (2)| 00:07:51 |
|   1 |  HASH GROUP BY     |                  | 48838 |   143K| 39247   (2)| 00:07:51 |
|*  2 |   TABLE ACCESS FULL| RES_ARTICLE_INFO |   117K|   343K| 39236   (2)| 00:07:51 |
---------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   2 - filter(NVL("PRODUCT_ID",0)>0)
我这里强制加上提示也不走这个执行路径,说明优化器认为这个执行路径不在考虑范围内.

 
SQL> explain plan for SELECT PRODUCT_ID,COUNT(PRODUCT_ID) FROM RES_ARTICLE_INFO WHERE PRODUCT_ID>0 GROUP BY PRODUCT_ID;
SQL> select * from table(dbms_xplan.display());
---------------------------------------------------------------------------------------------
| Id  | Operation             | Name                | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT      |                     | 17938 | 53814 |   495  (20)| 00:00:06 |
|   1 |  HASH GROUP BY        |                     | 17938 | 53814 |   495  (20)| 00:00:06 |
|*  2 |   INDEX FAST FULL SCAN| IND_ARTINFO_PROD_ID |   764K|  2240K|   415   (5)| 00:00:05 |
---------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   2 - filter("PRODUCT_ID">0)

这里PRODUCT_ID确实是可以为空的,存在索引index IND_ARTINFO_PROD_ID on RES_ARTICLE_INFO(product_id)
其实NVL(PRODUCT_ID,0)>0 和PRODUCT_ID>0 是完全等价的,但这里显然优化器没有意识到这一点,对于NVL(PRODUCT_ID,0)>0 这样的写法,它认为你会访问product为null的数据行,所以它选择了全表扫描,而没有像后者那样走了索引的快速全扫描.

 

****************************************

 

二.10.2.0.4下
create table zsj_test
as select owner,object_id,object_name,object_type,to_date('2000-01-01','yyyy-mm-dd')+rownum last_ddl_time
from all_objects
where rownum<=50000;

alter table zsj_test modify(last_ddl_time not null); --last_ddl_time上是有not null约束的,并且这个列上有索引
create index ind1_zsj_test on zsj_test(last_ddl_time);
exec dbms_stats.gather_table_stats(user,'ZSJ_TEST',cascade => TRUE,estimate_percent => 100,method_opt => 'FOR ALL INDEXED COLUMNS SIZE 1');

SELECT * FROM
(
   SELECT OWNER,OBJECT_ID,OBJECT_NAME,OBJECT_TYPE,LAST_DDL_TIME FROM ZSJ_TEST ORDER BY LAST_DDL_TIME DESC
)
WHERE ROWNUM<=10;

-----------------------------------------------------------------------------------------------
| Id  | Operation                     | Name          | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT              |               |    10 |   670 |     3   (0)| 00:00:01 |
|*  1 |  COUNT STOPKEY                |               |       |       |            |          |
|   2 |   VIEW                        |               |    10 |   670 |     3   (0)| 00:00:01 |
|   3 |    TABLE ACCESS BY INDEX ROWID| ZSJ_TEST      | 50000 |  2490K|     3   (0)| 00:00:01 |
|   4 |     INDEX FULL SCAN DESCENDING| IND1_ZSJ_TEST |    10 |       |     2   (0)| 00:00:01 |
-----------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   1 - filter(ROWNUM<=10)
Statistics
----------------------------------------------------------
          6  consistent gets
         10  rows processed
可以通过索引的降序全扫描避免排序的.

SELECT * FROM
(
   SELECT OWNER,OBJECT_ID,OBJECT_NAME,OBJECT_TYPE,LAST_DDL_TIME FROM ZSJ_TEST T ORDER BY LAST_DDL_TIME DESC NULLS LAST
)
WHERE ROWNUM<=10;   --这里画蛇添足添加了一个毫无必要的nulls last

--------------------------------------------------------------------------------------------
| Id  | Operation               | Name     | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT        |          |    10 |   670 |       |   730   (1)| 00:00:09 |
|*  1 |  COUNT STOPKEY          |          |       |       |       |            |          |
|   2 |   VIEW                  |          | 50000 |  3271K|       |   730   (1)| 00:00:09 |
|*  3 |    SORT ORDER BY STOPKEY|          | 50000 |  2490K|  6680K|   730   (1)| 00:00:09 |
|   4 |     TABLE ACCESS FULL   | ZSJ_TEST | 50000 |  2490K|       |    91   (2)| 00:00:02 |
--------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   1 - filter(ROWNUM<=10)
   3 - filter(ROWNUM<=10)
Statistics
----------------------------------------------------------
        395  consistent gets
         10  rows processed
你可以看到它变成全表扫描后排序的操作了

SELECT * FROM
(
   SELECT /*+ index_desc(t)*/OWNER,OBJECT_ID,OBJECT_NAME,OBJECT_TYPE,LAST_DDL_TIME FROM ZSJ_TEST T ORDER BY LAST_DDL_TIME DESC NULLS LAST
)
WHERE ROWNUM<=10;

--------------------------------------------------------------------------------------------------------
| Id  | Operation                      | Name          | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT               |               |    10 |   670 |       |  1167   (1)| 00:00:14 |
|*  1 |  COUNT STOPKEY                 |               |       |       |       |            |          |
|   2 |   VIEW                         |               | 50000 |  3271K|       |  1167   (1)| 00:00:14 |
|*  3 |    SORT ORDER BY STOPKEY       |               | 50000 |  2490K|  6680K|  1167   (1)| 00:00:14 |
|   4 |     TABLE ACCESS BY INDEX ROWID| ZSJ_TEST      | 50000 |  2490K|       |   528   (1)| 00:00:07 |
|   5 |      INDEX FULL SCAN DESCENDING| IND1_ZSJ_TEST | 50000 |       |       |   135   (1)| 00:00:02 |
--------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   1 - filter(ROWNUM<=10)
   3 - filter(ROWNUM<=10)
Statistics
----------------------------------------------------------
        526  consistent gets
         10  rows processed
强制走索引降序扫描,可也避免不了排序操作,逻辑读更大了.

SELECT * FROM
(
   SELECT /*+ index(t)*/OWNER,OBJECT_ID,OBJECT_NAME,OBJECT_TYPE,LAST_DDL_TIME FROM ZSJ_TEST T ORDER BY LAST_DDL_TIME DESC NULLS LAST
)
WHERE ROWNUM<=10;

--------------------------------------------------------------------------------------------------------
| Id  | Operation                      | Name          | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT               |               |    10 |   670 |       |  1167   (1)| 00:00:14 |
|*  1 |  COUNT STOPKEY                 |               |       |       |       |            |          |
|   2 |   VIEW                         |               | 50000 |  3271K|       |  1167   (1)| 00:00:14 |
|*  3 |    SORT ORDER BY STOPKEY       |               | 50000 |  2490K|  6680K|  1167   (1)| 00:00:14 |
|   4 |     TABLE ACCESS BY INDEX ROWID| ZSJ_TEST      | 50000 |  2490K|       |   528   (1)| 00:00:07 |
|   5 |      INDEX FULL SCAN           | IND1_ZSJ_TEST | 50000 |       |       |   135   (1)| 00:00:02 |
--------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   1 - filter(ROWNUM<=10)
   3 - filter(ROWNUM<=10)
Statistics
----------------------------------------------------------
        526  consistent gets
         10  rows processed

这里因为一个画蛇添足的nulls last(字段上有not null约束的),优化器放弃了最优的执行计划.

这些都是工作中实际碰到的问题,你当然可以说是因为优化器不足够智能.可问题是我们写出这样的语句的时候,我们是否足够清醒,知道自己在干什么呢?优化器不足够智能,这个我们必须承认,我们也改变不了,我们能做的就是意识到优化器还不足够智能,所以我们在写语句的时候,要保持足够的清醒,知道自己在干什么,不要制造不必要的混乱把优化器给弄糊涂了

****************************************

 

其实http://blog.csdn.net/zhaosj1726/archive/2010/12/05/6056347.aspx 这里提到的sql2无法通过索引避免排序也是同样的问题,没有意识到排序字段中使用的是列的别名.

 

****************************************

以后工作中碰到了类似需要注意的问题,也会补充到这里的.