Re: index usage

Hi Hegyvari,

The very simple answer is you do *NOT* want to use the index !!

If you have no where clause, you basically want to select all 3 million rows (do you really ?). To use an index to select all 3 million rows means you want to perform 3 million logical reads (plus the number of blocks in your index). A good many of these will no doubt be physical reads as well.

By performing a full table scan, lets say you have 10 rows per block (that's 300,000 blocks). Lets say you have a multiblock read set to 8. That means you can read this table is approximately 300,000/8 = 37,500 logical I/Os. All things being equal the close to 2,750,000 ish logical reads you save more than compensates for the sort you now incur.

My strong recommendation is focus on tuning the sort (by setting an appropriate sort_area_size and ensuring your temp tablespace is "tuned") rather than trying to use the index. Using the index will only make things go slower still.

Good luck

Richard
"Hegyvari Krisztian" <hegyvari_at_ardents.hu> wrote in message news:9003d46f.0208010500.3d228d13_at_posting.google.com...
> Hello,
>
> I have a table with more than 3 million records, 15 columns. I have an
> index on the first two columns. When I do a select * from mytable
> order by col1,col2 the query takes ages to complete. According to the
> plan, oracle does not use the index but sorts the records after doing
> a full table scan. I more or less understand why the optimizer chooses
> this path, but how can I change its decision and force it to use the
> index. I tried to use the use index hint to no avail.
>
> Please cc the answers to hegyvari_at_ardents.
>
> Any help is highly appreciated.
>
> Best Regards,
>
> Hegyvari Krisztian
Received on Thu Aug 01 2002 - 08:12:17 CDT