| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Usenet -> c.d.o.tools -> Re: Is this an bug in Oracle's SQL compiler
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>...Received on Wed Jan 24 2001 - 07:19:53 CST
>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
>
>
>
>
>
 |
 |