Home » SQL & PL/SQL » SQL & PL/SQL » Can any one help me in tuning this query (merged)
Can any one help me in tuning this query (merged) [message #366209] Fri, 12 December 2008 02:35 Go to next message
janardhanam.k
Messages: 29
Registered: April 2007
Junior Member
Report Search Count Query
==========================

SELECT COUNT (1)
  FROM (SELECT SUBSTR (filename, 1, (INSTR (filename, '.') - 1)) filename,
                    SUM (CASE (SUBSTR (filename, -4))
                            WHEN '.csv'
                               THEN 1
                            ELSE 0
                         END)
                 || ','
                 || SUM (CASE (SUBSTR (filename, -4))
                            WHEN '.txt'
                               THEN 1
                            ELSE 0
                         END)
                 || ','
                 || SUM (CASE (SUBSTR (filename, -4))
                            WHEN '.xls'
                               THEN 1
                            ELSE 0
                         END)
                 || ','
                 || SUM (CASE (SUBSTR (filename, -4))
                            WHEN '.zip'
                               THEN 1
                            ELSE 0
                         END) mime,
                 MAX (report_index_seq) rpt_index_seq
            FROM (SELECT /*+ ORDERED PARALLEL(ri,5)*/ ri.report_index_seq, ri.report_seq,
                         ri.end_date AS reportdate, ri.view_ind, r.mime_code,
                         ri.mime_type, r.NAME, r.description AS description,
                         r.frequency, rc.product, ri.client_acct_num,
                         ca.NAME AS client_name, ri.filename filename
                    FROM report_index ri,
                         report r,
                         report_category rc,
                         client_accounts ca,
                         client_reports cr
                   WHERE ri.report_seq = r.report_seq
                     AND r.report_category_seq = rc.report_category_seq
                     AND ri.client_acct_num = ca.client_acct_num
                     AND cr.report_seq = r.report_seq
                     AND cr.LOCATION = rc.product
                     AND cr.run_ind = 'Y'
                     AND cr.client_seq = ca.client_seq
                     AND (   ri.mime_type = 'txt'
                          OR ri.mime_type = 'csv'
                          OR ri.mime_type = 'zip'
                          OR ri.mime_type = 'xls'
                         )
                     AND (    TRUNC (ri.end_date) >=
                                          TO_DATE ('2008-12-06', 'YYYY-MM-DD')
                          AND TRUNC (ri.end_date) <=
                                       (TO_DATE ('2008-12-08', 'YYYY-MM-DD')
                                       )
                         )
                     AND ri.report_seq IN
                            (1065,
                             1066,
                             1067,
                             1602,
                             1068,
                             1069,
                             1070,
                             1102,
                             1071,
                             1300,
                             1500,
                             1501,
                             1502,
                             1503,
                             1821,
                             1822,
                             1825,
                             1826,
                             1824,
                             1827,
                             1829,
                             1828,
                             2968,
                             2967,
                             1842,
                             2301,
                             2363,
                             2364,
                             2421,
                             2871,
                             3474,
                             2907,
                             2909,
                             3127,
                             3323,
                             3473,
                             3476,
                             3266,
                             3315,
                             3317,
                             3319,
                             3330,
                             3331,
                             3332,
                             3333,
                             3742,
                             3748,
                             3656
                            )
                     AND r.frequency IN
                            ('',
                             'DAILY',
                             'FULL',
                             'MONTHLY',
                             'QUARTERLY',
                             'WEEKLY'
                            )
                     AND ri.client_acct_num IN
                            (8000000307282,
                             8000000150955,
                             8000000150948,
                             8000000156275,
                             8000000156440,
                             8000000156457,
                             8000000156739,
                             8000000156747,
                             8000000157018,
                             8000000307332,
                             8000000307373,
                             8000000307365,
                             8000000307381,
                             8000000379992,
                             8000000307423,
                             8000000307407,
                             8000000307415,
                             8000000307431,
                             8000000150989,
                             8000000151003,
                             8000000150997
                            )
                     AND (cr.user_client = 'Y')
                     AND cr.dlvry_medium_rpt_server = 'Y'
                     AND ri.client_acct_num IN
                            (8000000307282,
                             8000000150955,
                             8000000150948,
                             8000000156275,
                             8000000156440,
                             8000000156457,
                             8000000156739,
                             8000000156747,
                             8000000157018,
                             8000000307332,
                             8000000307373,
                             8000000307365,
                             8000000307381,
                             8000000379992,
                             8000000307423,
                             8000000307407,
                             8000000307415,
                             8000000307431,
                             8000000150989,
                             8000000151003,
                             8000000150997
                            )
                     AND ri.report_seq IN
                            (1065,
                             1066,
                             1067,
                             1602,
                             1068,
                             1069,
                             1070,
                             1102,
                             1071,
                             1300,
                             1500,
                             1501,
                             1502,
                             1503,
                             1821,
                             1822,
                             1825,
                             1826,
                             1824,
                             1827,
                             1829,
                             1828,
                             2968,
                             2967,
                             1842,
                             2301,
                             2363,
                             2364,
                             2421,
                             2871,
                             3474,
                             2907,
                             2909,
                             3127,
                             3323,
                             3473,
                             3476,
                             3266,
                             3315,
                             3317,
                             3319,
                             3330,
                             3331,
                             3332,
                             3333,
                             3742,
                             3748,
                             3656
                            )
                     AND rc.product = 'EFUNDS'
                     AND r.frequency IN
                            ('',
                             'DAILY',
                             'FULL',
                             'MONTHLY',
                             'QUARTERLY',
                             'WEEKLY'
                            )
                     AND (    TRUNC (ri.end_date) >=
                                          TO_DATE ('2008-12-06', 'YYYY-MM-DD')
                          AND TRUNC (ri.end_date) <=
                                       (TO_DATE ('2008-12-08', 'YYYY-MM-DD')
                                       )
                         ))
        GROUP BY report_seq,
                 reportdate,
                 NAME,
                 description,
                 frequency,
                 client_acct_num,
                 client_name,
                 SUBSTR (filename, 1, (INSTR (filename, '.') - 1)))


Report Search Query
====================


SELECT   *
    FROM (SELECT /*+ ORDERED PARALLEL(xx,5)*/ xx.*, ROWNUM rnum
            FROM (SELECT   report_seq, reportdate, NAME, description,
                           frequency, client_acct_num, client_name,
                           SUBSTR (filename,
                                   1,
                                   (INSTR (filename, '.') - 1)
                                  ) filename,
                              SUM (CASE (SUBSTR (filename, -4))
                                      WHEN '.csv'
                                         THEN 1
                                      ELSE 0
                                   END
                                  )
                           || ','
                           || SUM (CASE (SUBSTR (filename, -4))
                                      WHEN '.txt'
                                         THEN 1
                                      ELSE 0
                                   END
                                  )
                           || ','
                           || SUM (CASE (SUBSTR (filename, -4))
                                      WHEN '.xls'
                                         THEN 1
                                      ELSE 0
                                   END
                                  )
                           || ','
                           || SUM (CASE (SUBSTR (filename, -4))
                                      WHEN '.zip'
                                         THEN 1
                                      ELSE 0
                                   END
                                  ) mime,
                           MAX (report_index_seq) rpt_index_seq,
                           DECODE (frequency,
                                   'DAILY', 1,
                                   'WEEKLY', 2,
                                   'MONTHLY', 3,
                                   'QUARTERLY', 4,
                                   'FULL', 5,
                                   'RANGE', 6
                                  ) AS freq1
                      FROM (SELECT /*+ ORDERED PARALLEL(ri,5)*/ ri.report_index_seq, ri.report_seq,
                                   ri.end_date AS reportdate, ri.view_ind,
                                   r.mime_code, ri.mime_type, r.NAME,
                                   r.description AS description, r.frequency,
                                   rc.product, ri.client_acct_num,
                                   ca.NAME AS client_name,
                                   ri.filename filename
                              FROM report_index ri,
                                   report r,
                                   report_category rc,
                                   client_accounts ca,
                                   client_reports cr
                             WHERE ri.report_seq = r.report_seq
                               AND r.report_category_seq =
                                                        rc.report_category_seq
                               AND ri.client_acct_num = ca.client_acct_num
                               AND cr.report_seq = r.report_seq
                               AND cr.LOCATION = rc.product
                               AND cr.run_ind = 'Y'
                               AND cr.client_seq = ca.client_seq
                               AND (   ri.mime_type = 'txt'
                                    OR ri.mime_type = 'csv'
                                    OR ri.mime_type = 'zip'
                                    OR ri.mime_type = 'xls'
                                   )
                               AND (    TRUNC (ri.end_date) >=
                                           TO_DATE ('2008-12-06',
                                                    'YYYY-MM-DD')
                                    AND TRUNC (ri.end_date) <=
                                           (TO_DATE ('2008-12-08',
                                                     'YYYY-MM-DD'
                                                    )
                                           )
                                   )
                               AND ri.report_seq IN
                                      (1065,
                                       1066,
                                       1067,
                                       1602,
                                       1068,
                                       1069,
                                       1070,
                                       1102,
                                       1071,
                                       1300,
                                       1500,
                                       1501,
                                       1502,
                                       1503,
                                       1821,
                                       1822,
                                       1825,
                                       1826,
                                       1824,
                                       1827,
                                       1829,
                                       1828,
                                       2968,
                                       2967,
                                       1842,
                                       2301,
                                       2363,
                                       2364,
                                       2421,
                                       2871,
                                       3474,
                                       2907,
                                       2909,
                                       3127,
                                       3323,
                                       3473,
                                       3476,
                                       3266,
                                       3315,
                                       3317,
                                       3319,
                                       3330,
                                       3331,
                                       3332,
                                       3333,
                                       3742,
                                       3748,
                                       3656
                                      )
                               AND r.frequency IN
                                      ('',
                                       'DAILY',
                                       'FULL',
                                       'MONTHLY',
                                       'QUARTERLY',
                                       'WEEKLY'
                                      )
                               AND ri.client_acct_num IN
                                      (8000000307282,
                                       8000000150955,
                                       8000000150948,
                                       8000000156275,
                                       8000000156440,
                                       8000000156457,
                                       8000000156739,
                                       8000000156747,
                                       8000000157018,
                                       8000000307332,
                                       8000000307373,
                                       8000000307365,
                                       8000000307381,
                                       8000000379992,
                                       8000000307423,
                                       8000000307407,
                                       8000000307415,
                                       8000000307431,
                                       8000000150989,
                                       8000000151003,
                                       8000000150997
                                      )
                               AND (cr.user_client = 'Y')
                               AND cr.dlvry_medium_rpt_server = 'Y'
                               AND ri.client_acct_num IN
                                      (8000000307282,
                                       8000000150955,
                                       8000000150948,
                                       8000000156275,
                                       8000000156440,
                                       8000000156457,
                                       8000000156739,
                                       8000000156747,
                                       8000000157018,
                                       8000000307332,
                                       8000000307373,
                                       8000000307365,
                                       8000000307381,
                                       8000000379992,
                                       8000000307423,
                                       8000000307407,
                                       8000000307415,
                                       8000000307431,
                                       8000000150989,
                                       8000000151003,
                                       8000000150997
                                      )
                               AND ri.report_seq IN
                                      (1065,
                                       1066,
                                       1067,
                                       1602,
                                       1068,
                                       1069,
                                       1070,
                                       1102,
                                       1071,
                                       1300,
                                       1500,
                                       1501,
                                       1502,
                                       1503,
                                       1821,
                                       1822,
                                       1825,
                                       1826,
                                       1824,
                                       1827,
                                       1829,
                                       1828,
                                       2968,
                                       2967,
                                       1842,
                                       2301,
                                       2363,
                                       2364,
                                       2421,
                                       2871,
                                       3474,
                                       2907,
                                       2909,
                                       3127,
                                       3323,
                                       3473,
                                       3476,
                                       3266,
                                       3315,
                                       3317,
                                       3319,
                                       3330,
                                       3331,
                                       3332,
                                       3333,
                                       3742,
                                       3748,
                                       3656
                                      )
                               AND rc.product = 'EFUNDS'
                               AND r.frequency IN
                                      ('',
                                       'DAILY',
                                       'FULL',
                                       'MONTHLY',
                                       'QUARTERLY',
                                       'WEEKLY'
                                      )
                               AND (    TRUNC (ri.end_date) >=
                                           TO_DATE ('2008-12-06',
                                                    'YYYY-MM-DD')
                                    AND TRUNC (ri.end_date) <=
                                           (TO_DATE ('2008-12-08',
                                                     'YYYY-MM-DD'
                                                    )
                                           )
                                   ))
                  GROUP BY report_seq,
                           reportdate,
                           NAME,
                           description,
                           frequency,
                           client_acct_num,
                           client_name,
                           SUBSTR (filename, 1, (INSTR (filename, '.') - 1))
                  ORDER BY client_name, freq1, NAME, reportdate ASC) xx
           WHERE ROWNUM <= 15)
   WHERE rnum >= 1
ORDER BY client_name, freq1, NAME, reportdate ASC


Re: Can any one help me in tuning this query [message #366212 is a reply to message #366209] Fri, 12 December 2008 02:48 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Based on the information thst you've given?

No, no-one can help you.

You're shy some explain plans, some indexing details, and some descriptions of how large the tables are and how many rows you'd expect back from each table.
Re: Can any one help me in tuning this query [message #366213 is a reply to message #366209] Fri, 12 December 2008 02:49 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Please do not multipost questions.
Re: Can any one help me in tuning this query (merged) [message #366235 is a reply to message #366209] Fri, 12 December 2008 04:00 Go to previous message
S.Rajaram
Messages: 1027
Registered: October 2006
Location: United Kingdom
Senior Member
Just to add few more points :

Why do you want to repeat the same condition more than once in your where clause and why enclosing an AND condition in a parenthesis? It will only confuse anybody who is looking at it.

Regards

Raj
Previous Topic: Statspack Error
Next Topic: case or decode
Goto Forum:
  


Current Time: Tue Dec 06 02:20:19 CST 2016

Total time taken to generate the page: 0.07503 seconds