How can I optimize the SELECT statement running on an Oracle database?
- by Elvis Lou
I have a SELECT statement in ORACLE:
SELECT COUNT(DISTINCT ds1.endpoint_msisdn) multiple30,
dss1.service,
dss1.endpoint_provisioning_id,
dss1.company_scope,
Nvl(x.subscription_status, dss1.subscription_status) subscription_status
FROM daily_summary ds1
join daily_summary ds2
ON ds1.endpoint_msisdn = ds2.endpoint_msisdn,
daily_summary_static dss1,
daily_summary_static dss2,
(SELECT NULL subscription_status
FROM dual
UNION ALL
SELECT -2 subscription_status
FROM dual) x
WHERE ds1.summary_ts >= To_date('10-04-2012', 'dd-mm-yyyy') - 30
AND ds1.summary_ts <= To_date('10-04-2012', 'dd-mm-yyyy')
AND dss1.last_active >= To_date('10-04-2012', 'dd-mm-yyyy') - 30
AND dss1.last_active <= To_date('10-04-2012', 'dd-mm-yyyy')
AND dss2.last_active >= To_date('10-04-2012', 'dd-mm-yyyy') - 30
AND dss2.last_active <= To_date('10-04-2012', 'dd-mm-yyyy')
AND dss1.service <> dss2.service
AND ( dss1.company_scope = 2
OR dss1.company_scope = 5 )
AND ( dss2.company_scope = 2
OR dss2.company_scope = 5 )
AND dss1.company_scope = dss2.company_scope
AND ds1.endpoint_noc_id = dss1.endpoint_noc_id
AND ds1.endpoint_host_id = dss1.endpoint_host_id
AND ds1.endpoint_instance_id = dss1.endpoint_instance_id
AND ds2.endpoint_noc_id = dss2.endpoint_noc_id
AND ds2.endpoint_host_id = dss2.endpoint_host_id
AND ds2.endpoint_instance_id = dss2.endpoint_instance_id
AND dss1.endpoint_provisioning_id = dss2.endpoint_provisioning_id
AND Least(1, ds1.total_actions) = 1
AND Least(1, ds2.total_actions) = 1
GROUP BY dss1.service,
dss1.endpoint_provisioning_id,
dss1.company_scope,
Nvl(x.subscription_status, dss1.subscription_status);
This query took about 26 minutes to return in my environment, but if I remove the section:
dss1.last_active >= to_date('10-04-2012','dd-mm-yyyy') - 30 AND
dss1.last_active <= to_date('10-04-2012','dd-mm-yyyy') AND
dss2.last_active >= to_date('10-04-2012','dd-mm-yyyy') - 30 AND
dss2.last_active <= to_date('10-04-2012','dd-mm-yyyy') AND
it only took 20 seconds to run.
We have index on the column last_active, I don't know why the section slow down the performance so much? any ideas?
