Re: Is this an bug in Oracle's SQL compiler

1. I've fully qualified all the columns and it made no difference.
2. Do you mean my plan table is out of date or that you need more information from the plan table? The alias name would be useful but I can't see a reference to this.

Thanks for your time.

Jonathan Lewis <jonathan_at_jlcomp.demon.co.uk> wrote in message news:980342216.2113.0.nnrp-02.9e984b29_at_news.demon.co.uk...
>
> If the two statements are identical, and the choice
> of opimiser returns different result sets and there
> is no STOPKEY (i.e. rownum) involved, then you've
> got a bug. And I can't see any difference between the
> two statements.
>
> It would be interesting to see if the bug goes away
> if you fully qualify every column with its table alias.
>
> It would also be interesting if you used a full explain plan on
> the query, so that we could see which copy of the
> CLF_CLIENT_REF in the query was matched against
> the one occurence in the plan.
>
>
> --
> 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
>
>
>
> Mike Burden wrote in message <94mi68$pcf$1_at_taliesin2.netcom.net.uk>...
> >Server 8.1.6.2
> >
> >We have an SQL statement that returns a row using the rule based
 optimiser
> >but not with the cost based optimiser (IMO that's a bug). Below is the
 SQL
> >and the explain. It should return a row.
> >
> >It seems to be related to the FIRST ROW RANGE SCAN MAX/MIN in the
 explain.
> >
> >Has anyone come across this problem or had a similar experience?
> >
> >
> >SQL> SELECT min(clientn)
> > 2 FROM clf_client_ref cr1
> > 3 WHERE refn = 'G24G039440'
> > 4 AND ref_type = 'G '
> > 5 AND clientn IN (
> > 6 SELECT clientn
> > 7 FROM clf_client_ref cr2
> > 8 /* WHERE clientn in ('C24D034037','C24D034037','C24J039440') */
> > 9 WHERE clientn = cr1.clientn
> > 10 AND ( ref_type = 'SO'
> > 11 OR ( ref_type = 'PH'
> > 12 AND not exists (
> > 13 SELECT ROWID
> > 14 FROM clf_client_ref
> > 15 WHERE ref_type = 'SO'
> > 16 AND clientn = cr2.clientn
> > 17 )
> > 18 )
> > 19 )
> > 20 )
> > 21 /
> >
> >MIN(CLIENT
> >----------
> >
> >
> >SQL> set autot trace explain
> >SQL> /
> >
> >Execution Plan
> >----------------------------------------------------------
> > 0 SELECT STATEMENT Optimizer=CHOOSE (Cost=8 Card=1 Bytes=34)
> > 1 0 SORT (AGGREGATE)
> > 2 1 FILTER
> > 3 2 NESTED LOOPS (Cost=8 Card=1 Bytes=34)
> > 4 3 TABLE ACCESS (BY INDEX ROWID) OF 'CLIENT_REF' (Cost=
> > 1 Card=21 Bytes=462)
> >
> > 5 4 INDEX (RANGE SCAN) OF 'CLIENT_REF_IDX1' (NON-UNIQU
> > E) (Cost=3 Card=21)
> >
> > 6 3 FIRST ROW (Cost=3 Card=534607 Bytes=6415284)
> > 7 6 INDEX (RANGE SCAN (MIN/MAX)) OF 'PK_CLIENT_REF' (U
> > NIQUE) (Cost=3 Card=534607)
> >
> > 8 2 INDEX (RANGE SCAN) OF 'PK_CLIENT_REF' (UNIQUE) (Cost=3
> > Card=1 Bytes=12)
> >
> >
> >
> >
> >SQL> SELECT /*+rule */ min(clientn)
> > 2 FROM clf_client_ref cr1
> > 3 WHERE refn = 'G24G039440'
> > 4 AND ref_type = 'G '
> > 5 AND clientn IN (
> > 6 SELECT clientn
> > 7 FROM clf_client_ref cr2
> > 8 /* WHERE clientn in ('C24D034037','C24D034037','C24J039440') */
> > 9 WHERE clientn = cr1.clientn
> > 10 AND ( ref_type = 'SO'
> > 11 OR ( ref_type = 'PH'
> > 12 AND not exists (
> > 13 SELECT ROWID
> > 14 FROM clf_client_ref
> > 15 WHERE ref_type = 'SO'
> > 16 AND clientn = cr2.clientn
> > 17 )
> > 18 )
> > 19 )
> > 20 )
> > 21 /
> >
> >Execution Plan
> >----------------------------------------------------------
> > 0 SELECT STATEMENT Optimizer=HINT: RULE
> > 1 0 SORT (AGGREGATE)
> > 2 1 FILTER
> > 3 2 NESTED LOOPS
> > 4 3 TABLE ACCESS (BY INDEX ROWID) OF 'CLIENT_REF'
> > 5 4 INDEX (RANGE SCAN) OF 'CLIENT_REF_IDX1' (NON-UNIQU
> > E)
> >
> > 6 3 INDEX (RANGE SCAN) OF 'PK_CLIENT_REF' (UNIQUE)
> > 7 2 INDEX (RANGE SCAN) OF 'PK_CLIENT_REF' (UNIQUE)
> >
> >
> >
> >SQL> set autot off
> >SQL> /
> >
> >MIN(CLIENT
> >----------
> >C24D034037
> >
> >
> >
> >
> >
>
>
Received on Wed Jan 24 2001 - 09:30:28 CST