SQL & PL/SQL

Hi All,

Below Query has performance Issue. Could give me suggestion on this.


TRANSACTION table has 14 Corer records.

POS_LINE_ITEM table has 35 Corer records.

If 1 week Data-> 3- 10 minutes -> 3,500 to 4,000 (approx) Record Count.

If 6 Month Data-> 1-5 Hours -> 85,000 (approx) Record Count.



SELECT
ORDLN.ORDER_ID,
ORDLN.SKU ,
SUM(ORDLN.QUANTITY_PROCESSED),
DECIMAL(SUBSTRING(LNITM.LINE_ITEM_RETAIL_PRICE,4),10,2),
TRN.TRANSACTION_TYPES AS TRANSACTION_TYPES,
TRN.TRANSACTION_THE_OPERATOR_ID AS TRANSACTION_THE_OPERATOR_ID ,
EMP.USERNAME ,
EMP.FIRST_NAME,
EMP.LAST_NAME
FROM JPPRDDATA.TRANSACTION TRN,
JPPRDDATA.IONS_ORDER_LINE ORDLN,
JPPRDDATA.POS_LINE_ITEM LNITM,
JPPRDDATA.EMPLOYEE EMP
WHERE TRN.TRANSACTION_ID = LNITM.LINE_TRANSACTION_ID
AND LNITM.CATEGORY IN ('03', '11', '14', '1A', '2A', '2B', '2C', '2D')
AND (Date(TRN.TRANSACTION_CREATE_DATE) >= Date(Current_Date - 663 Days))
AND (Date(TRN.TRANSACTION_CREATE_DATE) <= Date(Current_Date - 480 Day))
AND (TRN.TRANSACTION_VOID_ID IS NULL OR TRN.TRANSACTION_VOID_ID = '')
AND TRN.TRANSACTION_TRAINING_FLAG = 0
AND TRIM(ORDLN.ORDER_TRANSACTION_ID) = TRIM(TRN.TRANSACTION_ID)
AND LNITM.LINE_SEQUENCE_NUMBER = ORDLN.ORDER_TRANSACTION_LINE_NUMBER
AND LNITM.LINE_ITEM_ID = decimal(ORDLN.SKU)
AND TRN.TRANSACTION_THE_OPERATOR_ID = EMP.ID
GROUP BY
ORDLN.ORDER_ID,
ORDLN.SKU,
LNITM.LINE_ITEM_RETAIL_PRICE,
TRN.TRANSACTION_TYPES,
TRN.TRANSACTION_THE_OPERATOR_ID,
EMP.USERNAME,
EMP.FIRST_NAME,
EMP.LAST_NAME

Thanks in advance
deb