Link to home
Start Free TrialLog in
Avatar of pinkuray
pinkurayFlag for India

asked on

Tune Oracle query

Hello experts,

I have one query which is taking more time due to huge records in the tables.
My query returns 300000 records and which takes time.

Please help in tuning this query.

Please find my query below:

WITH a1 AS
     (
        SELECT subscriber_id
          FROM ps_sms_subscrb_tbl a
         WHERE 'A' = a.eff_status
           AND SUBSTR (a.sic_cd, 1, 2) IN
                  ('70',
                   '71',
                   '72',
                   '73',
                   '74',
                   '75',
                   '76',
                   '77',
                   '78',
                   '79',
                   '80',
                   '81',
                   '82',
                   '83',
                   '84',
                   '85',
                   '86',
                   '87',
                   '88',
                   '89'
                  )
           AND a.effdt =
                  (SELECT MAX (a1.effdt)
                     FROM ps_sms_subscrb_tbl a1
                    WHERE a1.subscriber_id = a.subscriber_id
                      AND a1.effdt <= TO_DATE ('12/31/2010', 'MM/DD/YYYY')))
SELECT /*+ INDEX_JOIN(A1) */
       PERCENTILE_CONT (0.25) WITHIN GROUP (ORDER BY annual_rt) percentile_25,
       PERCENTILE_CONT (0.50) WITHIN GROUP (ORDER BY annual_rt) percentile_50,
       PERCENTILE_CONT (0.75) WITHIN GROUP (ORDER BY annual_rt) percentile_75
  FROM a1, ps_job j
 WHERE j.paygroup = a1.subscriber_id
   AND j.al_empl_status IN ('A', 'L')
   AND j.effdt =
          (SELECT MAX (effdt)
             FROM ps_job j21
            WHERE j.emplid = j21.emplid
              AND j.file_nbr = j21.file_nbr
              AND j.paygroup = j21.paygroup
              AND j21.effdt <= TO_DATE ('12/31/2010', 'MM/DD/YYYY'))
   AND j.effseq =
          (SELECT MAX (effseq)
             FROM ps_job j22
            WHERE j.emplid = j22.emplid
              AND j.file_nbr = j22.file_nbr
              AND j.effdt = j22.effdt
              AND j.paygroup = j22.paygroup)

Open in new window

ASKER CERTIFIED SOLUTION
Avatar of Mark Geerlings
Mark Geerlings
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of pinkuray

ASKER

can you please modify the code as you said?
I will also post the explain plan  soon.
Avatar of slightwv (䄆 Netminder)
slightwv (䄆 Netminder)

>>I will also post the explain plan  soon.

Break the individual queries down to their core pieces to see where the main delay is.
thanks for all your help