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
> 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.
> 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
> 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,
> 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
> the speed, Please let me know if I can get it work faster.
> Thank you very much for your help,