Re: Help - Simple SQL query doing full table scans

You're doing a 'select *', so that returns all columns, which aren't in your index. Hence we are going to have to visit the table in any case. You then don't have a 'where' clause, so we are going to be returning 100% of the rows in the table. Indexes are only used when less than about 5% of the rows are going to be returned (unless, as I think you are hinting at, but which doesn't apply in your case, the entire query can be resolved within the index itself).

You're then using the incorrect syntax for the index hint.

But the real point is that forcing the thing to use the index is not going to help you very much (because if it did, the optimizer would have chosen to use it in the first place!).

If it is the 'order by' that is causing it to be too slow, make sure that the ordering can take place in memory by having a decent-sized sort_area_size parameter for your session.

Oh -and in future, always make sure you include the platform and Oracle version in your posts. Questions related to the optimizer are nearly always very version-sensitive.

Regards
HJR "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 - 13:31:56 CST