From oracle-l-bounce@freelists.org Thu May 5 07:55:32 2005 Return-Path: Received: from air891.startdedicated.com (root@localhost) by orafaq.com (8.12.10/8.12.10) with ESMTP id j45CtWoY027327 for ; Thu, 5 May 2005 07:55:32 -0500 X-ClientAddr: 206.53.239.180 Received: from turing.freelists.org (freelists-180.iquest.net [206.53.239.180]) by air891.startdedicated.com (8.12.10/8.12.10) with ESMTP id j45CtW4Z027323 for ; Thu, 5 May 2005 07:55:32 -0500 Received: from localhost (localhost [127.0.0.1]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id 860531906B0; Thu, 5 May 2005 06:52:56 -0500 (EST) Received: from turing.freelists.org ([127.0.0.1]) by localhost (turing [127.0.0.1]) (amavisd-new, port 10024) with ESMTP id 17395-03; Thu, 5 May 2005 06:52:56 -0500 (EST) Received: from turing (localhost [127.0.0.1]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id 0C16719068A; Thu, 5 May 2005 06:52:56 -0500 (EST) X-MimeOLE: Produced By Microsoft Exchange V6.5.7226.0 Content-class: urn:content-classes:message MIME-Version: 1.0 Content-type: text/plain; charset=us-ascii Content-Transfer-Encoding: 8bit Subject: RE: Partitioned tables & indexes Date: Thu, 5 May 2005 07:51:03 -0400 Message-ID: X-MS-Has-Attach: X-MS-TNEF-Correlator: Thread-Topic: Partitioned tables & indexes Thread-Index: AcVQ7I8wilHxDMeZS6CxQnz9BJ0mGQAe+Eng From: "Mercadante, Thomas F (LABOR)" To: , "Oracle-L Freelists" X-OriginalArrivalTime: 05 May 2005 11:51:04.0256 (UTC) FILETIME=[B73C5400:01C55168] X-archive-position: 19363 X-ecartis-version: Ecartis v1.0.0 Sender: oracle-l-bounce@freelists.org Errors-To: oracle-l-bounce@freelists.org X-original-sender: Thomas.Mercadante@labor.state.ny.us Precedence: normal Reply-To: Thomas.Mercadante@labor.state.ny.us X-list: oracle-l X-Virus-Scanned: by amavisd-new-20030616-p9 (Debian) at avenirtech.net X-Spam-Level: X-Spam-Checker-Version: SpamAssassin 2.63 (2004-01-11) on air891.startdedicated.com X-Spam-Status: No, hits=0.1 required=5.0 tests=AWL autolearn=ham version=2.63 It looks to me like you have a design problem. You partitioned by month but you are querying by quarter. Maybe you should have partitioned by quarter - you know - another column in the table that a trigger would populate that could hold values like 200501 which means Jan. thru March of 2005? -----Original Message----- From: oracle-l-bounce@freelists.org [mailto:oracle-l-bounce@freelists.org] On Behalf Of sol beach Sent: Wednesday, May 04, 2005 5:02 PM To: Oracle-L Freelists Subject: Partitioned tables & indexes Oracle v9.2 I'll admit I don't have a lot of real world experience using partitioned tables or indexes. I've inherited an application which has a couple of tables partitioned on "DATE_CREATED"; where each partition contains 1 month data. Records are ONLY inserted into this table at a rate about a million per day=3D . There is an index (partitioned) on the DATE_CREATED column. I've run EXPLAIN PLANS on a half dozen or more SQL queries this morning all of which have a where clause similar to the following: WHERE date_created >=3D3D TO_DATE('2005-01-01 00:00:00','YYYY-MM-DD HH24:MI=3D :SS') AND date_created < TO_DATE('2005-05-04 00:00:00','YYYY-MM-DD HH24:MI:SS') NONE of queries ever used the index & always did a FTS. What could be done to actually start using this index? The bottom line problem is that queries against this table are just taking longer & longer to complete. Any ideas, hints, suggestions or Fine Manual names so I can RTFM would be welcomed. TIA! -- http://www.freelists.org/webpage/oracle-l -- http://www.freelists.org/webpage/oracle-l