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

[Quoted] A copy of this was sent to masyedkasim_at_hotmail.com (if that email address didn't require changing) On Tue, 16 Nov 1999 12:24:38 GMT, you wrote:

>Hi,
>
> I want to write an efficient join query with more
>than 3 tables. For example,
>
>SELECT
> OOT.NO,
> OOT.BUYER_TEXT,
> OOT.SLR_CODE,
> SLR.NAME,
> PSE.STE_CODE,
> PSE.PRT_ID,
> PSO.EFFECT_FROM_DATE,
> PSO.EFFECT_TO_DATE,
> PSO.PRIORITY
> FROM
> PRO_PART_SITES PSE, -- 80,000 rows
> PRO_PART_SRO_OPEITE_OOCS PSO, -- 800,000 rows
> PN_ORDER_CONTRACTS OOT, -- 1000 rows
> PRO_SUPPLIERS SLR -- 25,000 rows
> WHERE
> PSE.ORDER_FREEZE != 'Y' AND
> NVL(PSO.EFFECT_TO_DATE,SYSDATE) >= SYSDATE AND
> NVL(PSO.EFFECT_FROM_DATE,SYSDATE) <= SYSDATE AND
> OOT.SLR_CODE = SLR.CODE AND
> OOT.NO = PSO.OOT_NO AND
> PSO.PSE_ID = PSE.ID
>
>Is there any way of ordering the tables based on the no of rows and
>using columns in the join condition ( to speed up the retrieval ).
>

if you are using the CBO -- and have the tables analyzed, it will do it naturally and you can give HINTS to the query to help it along if need be (see [Quoted] the server tuning manual).

if you are using RBO, put the tables in order from right to left (backwards) in [Quoted] the order you want to join them. eg: if you want PN_ORDER_CONTRACTS OOT to be [Quoted] the 'driving' table, make it be LAST in the from list -- it gets processed from [Quoted] RIGHT to LEFT. Put the most discriminating predicates at the bottom of the [Quoted] where clause as they tend to get evaluated from the bottom up.

the cbo ignores the ordering of elements in the query. the rbo is sensitive to them.

>Good solutions would be appreciated.
>
>Thanks
>Syed.
>
>
>Sent via Deja.com http://www.deja.com/
>Before you buy.

-- 
See http://osi.oracle.com/~tkyte/ for my columns 'Digging-in to Oracle8i'...
[Quoted] Current article is "Part I of V, Autonomous Transactions" updated June 21'st
 
[Quoted] Thomas Kyte                   tkyte_at_us.oracle.com
Oracle Service Industries     Reston, VA   USA

Opinions are mine and do not necessarily reflect those of Oracle Corporation