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 Mon Jan 07 2002 - 19:45:32 CST
