Hi,
santysharma is right. You are reading all the columns so full table scan
is necessary. Want you want to achieve is not doing the sort, but I doubt
that with only one table, that is that small this is worth while.
Having said that, if you want to use the index hint, you should use the
right syntax. Don't use any comma's in the hint description.
Regards
Stefan
santysharma wrote:
> well, you are selecting all rows and all columns from table and hence
> table scan has to used.
> You can improve upon order by clause by increasing sort area size in your
> session.
> hth
> santysharma
> "Sunil Bakshi" <Sunil_Bakshi_at_premierintl.com> wrote in message
> news:u3ki18iarq1ncc_at_corp.supernews.com...
>>
>> Hi All,
>>
>> I have a table that I need to query which has around 64 fields and around
>> 28,000 recs with a ORDERBY clause.
>> and it takes 12 secs. I am pasting the query below. I am also listing
>> all
>> the options I tried out. Please help if you have suggestions.
>>
>> SELECT
>> *
>> FROM
>> SYBASE.HW
>> ORDER BY
>> CLIENT, SSN, H_COVTP
>>
>> There is an index defined on CLIENT, SSN, H_COVTP called HW_IDX_01
>>
>> I analyzed the table with ANALYSE TABLE HW COMPUTE STATISTICS;
>>
>> I also changed the optimizer goal to FIRST_ROWS by using the statement
> ALTER
>> TABLE SET OPTIMIZER_GOAL = FIRST_ROWS.
>>
>> I tried to hint the optimizer by specifying the following
>> SELECT /*+ INDEX(HW, HW_IDX_01) */ * FROM SYBASE.HW ORDER BY CLIENT,
>> SSN, H_COVTP
>>
>> I am sure that the ORDER BY is talking time, but I have to specify it and
>> get it to work faster.
>>
>> I tried to use the EXPLAIN PLAN on each of the modified form of the query
>> and it still says TABLE ACCESS as FULL which is why I think I am not
> getting
>> the speed, Please let me know if I can get it work faster.
>>
>> Thank you very much for your help,
>> Sunil
>>
>>
>>
>>
Received on Tue Jan 08 2002 - 00:33:44 CST
