Hi Experts,
The attached query is performing slow, this needs to be optimized to
improve the performance.
Could you help me with query rewrite (or) on new indexes to be created to
improve the performance?
Thanks a ton in advance for your support.
SELECT tab2.rulename,
tab2.totalexecuted,
tab2.uniqueorder,
tab1.des description,
tab2.max,
tab1.ruletype,
tab2.uniqueorder pertange
FROM (SELECT re.rule_name ruleName,
Count (*)totalExecuted,
Count (DISTINCT re.order_id) uniqueOrder,
Max (re.id)
FROM rule_execution re
WHERE ? = ?
AND re.status = ?
AND re. type IN ( ?, ? )
AND re.order_id IN ( ?, ?, ?, ?,
?, ? )
GROUP BY re.rule_name) tab2
INNER JOIN (SELECT re2.rule_name,
Max (re2.rule_description) des,
Max (re2. type)ruleType
FROM (SELECT re4.rule_name,
Max (re4.created_date)
FROM sample.rule_execution re4
WHERE ? = ?
AND re4.status = ?
AND re4. type IN ( ?, ? )
AND re4.order_id IN ( ?, ?, ?, ?,
?, ? )
GROUP BY re4.rule_name) re1
INNER JOIN rule_execution re2
ON re2.rule_name = re1.rule_name
AND re2.created_date = re1. max
GROUP BY re2.rule_name) tab1
ON tab1.rule_name = tab2.rulename
ORDER BY totalexecuted DESC,
rulename ASC
LIMIT ?
currrent indexes on rule_execution table :
sample_rule_execution_upper_sample_id_idx
sample_rule_execution_sample_id_idx
sample_rule_execution_order_id_idx
rule_pkey
rule_execution_migration_unique
