SELECT
XCRV.CROSS_REFERENCE JENIS
,
XTD_INV_CONVERT_QTY_UOM_FNC (
(select min(mcrv2.INVENTORY_ITEM_ID)
from XTD_CROSS_REFF_ITEM_V mcrv2
where 1=1
and mcrv2.cross_reference = XCRV.CROSS_REFERENCE
and mcrv2.organization_id = MMT.ORGANIZATION_ID) ,
MMT.ORGANIZATION_ID,
(SELECT NVL(SUM(MMT2.PRIMARY_QUANTITY), 0)
FROM
MTL_MATERIAL_TRANSACTIONS MMT2,
XTD_CROSS_REFF_ITEM_V XCRV2
WHERE
MMT2.ORGANIZATION_ID = XCRV2.ORGANIZATION_ID
AND MMT2.INVENTORY_ITEM_ID = XCRV2.INVENTORY_ITEM_ID
AND XCRV2.CROSS_REFERENCE = XCRV.CROSS_REFERENCE
AND TRUNC(MMT2.TRANSACTION_DATE) <= TRUNC( TO_DATE( :P_DATE_FROM, 'YYYY/MM/DD' ) )
),
MSIB.PRIMARY_UOM_CODE,
'BAL'
) || '-' || XTD_INV_CONVERT_QTY_UOM_FNC (
(select min(mcrv2.INVENTORY_ITEM_ID)
from XTD_CROSS_REFF_ITEM_V mcrv2
where 1=1
and mcrv2.cross_reference = XCRV.CROSS_REFERENCE
and mcrv2.organization_id = MMT.ORGANIZATION_ID) ,
MMT.ORGANIZATION_ID,
(SELECT NVL(SUM(MMT2.PRIMARY_QUANTITY), 0)
FROM
MTL_MATERIAL_TRANSACTIONS MMT2,
XTD_CROSS_REFF_ITEM_V XCRV2
WHERE
MMT2.ORGANIZATION_ID = XCRV2.ORGANIZATION_ID
AND MMT2.INVENTORY_ITEM_ID = XCRV2.INVENTORY_ITEM_ID
AND XCRV2.CROSS_REFERENCE = XCRV.CROSS_REFERENCE
AND TRUNC(MMT2.TRANSACTION_DATE) <= TRUNC( TO_DATE( :P_DATE_FROM, 'YYYY/MM/DD' ) )
),
MSIB.PRIMARY_UOM_CODE,
'PRS'
) || '-' || XTD_INV_CONVERT_QTY_UOM_FNC (
(select min(mcrv2.INVENTORY_ITEM_ID)
from XTD_CROSS_REFF_ITEM_V mcrv2
where 1=1
and mcrv2.cross_reference = XCRV.CROSS_REFERENCE
and mcrv2.organization_id = MMT.ORGANIZATION_ID) ,
MMT.ORGANIZATION_ID,
(SELECT NVL(SUM(MMT2.PRIMARY_QUANTITY), 0)
FROM
MTL_MATERIAL_TRANSACTIONS MMT2,
XTD_CROSS_REFF_ITEM_V XCRV2
WHERE
MMT2.ORGANIZATION_ID = XCRV2.ORGANIZATION_ID
AND MMT2.INVENTORY_ITEM_ID = XCRV2.INVENTORY_ITEM_ID
AND XCRV2.CROSS_REFERENCE = XCRV.CROSS_REFERENCE
AND TRUNC(MMT2.TRANSACTION_DATE) <= TRUNC( TO_DATE( :P_DATE_FROM, 'YYYY/MM/DD' ) )
),
MSIB.PRIMARY_UOM_CODE,
'BKS'
) AS SALDO_AWAL
FROM
MTL_MATERIAL_TRANSACTIONS MMT,
MTL_TRANSACTION_TYPES MTT,
MTL_SYSTEM_ITEMS_B MSIB,
XTD_CROSS_REFF_ITEM_V XCRV,
ORG_ORGANIZATION_DEFINITIONS OOD,
HR_OPERATING_UNITS HOU,
GL_LEDGERS GL
WHERE
MMT.INVENTORY_ITEM_ID = MSIB.INVENTORY_ITEM_ID
AND MMT.ORGANIZATION_ID = MSIB.ORGANIZATION_ID
AND MMT.TRANSACTION_TYPE_ID = MTT.TRANSACTION_TYPE_ID
AND MMT.ORGANIZATION_ID = OOD.ORGANIZATION_ID
AND XCRV.INVENTORY_ITEM_ID = MMT.INVENTORY_ITEM_ID
AND HOU.BUSINESS_GROUP_ID = OOD.BUSINESS_GROUP_ID
AND OOD.SET_OF_BOOKS_ID = GL.LEDGER_ID
--HARDCODE
AND HOU.ORGANIZATION_ID NOT IN ('82')
AND XCRV.CROSS_REFERENCE = 'ARB12'
-- PARAMETERS
AND GL.LEDGER_ID = NVL(:P_LEDGER, GL.LEDGER_ID)
AND HOU.ORGANIZATION_ID = NVL(:P_OU_ID, HOU.ORGANIZATION_ID)
AND OOD.ORGANIZATION_ID = NVL(:P_CABANG, OOD.ORGANIZATION_ID)
-- HEADING
AND TRUNC(MMT.TRANSACTION_DATE) BETWEEN TRUNC( TO_DATE( :P_DATE_FROM, 'YYYY/MM/DD' )) AND TRUNC( TO_DATE( :P_DATE_TO, 'YYYY/MM/DD' ))
GROUP BY
XCRV.CROSS_REFERENCE,
MMT.ORGANIZATION_ID,
MSIB.PRIMARY_UOM_CODE
ORDER BY
XCRV.CROSS_REFERENCE
Please optimize my query snippet. When hardcoding only one piece of data, it takes about 4 minutes to execute. However, there are still many data to be loaded, and I also have other columns to add.
When I try to run the query for just one piece of data, it takes about 4 minutes from the attached query. However, there are still many more data to be loaded from the database. For the result, I want to achieve a short execution time.
You have many repeated sub-queries. You can improve performance by not repeating them and calculating the values only once in the JOIN clause rather than multiple times in every row in the WHERE clause:
For example, by using a LATERAL JOIN which is available from Oracle 12 (untested as you provided no sample data or code for the functions you are calling):
SELECT XCRV.CROSS_REFERENCE JENIS,
XTD_INV_CONVERT_QTY_UOM_FNC(
XCRV.min_inventory_item_idm
mii.ORGANIZATION_ID,
tpq.total_primary_quantity,
MSIB.PRIMARY_UOM_CODE,
'BAL'
) || '-' || XTD_INV_CONVERT_QTY_UOM_FNC (
XCRV.min_inventory_item_idm
mii.ORGANIZATION_ID,
tpq.total_primary_quantity,
MSIB.PRIMARY_UOM_CODE,
'PRS'
) || '-' || XTD_INV_CONVERT_QTY_UOM_FNC (
XCRV.min_inventory_item_idm
mii.ORGANIZATION_ID,
tpq.total_primary_quantity,
MSIB.PRIMARY_UOM_CODE,
'BKS'
) AS SALDO_AWAL
FROM MTL_MATERIAL_TRANSACTIONS MMT
INNER JOIN XTD_CROSS_REFF_ITEM_V xcrv
CROSS JOIN LATERAL (
select min(mcrv2.INVENTORY_ITEM_ID) AS min_inventory_item_id
from XTD_CROSS_REFF_ITEM_V cr
where cr.cross_reference = XCRV.CROSS_REFERENCE
and cr.organization_id = MMT.ORGANIZATION_ID
) miii
CROSS JOIN LATERAL (
SELECT COALESCE(SUM(MMT2.PRIMARY_QUANTITY), 0) AS total_primary_quantity
FROM MTL_MATERIAL_TRANSACTIONS MMT2
INNER JOIN XTD_CROSS_REFF_ITEM_V XCRV2
ON MMT2.ORGANIZATION_ID = XCRV2.ORGANIZATION_ID
AND MMT2.INVENTORY_ITEM_ID = XCRV2.INVENTORY_ITEM_ID
WHERE XCRV2.CROSS_REFERENCE = XCRV.CROSS_REFERENCE
AND MMT2.TRANSACTION_DATE < TO_DATE(:P_DATE_FROM, 'YYYY/MM/DD') + 1
) tpq
INNER JOIN MTL_TRANSACTION_TYPES MTT
ON MMT.TRANSACTION_TYPE_ID = MTT.TRANSACTION_TYPE_ID
INNER JOIN MTL_SYSTEM_ITEMS_B MSIB
ON MMT.INVENTORY_ITEM_ID = MSIB.INVENTORY_ITEM_ID
AND MMT.ORGANIZATION_ID = MSIB.ORGANIZATION_ID
ON XCRV.INVENTORY_ITEM_ID = MMT.INVENTORY_ITEM_ID
INNER JOIN ORG_ORGANIZATION_DEFINITIONS OOD
ON MMT.ORGANIZATION_ID = OOD.ORGANIZATION_ID
INNER JOIN HR_OPERATING_UNITS HOU
ON HOU.BUSINESS_GROUP_ID = OOD.BUSINESS_GROUP_ID
INNER JOIN GL_LEDGERS GL
ON OOD.SET_OF_BOOKS_ID = GL.LEDGER_ID
WHERE --HARDCODE
HOU.ORGANIZATION_ID NOT IN ('82')
AND XCRV.CROSS_REFERENCE = 'ARB12'
-- PARAMETERS
AND (:P_LEDGER IS NULL OR GL.LEDGER_ID = :P_LEDGER)
AND (:P_OU_ID IS NULL OR HOU.ORGANIZATION_ID = :P_OU_ID)
AND (:P_CABANG IS NULL OR OOD.ORGANIZATION_ID = :P_CABANG)
-- HEADING
AND MMT.TRANSACTION_DATE >= TO_DATE(:P_DATE_FROM, 'YYYY/MM/DD')
AND MMT.TRANSACTION_DATE < TO_DATE(:P_DATE_TO, 'YYYY/MM/DD') + 1
GROUP BY
XCRV.CROSS_REFERENCE,
MMT.ORGANIZATION_ID,
MSIB.PRIMARY_UOM_CODE
ORDER BY
XCRV.CROSS_REFERENCE
You can also directly use MMT.TRANSACTION_DATE (rather than TRUNC(MMT.TRANSACTION_DATE)) which may allow Oracle to use an index on that column; otherwise you would need to use a function-based index on TRUNC(MMT.TRANSACTION_DATE).