Can someone please explain why?

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!