how to force an index range scan?

Hi,

I am running 8.1.5 with the cost based optimizer. I have all statistics current and I have this one very simple query that is taking much too long, consuming way too many resources and using a horrendous execution plan.

The query is this:

select max(misc_id) from f_misc where account_id =564;

the table contains 1.7 million rows.
misc_id is indexed (it is also the primary key) account_id is indexed.

All indexes are enabled.
All indexes have been rebuilt. (I thought perhaps fragmentation in the index may have been the cause so I rebuilt them). All indexes have exact statistics computed.

The execution plan of the query shows a full table scan being employed, with an overall cost of 482. The query also is prone to the snapshot too old error if something happens to update the table while it is doing the full scan.

I tried using several hints:

select /*+ index(f_misc F_MISC_ACCOUNT_ID_IDX ) index(f_misc pk_f_misc ) */ max(misc_id) from f_misc where account_id =564;

I also tried the other index hints such as index_desc, index_ffs, etc...

When I do this the query plan and performance is much worse. It shows full index scans being employed with cost of ~10000 (not sure...because I'm still waiting for the query to come back). In the past this query has also suffered the snapshot too old error.

Right now, the only way I can get this query to use the plan that I want is to first:

alter session set optimizer_index_cost_adj=1; then run the query.
it comes back immediately with a cost of 6.

So, I do have a workaround...(I can set the cost_adj back to 100 after the query comes back)...but I'd rather use a hint.

Does anyone have any ideas on why for a 1.7 million row table with no joins, that the optimizer would elect to employ a full scan of both the tables and indexes. And if possible what the magic hint would be to force an index range scan?

thanks,
Gavin

Sent via Deja.com http://www.deja.com/
Before you buy. Received on Fri Sep 22 2000 - 14:56:03 CDT