Re: cost

From: Wolfgang Breitling <breitliw_at_centrexcc.com>
Date: Tue, 06 Apr 2004 04:15:48 -0600
Message-Id: <6.0.3.0.2.20040406041418.02ccf120@pop.centrexcc.com>

Does not appears to make a difference in this case:

SQL> alter session set "_new_initial_join_orders"=false;

Session altered.

SQL> explain plan set statement_id='00002' for

   2 select e.ename, d.dname
   3 from emp e inner join dept d
   4 using (deptno)
   5 where d.dname = 'RESEARCH'
   6 /

Explained.

SQL> @xplain 00002

|   0 | SELECT STATEMENT     |             |     5 |   110 |     6  (34)|
|*  1 |  HASH JOIN           |             |     5 |   110 |     6  (34)|
|*  2 |   TABLE ACCESS FULL  | DEPT        |     1 |    13 |     3  (34)|
|   3 |   TABLE ACCESS FULL  | EMP         |    14 |   126 |     3  (34)|
-------------------------------------------------------------------------

Predicate Information (identified by operation id):

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

At 02:16 AM 4/6/2004, you wrote:
> > My guess would be that this is a deliberate
> > heuristic introduced some time around 9
> > to avoid nested loop full tablescans when
> > the numbers are small.
>
>I wonder could this behaviour be changed with _new_initial_join_orders
>parameter? (Don't have a chance to test it myself right now)
>
>Tanel.
>
>
>----------------------------------------------------------------
>Please see the official ORACLE-L FAQ: http://www.orafaq.com
>----------------------------------------------------------------
>To unsubscribe send email to: oracle-l-request_at_freelists.org
>put 'unsubscribe' in the subject line.
>--
>Archives are at http://www.freelists.org/archives/oracle-l/
>FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html
>-----------------------------------------------------------------

Wolfgang Breitling
Oracle7, 8, 8i, 9i OCP DBA
Centrex Consulting Corporation
http://www.centrexcc.com

Please see the official ORACLE-L FAQ: http://www.orafaq.com

To unsubscribe send email to: oracle-l-request_at_freelists.org put 'unsubscribe' in the subject line.

--
Archives are at http://www.freelists.org/archives/oracle-l/
FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html
-----------------------------------------------------------------

Received on Tue Apr 06 2004 - 05:13:52 CDT