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: [EMAIL PROTECTED]
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: [EMAIL PROTECTED] (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).