Re: Can someone please explain why?

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!
>
Received on Mon Oct 04 1999 - 12:28:23 CDT