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
The following recommendations will help you in your SQL tuning process.
You'll find 3 sections below:
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);
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