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>