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.

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)

Regards

Sandeep