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
