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

Mike,

Have a look at Metalink and bug number 1210899 "WRONG RESULTS WHEN USING CBO WITH SELF-JOINS AND SUBQUERIES ". This bug is said to be fixed in 8.1.7, suggested workaround is to use rule-based optimisation!

There is talk of a backport ST_RDBMS_8.1.6.2.0_BACKPORT_1210899, but I'm unclear about whether this is a Solaris only port.

Andy

Mike Burden <michaelburden_at_capgemini.co.uk> wrote in message news: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 Thu Jan 25 2001 - 10:55:02 CST