-
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)
-
try posting it again properly using the [ code ] tages, impossible to read otherwise
-
Hi Balkrishna,
If you have all the tables regulerly analyzed, the CBO will use best execution path,
Still if you want to use any index hint, or join mechanism like HASH JOIN or Nested loop you can keep hints like this.
For Index use /*+ index(index_name) */
For specified join /*+ use_hash(table1,table2) */ for hash join
or /*+ use_nl(table1,table2) */
Dilip.
Dilip Patel
OCP 8i
Catch me online at Yahoo: ddpatel256
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|