1 2 Previous Next 16 Replies Latest reply: Apr 4, 2013 10:02 PM by user648773 RSS

    Need help in query rewrite

    user590978
      Hi, I have following below query and showing more cost.
        SELECT COUNT(1)
                FROM CCTR_INQUIRIES CI, CCTR_CONTACTS CCC
               WHERE INQUIRY_ID IN (SELECT DISTINCT CA.INQUIRY_ID
                                      FROM CCTR_ACTIONS CA
                                     WHERE CA.ACTION_ID IN
                                           (SELECT MAX(CA2.ACTION_ID)
                                              FROM CCTR_ACTIONS CA2
                                             WHERE TRUNC(CA2.ACTION_START_DATE) = &PDATE
                                               AND CA2.INQUIRY_ID = CA.INQUIRY_ID)
                                       AND CA.STATUS <> 'Z')
                 AND CI.CONTACT_ID = CCC.CONTACT_ID
                 AND (TRUNC(SYSDATE) - TRUNC(CI.START_INQUIRY)) >= 60
                 AND CI.ID_TYPE = &PDEPARTMENT
                 AND GET_CSRUSER_QA(CI.CURRENT_QUEUE) = &PCSR_USERID
                 AND CI.CATEGORY_ID IN
                     (SELECT CODE_ID
                        FROM CC_USER_DEFINED_CODES
                       WHERE CODE_TYPE = 'RTCAT');
      Can you please help me out to rewrite the query.

      Thanks in advacne..
        • 1. Re: Need help in query rewrite
          sb92075
          HOW To Make TUNING request
          SQL and PL/SQL FAQ
          • 2. Re: Need help in query rewrite
            SomeoneElse
            Please read these:

            When your query takes too long
            When your query takes too long ...

            How to Post a SQL statement tuning request
            HOW TO: Post a SQL statement tuning request - template posting
            • 3. Re: Need help in query rewrite
              sushaant
              SELECT COUNT(1)
              FROM CCTR_INQUIRIES CI, CCTR_CONTACTS CCC,CC_USER_DEFINED_CODES CUDC
              WHERE CI.CONTACT_ID = CCC.CONTACT_ID
              AND CI.START_INQUIRY >= TRUNC(SYSDATE - 60)
              AND CI.ID_TYPE = &PDEPARTMENT
              AND GET_CSRUSER_QA(CI.CURRENT_QUEUE) = &PCSR_USERID
              AND CI.CATEGORY_ID = CUDC.CODE_ID
                   AND CUDC.CODE_TYPE = 'RTCAT'
                   AND EXISTS (SELECT NULL
              FROM CCTR_ACTIONS CA
              WHERE CI.INQUIRY_ID = CA.INQUIRY_ID
                                       AND CA.ACTION_ID = (SELECT MAX(CA2.ACTION_ID)
              FROM CCTR_ACTIONS CA2
              WHERE TRUNC(CA2.ACTION_START_DATE) = &PDATE
              AND CA2.INQUIRY_ID = CA.INQUIRY_ID)
              AND CA.STATUS 'Z')
              • 4. Re: Need help in query rewrite
                user590978
                Thanks so much for reply..

                I am sending the explian plan for the query:
                Plan hash value: 2196234398
                 
                ----------------------------------------------------------------------------------------------------------
                | Id  | Operation                            | Name              | Rows  | Bytes | Cost (%CPU)| Time     |
                ----------------------------------------------------------------------------------------------------------
                |   0 | SELECT STATEMENT                     |                   |     1 |    46 |    23   (9)| 00:00:01 |
                |   1 |  SORT AGGREGATE                      |                   |     1 |    46 |            |          |
                |   2 |   NESTED LOOPS SEMI                  |                   |     1 |    46 |    23   (9)| 00:00:01 |
                |   3 |    NESTED LOOPS                      |                   |     1 |    36 |    23   (9)| 00:00:01 |
                |   4 |     VIEW                             | VW_NSO_2          |     1 |    13 |    21   (5)| 00:00:01 |
                |   5 |      HASH UNIQUE                     |                   |     1 |    41 |            |          |
                |   6 |       NESTED LOOPS                   |                   |       |       |            |          |
                |   7 |        NESTED LOOPS                  |                   |     1 |    41 |    21   (5)| 00:00:01 |
                |   8 |         VIEW                         | VW_SQ_1           |    15 |   390 |     6  (17)| 00:00:01 |
                |   9 |          HASH GROUP BY               |                   |    15 |   300 |     6  (17)| 00:00:01 |
                |  10 |           TABLE ACCESS BY INDEX ROWID| CCTR_ACTIONS      |    15 |   300 |     5   (0)| 00:00:01 |
                |* 11 |            INDEX RANGE SCAN          | CCTR_ACTIONS_IDX2 |    15 |       |     3   (0)| 00:00:01 |
                |* 12 |         INDEX UNIQUE SCAN            | CCTR_ACTIONS_PK   |     1 |       |     0   (0)| 00:00:01 |
                |* 13 |        TABLE ACCESS BY INDEX ROWID   | CCTR_ACTIONS      |     1 |    15 |     1   (0)| 00:00:01 |
                |* 14 |     TABLE ACCESS BY INDEX ROWID      | CCTR_INQUIRIES    |     1 |    23 |     1   (0)| 00:00:01 |
                |* 15 |      INDEX UNIQUE SCAN               | CCTR_INQUIRIES_PK |     1 |       |     0   (0)| 00:00:01 |
                |* 16 |    INDEX UNIQUE SCAN                 | CC_CUD_PK         |    15 |   150 |     0   (0)| 00:00:01 |
                ----------------------------------------------------------------------------------------------------------
                 
                Predicate Information (identified by operation id):
                ---------------------------------------------------
                 
                  11 - access(TRUNC(INTERNAL_FUNCTION("ACTION_START_DATE"))=TO_DATE(' 2013-03-11 00:00:00', 
                              'syyyy-mm-dd hh24:mi:ss'))
                  12 - access("CA"."ACTION_ID"="MAX(CA2.ACTION_ID)")
                  13 - filter("CA"."STATUS"<>'Z' AND "ITEM_1"="CA"."INQUIRY_ID")
                  14 - filter("CI"."ID_TYPE"='F' AND TRUNC(SYSDATE@!)-TRUNC(INTERNAL_FUNCTION("START_INQUIRY"))>=6
                              0 AND "BCBSM_SERVICING_REPORTS"."GET_CSRUSER_QA"("CI"."CURRENT_QUEUE")=12458)
                  15 - access("INQUIRY_ID"="INQUIRY_ID")
                  16 - access("CODE_ID"="CI"."CATEGORY_ID" AND "CODE_TYPE"='RTCAT')
                • 5. Re: Need help in query rewrite
                  sb92075
                  user590978 wrote:
                  Hi, I have following below query and showing more cost.
                  your post EXPLAIN PLAN shows 1 row returned in 1 second.
                  It appears that you suffer from Compulsive Tuning Disorder.


                  Are statistics current for all tables indexes?
                  • 6. Re: Need help in query rewrite
                    chris227
                    Did you try the suggestion posted by sushaant?
                    At least you dont need the distinct and the rewrite of
                    AND CI.START_INQUIRY >= TRUNC(SYSDATE - 60) as proposed is also worth a try if there is an index on CI.START_INQUIRY
                    • 7. Re: Need help in query rewrite
                      user590978
                      Yes, This is actual explain plan from prod i got it..
                      DESCRIPTION                    OWNER_NAME     OBJECT_NAME          COST     CARDINALITY     BYTES                         
                      -------------------------------------------------------------------------------------------------------------
                      SELECT STATEMENT, GOAL = ALL_ROWS                              4092     1     
                       SORT AGGREGATE                                             1     
                        VIEW     SYS                                             4092     1     
                         HASH UNIQUE                                             4092     1          174
                          NESTED LOOPS                                        4091     1          174
                           NESTED LOOPS                                        4089     1          135
                            NESTED LOOPS                                        4089     21          2373
                             NESTED LOOPS                                        3487     305          10980
                              VIEW     SYS     VW_SQ_1                                   94     1696          20352
                               HASH GROUP BY                                        94     1696          49184
                                FILTER                         
                                 TABLE ACCESS BY INDEX ROWID     BCBS_OWNER     CCTR_ACTIONS          93     1696          49184
                                  INDEX RANGE SCAN          BCBS_OWNER     CCTR_ACTIONS_IDX     2     7          1702     
                              TABLE ACCESS BY INDEX ROWID     BCBS_OWNER     CCTR_ACTIONS          2     1          24
                               INDEX UNIQUE SCAN          BCBS_OWNER     CCTR_ACTIONS_PK          1     1     
                             TABLE ACCESS BY INDEX ROWID     BCBS_OWNER     CCTR_INQUIRIES          2     1          77
                              INDEX UNIQUE SCAN          BCBS_OWNER     CCTR_INQUIRIES_PK     1     1     
                            INDEX UNIQUE SCAN     BCBS_OWNER     CC_CUD_PK                    0     1          22
                           TABLE ACCESS BY INDEX ROWID     BCBS_OWNER     CCTR_CONTACTS          2     1          39
                            INDEX UNIQUE SCAN     BCBS_OWNER     CCTR_CONTACTS_PK               1     1     
                      
                      -------------------------------------------------------------------------------------------------------------------
                      • 8. Re: Need help in query rewrite
                        user590978
                        Yes Chris, I tried what Sushant suggested but cost is same.
                        • 9. Re: Need help in query rewrite
                          SomeoneElse
                          You have yet to tell us what actual problem you are having.
                          • 10. Re: Need help in query rewrite
                            user590978
                            Hi, Oh my bad. Because of this query the job taking 21 minutes to execute it.
                            I want to reduce the cost so it will be run fast. as this is impacting other as well.
                            • 11. Re: Need help in query rewrite
                              chris227
                              Do not compare cost. Cost are not comparable between different queries.
                              Are the results the same?
                              Does it takes the same runtime?
                              If it gives the correct results and ifit is also faster i would examine the plan of the new query.
                              And dont use explain plan, use something like
                              select * from table(dbms_xplan.display_cursor(null, null, 'ALLSTATS LAST'));
                              Read on this before in this thread
                              HOW TO: Post a SQL statement tuning request - template posting
                              • 12. Re: Need help in query rewrite
                                user10857924
                                (1) DISTINCT inside "in suquery" does not make sense
                                (2) trunc on the table column elimate the chance of using index
                                (3) Also don't compare cost its just a number, do you have any performance issue?
                                (4) last why you are counting? are you checking the exisitng of row? what business requirement you are trying to solve?

                                I just rewrite the query as below
                                select count(*)
                                FROM CCTR_INQUIRIES CI, CCTR_CONTACTS CCC,
                                (
                                     select CA.INQUIRY_ID
                                     from
                                     (
                                          SELECT CA.INQUIRY_ID, rank()over(partition by INQUIRY_ID order by ACTION_ID desc) as rnk
                                          FROM CCTR_ACTIONS CA
                                          where CA.STATUS!='Z' and ACTION_START_DATE= trunc(&PDATE)
                                     )X where rnk=1
                                )X
                                where 
                                x.INQUIRY_ID=INQUIRY_ID 
                                and CI.CONTACT_ID = CCC.CONTACT_ID
                                and CI.START_INQUIRY >= trunc(sysdate -60)
                                AND CI.ID_TYPE = &PDEPARTMENT
                                AND GET_CSRUSER_QA(CI.CURRENT_QUEUE) = &PCSR_USERID
                                AND CI.CATEGORY_ID IN
                                                    (SELECT CODE_ID
                                                  FROM CC_USER_DEFINED_CODES
                                                 WHERE CODE_TYPE = 'RTCAT');
                                HTH

                                Thanks...
                                • 13. Re: Need help in query rewrite
                                  sushaant
                                  can u provide us approximate record size of these tables and available indexes
                                  • 14. Re: Need help in query rewrite
                                    user590978
                                    Thank you so much sushant , this query is running fast after i changes the indexes.

                                    Thanks everyone for helping me out..

                                    Appreciated....
                                    1 2 Previous Next