🚀 go-pugleaf

U�ytkownik "Wayne Niddery [TeamB]" <wniddery@chaffaci.on.ca> napisa� w 
wiadomo�ci news:46ea9edd$1@newsgroups.borland.com...
> Karol Bieniaszewski wrote:
>>
>> FROM
>> TABLE_B B
>> INNER JOIN TABLE_A A ON B.ID_OBJ=A.ID
>> INNER JOIN TABLE_C C ON C.ID_OBJ=A.ID AND B.SYSTEM=C.SYSTEM AND
>> C.ZRODLO=:PARAM_X
>> WHERE
>> B.KOD=PARAM_Y
>> AND B.SYSTEM=PARAM_Z
>
> Don't know if it will change anything, but "C.ZRODLO=:PARAM_X" should be 
> in the Where clause, not in the join condition.
>
>> FROM
>> TABLE_B B
>> INNER JOIN TABLE_A A ON B.ID_OBJ=A.ID
>> INNER JOIN (TABLE_C C LEFT JOIN TABLE_D D ON C.ALARM=D.NAZWA) ON
>> C.ID_OBJ=A.ID AND B.SYSTEM=C.SYSTEM AND C.ZRODLO=:PARAM_X
>> WHERE
>> B.KOD=PARAM_Y
>> AND B.SYSTEM=PARAM_Z
>
> I have never seen any advantage in nesting joins, breaking them out as you 
> do below is more readable, and, IME, has no negative effect on 
> performance.
>
>> FROM
>> TABLE_B B
>> INNER JOIN TABLE_A A ON B.ID_OBJ=A.ID
>> INNER JOIN TABLE_C C  ON C.ID_OBJ=A.ID AND B.SYSTEM=C.SYSTEM AND
>> C.ZRODLO=:PARAM_X
>> LEFT JOIN TABLE_D D ON C.ALARM=D.NAZWA
>> WHERE
>> B.KOD=PARAM_Y
>> AND B.SYSTEM=PARAM_Z
>>
>> but without success
>> now A is NATURAL
>
> A natural scan on A is more easily understandable here because you clearly 
> have no conditions on table A, thus it makes more sense to simply scan it 
> and perform indexed lookups in the other tables. What performance are you 
> getting with this version compared to any of the others?
>
> I would try this:
>
> FROM
> TABLE_C C
> INNER JOIN TABLE_A A ON A.ID=C.ID_OBJ
> INNER JOIN TABLE_B B ON B.ID_OBJ=C.ID_OBJ AND B.SYSTEM=C.SYSTEM
> LEFT JOIN TABLE_D D ON D.NAZWA=C.ALARM
> WHERE
> C.ZRODLO=:PARAM_X
> AND B.KOD=:PARAM_Y
> AND B.SYSTEM=:PARAM_Z
>
> How does that change the plan and performance?
>
> -- 
now plan is :(
PLAN JOIN (JOIN (A NATURAL,C INDEX (IXA_TABLE_C__OBJ__ZRO__SYS),B INDEX 
(IXA_TABLE_B__KOD__SYSTEM, FK_TABLE_B__ID_OBJ)),D INDEX 
(IXAU__TABLE_D__NAZWA))



i write best (at this time) plan manually
PLAN JOIN (B INDEX (IXA_TABLE_B__KOD__SYSTEM), A INDEX (PK_TABLE_A__ID), C 
INDEX (IXA_TABLE_C__OBJ__ZRO__SYS))
but this query (where clause) can by modified by user
by type in search form

thanks for help
Karol Bieniaszewski