From oracle-l-bounce@freelists.org Wed May 5 08:26:16 2004 Return-Path: Received: from air189.startdedicated.com (root@localhost) by orafaq.com (8.11.6/8.11.6) with ESMTP id i45DPlN05435 for ; Wed, 5 May 2004 08:25:57 -0500 X-ClientAddr: 206.53.239.180 Received: from turing.freelists.org (freelists-180.iquest.net [206.53.239.180]) by air189.startdedicated.com (8.11.6/8.11.6) with ESMTP id i45DPb605409 for ; Wed, 5 May 2004 08:25:47 -0500 Received: from localhost (localhost [127.0.0.1]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id 8B14272CE56; Wed, 5 May 2004 08:16:20 -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 22170-94; Wed, 5 May 2004 08:16:20 -0500 (EST) Received: from turing (localhost [127.0.0.1]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id CD4DE72CE54; Wed, 5 May 2004 08:16:19 -0500 (EST) Received: with ECARTIS (v1.0.0; list oracle-l); Wed, 05 May 2004 08:15:05 -0500 (EST) X-Original-To: oracle-l@freelists.org Delivered-To: oracle-l@freelists.org Received: from localhost (localhost [127.0.0.1]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id 9A4EF72CC19 for ; Wed, 5 May 2004 08:14:52 -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 22170-61 for ; Wed, 5 May 2004 08:14:52 -0500 (EST) Received: from mail.sagelogix.com (unknown [69.15.85.3]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id 4770372CB3B for ; Wed, 5 May 2004 08:14:52 -0500 (EST) Received: by mail.sagelogix.com (Postfix, from userid 70101) id 6ECEF31798; Wed, 5 May 2004 07:17:00 -0600 (MDT) Received: from ocs.sagelogix.com (ocs.sagelogix.com [192.168.25.20]) by mail.sagelogix.com (Postfix) with ESMTP id 2EFD231797 for ; Wed, 5 May 2004 07:16:50 -0600 (MDT) Received: from 0-2pool82-55.nas33.thornton1.co.us.da.qwest.net by ocs.sagelogix.com with ESMTP id 1356301083759342; Wed, 05 May 2004 06:15:42 -0600 User-Agent: Microsoft-Entourage/10.1.4.030702.0 Date: Wed, 05 May 2004 07:29:14 -0600 Subject: Re: Partition Pruning From: Tim Gorman To: Message-ID: In-Reply-To: <20040505131638.24942.qmail@web20721.mail.yahoo.com> Mime-version: 1.0 Content-type: text/plain; charset=US-ASCII Content-Transfer-Encoding: 8bit X-Spam-Checker-Version: SpamAssassin 2.63 (2004-01-11) on mail.sagelogix.com X-Spam-Level: X-Spam-Status: No, hits=-4.9 required=2.3 tests=BAYES_00 autolearn=ham version=2.63 X-Virus-Scanned: by amavisd-new at freelists.org X-archive-position: 4425 X-ecartis-version: Ecartis v1.0.0 Sender: oracle-l-bounce@freelists.org Errors-To: oracle-l-bounce@freelists.org X-original-sender: tim@sagelogix.com Precedence: normal Reply-To: oracle-l@freelists.org X-list: oracle-l X-Virus-Scanned: by amavisd-new at freelists.org Sanjay, Partition pruning is the Oracle optimizer limiting the scope of your query according to the criteria you have specified either explicitly in the FROM clause (i.e. using the PARTITION or SUBPARTITION clauses) or implicitly in the WHERE clause (i.e. by referencing the partition-key column). Partition pruning is *not* the act of simply scanning a partitioned table or partitioned index. Performance gains from partitioning do not come from the mere presence of partitioning, but by how you use it. If you do not give the Oracle optimizer the opportunity to prune by telling it how to do so (either explicitly or implicitly), then you will scan all partitions. What you are seeing is expected behavior. Hope this helps... -Tim on 5/5/04 7:16 AM, Sanjay Mishra at smishra_97@yahoo.com wrote: > Tim > > Is it also the casse even if I have local partitioned index on Mgrid. Is there > any other way that I can put partition as clause in mine query to use the > particular partitions atleast for some of mine report. > > Thanks for your input > Sanjay > Tim Gorman wrote: > You partitioned the MANAGER table by MGRID, not by EMPID. Oracle can't > perform pruning unless your query is referencing the partition-key column. ---------------------------------------------------------------- Please see the official ORACLE-L FAQ: http://www.orafaq.com ---------------------------------------------------------------- To unsubscribe send email to: oracle-l-request@freelists.org put 'unsubscribe' in the subject line. -- Archives are at http://www.freelists.org/archives/oracle-l/ FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html -----------------------------------------------------------------