Home » SQL & PL/SQL » SQL & PL/SQL » what's the effective ways for data existence check (9i,10g,11g)
what's the effective ways for data existence check [message #342290] Thu, 21 August 2008 23:27 Go to next message
kang
Messages: 89
Registered: November 2007
Member
what's the effective ways for data existence check

with v_plc as
( select plc_seq from (
select d.plc_seq from tbl_1 d union
select d.plc_seq from tbl_2 d union
select d.plc_seq from tbl_3 d
) x
)
select *
from (select col1
nvl2(v_plc.plc_seq,'Y','N') as ref_yn
from tb_plc a, v_plc v
where a.use_plc_seq = b.facl_seq(+)
and a.use_plc_seq = v.use_plc_seq(+))

tbl_1 & tbl_2, tbl_3 are very large tables.

I just want to check whether the rows in tb_plc is referenced by v_plc or not.

Thanks.
Re: what's the effective ways for data existence check [message #342292 is a reply to message #342290] Thu, 21 August 2008 23:32 Go to previous messageGo to next message
BlackSwan
Messages: 25036
Registered: January 2009
Location: SoCal
Senior Member
http://www.orafaq.com/forum/t/88153/0/
Please read & FOLLOW the Posting Guidelines as stated in URL above
Re: what's the effective ways for data existence check [message #342748 is a reply to message #342290] Sun, 24 August 2008 21:03 Go to previous message
Kevin Meade
Messages: 2101
Registered: December 1999
Location: Connecticut USA
Senior Member
There are to my recollection three basic forms of existential query

Quote:
correlated subquery
nested select
unique key join


Some basic setup...

/*
drop table t1
/
drop table t2
/
*/

create table t1 (key number)
/
create table t2 (key number)
/

insert into t1 values (1)
/
insert into t1 values (2)
/
insert into t2 values (1)
/
commit
/


Examples of existential exists

--
-- variations of existential exists
--
select *
from t1
where exists (
               select null
               from t2
               where t2.key = t1.key)
/

select *
from t1
where key in (
               select key
               from t2
             )
/

select t1.*
from t1
    ,t2
where t1.key = t2.key
/


Examples of existential not exists

--
-- variations of existential not exists
--
select *
from t1
where not exists (
               select null
               from t2
               where t2.key = t1.key)
/

select *
from t1
where key not in (
               select key
               from t2
             )
/

select t1.*
from t1
    ,t2
where t1.key = t2.key(+)
and t2.key is null
/


In early versions of oracle (Pre-9), these different variations of queries operated differently in terms of query paths. Thus it was possible to do some amount of tuning with the alternatives given special situations that demanded it.

However, for 9i and forward, the differences between these variations of existential querying have become less and less. The optimizer understands the different versions and with each release, the hueristics and statistics methods know better how each varation will respond for a particular query. In short Oracle keeps getting better at switching between these alternatives as its query re-write intelligence grows. It is now at the point where it is no longer necessary in most situations to bother re-writing any of the variations to another form as Oracle will do it for us behind the scenes.

Additionally, you should be aware of a significant change in behavior of one of these query alternatives. NOT IN. NOT IN only works in current releases of Oracle if the nested query is providing a list that contains all non-null values. Consider this:

--
-- trouble awaits, not intuitive that this row should not appear
--
select * from t1;
select * from t2;

select *
from t1
where key not in (
               select key
               from t2
             )
/

insert into t2 values (null);
select * from t2;

select *
from t1
where key not in (
               select key
               from t2
             )
/

select *
from t1
where key not in (
               select key
               from t2
               where key is not null
             )
/

OUCH! That is one nasty surprise. Inserting a null value into table T2 means that the NOT IN will go up against an entry to check that is null. This causes no data to return.

Wonder if other have comments to offer?

Kevin

SQL> drop table t1
  2  /

Table dropped.

SQL> drop table t2
  2  /

Table dropped.

SQL> 
SQL> create table t1 (key number)
  2  /

Table created.

SQL> create table t2 (key number)
  2  /

Table created.

SQL> 
SQL> insert into t1 values (1)
  2  /

1 row created.

SQL> insert into t1 values (2)
  2  /

1 row created.

SQL> insert into t2 values (1)
  2  /

1 row created.

SQL> commit
  2  
SQL> --
SQL> -- variations of existential exists
SQL> --
SQL> select *
  2  from t1
  3  where exists (
  4                 select null
  5                 from t2
  6                 where t2.key = t1.key)
  7  /

       KEY
----------
         1

1 row selected.

SQL> 
SQL> select *
  2  from t1
  3  where key in (
  4                 select key
  5                 from t2
  6               )
  7  /

       KEY
----------
         1

1 row selected.

SQL> 
SQL> select t1.*
  2  from t1
  3      ,t2
  4  where t1.key = t2.key
  5  /

       KEY
----------
         1

1 row selected.

SQL> 
SQL> --
SQL> -- variations of existential not exists
SQL> --
SQL> select *
  2  from t1
  3  where not exists (
  4                 select null
  5                 from t2
  6                 where t2.key = t1.key)
  7  /

       KEY
----------
         2

1 row selected.

SQL> 
SQL> select *
  2  from t1
  3  where key not in (
  4                 select key
  5                 from t2
  6               )
  7  /

       KEY
----------
         2

1 row selected.

SQL> 
SQL> select t1.*
  2  from t1
  3      ,t2
  4  where t1.key = t2.key(+)
  5  and t2.key is null
  6  /

       KEY
----------
         2

1 row selected.

SQL> 
SQL> --
SQL> -- trouble awaits, not intuitive that this row should not appear
SQL> --
SQL> select * from t1;

       KEY
----------
         1
         2

2 rows selected.

SQL> select * from t2;

       KEY
----------
         1

1 row selected.

SQL> 
SQL> select *
  2  from t1
  3  where key not in (
  4                 select key
  5                 from t2
  6               )
  7  /

       KEY
----------
         2

1 row selected.

SQL> 
SQL> insert into t2 values (null);

1 row created.

SQL> select * from t2;

       KEY
----------
         1


2 rows selected.

SQL> 
SQL> select *
  2  from t1
  3  where key not in (
  4                 select key
  5                 from t2
  6               )
  7  /

no rows selected

SQL> 
SQL> select *
  2  from t1
  3  where key not in (
  4                 select key
  5                 from t2
  6                 where key is not null
  7               )
  8  /

       KEY
----------
         2

1 row selected.

SQL> 
Previous Topic: Materialized views
Next Topic: creating a cursor for every class?
Goto Forum:
  


Current Time: Sun Dec 04 22:42:54 CST 2016

Total time taken to generate the page: 0.14581 seconds