Re: How to form an efficient Join query with more than 3 tables...?

masyedkasim_at_hotmail.com wrote:
> I want to write an efficient join query with more
> than 3 tables. For example,

It really depends upon the type of optimisation the database server is performing, i.e. rule- or cost- based optimisation. If the former, list the tables in order of least to most restrictive, with the one which is the most restrictive last, in your case it would be PRO_OPEN_ORDER_CONTRACTS,
i.e.

    FROM

       PRO_PART_SITE_OOCS PSO,       -- 800,000 rows
       PRO_PART_SITES PSE,           -- 80,000 rows
       PRO_SUPPLIERS SLR,             -- 25,000 rows
       PRO_OPEN_ORDER_CONTRACTS OOT -- 1000 rows

If your server is performing cost-based optimisation, ensure that your tables have all been analysed and it won't matter (but you can force the optimiser to use a particular access-path by using hints embedded in the query).

-- 
Regards,
John.
p.s. remove xxx from the email address if you want to reply.