|
View previous topic :: View next topic
|
| Author |
Message |
rockish
Active User
Joined: 05 Jun 2009
Posts: 185
Location: Planet Earth
|
|
|
|
Hello,
I have a query as follows.
| Code: |
SELECT T1ID
FROM TABLE1 F
, TABLE2 R
, TABLE3 P
WHERE
T1_CD = :H
AND T1_ID = T2_ID
AND T3_ID = T1_ID
AND T3_NB = T2_NB
AND EXISTS (SELECT 1
FROM TABLE2 B
WHERE B.T2_ID = R.T2_ID
AND B.T2_NB = R.T2_NB
AND T2_CD = :H) |
I am planning to rewrite the above query as follows. But I am just worried if there could be any difference between the output of these two queries. Could anyone comment if you can find differences in the way these queries might behave?
| Code: |
SELECT T1ID
FROM TABLE1 F
, TABLE2 R
, TABLE3 P
WHERE
T1_CD = :H
AND T1_ID = T2_ID
AND T3_ID = T1_ID
AND T3_NB = T2_NB
AND T2_CD = :H |
|
|
| Back to top |
|
 |
dick scherrer
Moderator Emeritus
Joined: 23 Nov 2006
Posts: 19244
Location: Inside the Matrix
|
|
|
|
Hello,
Seems like running a little test in spufi would give the answer immediately. . .
If something unexpected happens, someone here may be able to clarify. . . |
|
| Back to top |
|
|
rockish
Active User
Joined: 05 Jun 2009
Posts: 185
Location: Planet Earth
|
|
|
|
Thanks for the reply d.sch.
Indeed i ran a sample run and it gave same results, but as it involved host variables, it might not be the only case. I just wanted to confirm with thoughts from experts as well. |
|
| Back to top |
|
|
dick scherrer
Moderator Emeritus
Joined: 23 Nov 2006
Posts: 19244
Location: Inside the Matrix
|
|
|
|
Hello,
| Quote: |
| but as it involved host variables, it might not be the only case |
I don't understand the concern here. . .
Using host variables should not change the outcome of the query, unless there is something about your question that i misunderstand. |
|
| Back to top |
|
|
rockish
Active User
Joined: 05 Jun 2009
Posts: 185
Location: Planet Earth
|
|
|
|
I am sorry for not being clear on what I said.
What I meant to say was, I just randomly picked one value for the host variable, and I got same outputs. I was just afraid, if the test might fail for a different value for the host variable. I could not consolidate all possible host variable values to perform an extensive testing in a short time. That is why I preferred to post it here. |
|
| Back to top |
|
|
dick scherrer
Moderator Emeritus
Joined: 23 Nov 2006
Posts: 19244
Location: Inside the Matrix
|
|
|
|
Hello,
| Quote: |
| I am sorry for not being clear on what I said. |
Not to worry - it may take a couple of iterations, but eventually, we usually "get there" 
While not every specific value can be tested, different conditions can be tested.
I'd make sure that testing was done with values that will have multiple rows returned. . . |
|
| Back to top |
|
|
rockish
Active User
Joined: 05 Jun 2009
Posts: 185
Location: Planet Earth
|
|
|
|
| yeah.. Thanks d.sch.. I'll try to test maximum cases possible in the short span of time.. |
|
| Back to top |
|
|
dbzTHEdinosauer
Global Moderator
Joined: 20 Oct 2006
Posts: 6966
Location: porcelain throne
|
|
|
|
both queries have the same host variable.
the first query probably executes the EXIST against the table for every row queried by the outside select.
the second query just incorporates the conditional check against each row.
did you bother to run an explain? |
|
| Back to top |
|
|
ashimer
Active Member
Joined: 13 Feb 2004
Posts: 551
Location: Bangalore
|
|
|
|
On the first glance, your first query searches for T2_CD in TABLE2 where there is no dependency on T2_ID and T2_NB.
In second query T2_CD will depend on T2_ID and T2_NB's values which in turn depends on T1_ID and T3_NB.
As dick mentioned use test cases where you get multiple rows; to confirm. |
|
| Back to top |
|
|
GuyC
Senior Member
Joined: 11 Aug 2009
Posts: 1281
Location: Belgium
|
|
|
|
it looks like there could be a difference :
SQL 2 gives the rows of T2 where (T1_ID = T2_ID AND T3_NB = T2_NB) and T2_CD = :H
SQL 1 gives the rows of T2 where (T1_ID = T2_ID AND T3_NB = T2_NB) as long as there is 1 where T2_CD = :H
thus
If T2_ID, T2_NB is unique then the output is the same.
If not then SQL1 can return more rows than SQL2. |
|
| Back to top |
|
|
rockish
Active User
Joined: 05 Jun 2009
Posts: 185
Location: Planet Earth
|
|
|
|
Thank you all for your replies..
@Dick Brenholtz - I indeed ran EXPLAIN and got different access path for each of them. I thought it was obvious because we are eliminating a subquery altogether. Hence did not wanted to just depend on it.
@GuyC - You are pretty much correct. This query might depend on the uniqueness of T2_ID and T2_NB and unfortunately in my case they are not unique. So I think it wont work out.
Thank you all once again. |
|
| Back to top |
|
|
|
|
