Sunday, April 19, 2015

Avoid count(1) In Inner Query and add sum and group by in outer query

If the requirement to  find the count from different tables avoid count and group by in inner query  in which cost will be more we can avoid that by adding below changes

 SELECT try.employee_id,
            Officer_name,
            (SELECT (FIRST_NAME_EN || MIDDLE_NAME_EN || LAST_NAME_EN)
               FROM EMPLOYEE
              WHERE employee_id = try.employee_id)
               Name_en,
             SUM (no_of_entry) no_of_entry,
            SUM (no_of_exits) no_of_exits,
          trg_date AS Transaction_date
       FROM ( (SELECT au.employee_id,
                      AU.USER_GROUP_ID,
                      au.user_name Officer_name,
                      1 no_of_entry,
                      NULL no_of_exits,
                      NULL cs_application_count,
                      NULL no_entrt_permits,
                      NULL no_approved_ras,
                      NULL no_outpass_application,
                      NULL no_approved_nsd,
                      NULL no_approved_bl,
                      TRUNC (travel_date) trg_date
                 FROM travel_detail_log td, application_user au
                WHERE     td.created_by = AU.CREATED_BY
                      AND TRAVEL_TYPE_ID = 1
                      AND td.revtype <> 2
                      AND td.is_settled = 0)
             UNION ALL
             (SELECT au.employee_id,
                     AU.USER_GROUP_ID,
                     au.user_name Officer_name,
                     NULL no_of_entry,
                     1 no_of_exits,
                     NULL cs_application_count,
                     NULL no_entrt_permits,
                     NULL no_approved_ras,
                     NULL no_outpass_application,
                     NULL no_approved_nsd,
                     NULL no_approved_bl,
                     TRUNC (travel_date) trg_date
                FROM travel_detail_log td, application_user au
               WHERE     td.created_by = AU.CREATED_BY
                     AND TRAVEL_TYPE_ID = 2
                     AND td.revtype <> 2
                     AND td.is_settled = 0)
          ) try
     GROUP BY try.employee_id,
            Officer_name,
            trg_date,
            user_group_id      

Solution:
By adding group by in and sum  outer query we can bring down the cost of the Query