Re: Explain Plan anomaly

Yes,

There are a couple of other new Oracle features that don't show up in excution plans. Can't remember what they are off-hand. But the Analytic Function comments aren't entirely helpful either.

--
Jonathan Lewis
Yet another Oracle-related web site:  http://www.jlcomp.demon.co.uk

Practical Oracle 8i:  Building Efficient Databases

Publishers:  Addison-Wesley
See a first review at:
http://www.ixora.com.au/resources/index.htm#practical_8i
More reviews at: http://www.jlcomp.demon.co.uk/book_rev.html



Myron Wintonyk wrote in message <3A6CE570.5C2132BC_at_med.ualberta.ca>...


>Someone came to me and asked me to tune this query.

>

>SELECT    ( SELECT count(*)

>                    FROM ALC_PATTERNS E, ALC_STNT_ALC F

>                    WHERE E.CLASS_KEY = 956

>                         AND E.PATTERN_KEY =  F.PATTERN_KEY )

>

>        FROM ALC_CLASS_TT C, ALC_CRSE_REG B

>

>         WHERE C.SUBJECT = B.SUBJECT

>             AND C.CATALOG_NBR = B.CATALOG_NBR

>             AND C.STRM = B.STRM

>             AND C.LOCATION = B.LOCATION

>

>As with all queries, the first thing I do is an explain plan.

>Here is the plan reported by ORACLE:

>

>    SELECT STATEMENT   Cost = 2

>        NESTED LOOPS

>            INDEX FAST FULL SCAN PS_UQ_ALC_CLASS_TT

>            INDEX UNIQUE SCAN PS_UQ_ALC_CRSE_REG

>

>Interestingly, the sub-query is not in the plan at all.  Has anyone seen

>this

>before?  Does anyone have an explanation?

>

>By the way, I've tuned the query already, so that's nto the question.

>There was no index on E.CLASS_KEY and it was a BIG table.

>