Costs in the CBO


This is from a thread on the ORACLE-L forum, where a simple script can be used to demonstrate that it is in fact possible for the CBO to choose an access path which does NOT have the lowest cost

Consider the following example. First we check the costs of a simple join via a nested loop or a hash join.

C:\>sqlplus scott/tiger

SQL*Plus: Release 9.2.0.4.0 - Production on Tue Apr 6 18:57:39 2004

Copyright (c) 1982, 2002, Oracle Corporation.  All rights reserved.


Connected to:
Oracle9i Enterprise Edition Release 9.2.0.4.0 - Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.4.0 - Production

SQL> explain plan for
  2    select /*+ USE_HASH(d e) */ e.ename, d.dname
  3    from  emp e , dept d
  4    where e.deptno = d.deptno
  5    and   d.dname = 'RESEARCH';

Explained.

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

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------

--------------------------------------------------------------------
| Id  | Operation            |  Name       | Rows  | Bytes | Cost  |
--------------------------------------------------------------------
|   0 | SELECT STATEMENT     |             |     5 |    90 |     5 |
|*  1 |  HASH JOIN           |             |     5 |    90 |     5 |
|*  2 |   TABLE ACCESS FULL  | DEPT        |     1 |    11 |     2 |
|   3 |   TABLE ACCESS FULL  | EMP         |    14 |    98 |     2 |
--------------------------------------------------------------------

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

   1 - access("E"."DEPTNO"="D"."DEPTNO")
   2 - filter("D"."DNAME"='RESEARCH')

Note: cpu costing is off

17 rows selected.

SQL> explain plan for
  2    select /*+ USE_NL(d e) */ e.ename, d.dname
  3    from  emp e , dept d
  4    where e.deptno = d.deptno
  5    and   d.dname = 'RESEARCH';

Explained.

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

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------

--------------------------------------------------------------------
| Id  | Operation            |  Name       | Rows  | Bytes | Cost  |
--------------------------------------------------------------------
|   0 | SELECT STATEMENT     |             |     5 |    90 |     4 |
|   1 |  NESTED LOOPS        |             |     5 |    90 |     4 |
|*  2 |   TABLE ACCESS FULL  | DEPT        |     1 |    11 |     2 |
|*  3 |   TABLE ACCESS FULL  | EMP         |     5 |    35 |     2 |
--------------------------------------------------------------------

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

   2 - filter("D"."DNAME"='RESEARCH')
   3 - filter("E"."DEPTNO"="D"."DEPTNO")

Note: cpu costing is off

17 rows selected.

So we can see that the cost for the nested loop is 4 and the cost for the hash join is 5. Given a choice between the two, one would expect the CBO to choose the nested loop. However, consider what happens when no hint is specified

SQL> explain plan for
  2    select e.ename, d.dname
  3    from  emp e , dept d
  4    where e.deptno = d.deptno
  5    and   d.dname = 'RESEARCH';

Explained.

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

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------

--------------------------------------------------------------------
| Id  | Operation            |  Name       | Rows  | Bytes | Cost  |
--------------------------------------------------------------------
|   0 | SELECT STATEMENT     |             |     5 |    90 |     5 |
|*  1 |  HASH JOIN           |             |     5 |    90 |     5 |
|*  2 |   TABLE ACCESS FULL  | DEPT        |     1 |    11 |     2 |
|   3 |   TABLE ACCESS FULL  | EMP         |    14 |    98 |     2 |
--------------------------------------------------------------------

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

   1 - access("E"."DEPTNO"="D"."DEPTNO")
   2 - filter("D"."DNAME"='RESEARCH')

Note: cpu costing is off

17 rows selected.

SQL>