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;