Re: Need help with query. Doesnt work and dont know why?

From: Thomas J. Kyte <tkyte_at_us.oracle.com>
Date: 2000/06/28
Message-ID: <8jbhk7$8on$1@nnrp1.deja.com>#1/1

In article <3959413b.5517373_at_news.mindspring.com>, nitefrog_at_yahoo.com wrote:
> Why does this query not return any rows? I cant figure this one out?
> Also I want to note that the tablespaces are full or very close to
> being according to the storage manager. Will this cause the problem?
> Also this query takes for ever to run as well. Any help is greatly
> appreciated.
>
> select patientid
> from B
> where patientid not in (select patientid from A);
>
> TABLE A - patientid
> -------
> 1
> 2
> 3
> 4
>
> TABLE B - patientid
> -------
> 1
> 2
> 3
> 4
> 5
> 6
> 7
>
> Thanks,
>
> Kev.-
>

you have a NULL patientid in table A. When you say "where x not in ( ....) " and the set contains a NULL -- it is "unknown" whether x is not in that set. Here is an example showing your issue and then the solutions are below. This also explains why its "slow". We are doing a full scan of table A for *every* row in B. We cannot use an index as NULLS are not indexed in conventional b*trees. Once we use the correct query and the CBO, it'll do much much better:

ops$tkyte_at_8i> create table t1 ( x int, constraint t1_pk primary key (x)); Table created.

ops$tkyte_at_8i> create table t2 ( x int, constraint t2_unique unique (x) ); Table created.

ops$tkyte_at_8i> insert into t1 values ( 1 ); 1 row created.

ops$tkyte_at_8i> insert into t1 values ( 2 ); 1 row created.

ops$tkyte_at_8i> insert into t2 values ( 1 ); 1 row created.

ops$tkyte_at_8i> select * from t1 where x not in ( select x from t2 );

There we get what we expect but as soon as we stick a single NULL in there:

ops$tkyte_at_8i> insert into t2 values ( NULL ); 1 row created.

ops$tkyte_at_8i> select * from t1 where x not in ( select x from t2 ); no rows selected

That query will return 0 rows for ever.

ops$tkyte_at_8i> set autotrace on explain
ops$tkyte_at_8i> select * from t1 where x not in ( select x from t2 where x is NOT NULL );

Execution Plan

  0 SELECT STATEMENT Optimizer=CHOOSE   1 0 FILTER
  2 1 TABLE ACCESS (FULL) OF 'T1'
  3 1 TABLE ACCESS (FULL) OF 'T2' That shows how the get the right answer, unfortunately it'll be slow, (full scan of t2 for every row in t1). So, lets flip over to CBO:

ops$tkyte_at_8i> select /*+ first_rows */ * from t1 where x not in ( select x from t2 where x is NOT NULL );

Execution Plan

  0      SELECT STATEMENT Optimizer=HINT: FIRST_ROWS (Cost=26
Card=5           Bytes=130)

  1    0  INDEX (FULL SCAN) OF 'T1_PK' (UNIQUE) (Cost=26 Card=5
Byte           s=130)

  2    1    INDEX (UNIQUE SCAN) OF 'T2_UNIQUE' (UNIQUE) (Cost=1
Card           =1 Bytes=26)

Much better -- it'll still full scan t1 (but using the index this time -

great if there is lots of other columns in t1) and it'll do an index probe into t2 for each row in t1. You can also use a "hash anti-join" hint instead -- select /*+ HASH_AJ */ ..... to get a different, perhaps faster, plan

Another way to do the above with the RBO is to use where exists. It'll full scan t1 and index probe into t2 for each row:

ops$tkyte_at_8i> select * from t1 where NOT EXISTS ( select null from t2 where t2.x = t1.x );

Execution Plan

  0 SELECT STATEMENT Optimizer=CHOOSE   1 0 FILTER
  2 1 TABLE ACCESS (FULL) OF 'T1'
  3 1 INDEX (UNIQUE SCAN) OF 'T2_UNIQUE' (UNIQUE) And for the last one:

ops$tkyte_at_8i> select x from t1
  2 minus
  3 select x from t2
  4 /

Execution Plan

  0 SELECT STATEMENT Optimizer=CHOOSE   1 0 MINUS
  2 1 SORT (UNIQUE)
  3 2 TABLE ACCESS (FULL) OF 'T1'   4 1 SORT (UNIQUE)
  5 4 TABLE ACCESS (FULL) OF 'T2' is yet another way to get the same result.

ops$tkyte_at_8i> set autotrace off
>

--
Thomas Kyte (tkyte_at_us.oracle.com) Oracle Service Industries
Howtos and such: http://osi.oracle.com/~tkyte/index.html
Oracle Magazine: http://www.oracle.com/oramag
Opinions are mine and do not necessarily reflect those of Oracle Corp


Sent via Deja.com http://www.deja.com/
Before you buy.

Received on Wed Jun 28 2000 - 00:00:00 CDT