Home » SQL & PL/SQL » SQL & PL/SQL » Why the difference?
Why the difference? [message #201015] Thu, 02 November 2006 04:28 Go to next message
sujay_1978
Messages: 3
Registered: November 2006
Location: Bangalore
Junior Member
Please take a look at the two queries
SELECT LOCATION,PARENT,LEVEL,'N','N' FROM
(SELECT * FROM
LOCHIERARCHY
WHERE SYSTEMID = 'ELECTRIC' AND LOCATION <> PARENT)
CONNECT BY PRIOR LOCATION = PARENT
START WITH PARENT = 'LCSES'

This one gives me correct result, i.e. around 112 records in the hierarchy

SELECT LOCATION,PARENT,LEVEL,'N','N' FROM LOCHIERARCHY
WHERE SYSTEMID = 'ELECTRIC' AND LOCATION <> PARENT
CONNECT BY PRIOR LOCATION = PARENT
START WITH PARENT = 'LCSES'

This one being essentially the same, though I specified where clause and connect by together on the same table, results in huge huge number of duplicate records being selected and gives me around 2.5 million. The actual distinct records are still 112 but total is much much more. I fixed the issue which obiviously was causing too much of a performance problem; but I am curious why the second one gives me duplicates

[Updated on: Thu, 02 November 2006 04:56]

Report message to a moderator

Re: Why the difference? [message #201052 is a reply to message #201015] Thu, 02 November 2006 06:43 Go to previous messageGo to next message
michael_bialik
Messages: 611
Registered: July 2006
Senior Member
Look at EXPLAIN of both queries.
Re: Why the difference? [message #201053 is a reply to message #201052] Thu, 02 November 2006 06:51 Go to previous messageGo to next message
sujay_1978
Messages: 3
Registered: November 2006
Location: Bangalore
Junior Member
Thank you. I shall do that, but it is not just a performance difference, one query returns 112 rows and the other 2.5 million. So I was wondering if there is a restriction against using where clause in combination with a connect by.
Re: Why the difference? [message #201085 is a reply to message #201053] Thu, 02 November 2006 08:15 Go to previous messageGo to next message
michael_bialik
Messages: 611
Registered: July 2006
Senior Member
IMHO there is a different order of execution/data access for each case.
Re: Why the difference? [message #201094 is a reply to message #201015] Thu, 02 November 2006 08:50 Go to previous messageGo to next message
ebrian
Messages: 2794
Registered: April 2006
Senior Member
My guess would be that Oracle is applying the predicates SYSTEMID = 'ELECTRIC' AND LOCATION <> PARENT after doing the CONNECT BY in the second case.
Re: Why the difference? [message #201283 is a reply to message #201094] Fri, 03 November 2006 06:46 Go to previous message
sujay_1978
Messages: 3
Registered: November 2006
Location: Bangalore
Junior Member
Yes, I had a look at two explain plans and oracle does seem to be applying the filter after it gets the connect by in the latter case. But that way too I am not sure how it managed to give me 2.5 million by duplicating an available 112 records. Whatever, the problem is solved and I shall be more careful with connect by and where clauses in future. Thanks for the responses./fa/1691/0/
Previous Topic: Diff between count(1) and count(*)
Next Topic: query
Goto Forum:
  


Current Time: Sun Dec 04 10:10:56 CST 2016

Total time taken to generate the page: 0.11618 seconds