Skip to Main Content

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Extracting weekly data from mtl_material_transactions table taking 2 hours

Tom_GFeb 13 2017 — edited Feb 14 2017

I am trying to extract data from mtl_material_transactions in weekly buckets for past 1 year using the below Query. The Query is taking almost 2 hours to complete. How can I improve the performance of it?

SELECT msib.inventory_item_id,

       msib.segment1 item,

       msib.organization_id,

       msib.primary_uom_code,

       TO_CHAR(TRUNC(transaction_date), 'IYIW') Week,

       SUM(mmt.transaction_quantity)

  FROM mtl_material_transactions mmt,

       mtl_system_items_b msib

WHERE 1 = 1

   AND mmt.inventory_item_id = msib.inventory_item_id

   AND mmt.organization_id = msib.organization_id

   AND mmt.transaction_type_id = 35

   AND mmt.transaction_date <= SYSDATE

   AND mmt.transaction_date >= SYSDATE - 365

GROUP BY msib.inventory_item_id, msib.segment1, msib.organization_id, msib.primary_uom_code, TO_CHAR(TRUNC(transaction_date), 'IYIW')

ORDER BY TO_CHAR(TRUNC(transaction_date), 'IYIW') DESC;

Comments
Locked Post
New comments cannot be posted to this locked post.
Post Details
Locked on Mar 14 2017
Added on Feb 13 2017
9 comments
2,253 views