Re: OCP Question (Perf Tuning)

Jonathan, you're right. Interesting thing is that bitmap indexes, which were made for DW processing and not for OLTP will also be considered for NL context in First_Rows mode. Here is the proof, which also proves that I'm a lousy typist:

SQL> set autorace on explain
SP2-0158: unknown SET option "autorace"
SQL> set autotrace on explain
SQL> select /*+ first_rows ordered */ ename,dname,loc from emp e,dept d   2 where e.deptno=d.deptno
  3 /  

ENAME DNAME LOC
---------- -------------- -------------

ALLEN      SALES          CHICAGO
WARD       SALES          CHICAGO
JAMES      SALES          CHICAGO
FORD       RESEARCH       DALLAS
MILLER     ACCOUNTING     NEW YORK
SMITH      RESEARCH       DALLAS
JONES      RESEARCH       DALLAS
MARTIN     SALES          CHICAGO
BLAKE      SALES          CHICAGO
CLARK      ACCOUNTING     NEW YORK
SCOTT      RESEARCH       DALLAS
 
ENAME      DNAME          LOC
---------- -------------- -------------
KING       ACCOUNTING     NEW YORK
TURNER     SALES          CHICAGO
ADAMS      RESEARCH       DALLAS
 

14 rows selected.    

Execution Plan

   0      SELECT STATEMENT Optimizer=HINT: FIRST_ROWS (Cost=135 Card=8
          2 Bytes=4100)
 
   1    0   TABLE ACCESS (BY INDEX ROWID) OF 'DEPT' (Cost=135 Card=1 B
          ytes=30)
 
   2    1     NESTED LOOPS (Cost=135 Card=82 Bytes=4100)
   3    2       TABLE ACCESS (FULL) OF 'EMP' (Cost=2 Card=82 Bytes=164
          0)
 
   4    2       BITMAP CONVERSION (TO ROWIDS)
   5    4         BITMAP INDEX (SINGLE VALUE) OF 'DEPT_DEPTNO'
 
 
 

On 2004.01.06 14:49, Jonathan Lewis wrote:
>
> Note in-line.
>
> Regards
>
> Jonathan Lewis
> http://www.jlcomp.demon.co.uk
>
> The educated person is not the person
> who can answer the questions, but the
> person who can question the answers -- T. Schick Jr
>
>
> One-day tutorials:
> http://www.jlcomp.demon.co.uk/tutorial.html
>
>
> Three-day seminar:
> see http://www.jlcomp.demon.co.uk/seminar.html
> ____UK___November
>
>
> The Co-operative Oracle Users' FAQ
> http://www.jlcomp.demon.co.uk/faq/ind_faq.html
>
>
> ----- Original Message -----
> To: "Multiple recipients of list ORACLE-L" <ORACLE-L_at_fatcity.com>
> Sent: Tuesday, January 06, 2004 6:59 PM
>
>
> > Nope. The answer is b). In the FIRST_ROWS mode, optimizer prefers NL to
> all other
> > methos despite the price.
> >
>
> Unless the alternative is a full tablescan on the inner
> table - in which case merge or hash joins can be
> considered.
>
> The question itself is non-trivial, as the cost of
> a nested loop is:
> Cost of outer acquisition +
> Cost of inner access * cardinality of outer acquisition.
>
> But the cost of a merge join is:
> Cost of first acquisition + cost of first sort +
> Cost of second acquisition + cost of second sort +
> Cost of merge
>
> It seems likely that if the first table returned 1 or 2 rows,
> then a nested loop with FTS could be cheaper than a
> sort merge, but if the outer table returned 3 Oracle would
> switch to a sort merge. (Assuming equijoin).
>
> On the other hand, if the second table required a very
> large sort, I'm sure you could engineer a sort_area_size
> that would make the sort cost more than three times the
> cost of a simple tablescan - which means you could change
> the access path by changing the sort_area_size.
>
>
> --
> Please see the official ORACLE-L FAQ: http://www.orafaq.net
> --
> Author: Jonathan Lewis
> INET: jonathan_at_jlcomp.demon.co.uk
>
> Fat City Network Services -- 858-538-5051 http://www.fatcity.com
> San Diego, California -- Mailing list and web hosting services
> ---------------------------------------------------------------------
> To REMOVE yourself from this mailing list, send an E-Mail message
> to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
> the message BODY, include a line containing: UNSUB ORACLE-L
> (or the name of mailing list you want to be removed from). You may
> also send the HELP command for other information (like subscribing).
>

-- 
Mladen Gogala
Oracle DBA
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Mladen Gogala
  INET: mgogala_at_adelphia.net

Fat City Network Services    -- 858-538-5051 http://www.fatcity.com
San Diego, California        -- Mailing list and web hosting services
---------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).