[Solved] Group by clause is taking more time for executing in oracle
Looking to automatically optimize YOUR SQL query? Start for free.

EverSQL Database Performance Knowledge Base

Group by clause is taking more time for executing in oracle

Database type:

I have a select query with group by clause. When I execute this query it is taking more time to execute. . Without using aggregate functions and group by clause I am getting the result very fast. If I use group by clause and aggregate functions then it takes time. Is there any way to improve the performance for the below query? 

SELECT a.unit_class_su class_type,
    a.unit_type_su class_unit_type,
    a.unit_code_su class_unit_code,
    a.item_no,
    a.item_type,
    b.item_name_official item_name_global,
    NULL in_stock_range_code,
    NULL in_cust_ord_range_code,
    NULL valid_from_dtime,
    NULL valid_to_dtime,
    MIN (a.iu_date) upd_dtime,
    MIN (a.ii_date) ins_dtime,
    NULL del_dtime
  FROM scpixcem01.cox_cem_relation_t a,
    crccem01.cox_item_v2_t b
  WHERE a.su_relation_status = 'COMMITTED'
  AND a.item_no              = b.item_no
  AND a.item_type            = b.item_type
  AND a.unit_code_su NOT    IN (SELECT BU_CODE FROM COX.COX_STORES_T WHERE IS_HOME_SHOPPING_STORE = 'YES' AND DELETE_DTIME IS NULL)
  GROUP BY a.unit_class_su,
    a.unit_type_su,
    a.unit_code_su,
    a.item_no,
    a.item_type,
    b.item_name_official

How to optimize this SQL query?

The following recommendations will help you in your SQL tuning process.
You'll find 3 sections below:

  1. Description of the steps you can take to speed up the query.
  2. The optimal indexes for this query, which you can copy and create in your database.
  3. An automatically re-written query you can copy and execute in your database.
The optimization process and recommendations:
  1. Create Optimal Indexes (modified query below): The recommended indexes are an integral part of this optimization effort and should be created before testing the execution duration of the optimized query.
  2. Replace In Subquery With Correlated Exists (modified query below): In many cases, an EXISTS subquery with a correlated condition will perform better than a non correlated IN subquery.
Optimal indexes for this query:
CREATE INDEX cox_t_idx_is_store_delete_dti_bu_code ON COX_STORES_T (IS_HOME_SHOPPING_STORE,DELETE_DTIME,BU_CODE);
CREATE INDEX cox_relation_idx_su_status_item_no_item_type ON cox_cem_relation_t (su_relation_status,item_no,item_type);
CREATE INDEX cox_v2_idx_item_no_item_type ON cox_item_v2_t (item_no,item_type);
The optimized query:
SELECT
        a.unit_class_su class_type,
        a.unit_type_su class_unit_type,
        a.unit_code_su class_unit_code,
        a.item_no,
        a.item_type,
        b.item_name_official item_name_global,
        NULL in_stock_range_code,
        NULL in_cust_ord_range_code,
        NULL valid_from_dtime,
        NULL valid_to_dtime,
        MIN(a.iu_date) upd_dtime,
        MIN(a.ii_date) ins_dtime,
        NULL del_dtime 
    FROM
        scpixcem01.cox_cem_relation_t a,
        crccem01.cox_item_v2_t b 
    WHERE
        a.su_relation_status = 'COMMITTED' 
        AND a.item_no = b.item_no 
        AND a.item_type = b.item_type 
        AND NOT EXISTS (
            SELECT
                1 
            FROM
                COX.COX_STORES_T 
            WHERE
                (
                    COX.COX_STORES_T.IS_HOME_SHOPPING_STORE = 'YES' 
                    AND COX.COX_STORES_T.DELETE_DTIME IS NULL
                ) 
                AND (
                    a.unit_code_su = COX.COX_STORES_T.BU_CODE
                )
        ) 
    GROUP BY
        a.unit_class_su,
        a.unit_type_su,
        a.unit_code_su,
        a.item_no,
        a.item_type,
        b.item_name_official

Related Articles



* original question posted on StackOverflow here.