Re: explain plan

On Fri, Jun 15, 2001 at 02:05:44AM -0800, Raymond Lee Meng Hong wrote:
> 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
Properly joined
> 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
Improperly Joined
> 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

You're missing missing a bracket here: (oldidno=icno or idno=icno)

What you're actually getting is any entries from the 4 tables where oldidno=icno, and no other joins are taking place, in conjunction with those entries where idno=icon and the four tables are properly joined.

The first alternative gives you the Improperly Joined section of the plan above.

> 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 ???

-- 
T.
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Tommy Wareing
  INET: twareing_at_oup.co.uk

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).