I have below query which returns 90,000 records and its taking almost 10 minutes to run. I dont have indexes on any of the tables and i cannot provide index as i am running this query in PROD.
I am viewing the execution plan in sql developer but not understanding exactly which condition is taking too much time and how can i improve it.
Select * from ORDER_MART FOL where FOL.PARENT_PROD_SRCID
IN
(
select e.PARENT_PROD_SRCID
from SRC_GRP a
JOIN MAR_GRP b ON a.h_lpgrp_id = b.h_lpgrp_id
JOIN DATA_GRP e ON e.parent_prod_srcid = b.H_LOCPR_ID
WHERE a.CHILD_LOCPR_ID != 0
AND dt_id BETWEEN 20170101 AND 20170731
AND valid_order = 1
AND a.PROD_TP_CODE like 'C%'
)
AND FOL.PROD_SRCID = 0 and IS_CAPS = 1;
Below is my query execution plan:
