query performance is very slow
select pn.produce_name,produce_grade,pn.produce_code,a.mpproducecode,p.UOMID, pn.catcode,pm.catname ,a.mpmodalprice,
round( sum(p.AMOUNT_REC) / sum(p.QTY_ALLOTED*pq.wtperpack),2)prev_wtavg2
from
safal.tb_produce_name pn, safal.tb_packuomqty_master pq , safal.tb_prodcat_master pm , safal.tbl_au_postauct_details p,
mis.tb_misproducedetail a
where
p.au_date between TO_DATE('1-FEB-2006','DD-MON-YYYY') and TO_DATE('1-FEB-2006','DD-MON-YYYY')
and pn.catcode=pm.catcode and pn.produce_code=p.itemid and pq.pkid=p.pk_id and p.uom=auom and
pn.produce_code=a.mpproducecode
and pn.catcode='104'
group by pm.catname,pn.catcode,produce_name,produce_grade,p.uomid,a.mpmodalprice,pn.produce_code,a.mpproducec ode
1. i have taken the explain plan for the above query,
this query is taking very long time to run and i have created index on some columns but even then indexes r not used in this query and query performance is not increased.i just want to know even thougn i created indexes why it is going for full table scan and why indexes r not used.pls clarify me on this.
2. how to force the index through Hints for the above query pls let me know about it.
explain plan statistics
SELECT STATEMENT Optimizer=CHOOSE (Cost=90 Card=8278 Bytes=836078)
SORT* (GROUP BY) (Cost=90 Card=8278 Bytes=836078)
HASH JOIN* (Cost=68 Card=8278 Bytes=836078)
HASH JOIN* (Cost=67 Card=780 Bytes=69420)
TABLE ACCESS* (FULL) OF TB_PRODUCE_NAME (Cost=1 Card=159 Bytes=6201)
HASH JOIN* (Cost=66 Card=3245 Bytes=162250)
NESTED LOOPS* (Cost=2 Card=2381 Bytes=42858)
TABLE ACCESS* (BY INDEX ROWID) OF TB_PRODCAT_MASTER (Cost=1 Card=1 Bytes=10)
INDEX (UNIQUE SCAN) OF PK_PRODCATMASTER (UNIQUE)
TABLE ACCESS* (FULL) OF TB_PACKUOMQTY_MASTER (Cost=1 Card=2381 Bytes=19048)
TABLE ACCESS* (FULL) OF TBL_AU_POSTAUCT_DETAILS (Cost=64 Card=586 Bytes=18752)
TABLE ACCESS* (FULL) OF TB_MISPRODUCEDETAIL (Cost=1 Card=3638 Bytes=43656)
