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: Andy <andy.spaven_at_eps-hq.co.uk>
Date: Thu, 1 Aug 2002 15:38:41 +0100
Message-ID: <CFb29.257$9R.1282958@newsr2.u-net.net>


Whoops - ignore the , in the hint. It's just a space really :-))

select /*+ index(<table_name_or_alias> <index_name>) */ ....

"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 Thu Aug 01 2002 - 09:38:41 CDT

Original text of this message

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