one place to look would be your sort_area_size. your doing summing and group
bys... if this is too small your going to do that in a temp tablespace
instead of in memory.
your using bitmap indexes right? this implies that some of this data atleast
is non-transactional correct? if its non-transactional it can be
pre-computed with a materialized view. tihs could potentially save alot..
you are also using a hash join. look at your hash_area_size as well.
- Original Message -
To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED]
Sent: Tuesday, July 15, 2003 8:49 PM
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
[EMAIL PROTECTED]To: Multiple
recipients of list ORACLE-L [EMAIL PROTECTED]
inal.comcc:
Sent by: Subject: Query tuning
stumper
[EMAIL PROTECTED]
.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?
SELECTSUM(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)
FROMCUST_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 BYMTL.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
