All Forums General
karthik_2244 36 posts Joined 12/10
27 Nov 2011
Many left joins in a query

Hi,

We have a table which has a relationship between customers:

ex:  Table A

child id | Relationship id

A|B

B|C

C|D

Need to populate a final table with 20 levels.

like A-B-C-D etc

right now query is having 20 left joins on Table A with relationship id = child id.

As the data is very huge in Table A, facing spool space issue. Collect stats also didnt help much on performance.

 

Need a way to tune the performance.

 

Thanks,

Karthik. N

RB185048 33 posts Joined 09/08
27 Nov 2011

May be you can use the work tables to join other tables in one sql and use that in final sql for joining with table A.

I have used same type of strategy when I have some repeating joins  in my views.

But this adds a maintenence of refreshing the work tables.

 

ulrich 816 posts Joined 09/09
28 Nov 2011

Check RECURSIVE queries.

But how do you know which record is the root (first) one in your list?

feel free to donate bitcoin:12kgAUHFUqvG2sQgaRBXFhCwyf9HXdkGud

You must sign in to leave a comment.