Home » SQL & PL/SQL » SQL & PL/SQL » Join query -- Is there a better way?
Join query -- Is there a better way? [message #342197] Thu, 21 August 2008 11:59 Go to next message
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 #342201 is a reply to message #342197] Thu, 21 August 2008 12:16 Go to previous messageGo to next message
annagel
Messages: 220
Registered: April 2006
Senior Member
Better than what?
Re: Join query -- Is there a better way? [message #342206 is a reply to message #342197] Thu, 21 August 2008 12:28 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
And why do you need to join 4 tables or use subqueries if all data are from tableA?

Regards
Michel
Re: Join query -- Is there a better way? [message #342213 is a reply to message #342197] Thu, 21 August 2008 12:47 Go to previous messageGo to next message
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 #342214 is a reply to message #342213] Thu, 21 August 2008 12:49 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
There is no better way to write it.

Regards
Michel
Re: Join query -- Is there a better way? [message #342216 is a reply to message #342197] Thu, 21 August 2008 13:00 Go to previous messageGo to next message
BlackSwan
Messages: 25036
Registered: January 2009
Location: SoCal
Senior Member
Does below perform any better?

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));
Re: Join query -- Is there a better way? [message #342415 is a reply to message #342197] Fri, 22 August 2008 05:07 Go to previous messageGo to next message
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 Go to previous message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
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

Previous Topic: File Line Count
Next Topic: Selecting Distinct values
Goto Forum:
  


Current Time: Sun Dec 04 20:19:48 CST 2016

Total time taken to generate the page: 0.05301 seconds