Re: index usage

Richard

If the sort will occur in memory then I'd agree. Otherwise I'd be much more cautious. The idea of avoiding the index in order to get all the rows, and so save reading index blocks, is sound except that the size of the table pretty much guarantees that sorts to disk will occur and writes are way more expensive than reads typically. 3M rows even at 100 rows/8K block would be 240M of data. Thats a lot of sort space. These disk sorts will almost certainly outweight the benefits of avoiding reading an index and then the data.

To force the index try hinting it - select /*+ index(<table_name_or_alias>, <index_name>) */ ....

Andy

"Richard Foote" <richard.foote_at_bigpond.com> wrote in message news:jla29.50561$Hj3.150664_at_newsfeeds.bigpond.com...
> 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 - 09:36:01 CDT