Re: Can someone please explain why?

It seems to me that in this case, the IO cost of searching the index and then going to the data pages to get the data is higher than scanning the table directly. It appears that the optimizer may be making the wrong choice in this case. I'm not an Oracle expert by any means, but I'd like to see if the results continue to show this "lack of reason" after the statistics for the table have been generated.

Carlos.
In article <939058193.7474.0.pluto.d4ee154e_at_news.demon.nl>,   "Sybrand Bakker" <postmaster_at_sybrandb.nospam.demon.nl> wrote:
> One lookup in an index for a primary key costs 4 Oracle blocks.
> In this case you could calculate how much I/O you would get if you
would
> retrieve all records using the index.
> You could also calculate the I/O if you use a full table scan, simply
the
> size of the table . In this case Oracle will read more than one block
in one
> request (usually 8 or 16, depending on db_file_multiblock_read in
> init<sid>.ora). This will not happen using index lookup, one block
will use
> one request.
> The bottom line is it will usually take less I/O when you select a
> substantial part of your table NOT using the index.
>
> Hth,
>
> --
> Sybrand Bakker, Oracle DBA
>
> Andy <abruskoNOabSPAM_at_binney-smith.com.invalid> wrote in message
> news:07d1af35.cecdb9c8_at_usw-ex0102-014.remarq.com...
> > I have an Oracle table with 343,689 rows. One of the
> > columns in the table is "state" and there is a non-unique
> > index on state.
> >
> > I ran the following query:
> > select sum(charged$) from frt.freight where state = 'WY';
> > There are 203 rows in the table with "state" = to "WY".
> > The runtimes with the index in place were about 2-3
> > seconds. When I removed the index on "state" and ran the
> > query, it ran consistanly at 20 seconds.
> > It makes perfect sense to me in this case that the query
> > runs much faster with the index in place compared to the
> > runtimes of when the index is not in place. What does not
> > make sense to me is the following:
> >
> > When I run the query with state = "CA"
> > (select sum(charged$) from frt.freight where state = 'CA';)
> > and the index is in place, the query runs in about 29-32
> > seconds. There are 38,359 rows with state = "CA" in the
> > table. When I remove the index and run this query, it
> > actually runs faster...rangeing from 22-23 seconds
> > consistantly.
> >
> > Why does the query run faster with the index in place when
> > the query returns 203 rows, but run slower with the index
> > in place when the query returns 38,359 rows even though I
> > am selecting against the same column in both cases?
> >
> > Hope I explained this clearly...does anyone know why this
> > is happening?
> >
> > Appreciate your help!
> >
> > Thanks,
> > Andy
> >
> >
> >
> > * Sent from RemarQ http://www.remarq.com The Internet's Discussion
Network
> *
> > The fastest and easiest way to search and participate in Usenet -
Free!
> >
>
>

Sent via Deja.com http://www.deja.com/
Before you buy. Received on Mon Oct 04 1999 - 17:21:43 CDT