-
Hi All.
I want to select data from one table BUT only if there is a reference to it in another table.
Which is better example A or Example B ?
Table Not_want pkey, data. more_data
Table want_from pkey, data_needed, use_data
example A
Select wf.data_needed, wf.use_data
from not_want nw, want_from wf
where nw.pkey = wf.pkey
;
example B
Select data_needed
from want_from
where wf.pkey in ( Select pkey from not_want )
;
thank you
Grah,
You'll always learn something if you take your time to ask.
-
Lot's of what-if's:
How much data is there total in each table?
What percentage of all rows returned will be in the result set?
What does the Explain Plan access paths reveal for each example?
What are your benchmark times for each (which performs better - wall clock)?
Are indexes worth the while (based on total rows returned in the result set) or SORTMERGE after full scans?
Benchmark!
-
Originally posted by Grah
Select data_needed
from want_from
where wf.pkey in ( Select pkey from not_want )
;
Oracle almost always converts sub-queries into join before proceed
-
Instead of IN use EXISTS
select * from table1 where exists(select 'x' from table2 where
table1.col1=table2.col1);
regards
anandkl
anandkl
-
This question has some reason with rule based optimazer.
in CBO, this is my mind, need get execution plan in any way.
-
I feel , using a dirct join comparison is better that using in clause ( reason could be based on the algoritham it follows for obvious reasons , it does lot of permutations in IN CLAUSE ) . u can check the same command on two different times by setting the clock on on your sqlplus . after clearing the sql parse )
siva prakash
DBA
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|