| Join query -- Is there a better way? [message #342197] |
Thu, 21 August 2008 11:59  |
aparangi_rhl
Messages: 10
Registered: January 2006
|
Junior Member |
|
|
Hi all,
I have the following tables,
ProdTable(PROD_ID NUMBER, DESC VARCHAR2(10));
TableA(ID NUMBER, CREATION_TIME DATE);
TableB(ID NUMBER, TableA_ID NUMBER); -- TableA_ID is a foreign key to ID column in TableA
TableC(ID NUMBER,TableB_ID NUMBER); -- TableB_ID is a foreign key to ID column in TableB
TableD(TableC_ID NUMBER, PROD_ID NUMBER); -- TableC_ID is a foreign key to ID column in TableC. PROD_ID is a foreign key to PROD_ID column in ProdTable. (Table_C,PROD_ID) is a composite primary key in TableD.
There are indexes on all foreign keys including indexes on TableC_ID and PROD_ID columns in TableD.
All the tables have close to 10million rows.
My requirement is to generate the output TableC_ID, PROD_ID, CREATION_TIME (which is from TableA).
This output can be generated using a JOIN of the necessary 4 tables or by using inline queries in the FROM clause.
Is there any other better/efficient way to write a query to generate the output?
|
|
|
|
|
|
|
|
| Re: Join query -- Is there a better way? [message #342213 is a reply to message #342197] |
Thu, 21 August 2008 12:47  |
aparangi_rhl
Messages: 10
Registered: January 2006
|
Junior Member |
|
|
The complete data is not in TableA. I want data from TableD and TableA. TableC_ID and PROD_ID are from TableD and CREATION_TIME is from TableA.
The Primary-Foreign key relationships are like this.
TableC_ID(TableD) --> ID(TableC, also a PK)
TableB_ID(TableC) --> ID(TableB, also a PK)
TableA_ID(TableB) --> ID(TableA, also a PK)
The ID column in Tables TableA, TableB, TableC will have different values and are not related in any way. I hope all the relationships are clear.
Please post if anyone wants more clarifications.
A JOIN query like
SELECT D.TableC_ID, D.PROD_ID, A.CREATION_TIME
FROM TableA A, TableB B, TableC C, TableD D
where A.ID = B.TableA_ID AND
B.ID = C.TableB_ID AND
C.ID = D.TableC_ID;
will produce the result.
But each table has close to 10 million rows. So, can anyone suggest any other way to write the query which will be better in terms of performance/time taken to execute.
|
|
|
|
|
|
|
|
| Re: Join query -- Is there a better way? [message #342415 is a reply to message #342197] |
Fri, 22 August 2008 05:07 |
aparangi_rhl
Messages: 10
Registered: January 2006
|
Junior Member |
|
|
I checked the EXPLAIN PLAN for
SELECT D.TABLEC_ID,
D.PROD_ID,
A.CREATION_TIME
FROM TABLEA A,
TABLED D
WHERE A.ID IN (SELECT B.TABLEA_ID
FROM TABLEB B
WHERE B.ID IN (SELECT C.TABLEB_ID
FROM TABLEC C
WHERE C.ID = D.TABLEC_ID));
It is equivalent in perfomance to the earlier query.
Thanks a lot for your reply!
|
|
|
|
| Re: Join query -- Is there a better way? [message #342417 is a reply to message #342415] |
Fri, 22 August 2008 05:10 |
 |
Michel Cadot
Messages: 68718
Registered: March 2007
Location: Saint-Maur, France, https...
|
Senior Member
Account Moderator |
|
|
Please read OraFAQ Forum Guide, especially "How to format your post?" section.
Make sure that lines of code do not exceed 80 characters when you format.
Indent the code (See SQL Formatter).
Use the "Preview Message" button to verify.
Do you see the difference between your post and Ana's one?
If your statistics are up to date the queries are equivalent and Oracle knows when to use the former or the latter one.
Ana's query is better from a logical point of view: if you don't need a column in the select clause then use a subquery and not a join.
Regards
Michel
[Updated on: Fri, 22 August 2008 05:10] Report message to a moderator |
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
