1 Reply Latest reply: Feb 4, 2013 10:53 PM by jeneesh RSS

    Query tuning:

    user12090988
      Hi all,

      How can I tune the below query for better performance.
      SELECT COUNT(1) 
      FROM AFMS_RELT_FEEDBK
      WHERE
      (CSE_CD IN (SELECT CSE_CD FROM FMS_RELT_FEEDBK)) OR
      (RELT_CSE_CD IN (SELECT RELT_CSE_CD FROM FMS_RELT_FEEDBK));
      
      
      PLAN_TABLE_OUTPUT
      --------------------------------------------------------------------------------------------------------------------------------------------------------------------------
      Plan hash value: 3722087114
      
      ----------------------------------------------------------------------------------------------
      | Id  | Operation              | Name                | Rows  | Bytes | Cost (%CPU)| Time     |
      ----------------------------------------------------------------------------------------------
      |   0 | SELECT STATEMENT       |                     |     1 |    20 |   175M  (1)|584:08:35 |
      |   1 |  SORT AGGREGATE        |                     |     1 |    20 |            |          |
      |*  2 |   FILTER               |                     |       |       |            |          |
      |   3 |    INDEX FAST FULL SCAN| AFMS_RELT_FEEDBK_PK |    44M|   851M| 62751   (1)| 00:12:34 |
      |*  4 |    INDEX RANGE SCAN    | FMS_RELT_FEEDBK_PK  |     2 |    28 |     4   (0)| 00:00:01 |
      |*  5 |    INDEX FAST FULL SCAN| FMS_RELT_FEEDBK_PK  |     2 |    26 |   764   (1)| 00:00:10 |
      ----------------------------------------------------------------------------------------------
      
      Predicate Information (identified by operation id):
      ---------------------------------------------------
      
         2 - filter( EXISTS (SELECT 0 FROM "FMS"."FMS_RELT_FEEDBK" "FMS_RELT_FEEDBK" WHERE
                    "CSE_CD"=:B1) OR  EXISTS (SELECT 0 FROM "FMS"."FMS_RELT_FEEDBK" "FMS_RELT_FEEDBK"
                    WHERE "RELT_CSE_CD"=:B2))
         4 - access("CSE_CD"=:B1)
         5 - filter("RELT_CSE_CD"=:B1)
      
      21 rows selected.