Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: index usage

Re: index usage

From: Richard Foote <richard.foote_at_bigpond.com>
Date: Fri, 2 Aug 2002 15:50:00 +1000
Message-ID: <EYo29.51039$Hj3.151692@newsfeeds.bigpond.com>


Hi Andy,

Hate to disagree but ...

With my basic statistics, the use of the index was approximately *100x* more expensive. If there are actually more rows per block than 10, it would be even more expensive. If you use parallel processes, a FTS could become even more efficient (10 effective PP and now we looking in the order of 1000x magnitude).

If you tune the sort such that the sort_area_size is as large as resources and effectiveness allow, then the additional costs of the sort, albeit expensive should not even come close to overtaking the costs of the index scan.

But of course there are a lot of ifs and buts so don't take my word for it, test the thing. Hegyvari, if you're there, set timing on, run the thing with the index (and no sort), run the thing with no index (but with sort) and see which comes out on top.

I know what I'll put my money on :)

Cheers

Richard
"Andy" <andy.spaven_at_eps-hq.co.uk> wrote in message news:2Db29.256$9R.1284024_at_newsr2.u-net.net...
> 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 Fri Aug 02 2002 - 00:50:00 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US