Big table, small table

From: <Sherrie.Kubis_at_swfwmd.state.fl.us>
Date: Mon, 29 Sep 2003 04:59:55 -0800
Message-ID: <F001.005D15D1.20030929045955@fatcity.com>

I have five tables, three are > 3 million rows, one is a little more than 1 million, one a little less than one million. All tables are structured the same, with the same indexing, just different types of data. There is a date column
with a normal index.

A query with a 'between' on the date column against the largest table screams. Any of the largest tables are
acceptable performance. The smallest is extremely slow, while the million row table sits around for 20 minutes
or so.

If I remove the index from the million row table, I get acceptable results. All of the tables and indexes are
analyzed.

Why would taking an index off make this query faster? What doesn't the CBO know that not using an index
is the best path?

---

Sherrie Kubis
Southwest Florida Water Management District 2379 Broad Street
Brooksville FL 34604-6899

Phone: (352) 796-7211, Ext. 4033
Fax: (352) 754-6776
Email: Mailto:Sherrie.Kubis_at_swfwmd.state.fl.us http://WaterMatters.org

--

Please see the official ORACLE-L FAQ: http://www.orafaq.net
--

Author:
  INET: Sherrie.Kubis_at_swfwmd.state.fl.us

Fat City Network Services    -- 858-538-5051 http://www.fatcity.com
San Diego, California        -- Mailing list and web hosting services
---------------------------------------------------------------------

To REMOVE yourself from this mailing list, send an E-Mail message to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). Received on Mon Sep 29 2003 - 07:59:55 CDT