explain plan

From: Raymond Lee Meng Hong <RAYMOND_at_infopro.com.my>
Date: Fri, 15 Jun 2001 02:02:27 -0700
Message-ID: <F001.0032AFC7.20010615020544@fatcity.com>

Hei Guru.

I have a query which involve to some across table , here is my explain plan . As a developer is we alway choice a best way to select table , I do join those field related together ,but why it still perform table access(FULL) ???FULL SCANING ? SELECT STATEMENT Optimizer=CHOOSE
  SORT (ORDER BY)
    CONCATENATION

      MERGE JOIN
        SORT (JOIN)
          MERGE JOIN
            SORT (JOIN)
              NESTED LOOPS
                MERGE JOIN
                  TABLE ACCESS (BY ROWID) OF CF
                    INDEX (UNIQUE SCAN) OF CF_PK (UNIQUE)
                  FILTER
                    TABLE ACCESS (FULL) OF LN01
                TABLE ACCESS (BY ROWID) OF CF99
                  INDEX (UNIQUE SCAN) OF CF99_PK (UNIQUE)
            SORT (JOIN)
              TABLE ACCESS (FULL) OF LN01OTH
        SORT (JOIN)
          TABLE ACCESS (FULL) OF LN21PNB
      NESTED LOOPS
        NESTED LOOPS
          NESTED LOOPS
            NESTED LOOPS
              TABLE ACCESS (FULL) OF LN21PNB
              TABLE ACCESS (BY ROWID) OF CF01
                INDEX (UNIQUE SCAN) OF OLDIDNO_CON (UNIQUE)
            TABLE ACCESS (FULL) OF LN01OTH
          TABLE ACCESS (FULL) OF CF99
        TABLE ACCESS (FULL) OF LN01

here is my SQl.
select A.cifkey ,b.BRNCD,
b.ACNO,b.CHKDGT,acsts,REPAY_AMT,trunc(fldchar),icno,finance_cd,agent_br_cd from ln21pnb , cf01 A,LN01 B , cf99 c , ln01oth d where oldidno = icno
or idno = icno

AND A.CIFKEY = B.CIFKEY
and b.brncd = c.brncd
and b.acno = d.acno
and b.chkdgt = d.chkdgt
and a.cifkey = '40'

and cd ='PNBMEMBERNO'
AND acsts in ('A','R','L','2')
and fldchar is not null
order by a.cifkey

can it be optimizer ???

--

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

Author: Raymond Lee Meng Hong
  INET: RAYMOND_at_infopro.com.my

Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051
San Diego, California        -- Public Internet access / Mailing Lists
--------------------------------------------------------------------

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). Received on Fri Jun 15 2001 - 04:02:27 CDT