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

About your query,

If you have masters without detail(s) records, some record will not be displayed because of your
OOT.SLR_CODE = SLR.CODE AND
> > OOT.NO = PSO.OOT_NO AND
> > PSO.PSE_ID = PSE.ID
AND conditions...

To resolve this, you can create stored function to retrieve the info of the details and make the query only on a master table.

O. Régis
MCSE+I, Oracle CP.
Switzerland

Thomas Kyte <tkyte_at_us.oracle.com> wrote in message news:U2AxOFY6DldvgSzVLQmmr2gmkMPM_at_4ax.com...
> 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
> the server tuning manual).
>
> if you are using RBO, put the tables in order from right to left
(backwards) in
> the order you want to join them. eg: if you want PN_ORDER_CONTRACTS OOT
to be
> the 'driving' table, make it be LAST in the from list -- it gets processed
from
> RIGHT to LEFT. Put the most discriminating predicates at the bottom of
the
> 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'...
> Current article is "Part I of V, Autonomous Transactions" updated June
21'st
>
> 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 Received on Tue Nov 16 1999 - 18:07:18 CET