Are you using any trunc functions while querying.
Do this. SELECT column_name,count(*) from small_table group by column_name;
Run this for all the tables. My guess is that atleast 30% of the rows in the
small table is for the value that you gave. So the optimizer is forced to read
the index first followed by data read. When you drop the index, it does a FTS.
Hence it is faster.
HTH
GovindanK-Original Message-
From: [EMAIL PROTECTED]Sent: 9/29/2003 6:01:55 AMTo: [EMAIL PROTECTED]Subject: Re: Big table, small tableI 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 in!
dexes 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:[EMAIL PROTECTED] http://WaterMatters.org -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: INET: [EMAIL PROTECTED] 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: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line con!
taining: 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). .
___Get Your 10MB account for FREE at http://mail.arabia.com !Access MILLIONS of JOBS NOW!
