Hi Tom,

It was a really useful info and thanks for pointing out to me to the relavant docs. I will surely go through that.

Few questions.
1 >And remember, a subquery cannot simply be replaced by a join (and vice versa),
since they often result in DIFFERENT ANSWERS.
Can you explain this with a short example where join converted as a subquery (and vise versa) producing different results

2 > select * from emp where deptno in ( select deptno from dept );
would be "better" than
select emp.* from emp, dept where emp.deptno = dept.deptno;
why ?

3. Can we re-write following query with outer join using subquery ?
select emp.* from emp, dept where emp.deptno = dept.deptno(+);

Thanks,
Raj.

1) ops$tkyte@ORA9IR2> select * from scott.dept
  2  where deptno in ( select deptno from scott.emp );
 
    DEPTNO DNAME          LOC
---------- -------------- -------------
        10 ACCOUNTING     NEW YORK
        20 RESEARCH       DALLAS
        30 SALES          CHICAGO
 
ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> select dept.* from scott.dept, scott.emp
  2  where dept.deptno = emp.deptno;
 
    DEPTNO DNAME          LOC
---------- -------------- -------------
        10 ACCOUNTING     NEW YORK
        10 ACCOUNTING     NEW YORK
        10 ACCOUNTING     NEW YORK
        20 RESEARCH       DALLAS
        20 RESEARCH       DALLAS
        20 RESEARCH       DALLAS
        20 RESEARCH       DALLAS
        20 RESEARCH       DALLAS
        30 SALES          CHICAGO
        30 SALES          CHICAGO
        30 SALES          CHICAGO
        30 SALES          CHICAGO
        30 SALES          CHICAGO
        30 SALES          CHICAGO
 
14 rows selected.


2) because it does what you are asking - "give me the rows in emp where the emp.deptno is in this (set)"

It askes the question you wanted to ask.  A join does not.

3) why bother?  well, sure - assuming that dept.deptno is a primary key, the rewritten query would be;

select * from emp;


 

Tom,

What I feel is Oracle internally tranforms all subqueries to joines before result is output and also if just a check is done using EXISTS for exist or not exists condition in a subquery that would be better than getting a row to verify something

What I mean is

select e.* from emp
where exists ( select work from emp_new);

is better than

select e.* from emp
where job in ( select work from emp_new);

When really work column is not required to be retrieved

Hope I am near correct

regds
Girish

Tom,

then why most people suggest joined are better than subquery when it comes to performance

/Girish

>"then why most people suggest joined are better than
>subquery when it comes to performance"...

Because "most people" don't know much about Oracle performance tuning!!

Think about it... if they did then "most" systems would be
trouble free.. clearly not the case in the real world (and
I'm not complaining!)

Don't take at face value what "most people" say - ask
for facts or do it yourself and get the stats.

Tom,

All your questions are valid.People here in my company
do not believe even when I show them detailed statistics .
They just go by saying if query is slow they ask me to create index and make it fast.They do not know there is nothing like fast=true.I told them on more than one occasion that try to write query optimized at first step during development.But they ask me that they write it then ask to tune it with HINTS

So is the experience of another DBA

Thsnk for your clarifications

Girish

"then why most people suggest joined are better than subquery?" -- Girish

Those people are still in stone age (like they started with oracle ver < 6) and still stuck there.

I do have a guy at work who is still living in the stone age also called Girish who thinks that way too. ;)

This link cleared one of my biggest doubt..Thank u a lot dear Tom..
But still i'm in Confusion ( by the way..my Oracle experience is just one 1 year. I learnt Oracle just after joining in my current company).

I had been working in an internal project and we decided to use Stored Procedures for reporting (In the previous version of that software they are using java to create the report..means all the validation and calculations had been done in Java.. and that version had a serious performance issues.. ).
When i created procedures for that report it also had performance issue.. it took 30 secs to complete where the requierment from higher management is that they need that report in < 2 secs.. In those procedures i used subqueries
where i need to refer 5 tables.. Required columns i need to fetch is in the 5th table and other tables are referred for some validation checking like date.. LoB etc..
Also we can arrange these 5 tables in a hierarchical order
mean Table5 refers to Table4 ..refers to Table 1
no of record in that table is <10000...
Because of the performance issue i had to sent my packages ot our Oracle techie's and they asked me to change all subqueries to Joins and when i did that my performance issue has been solved... :(
Could u please give any hints based on the above explanation about what could have happend for my procs

all my queries were in a format like
select cols from table5 where key in (select cols from table4 where table4.key in (.... till table 1)

One more sad thing is i usually fought with my collegues who have more Oracle experience than me about the Subquery / Join issue.. They argued for Join..
after this "performance tuning" they flatterd me :(

The first thing i did after reding this link is sending this link to all of them..I have an Oracle Guru(Scientist!) in my team!!!

If i wrote too much sorry for that..