玉面飞龙的BLOG

10g CBO synax check improvement

October 19th, 2007 | Categories: Boring | Tags:

10g can parse statement wiser than 9i and avoid unnecessary filter check.

Example is simple,

Oracle9i Enterprise Edition Release 9.2.0.5.0 - 64bit Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.5.0 - Production

SQL> explain plan for SELECT count(*) FROM TEST i
2 WHERE i.ID = :1 AND i.created BETWEEN
3 sysdate - 1 and sysdate AND (0=0 or i.object_type = 12);

Explained.

PLAN_TABLE_OUTPUT
-----------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost |
-----------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 15 | 6778 |
| 1 | SORT AGGREGATE | | 1 | 15 | |
|* 2 | FILTER | | | | |
| 3 | TABLE ACCESS BY INDEX ROWID| TEST | 51807 | 758K| 6778 |
|* 4 | INDEX RANGE SCAN | TEST_IDC_IDX | 9325 | | 44 |
-----------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
------------------------------------------------
2 - filter(SYSDATE@!-1<=SYSDATE@!)
4 - access("I"."ID"=2463805 AND "I"."created">=SYSDATE@!-1 AND "I"."created"<=SYSDATE@!)

Note: cpu costing is off

Then see 10g test,

Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - 64bit Production
With the Partitioning, OLAP and Data Mining options

SQL> explain plan for SELECT count(*) FROM TEST i
2 WHERE i.ID = :1 AND i.created BETWEEN
3 sysdate - 1 and sysdate AND (0=0 or i.object_type = 12);

Explained.

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost |
----------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 35 | 3 |
| 1 | SORT AGGREGATE | | 1 | 35 | |
|* 2 | INDEX RANGE SCAN| TEST_IDC_IDX | 1 | | 2 |
----------------------------------------------------------------------------
Predicate Information (identified by operation id):
--------------------------------------------------------------------------------
2 - access("I"."ID"=2463805 AND "I"."created">=SYSDATE@!-1 AND "I"."created"<=SYSDATE@!)

Just minor improvement. And it doesn’t work when using bind variable (still need to query table).