SELECT count(1) FROM datap.ITEM_MASTER, datap.DB_MASTER, datap.FISCAL_WEEK, datap.CUSTOMER_MASTER CUSTOMER_MASTER_SOLD, datap.FISCAL_WEEK DUE_WEEK, datap.INVOICE_LINE WHERE INVOICE_LINE.ITEM_ID = ITEM_MASTER.ITEM_ID AND INVOICE_LINE.FISCAL_WEEK = FISCAL_WEEK.FISCAL_WEEK AND INVOICE_LINE.DB_CODE = DB_MASTER.DB_CODE AND CUSTOMER_MASTER_SOLD.CUST_ID=INVOICE_LINE.CUST_SOLD_ID AND INVOICE_LINE.DUE_DATE >= DUE_WEEK.FISCAL_DT_START and INVOICE_LINE.DUE_DATE < DUE_WEEK.FISCAL_DT_END ; Explain Plan statistic: OPERATION OPTIONS OBJECT_NAME ------------------------------ --------------- -------------------- SELECT STATEMENT SORT AGGREGATE NESTED LOOPS NESTED LOOPS HASH JOIN INDEX FAST FULL SCAN PK_ITEM_MASTER HASH JOIN MERGE JOIN CARTESIAN TABLE ACCESS FULL FISCAL_WEEK BUFFER SORT INDEX FULL SCAN PK_DB_MASTER PARTITION RANGE ALL TABLE ACCESS FULL INVOICE_LINE INDEX UNIQUE SCAN PK_FISCAL_WEEK INDEX UNIQUE SCAN PK_CUSTOMER_MASTER
Requires Free Membership to View
I see a couple of possible problems that you should address:
1) The full scan on INVOICE_LINE is ignoring the partitioning (note the line in the explain plan which says PARTITION RANGE ALL). This indicates that your partition bounds/partition indexing is not adequate to place all similar rows into a single partition. My question here is, what are your partition bounds? Do you use fiscal_week, due_date or what as your partition bounds? If you've set up partition bounds that don't have some logical data divisions, then the optimizer won't be able to "prune" the unneeded partitions. By having to full scan the seven million plus INVOICE_LINE table, you've pretty well assured that your performance is going to stink.
2) What's up with the CARTESTIAN join? Do you really want that? You may need to check your join columns in the where clause to make sure that you are joining the tables correctly and on the proper columns. Anytime I see CARTESIAN in a plan, I want to know why it's there and if there's not an actual reason it's needed, I get rid of it.
Finally, you didn't mention what indexes you created that you expected to help. If you created indexes that aren't being used OR aren't being used properly (i.e. they actually help improve performance), then you need to look at dropping those indexes and creating ones that work.
Try looking into both these areas and see if you can't make progress from there.
This was first published in January 2004
Join the conversationComment
Share
Comments
Results
Contribute to the conversation