Explain Plan anomaly

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. Received on Mon Jan 22 2001 - 19:59:12 CST