| Why the difference? [message #201015] |
Thu, 02 November 2006 04:28  |
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 #201053 is a reply to message #201052] |
Thu, 02 November 2006 06:51  |
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 #201283 is a reply to message #201094] |
Fri, 03 November 2006 06:46 |
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
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
