From oracle-l-bounce@freelists.org Wed Aug 18 15:22:02 2004 Return-Path: Received: from air189.startdedicated.com (root@localhost) by orafaq.com (8.11.6/8.11.6) with ESMTP id i7IKM2H32411 for ; Wed, 18 Aug 2004 15:22:02 -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 i7IKM2I32406 for ; Wed, 18 Aug 2004 15:22:02 -0500 Received: from localhost (localhost [127.0.0.1]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id 3784372CCB3; Wed, 18 Aug 2004 15:26:58 -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 17614-47; Wed, 18 Aug 2004 15:26:58 -0500 (EST) Received: from turing (localhost [127.0.0.1]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id 81DE372C0AC; Wed, 18 Aug 2004 15:26:57 -0500 (EST) Received: with ECARTIS (v1.0.0; list oracle-l); Wed, 18 Aug 2004 15:25:31 -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 DC86E72CC35 for ; Wed, 18 Aug 2004 15:25:30 -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 17060-72 for ; Wed, 18 Aug 2004 15:25:30 -0500 (EST) Received: from ptb-relay01.plus.net (ptb-relay01.plus.net [212.159.14.212]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id 1FF7472CDEB for ; Wed, 18 Aug 2004 15:25:30 -0500 (EST) Received: from [80.229.0.239] (helo=samantha) by ptb-relay01.plus.net with smtp (Exim) id 1BxX0m-000IhA-3E for oracle-l@freelists.org; Wed, 18 Aug 2004 20:26:20 +0000 Message-ID: <001e01c48561$a9e9a4c0$0100000a@lan> From: "RSL" To: Subject: Partition pruning problem Date: Wed, 18 Aug 2004 21:26:37 +0100 MIME-Version: 1.0 Content-type: text/plain X-Priority: 3 X-MSMail-Priority: Normal X-Mailer: Microsoft Outlook Express 6.00.2800.1437 X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2800.1441 X-Virus-Scanned: by amavisd-new at freelists.org Content-Transfer-Encoding: 8bit X-archive-position: 7851 X-ecartis-version: Ecartis v1.0.0 Sender: oracle-l-bounce@freelists.org Errors-To: oracle-l-bounce@freelists.org X-original-sender: rob.langmuir@joking.plus.com Precedence: normal Reply-To: oracle-l@freelists.org X-list: oracle-l X-Virus-Scanned: by amavisd-new at freelists.org Background Info.. Oracle version 9.2.0.4 These tables are Informatica tables Main Fact table IA_PURCH_SCHLNS (partitioned on "ordered_on_dk" - julian date ) As most queries have predicate involving "ordered_on_dk" it was decided to partition on this attribute. 1st query (below) is built using Poweranalyzer tool The partition key value has not been determined before partition is accessed. CBO appears to have decided to full scan Fact table first. In the 2nd query (below) modified by me to include literal value ........ AND DWSPSCH.ORDERED_ON_DK = 2453235 partition pruning occurs ( as expected ) Is there anyway I can get the 1st query to get the "partitioned date_key value" before it goes accessing the Fact table ? Thanks very much ...../Bob SELECT DWSDEPOTBLOC.BUSN_LOC_NUM, TO_DATE(TO_CHAR(DWSDEPOTDATE.CAL_DAY_DT, 'yyyy-MM-dd'), 'yyyy-MM-dd'), DWSSPLR.SUPPLIER_NUM || ':' || DWSSPLR.SUPPLIER_NAME, (SUM(DWSPSCH.RECEIVED_QTY)) FROM IA_BUSN_LOCS DWSDEPOTBLOC, IA_SUPPLIERS DWSSPLR, IA_PRODUCTS DWSPROD, IA_DATES DWSDEPOTDATE, IA_PURCH_SCHLNS DWSPSCH WHERE DWSPSCH.STORAGE_LOC_KEY = DWSDEPOTBLOC.BUSN_LOC_KEY !!!!!*****AND DWSPSCH.ORDERED_ON_DK = DWSDEPOTDATE.DATE_KEY*****!!!!! AND DWSPSCH.SUPPLIER_KEY = DWSSPLR.SUPPLIER_KEY AND DWSPSCH.PRODUCT_KEY = DWSPROD.PRODUCT_KEY AND TO_CHAR(DWSDEPOTDATE.CAL_DAY_DT, 'yyyy-MM-dd') IN (TO_CHAR(SYSDATE-1,'yyyy-MM-dd')) AND DWSDEPOTBLOC.BUSN_LOC_NUM IN ('THA') AND DWSPROD.DIVISION_CODE = 'F01' GROUP BY DWSDEPOTBLOC.BUSN_LOC_NUM, TO_DATE(TO_CHAR(DWSDEPOTDATE.CAL_DAY_DT, 'yyyy-MM-dd'), 'yyyy-MM-dd'), DWSSPLR.SUPPLIER_NUM || ':' || DWSSPLR.SUPPLIER_NAME ORDER BY 1, 3 Rows Execution Plan ------- --------------------------------------------------- 0 SELECT STATEMENT GOAL: CHOOSE 20 SORT (GROUP BY) 218 HASH JOIN 1 TABLE ACCESS GOAL: ANALYZED (BY INDEX ROWID) OF 'IA_DATES' 1 INDEX GOAL: ANALYZED (RANGE SCAN) OF 'CHAR_DAY_DT' (NON-UNIQUE) 20662 HASH JOIN 3383 TABLE ACCESS GOAL: ANALYZED (FULL) OF 'IA_SUPPLIERS' 20662 HASH JOIN 1039 INDEX GOAL: ANALYZED (FAST FULL SCAN) OF 'IA_PRODUCTS_IDX_008' (NON-UNIQUE) 327634 HASH JOIN 1 TABLE ACCESS GOAL: ANALYZED (FULL) OF 'IA_BUSN_LOCS' 2188129 PARTITION RANGE (ALL) PARTITION: START=1 STOP=2 2188129 TABLE ACCESS GOAL: ANALYZED (FULL) OF 'IA_PURCH_SCHLNS' PARTITION: START=1 STOP=2 Execution Plan ------- --------------------------------------------------- 0 SELECT STATEMENT GOAL: CHOOSE 20 SORT (GROUP BY) 218 MERGE JOIN (CARTESIAN) 218 HASH JOIN 218 HASH JOIN 1039 INDEX GOAL: ANALYZED (FAST FULL SCAN) OF 'IA_PRODUCTS_IDX_008' (NON-UNIQUE) 3970 TABLE ACCESS GOAL: ANALYZED (BY LOCAL INDEX ROWID) OF 'IA_PURCH_SCHLNS' PARTITION: START=2 STOP=2 3972 NESTED LOOPS 1 TABLE ACCESS GOAL: ANALYZED (FULL) OF 'IA_BUSN_LOCS' 3970 INDEX GOAL: ANALYZED (RANGE SCAN) OF 'IA_PURCH_SCHLNS_IDX_001' (NON-UNIQUE) PARTITION: START=2 STOP=2 3383 TABLE ACCESS GOAL: ANALYZED (FULL) OF 'IA_SUPPLIERS' 218 BUFFER (SORT) 1 TABLE ACCESS GOAL: ANALYZED (BY INDEX ROWID) OF 'IA_DATES' 1 INDEX GOAL: ANALYZED (RANGE SCAN) OF 'CHAR_DAY_DT' (NON-UNIQUE) ***** Additional info TABLE_NAME INDEX_NAME COLUMN_NAME ------------------------------ ------------------------------ ------------------------------ IA_PURCH_SCHLNS IA_PURCH_SCHLNS_IDX_001 ORDERED_ON_DK STORAGE_LOC_KEY IA_PURCH_SCHLNS_IDX_002 PRODUCT_KEY PSCH_ATTR1_CODE IA_PURCH_SCHLNS_IDX_003 SUPPLIER_KEY IA_PURCH_SCHLNS_PK KEY_ID SOURCE_ID INDEX_NAME LOCALI ALIGNMENT ------------------------------ ------ ------------ IA_PURCH_SCHLNS_IDX_001 LOCAL PREFIXED IA_PURCH_SCHLNS_IDX_002 LOCAL NON_PREFIXED IA_PURCH_SCHLNS_IDX_003 LOCAL NON_PREFIXED IA_PURCH_SCHLNS_PK GLOBAL PREFIXED select count(*) from busanal.ia_dates 54787 select count(*) from busanal.ia_products 22342 select count(*) from busanal.ia_suppliers 3383 select count(*) from busanal.ia_busn_locs 192 Stats have been gathered on tables/indexes involved Table IA_DATES attr date_key ( all julian dates from 1900 - 2049 ) Hidden parameter settings _subquery_pruning_reduction 50 _subquery_pruning_enabled TRUE _subquery_pruning_cost_factor 20 ---------------------------------------------------------------- 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 -----------------------------------------------------------------