At 01:00 PM 1/12/2003 +1300, you wrote:

>Alan McDonald wrote:
>
> >for First case (Records read 45)
> >
> >
> >>SQL is
> >>select cln.*, anm.*
> >>from "Client_Main" cln left outer join "Animal_Main" anm
> >> on anm."Owner" = cln."Ref_Number"
> >>where cln."FName" starting with 'SA'
> >>
> >>Plan is
> >> [PLAN JOIN (CLN INDEX (CLN_BY_FNAME),ANM INDEX (Anm_By_Owner))]
> >>
> >>_________________________________________________________________
> >>for Second case (Records read 360)
> >>
> >>SQL is
> >>select cln.*, anm.*
> >>from "Client_Main" cln left outer join "Animal_Main" anm
> >> on anm."Owner" = cln."Ref_Number"
> >>where cln."FName" starting with 'SA'
> >>order by cln."FName" desc
> >>
> >>Plan is [PLAN SORT (JOIN (CLN INDEX (CLN_BY_FNAME),ANM INDEX
> >>(Anm_By_Owner)))]
> >>
> >>Regards
> >>
> >>Sandeep
> >>
> >>
> >
> >I imagine the 45 records returned is incomplete? (as opposed to 360 records
> >some of which do not apply to the starting with clause?)
> >Is "Ref_Number" indexed? a foreign key?
> >When you say slow - how slow?
> >And how many records are in these tables?
> >I am assuming that the in the 360 records you are getting duplicates?
> >
> >
>Ref_Number is indexed field.
>
>45 records returned is incomplete true, but then why should it change
>when using Order by.

Because the whole set has to be fetched for the sorting.

There is potentially a whole heap of null rows (all owners starting with SA
who don't have animals). If there are no owners who don't have animals,
what do you need the outer join for?

>In Client_Main there are 11336 records and in Animal_Main there are
>25476 records, each client acn have multiple animals.
>
>For single user its ok but when the number of users is 20+ the user has
>to wait 2+ seconds. (I know it also depends on what other users are doing)

Two seconds is completely absurd for fetching 45? 360? records. If you
don't need those other X,000 null records, drop the outer join. Also, why
not parameterise that WHERE clause?

Consider adding a little typing to your life and specifying column
lists. You don't need to double-up the join column from both streams, for
starters...I bet there's other stuff in those .* lists that you don't need
as well.

/heLen