Many left joins in a query

General
Enthusiast

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

2 REPLIES
Enthusiast

Re: Many left joins in a query

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.

Supporter

Re: Many left joins in a query

Check RECURSIVE queries.

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