Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Mailing Lists -> Oracle-L -> Query tuning stumper

Query tuning stumper

From: Meng, Dennis <Dennis.Meng_at_cardinal.com>
Date: Tue, 15 Jul 2003 14:51:40 -0500
Message-Id: <25929.337882@fatcity.com>


Hi all,
I have been struggling with the follwing query for hours with no avail. This is oracle 8.1.7.4 on Tru64 unix with a data warehouse setup. Invc_line is the fact table with about 267 mil records. There is a bitmap index on shipto_key(invc_line_bix04) and another bitmap index on BILLG_DT_KEY( invc_line_bix01). The optimal path should be a bitmap index merge of these two indexes, but instead it is ignoring the index on shipto_key. Can anybody shel some line on what gives here?

SELECT    SUM(INVC_LINE.EXTND_FI_COGS),                                                             
SUM(INVC_LINE.EXTND_SD_COGS),                                                                       
MTL.RPTG_SEG_CD,                                                                                    
MTL.RPTG_SEG_DESC,                                                                                  
SUM(INVC_LINE.EXTND_SD_REBT_ADJD_GP),                                                               
INVC_LINE.ITEM_CTGRY_DESC,                                                                          
INVC_LINE.ITEM_CTGRY_CD,                                                                            
SUM(INVC_LINE.EXTND_SD_REBT_ADJD_COGS),                                                             
SUM(INVC_LINE.EXTND_FI_SALES_PRC),                                                                  
SUM(INVC_LINE.EXTND_SD_SALES_PRC),                                                                  
INVC_LINE.ORD_TYP_CD,                                                                               
CUST_SHIPTO.SALES_ORD_CUST_GRP_CD,                                                                  
CUST_SHIPTO.SALES_GRP_PRES_REG_DESC,                                                                
SUM(INVC_LINE_ATTRB.SHP_DIRCT_CST),                                                                 
SUM(INVC_LINE.EXTND_REBT_PART_AMT)                                                                  
FROM    CUST_SHIPTO,                                                                                
INVC_LINE,                                                                                          
INVC_LINE_ATTRB,                                                                                    
BILL_DT,                                                                                            
MTL                                                                                                 
WHERE    ( INVC_LINE.SHPTO_KEY=CUST_SHIPTO.SHPTO_KEY  )                                             
AND  ( INVC_LINE.BILLG_DT_KEY=BILL_DT.DAY_DT_KEY  )                                                 
AND  ( MTL.MTL_KEY=INVC_LINE.MTL_KEY  )                                                             
AND  ( INVC_LINE_ATTRB.BILLG_NUM=INVC_LINE.BILLG_NUM                                                
AND INVC_LINE_ATTRB.BILLING_SEQ_NUM=INVC_LINE.BILLG_SEQ_NUM  )                                      
AND  (    BILL_DT.DT  BETWEEN  '2003/06/01'                                                         
AND '2003/06/30'                                                                                    
AND  INVC_LINE.SALES_CHNNL  IN  ('D',                                                               

'F',
'M',
'DPG') )
GROUP BY MTL.RPTG_SEG_CD, MTL.RPTG_SEG_DESC, INVC_LINE.ITEM_CTGRY_DESC, INVC_LINE.ITEM_CTGRY_CD, INVC_LINE.ORD_TYP_CD, CUST_SHIPTO.SALES_ORD_CUST_GRP_CD,
CUST_SHIPTO.SALES_GRP_PRES_REG_DESC The Plan

Select Statement CHOOSE Rows: 164,290 Cost: 466,524

         Sort Group By  Rows: 164,290 Cost: 466,524
         GROUP BY mtl.rptg_seg_cd, mtl.rptg_seg_desc, invc_line.item_ctgry_desc, invc_line.item_ctgry_cd, invc_line.ord_typ_cd, cust_shipto.sales_ord_cust_grp_cd, cust_shipto.sales_grp_pres_reg_desc
            Nested Loops   Rows: 164,290 Cost: 462,150
               Hash Join   Rows: 135,004 Cost: 57,138
               mtl.mtl_key = invc_line.mtl_key
                  Hash Join   Rows: 135,004 Cost: 53,334
                  invc_line.shpto_key = cust_shipto.shpto_key
                     Nested Loops   Rows: 135,004 Cost: 45,127
                        Unique Range Scan Dw.day_dt_idx1 [Analyzed] Keys:        0.000 Rows Per Key:          1.00  Rows: 1 Cost: 11
                        bill_dt.dt >= '2003/06/01', bill_dt.dt <= '2003/06/03'
                        Table Access By Index Rowid Dw.invc_line Rows: 271,897,386 Cost: 45,127
                        invc_line.sales_chnnl = 'D', invc_line.sales_chnnl = 'F', invc_line.sales_chnnl = 'M', invc_line.sales_chnnl = 'DPG'
                           Bitmap Conversion To Rowids  
                              Bitmap Index Single Value Dw.invc_line_bix01 
                              invc_line.billg_dt_key = bill_dt.day_dt_key
                     Table Access Full Dw.cust_shipto Rows: 1,179,146 Cost: 3,636
                  Unique Fast Full Scan Dw.mtl_ix2 [Analyzed] Keys:        0.000 Rows Per Key:          1.00  Rows: 812,615 Cost: 292
               Table Access By Index Rowid Dw.invc_line_attrb Rows: 272,311,352 Cost: 3
                  Unique Unique Scan Dw.pk_invc_line_attrb [Analyzed] Keys:        0.000 Rows Per Key:          1.00  Rows: 272,311,352 Cost: 2
                  invc_line_attrb.billg_num = invc_line.billg_num, invc_line_attrb.billing_seq_num = invc_line.billg_seq_num



-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: 
  INET: JApplewhite_at_austin.isd.tenet.edu

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
Received on Tue Jul 15 2003 - 14:51:40 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US