10g與11g在處理 >= nvl(:b1,0) and

SQL> create table t as select * from dba_objects;

表已建立。

SQL> create index t_idx on t(object_id);

索引已建立。

SQL> exec dbms_stats.gather_table_stats(user,'T',null,null);

PL/SQL 過程已成功完成。

SQL> var b1 number;
SQL> var b2 number;
SQL> exec :b1:=null;

PL/SQL 過程已成功完成。

SQL> exec :b2:=null;

PL/SQL 過程已成功完成。

先看看在10g下的表現，10.2.0.4以下

SQL_ID 66gnjuyzgnhv3, child number 0
-------------------------------------
select * from t where t.object_id >= nvl(:b1,0) and t.object_id <=
nvl(:b2,9999999999999999)

Plan hash value: 1865580073

---------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 41 (100)| |
| 1 | CONCATENATION | | | | | |
|* 2 | FILTER | | | | | |
|* 3 | TABLE ACCESS FULL | T | 13313 | 1118K| 36 (3)| 00:00:01 |
|* 4 | FILTER | | | | | |
| 5 | TABLE ACCESS BY INDEX ROWID| T | 663 | 57018 | 5 (0)| 00:00:01 |
|* 6 | INDEX RANGE SCAN | T_IDX | 120 | | 2 (0)| 00:00:01 |
---------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

2 - filter((NVL(:B1,0)<=NVL(:B2,9999999999999999) AND :B2 IS NULL))
3 - filter(("T"."OBJECT_ID"<=9999999999999999 AND
"T"."OBJECT_ID">=NVL(:B1,0)))
4 - filter((NVL(:B1,0)<=NVL(:B2,9999999999999999) AND :B2 IS NOT NULL))
6 - access("T"."OBJECT_ID">=NVL(:B1,0) AND "T"."OBJECT_ID"<=:B2)

加no_expand hint後

SQL_ID apa0a127u80u7, child number 0
-------------------------------------
select /*+ no_expand */ * from t where t.object_id >= nvl(:b1,0) and
t.object_id <= nvl(:b2,9999999999999999)

Plan hash value: 1401356643

---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 36 (100)| |
|* 1 | FILTER | | | | | |
|* 2 | TABLE ACCESS FULL| T | 13315 | 1118K| 36 (3)| 00:00:01 |
---------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

1 - filter(NVL(:B1,0)<=NVL(:B2,9999999999999999))
2 - filter(("T"."OBJECT_ID">=NVL(:B1,0) AND
"T"."OBJECT_ID"<=NVL(:B2,9999999999999999)))

而10.0.2.5及11g則直接是後者。大概又是一個最佳化器改進的例子吧

[@more@]