| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> Re: Query tuning stumper
I've only looked quickly at this query, but why do you think it should be
using the shipto_key index? There doesn't appear to be any kind of
filtering on the cust_shipto table so I don't know why an index would help
since every record would match. Am I missing something?
Also, what explain plan tool are you using to produce that plan with embedded SQL - it looks like it would be very useful to teach concepts to people using that plan?
Regards,
Mark.
"Meng, Dennis"
<Dennis.Meng_at_card To: Multiple recipients of list ORACLE-L <ORACLE-L_at_fatcity.com>
inal.com> cc:
Sent by: Subject: Query tuning stumper
ml-errors_at_fatcity
.com
16/07/2003 06:49
Please respond to
ORACLE-L
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 servicesto: 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 also send the HELP command for other information (like subscribing).
---------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
Fat City Network Services -- 858-538-5051 http://www.fatcity.com San Diego, California -- Mailing list and web hosting servicesto: 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 also send the HELP command for other information (like subscribing).
---------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<---->>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
Privileged/Confidential information may be contained in this message.
If you are not the addressee indicated in this message
(or responsible for delivery of the message to such person),
you may not copy or deliver this message to anyone.
In such case, you should destroy this message and kindly notify the sender
by reply e-mail or by telephone on (61 3) 9612-6999.
Please advise immediately if you or your employer does not consent to
Internet e-mail for messages of this kind.
Opinions, conclusions and other information in this message
that do not relate to the official business of
Transurban City Link Ltd
shall be understood as neither given nor endorsed by it.
<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<---->>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<---->>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
Privileged/Confidential information may be contained in this message.
If you are not the addressee indicated in this message (or responsible for delivery of the message to such person), you may not copy or deliver this message to anyone.
In such a case, you should destroy this message and kindly notify the sender by reply e-mail or by telephone on (03) 9612-6999 or (61) 3 9612-6999.
Please advise immediately if you or your employer does not consent to Internet e-mail for messages of this kind.
Opinions, conclusions and other information in this message that do not relate to the official business of Transurban Infrastructure Developments Limited and CityLink Melbourne Limited shall be understood as neither given nor endorsed by them.
Received on Tue Jul 15 2003 - 18:49:34 CDT
 |
 |