How can I optimize the SELECT statement running on an Oracle database?

Posted by Elvis Lou on Stack Overflow See other posts from Stack Overflow or by Elvis Lou
Published on 2012-04-13T03:55:57Z Indexed on 2012/04/13 5:29 UTC
Read the original article Hit count: 261

Filed under:
|

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?

© Stack Overflow or respective owner

Related posts about sql

Related posts about Oracle