Home » SQL & PL/SQL » SQL & PL/SQL » Execute Plan About My Sqls (oracle 10g,10.2.0.1.0 ,winows 2003 entprise 64bit english)
icon5.gif  Execute Plan About My Sqls [message #275877] Tue, 23 October 2007 02:52 Go to next message
lzfhope
Messages: 67
Registered: July 2006
Member
hi,
I have one question about the execution plan.
Yesterday,I ran the sql below:
  select a.ID, a.TypeName, b.ChildAmount
  from  Trees a
  left outer join 
  (select TypeName, count(*) as ChildAmount
                     from tTable
                    group by TypeName
              ) b 
  on b.TypeName = a.TypeName
   where a.TypeName in
       (
          select c.TypeName
          from (select tname,TypeName
                  from tTable
                 where Type in ( 22, 24, 26,99,108)
                   and TypeName =(select TypeName from Trees  
                                  where ID = a.id)
                ) c
                left outer join 
               (select Parents
                  from tTable
                 where type in (1, 2, 12, 15, 21)
                 group by Parents
                 ) d 
               on d.Parents = c.Tname
       )

Then ,i found the the returned recordcount is equal to the recordcount of table "Trees".
I guessed that: the join clause is the first step ,and the where clause is the second clause in the sql execution plan.
 select c.TypeName
          from (select tname,TypeName
                  from tTable
                 where Type in ( 22, 24, 26,99,108)
                   and TypeName =(select TypeName from Trees where ID = a.id) --then ,replaced by " id=10"
                ) c
                left outer join 
               (select Parents
                  from tTable
                 where type in (1, 2, 12, 15, 21)
                 group by Parents
                 ) d 
               on d.Parents = c.Tname

The clause upon returned no rows,because i replaced "ID=A.ID" WITH "ID=10" ,and ran it solely.

I ran another sql:
select a.ID, a.TypeName, b.ChildAmount
  from  
  Trees a
  left outer join 
  (select TypeName, count(*) as ChildAmount
                     from tTable
                    group by TypeName
              ) b 
  on b.TypeName = a.TypeName

This sql returned the same recordset as the first clause,nothing changed!
so ,That means the where clause always returns true,or
something in oracle i do not know. Otherwise,My guess is wrong.
So ,can anyone else tell me the truth?
oracle bug? Wrong guess?


Re: Execute Plan About My Sqls [message #275879 is a reply to message #275877] Tue, 23 October 2007 02:56 Go to previous messageGo to next message
Michel Cadot
Messages: 64153
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
You use an OUTER join so... not an Oracle bug but a developer one.

Regards
Michel
Re: Execute Plan About My Sqls [message #275918 is a reply to message #275879] Tue, 23 October 2007 06:28 Go to previous messageGo to next message
lzfhope
Messages: 67
Registered: July 2006
Member
Thanks!
Maybe ,i did not tell what i thought!
Could you pay more attetion to that ? Maybe that is not what think so.
Need you help!
Re: Execute Plan About My Sqls [message #275949 is a reply to message #275918] Tue, 23 October 2007 08:34 Go to previous messageGo to next message
Michel Cadot
Messages: 64153
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Do you know what is an outer join?
One property of outer join is that it does not reduce the result set.

SQL Reference, Chapter 9 SQL Queries and Subqueries, Section Joins

Regards
Michel
Re: Execute Plan About My Sqls [message #276086 is a reply to message #275949] Tue, 23 October 2007 21:33 Go to previous messageGo to next message
lzfhope
Messages: 67
Registered: July 2006
Member
In fact ,I have known that long ago!
My ability to writing
---
Maybe,the sql could be simply expressed like that:
select a.*,b* from a,b where b.id(+)=a.id and (......c.id=a.id) 

My question only has little connection with the outer join,but more connection with the where subquery .
The subquery quotes the main table's column like that:
...TypeName =(select TypeName from Trees
where ID = a.id)

The main sql and the join condition will return the normal result,but the where clause will filter the result.for the clause, in my view,when a.id=1 ,the subquery or the where clause should return false,but it returns true. why?
It is not necesary to talking about the join clause.
Can you tell me how the oracle sql engine parse and execute the where clause?
Re: Execute Plan About My Sqls [message #276091 is a reply to message #275877] Tue, 23 October 2007 22:33 Go to previous messageGo to next message
flyboy
Messages: 1832
Registered: November 2006
Senior Member
Quote:

Can you tell me how the oracle sql engine parse and execute the where clause?
Not sure what you are trying to achieve, but it is described in the documentation: where_clause of SELECT

Quote:

In fact ,I have known that long ago!
So you know, that
select c.TypeName
          from (select tname,TypeName
                  from tTable
                 where Type in ( 22, 24, 26,99,108)
                   and TypeName =(select TypeName from Trees where ID = a.id) --then ,replaced by " id=10"
                ) c
                left outer join 
               (select Parents
                  from tTable
                 where type in (1, 2, 12, 15, 21)
                 group by Parents
                 ) d 
               on d.Parents = c.Tname
is equivalent to
select c.TypeName
          from (select tname,TypeName
                  from tTable
                 where Type in ( 22, 24, 26,99,108)
                   and TypeName =(select TypeName from Trees where ID = a.id) --then ,replaced by " id=10"
                ) c
and I do not see the reason why you introduced the table D.

Also your replacement seems strange. For test, I would replace it with ALL IDs from TREES table and check whether it filters out anything from Trees table
select c.TypeName
          from (select tname,TypeName
                  from tTable
                 where Type in ( 22, 24, 26,99,108)
                   and TypeName =(select TypeName from Trees where ID IN (SELECT a.id FROM Trees a)
                ) c
Re: Execute Plan About My Sqls [message #285962 is a reply to message #276091] Thu, 06 December 2007 03:33 Go to previous message
lzfhope
Messages: 67
Registered: July 2006
Member
THANK YOU!
Previous Topic: function
Next Topic: Recycle number
Goto Forum:
  


Current Time: Sun Dec 11 00:35:32 CST 2016

Total time taken to generate the page: 0.04838 seconds