Is this an bug in Oracle's SQL compiler

From: Mike Burden <michaelburden_at_capgemini.co.uk>
Date: Wed, 24 Jan 2001 12:35:08 -0000
Message-ID: <94mi68$pcf$1@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 - 06:35:08 CST