DELETE query is taking too long time in the production....

Database
Fan

DELETE query is taking too long time in the production....

Hi,

I need your help to identify the issue in the below query to optimize...

query :

DELETE ADSP
FROM
ARCPROD.AGG_DLY_STR_PROD ADSP,
ARCPROD.INC_ETL_CUR_DAYS INC_ETL
WHERE
ADSP.DATE_KEY = INC_ETL.DATE_KEY;

Here date_key for both aggregate table(ADSP) and control table(INC_ETL) is a primary key and also collect statistics available for both columns also.

explain plan :

Explanation
1) First, we lock a distinct ARCPROD."pseudo table" for write on a
RowHash to prevent global deadlock for ARCPROD.ADSP.
2) Next, we lock a distinct ARCPROD."pseudo table" for read on a
RowHash to prevent global deadlock for ARCPROD.INC_ETL.
3) We lock ARCPROD.ADSP for write, and we lock ARCPROD.INC_ETL for
read.
4) We do an all-AMPs RETRIEVE step from ARCPROD.INC_ETL by way of an
all-rows scan with no residual conditions into Spool 2 (all_amps),
which is duplicated on all AMPs. Then we do a SORT to partition
by rowkey. The size of Spool 2 is estimated with high confidence
to be 14 rows (238 bytes). The estimated time for this step is
0.01 seconds.
5) We do an all-AMPs JOIN step from Spool 2 (Last Use) by way of an
all-rows scan, which is joined to ARCPROD.ADSP by way of an
all-rows scan with no residual conditions. Spool 2 and
ARCPROD.ADSP are joined using a product join, with a join
condition of ("ARCPROD.ADSP.DATE_KEY = DATE_KEY") enhanced by
dynamic partition elimination. The input table ARCPROD.ADSP will
not be cached in memory, but it is eligible for synchronized
scanning. The result goes into Spool 1 (all_amps), which is built
locally on the AMPs. Then we do a SORT to partition Spool 1 by
rowkey and the sort key in spool field1 eliminating duplicate rows.
The size of Spool 1 is estimated with low confidence to be 347,603
rows (6,256,854 bytes). The estimated time for this step is 0.46
seconds.
6) We do an all-AMPs MERGE DELETE to ARCPROD.ADSP from Spool 1 (Last
Use) via the row id. The size is estimated with low confidence to
be 347,603 rows. The estimated time for this step is 19 minutes
and 59 seconds.
7) Finally, we send out an END TRANSACTION step to all AMPs involved
in processing the request.
-> No rows are returned to the user as the result of statement 1.
The total estimated time is 19 minutes and 59 seconds.

It shows 19 mins and 59 sec to run in the live environment. I am really wondering about the explain plan..

can you please anybody help me how to do optimize the above query ASAP?

Regards

Kumar
Tags (1)
4 REPLIES
Enthusiast

Re: DELETE query is taking too long time in the production....

Just a quick thought, did you consider or is this worth considering:
1) Create a new table with data that does *not* include DATE_KEY (TableNew).
2) Rename original table to something else (ADSP to Table2drop).
3) Rename TableNew to ADSP.

In many cases above solution is not feasible as there are other indexes, AJI, etc., defined on ADSP and require rebuilding. In some cases, you are just not allowed to do it in PROD.

Just curious, how do table definitions look on these tables.
Teradata Employee

Re: DELETE query is taking too long time in the production....

Please, paste both table definition. And the table demographic too.
This query really takes 19 minutes? Have you tested it yet?
l_k
Enthusiast

Re: DELETE query is taking too long time in the production....

Please find the table definition for both:

1) CREATE SET TABLE AGG_DLY_STR_PROD ,NO FALLBACK ,
NO BEFORE JOURNAL,
NO AFTER JOURNAL,
CHECKSUM = DEFAULT
(
DATE_KEY INTEGER NOT NULL,
STORE_KEY INTEGER NOT NULL,
PRODUCT_KEY INTEGER NOT NULL,
STORE_CODE VARCHAR(10) CHARACTER SET LATIN NOT CASESPECIFIC,
PRODUCT_CODE VARCHAR(20) CHARACTER SET LATIN NOT CASESPECIFIC,
PRODUCT_DETAIL_CODE VARCHAR(10) CHARACTER SET LATIN NOT CASESPECIFIC,
FLG_FRANCHISE INTEGER NOT NULL DEFAULT 0 COMPRESS 0 ,
SALE_TOT_QTY DECIMAL(12,3) DEFAULT 0.000 COMPRESS (0.000 ,10.000 ,9.000 ,8.000 ,7.000 ,6.000 ,5.000 ,4.000 ,14.000 ,3.000 ,13.000 ,2.000 ,12.000 ,1.000 ,11.000 ),
SALE_NET_VAL DECIMAL(16,4) DEFAULT 0.0000 COMPRESS (0.0000 ,1.6900 ,2.9700 ,4.8900 ,6.5800 ,3.3800 ,5.9400 ,6.9900 ,0.5900 ,3.7900 ,5.0700 ,1.0000 ,9.9600 ,2.6900 ,5.8900 ,13.9800 ,7.5800 ,4.3800 ,7.9900 ,1.5900 ,4.7900 ,2.0000 ,18.0000 ,20.9700 ,3.6900 ,11.3700 ,8.5800 ,2.1800 ,10.5000 ,5.3800 ,8.9900 ,2.5900 ,3.8700 ,5.7900 ,3.0000 ,11.9600 ,1.4900 ,4.6900 ,5.9700 ,15.9800 ,9.5800 ,3.1800 ,6.3800 ,6.7900 ,8.0700 ,9.9900 ,3.5900 ,20.0000 ,4.0000 ,2.4900 ,5.6900 ,7.3800 ,12.5000 ,19.9500 ,0.7500 ,1.3900 ,2.6700 ,3.9500 ,10.9900 ,4.5900 ,13.9600 ,5.0000 ,3.4900 ,4.7700 ,8.3800 ,1.9800 ,17.9800 ,5.1800 ,6.8700 ,2.3900 ,4.9500 ,11.9900 ,5.5900 ,6.0000 ,1.2900 ,8.9700 ,4.4900 ,2.9800 ,6.5900 ,3.3900 ,11.0700 ,12.9900 ,7.0000 ,15.9600 ,2.2900 ,3.5700 ,5.4900 ,19.9800 ,7.1800 ,3.9800 ,13.9900 ,1.1900 ,3.7500 ,4.3900 ,5.6700 ,8.0000 ,7.7700 ,3.2900 ,6.4900 ,0.5000 ,1.7800 ,4.9800 ,14.9900 ,2.1900 ,9.8700 ,5.3900 ,9.0000 ,5.1600 ,7.4900 ,1.0900 ,2.3700 ,4.2900 ,11.9700 ,1.5000 ,2.7800 ,17.5000 ,5.9800 ,3.1900 ,4.4700 ,6.3900 ,10.0000 ,6.5700 ,8.4900 ,2.0900 ,5.2900 ,6.9800 ,2.5000 ,3.7800 ,0.9900 ,9.9500 ,4.1900 ,19.9600 ,3.9600 ,6.2900 ,7.9800 ,1.5800 ,3.5000 ,4.7800 ,1.9900 ,5.1900 ,12.0000 ,0.8900 ,14.9700 ,5.3700 ,8.9800 ,2.5800 ,4.5000 ,17.9400 ,5.7800 ,7.4700 ,2.9900 ,2.7600 ,5.9600 ,1.2500 ,1.8900 ,9.5700 ,6.7800 ,9.9800 ,3.5800 ,0.7900 ,2.0700 ,3.9900 ,14.0000 ,1.2000 ,2.2500 ,2.8900 ,4.1700 ,7.7800 ,1.3800 ,10.9800 ,4.5800 ,1.7900 ,4.9900 ,7.9600 ,15.0000 ,23.9600 ,0.6900 ,8.3700 ,3.8900 ,17.9700 ,7.5000 ,2.3800 ,11.9800 ,5.5800 ,14.9500 ,2.7900 ,10.4700 ,5.9900 ),
SALE_RETURN_QTY DECIMAL(12,3) DEFAULT 0.000 COMPRESS 0.000 ,
SALE_RETURN_VAL DECIMAL(16,4) DEFAULT 0.0000 COMPRESS 0.0000 ,
SALE_GLOBAL_DISC_QTY DECIMAL(12,3) DEFAULT 0.000 COMPRESS 0.000 ,
SALE_GLOBAL_DISC_VAL DECIMAL(16,4) DEFAULT 0.0000 COMPRESS 0.0000 ,
SALE_EMP_DISC_QTY DECIMAL(12,3) DEFAULT 0.000 COMPRESS 0.000 ,
SALE_EMP_DISC_VAL DECIMAL(16,4) DEFAULT 0.0000 COMPRESS 0.0000 ,
SALE_MARKDOWN_QTY DECIMAL(12,3) DEFAULT 0.000 COMPRESS (0.000 ,10.000 ,9.000 ,8.000 ,7.000 ,6.000 ,5.000 ,4.000 ,3.000 ,13.000 ,2.000 ,12.000 ,1.000 ,11.000 ),
SALE_MARKDOWN_VAL DECIMAL(16,4) DEFAULT 0.0000 COMPRESS (0.0000 ,1.0000 ,2.0000 ,0.4900 ,0.9000 ,3.0000 ,1.4900 ,0.3900 ,0.8000 ,4.0000 ,0.9800 ,0.2900 ,0.7000 ,1.9800 ,6.0000 ,0.1900 ,0.6000 ,1.6000 ,0.5000 ,1.5000 ,0.4000 ,0.5800 ,0.9900 ,1.4000 ,0.3000 ,0.2000 ,0.7900 ,1.2000 ,0.1000 ,0.6900 ),
SALE_ADJ_QTY DECIMAL(12,3) DEFAULT 0.000 COMPRESS 0.000 ,
SALE_ADJ_VAL DECIMAL(12,3) DEFAULT 0.000 COMPRESS 0.000 ,
SALE_TOT_TAX_VAL DECIMAL(16,4) DEFAULT 0.0000 COMPRESS (0.0000 ,0.6400 ,0.4100 ,0.1800 ,0.5900 ,0.3600 ,0.1300 ,0.5400 ,0.3100 ,0.0800 ,0.4900 ,0.2600 ,0.0300 ,0.4400 ,0.2100 ,0.6200 ,0.3900 ,0.1600 ,0.5700 ,0.3400 ,0.1100 ,0.5200 ,0.2900 ,0.0600 ,0.4700 ,0.2400 ,0.0100 ,0.4200 ,0.1900 ,0.6000 ,0.3700 ,0.1400 ,0.5500 ,0.3200 ,0.0900 ,0.5000 ,0.2700 ,0.0400 ,0.4500 ,0.2200 ,0.4000 ,0.1700 ,0.5800 ,0.3500 ,0.1200 ,0.5300 ,0.3000 ,0.0700 ,0.4800 ,0.2500 ,0.0200 ,0.4300 ,0.2000 ,0.3800 ,0.1500 ,0.5600 ,0.3300 ,0.1000 ,0.5100 ,0.2800 ,0.0500 ,0.4600 ,0.2300 ),
SALE_TOT_VAL_AT_PP DECIMAL(16,4) DEFAULT 0.0000 COMPRESS (0.0000 ,1.2800 ,1.9200 ,2.5600 ,3.2000 ,3.8400 ,4.4800 ,1.0500 ,1.6900 ,4.8900 ,0.8200 ,1.4600 ,2.1000 ,2.7400 ,4.0200 ,6.9900 ,1.2300 ,2.5100 ,1.0000 ,1.6400 ,2.2800 ,2.9200 ,4.2000 ,1.4100 ,2.0500 ,2.6900 ,1.1800 ,1.8200 ,2.4600 ,3.1000 ,3.7400 ,4.3800 ,0.9500 ,1.5900 ,1.3600 ,2.0000 ,2.6400 ,3.2800 ,1.7700 ,2.4100 ,0.9000 ,1.5400 ,2.1800 ,2.8200 ,1.3100 ,1.9500 ,2.5900 ,1.0800 ,1.7200 ,2.3600 ,3.0000 ,1.4900 ,2.1300 ,0.6200 ,1.2600 ,1.9000 ,2.5400 ,1.0300 ,1.6700 ,2.3100 ,2.9500 ,7.2000 ,0.8000 ,1.4400 ,2.0800 ,2.7200 ,3.3600 ,4.0000 ,0.5700 ,1.8500 ,2.4900 ,0.9800 ,1.6200 ,2.2600 ,2.9000 ,3.5400 ,0.7500 ,1.3900 ,2.6700 ,1.1600 ,1.8000 ,2.4400 ,3.0800 ,3.7200 ,5.0000 ,0.9300 ,1.5700 ,2.2100 ,2.8500 ,0.7000 ,1.3400 ,1.9800 ,2.6200 ,3.9000 ,1.1100 ,1.7500 ,2.3900 ,5.5900 ,0.8800 ,1.5200 ,2.1600 ,2.8000 ,6.0000 ,1.2900 ,1.9300 ,1.0600 ,1.7000 ,2.3400 ,2.9800 ,3.6200 ,1.4700 ,2.1100 ,2.7500 ,1.2400 ,1.8800 ,2.5200 ,3.1600 ,3.8000 ,0.3700 ,1.6500 ,0.7800 ,1.4200 ,2.0600 ,2.7000 ,1.1900 ,1.8300 ,2.4700 ,0.9600 ,1.6000 ,2.2400 ,2.8800 ,3.5200 ,4.1600 ,4.8000 ,2.0100 ,2.6500 ,1.1400 ,1.7800 ,2.4200 ,3.0600 ,4.3400 ,0.9100 ,1.5500 ,2.1900 ,1.3200 ,1.9600 ,2.6000 ,3.2400 ,0.8600 ,1.5000 ,2.1400 ,3.4200 ,1.9100 ,2.5500 ,1.0400 ,1.6800 ,2.3200 ,2.9600 ,3.6000 ,4.2400 ,1.4500 ,2.0900 ,2.7300 ,1.2200 ,1.8600 ,2.5000 ,3.1400 ,3.7800 ,0.9900 ,2.2700 ,4.1900 ,0.7600 ,1.4000 ,2.0400 ,2.6800 ,3.3200 ,3.9600 ,5.2400 ,1.1700 ,1.8100 ,2.4500 ,0.9400 ,1.5800 ,2.2200 ,2.8600 ,3.5000 ,1.3500 ,1.9900 ,1.1200 ,1.7600 ,2.4000 ,3.0400 ,3.6800 ,4.3200 ,5.6000 ,0.8900 ,1.5300 ,2.1700 ,0.6600 ,1.3000 ,1.9400 ,2.5800 ,3.2200 ,4.5000 ,1.0700 ,1.7100 ,2.3500 ,0.8400 ,1.4800 ,2.1200 ,2.7600 ,3.4000 ,1.2500 ,1.8900 ,1.0200 ,1.6600 ,2.3000 ,2.9400 ,2.0700 ,1.2000 ,1.8400 ,2.4800 ,3.1200 ,3.7600 ,4.4000 ,5.0400 ,0.9700 ,1.6100 ,2.2500 ,0.7400 ,1.3800 ,2.0200 ,2.6600 ,3.3000 ,1.1500 ,1.7900 ,2.4300 ,0.9200 ,1.5600 ,2.2000 ,2.8400 ,3.4800 ,4.1200 ,5.4000 ,0.6900 ,1.3300 ,1.9700 ,1.1000 ,1.7400 ,2.3800 ,3.6600 ,5.5800 ,1.5100 ,2.1500 ,2.7900 ),
SALE_TOT_VAL_AT_WAP DECIMAL(16,4) DEFAULT 0.0000 COMPRESS 0.0000 ,
SALE_TOT_VAL_AT_HOSP DECIMAL(16,4) DEFAULT 0.0000 COMPRESS 0.0000 ,
SALE_TOT_VAL_AT_SP DECIMAL(16,4) DEFAULT 0.0000 COMPRESS (0.0000 ,80.0000 ,48.0000 ,160.0000 ,16.0000 ,64.0000 ,32.0000 ,1.6900 ,4.8900 ,6.9900 ,0.5900 ,8.9100 ,3.7900 ,13.1600 ,6.7600 ,1.0000 ,9.9600 ,3.5600 ,20.6100 ,2.6900 ,5.8900 ,7.9900 ,1.5900 ,4.7900 ,2.0000 ,18.0000 ,15.2100 ,3.6900 ,26.9100 ,8.9900 ,2.5900 ,5.7900 ,27.9600 ,15.1600 ,8.7600 ,3.0000 ,11.9600 ,5.5600 ,1.4900 ,4.6900 ,6.7900 ,9.9900 ,3.5900 ,20.0000 ,36.0000 ,55.8400 ,4.0000 ,33.2100 ,2.4900 ,5.6900 ,0.7500 ,1.3900 ,16.1100 ,10.9900 ,4.5900 ,44.9100 ,13.9600 ,7.5600 ,17.1600 ,10.7600 ,4.3600 ,5.0000 ,3.4900 ,2.3900 ,49.7500 ,10.7100 ,11.9900 ,5.5900 ,54.0000 ,6.0000 ,1.2900 ,22.4100 ,4.4900 ,6.5900 ,3.3900 ,62.9100 ,12.9900 ,7.0000 ,15.9600 ,9.5600 ,3.1600 ,31.9600 ,19.1600 ,12.7600 ,6.3600 ,2.2900 ,5.4900 ,13.9900 ,1.1900 ,99.7500 ,28.7100 ,4.3900 ,40.0000 ,8.0000 ,24.0000 ,360.0000 ,3.2900 ,11.6100 ,6.4900 ,14.9900 ,2.1900 ,23.3100 ,5.3900 ,14.7600 ,9.0000 ,17.9600 ,11.5600 ,5.1600 ,7.4900 ,1.0900 ,4.2900 ,6.2100 ,1.5000 ,3.1900 ,17.9100 ,250.0000 ,10.0000 ,90.0000 ,8.4900 ,2.0900 ,29.6100 ,5.2900 ,2.5000 ,0.9900 ,4.1900 ,12.5100 ,19.9600 ,13.5600 ,7.1600 ,27.0000 ,35.9600 ,16.7600 ,10.3600 ,3.9600 ,24.2100 ,6.2900 ,3.5000 ,1.9900 ,35.9100 ,5.1900 ,79.8400 ,28.0000 ,47.8400 ,15.8400 ,95.8400 ,63.8400 ,12.0000 ,31.8400 ,0.8900 ,2.9900 ,24.7500 ,125.0000 ,21.9600 ,15.5600 ,9.1600 ,2.7600 ,45.0000 ,5.9600 ,13.4100 ,1.2500 ,1.8900 ,0.7900 ,53.9100 ,3.9900 ,25.1100 ,2.8900 ,22.5000 ,19.7100 ,1.7900 ,74.7500 ,4.9900 ,39.9600 ,14.3600 ,7.9600 ,23.9600 ,11.1600 ,4.7600 ,0.6900 ,3.8900 ,31.4100 ,14.3100 ,2.7900 ,5.9900 ),
VO_TOT_RECD_QTY DECIMAL(12,3) DEFAULT 0.000 COMPRESS (0.000 ,10.000 ,9.000 ,8.000 ,6.000 ,16.000 ,48.000 ,5.000 ,4.000 ,36.000 ,3.000 ,24.000 ,2.000 ,12.000 ,1.000 ),
VO_NET_RECD_VAL DECIMAL(16,4) DEFAULT 0.0000 COMPRESS 0.0000 ,
VO_RETURN_QTY DECIMAL(12,3) DEFAULT 0.000 COMPRESS 0.000 ,
VO_RETURN_VAL DECIMAL(16,4) DEFAULT 0.0000 COMPRESS 0.0000 ,
VO_GLOBAL_DISC_QTY DECIMAL(12,3) DEFAULT 0.000 COMPRESS 0.000 ,
VO_GLOBAL_DISC_VAL DECIMAL(16,4) DEFAULT 0.0000 COMPRESS 0.0000 ,
VO_TOT_TAX_VAL DECIMAL(16,4) DEFAULT 0.0000 COMPRESS 0.0000 ,
VO_TOT_RECD_VAL_AT_PP DECIMAL(16,4) DEFAULT 0.0000 COMPRESS 0.0000 ,
VO_TOT_RECD_VAL_AT_WAP DECIMAL(16,4) DEFAULT 0.0000 COMPRESS 0.0000 ,
VO_TOT_RECD_VAL_AT_HOSP DECIMAL(16,4) DEFAULT 0.0000 COMPRESS 0.0000 ,
VO_TOT_RECD_VAL_AT_SP DECIMAL(16,4) DEFAULT 0.0000 COMPRESS 0.0000 ,
INV_TOT_TXN_QTY DECIMAL(12,3) DEFAULT 0.000 COMPRESS 0.000 ,
INV_TOT_TXN_VAL_AT_PP DECIMAL(16,4) DEFAULT 0.0000 COMPRESS 0.0000 ,
INV_TOT_TXN_VAL_AT_WAP DECIMAL(16,4) DEFAULT 0.0000 COMPRESS 0.0000 ,
INV_TOT_TXN_VAL_AT_HOSP DECIMAL(16,4) DEFAULT 0.0000 COMPRESS 0.0000 ,
INV_TOT_TXN_VAL_AT_SP DECIMAL(16,4) DEFAULT 0.0000 COMPRESS 0.0000 ,
ARC_DATE DATE FORMAT 'YYYY-MM-DD' NOT NULL COMPRESS (DATE '2006-11-20',DATE '2008-03-20',DATE '2006-11-21',DATE '2007-11-05',DATE '2008-03-21',DATE '2006-11-22',DATE '2007-11-06',DATE '2007-11-08',DATE '2007-11-10',DATE '2008-03-26',DATE '2007-11-11',DATE '2008-03-27',DATE '2007-11-12',DATE '2008-03-28',DATE '2007-11-13',DATE '2008-03-29',DATE '2007-06-02',DATE '2008-03-30',DATE '2008-03-31',DATE '2007-11-16',DATE '2007-06-05',DATE '2007-11-17',DATE '2007-11-18',DATE '2007-06-07',DATE '2007-11-19',DATE '2007-11-20',DATE '2007-11-21',DATE '2008-11-05',DATE '2008-11-07',DATE '2007-11-25',DATE '2008-11-09',DATE '2007-06-14',DATE '2007-06-15',DATE '2007-11-30',DATE '2008-11-14',DATE '2007-06-21',DATE '2008-06-07',DATE '2007-06-25',DATE '2008-11-21',DATE '2008-11-22',DATE '2007-06-28',DATE '2008-11-24',DATE '2008-11-26',DATE '2008-01-04',DATE '2008-06-16',DATE '2008-11-28',DATE '2008-01-05',DATE '2008-01-06',DATE '2008-06-19',DATE '2008-01-08',DATE '2008-01-11',DATE '2008-06-23',DATE '2008-01-12',DATE '2008-01-13',DATE '2007-09-01',DATE '2009-01-01',DATE '2008-01-18',DATE '2008-01-19',DATE '2009-01-03',DATE '2008-01-20',DATE '2006-04-09',DATE '2007-09-05',DATE '2009-01-05',DATE '2009-01-07',DATE '2008-01-25',DATE '2006-04-14',DATE '2008-01-26',DATE '2006-04-15',DATE '2008-01-27',DATE '2007-09-13',DATE '2006-09-30',DATE '2007-09-16',DATE '2008-09-01',DATE '2007-04-07',DATE '2007-09-19',DATE '2008-09-05',DATE '2007-09-22',DATE '2007-09-24',DATE '2007-04-13',DATE '2008-09-09',DATE '2007-04-14',DATE '2007-09-26',DATE '2008-09-11',DATE '2007-09-28',DATE '2007-09-30',DATE '2008-04-03',DATE '2008-04-04',DATE '2008-04-05',DATE '2008-09-17',DATE '2008-04-06',DATE '2008-09-19',DATE '2008-09-20',DATE '2008-04-11',DATE '2008-04-12',DATE '2008-04-13',DATE '2005-12-30',DATE '2008-09-26',DATE '2006-12-17',DATE '2007-12-01',DATE '2007-12-02',DATE '2008-04-18',DATE '2008-04-19',DATE '2008-04-20',DATE '2006-12-21',DATE '2006-12-22',DATE '2006-12-23',DATE '2007-12-07',DATE '2007-12-08',DATE '2007-12-09',DATE '2008-04-25',DATE '2008-04-26',DATE '2006-02-03',DATE '2007-12-11',DATE '2008-04-27',DATE '2006-02-04',DATE '2006-12-30',DATE '2007-12-14',DATE '2007-12-15',DATE '2007-07-04',DATE '2007-12-16',DATE '2007-12-17',DATE '2007-12-18',DATE '2006-02-11',DATE '2007-12-19',DATE '2006-02-12',DATE '2007-12-20',DATE '2007-12-21',DATE '2008-12-05',DATE '2007-07-10',DATE '2007-12-22',DATE '2008-12-06',DATE '2007-12-23',DATE '2008-12-08',DATE '2007-02-02',DATE '2007-02-03',DATE '2007-12-28',DATE '2008-12-12',DATE '2007-12-29',DATE '2008-12-13',DATE '2007-12-30',DATE '2008-07-02',DATE '2008-12-14',DATE '2007-07-19',DATE '2007-12-31',DATE '2008-12-15',DATE '2007-07-20',DATE '2008-07-04',DATE '2008-12-19',DATE '2008-07-08',DATE '2008-12-20',DATE '2008-12-22',DATE '2007-07-27',DATE '2008-07-11',DATE '2008-12-23',DATE '2007-07-28',DATE '2008-12-24',DATE '2008-02-01',DATE '2008-02-02',DATE '2007-07-31',DATE '2008-02-03',DATE '2008-07-16',DATE '2008-12-29',DATE '2008-07-18',DATE '2008-02-08',DATE '2008-02-09',DATE '2008-02-10',DATE '2008-07-23',DATE '2008-02-12',DATE '2008-07-25',DATE '2008-02-15',DATE '2008-02-16',DATE '2008-02-17',DATE '2008-07-29',DATE '2006-05-06',DATE '2007-10-02',DATE '2007-10-03',DATE '2007-10-05',DATE '2008-02-22',DATE '2008-02-23',DATE '2007-10-08',DATE '2008-02-24',DATE '2006-05-13',DATE '2007-10-09',DATE '2007-10-11',DATE '2007-10-12',DATE '2007-10-13',DATE '2008-02-29',DATE '2007-10-14',DATE '2007-10-15',DATE '2007-10-16',DATE '2008-10-03',DATE '2007-10-20',DATE '2007-10-21',DATE '2007-10-23',DATE '2008-10-07',DATE '2007-05-12',DATE '2007-10-25',DATE '2007-10-26',DATE '2008-10-10',DATE '2007-10-27',DATE '2008-10-12',DATE '2007-10-29',DATE '2007-05-18',DATE '2007-10-30',DATE '2008-05-02',DATE '2008-10-14',DATE '2007-05-19',DATE '2008-05-03',DATE '2008-05-05',DATE '2008-10-17',DATE '2007-05-23',DATE '2008-05-07',DATE '2007-05-24',DATE '2008-05-08',DATE '2007-05-25',DATE '2007-05-26',DATE '2008-05-10',DATE '2008-05-11',DATE '2008-10-23',DATE '2008-05-15',DATE '2008-10-27',DATE '2008-05-16',DATE '2008-10-31',DATE '2008-05-20',DATE '2008-05-22',DATE '2008-05-23',DATE '2008-05-27',DATE '2006-03-04',DATE '2006-03-05',DATE '2007-08-03',DATE '2008-05-31',DATE '2007-08-07',DATE '2007-08-10',DATE '2007-08-16',DATE '2007-08-17',DATE '2008-08-01',DATE '2007-08-20',DATE '2006-03-25',DATE '2007-08-29',DATE '2008-03-01',DATE '2008-08-13',DATE '2008-03-02',DATE '2008-08-15',DATE '2008-08-17',DATE '2008-03-07',DATE '2008-03-08',DATE '2008-03-09',DATE '2008-08-22',DATE '2008-03-14',DATE '2008-03-15',DATE '2008-03-16',DATE '2008-08-29',DATE '2006-11-18',DATE '2007-11-02',DATE '2006-11-19'),
ACCRUED_REBATE_VAL DECIMAL(16,4) DEFAULT 0.0000 COMPRESS 0.0000 ,
ACCRUED_REBATE_VAL_LY DECIMAL(16,4) DEFAULT 0.0000 COMPRESS 0.0000 ,
SALE_TOT_QTY_LY DECIMAL(12,3) DEFAULT 0.000 COMPRESS (0.000 ,10.000 ,9.000 ,8.000 ,7.000 ,6.000 ,5.000 ,4.000 ,14.000 ,3.000 ,13.000 ,2.000 ,12.000 ,1.000 ,11.000 ),
SALE_NET_VAL_LY DECIMAL(16,4) DEFAULT 0.0000 COMPRESS (0.0000 ,1.6900 ,2.9700 ,4.8900 ,6.5800 ,3.3800 ,6.9900 ,3.7900 ,5.0700 ,1.0000 ,2.6900 ,13.9800 ,7.5800 ,4.3800 ,7.9900 ,1.5900 ,4.7900 ,2.0000 ,3.6900 ,8.5800 ,2.1800 ,5.3800 ,8.9900 ,2.5900 ,3.8700 ,5.7900 ,3.0000 ,11.9600 ,1.4900 ,4.6900 ,5.9700 ,15.9800 ,3.1800 ,6.3800 ,8.0700 ,9.9900 ,3.5900 ,4.0000 ,2.4900 ,5.6900 ,7.3800 ,12.5000 ,19.9500 ,0.7500 ,1.3900 ,10.9900 ,4.5900 ,5.0000 ,3.4900 ,8.3800 ,1.9800 ,5.1800 ,6.8700 ,2.3900 ,4.9500 ,11.9900 ,5.5900 ,6.0000 ,1.2900 ,8.9700 ,4.4900 ,2.9800 ,3.3900 ,12.9900 ,7.0000 ,15.9600 ,2.2900 ,5.4900 ,19.9800 ,7.1800 ,3.9800 ,13.9900 ,1.1900 ,3.7500 ,4.3900 ,8.0000 ,3.2900 ,6.4900 ,0.5000 ,1.7800 ,4.9800 ,14.9900 ,2.1900 ,5.3900 ,9.0000 ,7.4900 ,1.0900 ,2.3700 ,4.2900 ,11.9700 ,1.5000 ,2.7800 ,5.9800 ,3.1900 ,4.4700 ,10.0000 ,6.5700 ,2.0900 ,5.2900 ,6.9800 ,2.5000 ,3.7800 ,0.9900 ,9.9500 ,4.1900 ,3.9600 ,6.2900 ,7.9800 ,1.5800 ,3.5000 ,4.7800 ,1.9900 ,5.1900 ,12.0000 ,0.8900 ,14.9700 ,5.3700 ,8.9800 ,2.5800 ,4.5000 ,5.7800 ,7.4700 ,2.9900 ,1.2500 ,1.8900 ,9.5700 ,6.7800 ,9.9800 ,3.5800 ,0.7900 ,2.0700 ,3.9900 ,2.8900 ,4.1700 ,7.7800 ,1.3800 ,10.9800 ,4.5800 ,1.7900 ,4.9900 ,7.9600 ,15.0000 ,0.6900 ,3.8900 ,17.9700 ,7.5000 ,2.3800 ,11.9800 ,5.5800 ,14.9500 ,2.7900 ,10.4700 ,5.9900 ),
SALE_RETURN_QTY_LY DECIMAL(12,3) DEFAULT 0.000 COMPRESS 0.000 ,
SALE_RETURN_VAL_LY DECIMAL(16,4) DEFAULT 0.0000 COMPRESS 0.0000 ,
SALE_GLOBAL_DISC_QTY_LY DECIMAL(12,3) DEFAULT 0.000 COMPRESS 0.000 ,
SALE_GLOBAL_DISC_VAL_LY DECIMAL(16,4) DEFAULT 0.0000 COMPRESS 0.0000 ,
SALE_EMP_DISC_QTY_LY DECIMAL(12,3) DEFAULT 0.000 COMPRESS 0.000 ,
SALE_EMP_DISC_VAL_LY DECIMAL(16,4) DEFAULT 0.0000 COMPRESS 0.0000 ,
SALE_MARKDOWN_QTY_LY DECIMAL(12,3) DEFAULT 0.000 COMPRESS (0.000 ,10.000 ,9.000 ,8.000 ,7.000 ,6.000 ,5.000 ,4.000 ,3.000 ,13.000 ,2.000 ,12.000 ,1.000 ,11.000 ),
SALE_MARKDOWN_VAL_LY DECIMAL(16,4) DEFAULT 0.0000 COMPRESS (0.0000 ,1.0000 ,2.0000 ,0.4900 ,0.9000 ,3.0000 ,1.4900 ,0.3900 ,0.8000 ,4.0000 ,0.9800 ,0.2900 ,0.7000 ,1.9800 ,6.0000 ,0.1900 ,0.6000 ,1.6000 ,0.5000 ,1.5000 ,0.4000 ,0.5800 ,0.9900 ,1.4000 ,0.3000 ,0.2000 ,0.7900 ,1.2000 ,0.1000 ,0.6900 ),
SALE_ADJ_QTY_LY DECIMAL(12,3) DEFAULT 0.000 COMPRESS 0.000 ,
SALE_ADJ_VAL_LY DECIMAL(12,3) DEFAULT 0.000 COMPRESS 0.000 ,
SALE_TOT_TAX_VAL_LY DECIMAL(16,4) DEFAULT 0.0000 COMPRESS (0.0000 ,0.6400 ,0.4100 ,0.1800 ,0.5900 ,0.3600 ,0.1300 ,0.5400 ,0.3100 ,0.0800 ,0.4900 ,0.2600 ,0.0300 ,0.4400 ,0.2100 ,0.6200 ,0.3900 ,0.1600 ,0.5700 ,0.3400 ,0.1100 ,0.5200 ,0.2900 ,0.0600 ,0.4700 ,0.2400 ,0.0100 ,0.4200 ,0.1900 ,0.6000 ,0.3700 ,0.1400 ,0.5500 ,0.3200 ,0.0900 ,0.5000 ,0.2700 ,0.0400 ,0.4500 ,0.2200 ,0.4000 ,0.1700 ,0.5800 ,0.3500 ,0.1200 ,0.5300 ,0.3000 ,0.0700 ,0.4800 ,0.2500 ,0.0200 ,0.4300 ,0.2000 ,0.3800 ,0.1500 ,0.5600 ,0.3300 ,0.1000 ,0.5100 ,0.2800 ,0.0500 ,0.4600 ,0.2300 ),
SALE_TOT_VAL_AT_PP_LY DECIMAL(16,4) DEFAULT 0.0000 COMPRESS (0.0000 ,0.6400 ,1.2800 ,1.9200 ,2.5600 ,3.2000 ,3.8400 ,4.4800 ,1.0500 ,1.6900 ,4.8900 ,0.8200 ,1.4600 ,2.1000 ,2.7400 ,4.0200 ,6.9900 ,2.5100 ,1.0000 ,1.6400 ,2.2800 ,2.9200 ,4.2000 ,1.4100 ,2.0500 ,1.1800 ,1.8200 ,2.4600 ,3.1000 ,3.7400 ,4.3800 ,6.3000 ,0.9500 ,1.5900 ,0.7200 ,1.3600 ,2.0000 ,2.6400 ,3.2800 ,1.1300 ,1.7700 ,0.9000 ,1.5400 ,2.1800 ,2.8200 ,1.3100 ,1.9500 ,2.5900 ,1.0800 ,1.7200 ,2.3600 ,3.0000 ,1.4900 ,2.1300 ,0.6200 ,1.2600 ,1.9000 ,2.5400 ,1.0300 ,1.6700 ,2.3100 ,2.9500 ,0.8000 ,1.4400 ,2.0800 ,2.7200 ,3.3600 ,4.0000 ,0.5700 ,1.8500 ,2.4900 ,0.9800 ,1.6200 ,2.2600 ,2.9000 ,3.5400 ,0.7500 ,1.3900 ,2.6700 ,1.1600 ,1.8000 ,2.4400 ,3.0800 ,3.7200 ,5.0000 ,0.9300 ,1.5700 ,2.2100 ,2.8500 ,0.7000 ,1.3400 ,1.9800 ,2.6200 ,3.9000 ,1.1100 ,1.7500 ,2.3900 ,5.5900 ,0.8800 ,1.5200 ,2.1600 ,2.8000 ,6.0000 ,0.6500 ,1.2900 ,1.9300 ,1.0600 ,1.7000 ,2.3400 ,2.9800 ,3.6200 ,1.4700 ,0.6000 ,1.2400 ,1.8800 ,2.5200 ,3.1600 ,3.8000 ,4.4400 ,0.3700 ,1.6500 ,0.7800 ,1.4200 ,2.0600 ,2.7000 ,1.1900 ,1.8300 ,2.4700 ,0.9600 ,1.6000 ,2.2400 ,2.8800 ,3.5200 ,4.1600 ,4.8000 ,2.0100 ,2.6500 ,1.1400 ,1.7800 ,2.4200 ,3.0600 ,3.7000 ,4.3400 ,0.9100 ,1.5500 ,2.1900 ,0.6800 ,1.3200 ,1.9600 ,2.6000 ,3.2400 ,5.1600 ,0.8600 ,1.5000 ,2.1400 ,3.4200 ,1.9100 ,2.5500 ,1.0400 ,1.6800 ,2.3200 ,2.9600 ,3.6000 ,4.2400 ,4.8800 ,1.4500 ,2.0900 ,2.7300 ,1.2200 ,1.8600 ,2.5000 ,3.1400 ,3.7800 ,0.9900 ,2.2700 ,4.1900 ,0.7600 ,1.4000 ,2.0400 ,2.6800 ,3.3200 ,3.9600 ,5.2400 ,1.1700 ,1.8100 ,2.4500 ,0.9400 ,1.5800 ,2.2200 ,2.8600 ,3.5000 ,1.3500 ,1.9900 ,1.1200 ,1.7600 ,2.4000 ,3.0400 ,3.6800 ,4.3200 ,0.8900 ,1.5300 ,2.1700 ,0.6600 ,1.3000 ,1.9400 ,2.5800 ,4.5000 ,1.0700 ,1.7100 ,2.3500 ,0.8400 ,1.4800 ,2.1200 ,2.7600 ,3.4000 ,1.2500 ,1.8900 ,1.0200 ,1.6600 ,2.3000 ,2.9400 ,2.0700 ,1.2000 ,1.8400 ,2.4800 ,3.1200 ,3.7600 ,4.4000 ,5.0400 ,0.9700 ,2.2500 ,0.7400 ,1.3800 ,2.0200 ,2.6600 ,3.3000 ,1.1500 ,1.7900 ,2.4300 ,0.9200 ,1.5600 ,2.2000 ,2.8400 ,3.4800 ,4.1200 ,5.4000 ,0.6900 ,1.3300 ,1.9700 ,1.1000 ,1.7400 ,2.3800 ,3.6600 ,2.1500 ,2.7900 ),
SALE_TOT_VAL_AT_WAP_LY DECIMAL(16,4) DEFAULT 0.0000 COMPRESS 0.0000 ,
SALE_TOT_VAL_AT_HOSP_LY DECIMAL(16,4) DEFAULT 0.0000 COMPRESS 0.0000 ,
SALE_TOT_VAL_AT_SP_LY DECIMAL(16,4) DEFAULT 0.0000 COMPRESS (0.0000 ,80.0000 ,160.0000 ,16.0000 ,1.6900 ,6.9900 ,8.9100 ,3.7900 ,13.1600 ,6.7600 ,1.0000 ,9.9600 ,2.6900 ,7.9900 ,1.5900 ,4.7900 ,2.0000 ,18.0000 ,3.6900 ,26.9100 ,8.9900 ,2.5900 ,5.7900 ,27.9600 ,15.1600 ,8.7600 ,3.0000 ,11.9600 ,5.5600 ,1.4900 ,4.6900 ,9.9900 ,3.5900 ,20.0000 ,36.0000 ,4.0000 ,2.4900 ,1.3900 ,10.9900 ,4.5900 ,44.9100 ,13.9600 ,7.5600 ,10.7600 ,5.0000 ,3.4900 ,2.3900 ,11.9900 ,6.0000 ,1.2900 ,22.4100 ,4.4900 ,3.3900 ,12.9900 ,7.0000 ,15.9600 ,9.5600 ,3.1600 ,31.9600 ,12.7600 ,6.3600 ,2.2900 ,5.4900 ,1.1900 ,4.3900 ,40.0000 ,8.0000 ,24.0000 ,3.2900 ,6.4900 ,2.1900 ,14.7600 ,9.0000 ,17.9600 ,11.5600 ,5.1600 ,7.4900 ,1.0900 ,4.2900 ,1.5000 ,3.1900 ,17.9100 ,10.0000 ,90.0000 ,5.2900 ,2.5000 ,0.9900 ,4.1900 ,19.9600 ,13.5600 ,7.1600 ,10.3600 ,3.9600 ,3.5000 ,1.9900 ,35.9100 ,5.1900 ,47.8400 ,15.8400 ,63.8400 ,12.0000 ,31.8400 ,0.8900 ,2.9900 ,9.1600 ,2.7600 ,45.0000 ,5.9600 ,1.2500 ,1.8900 ,0.7900 ,53.9100 ,3.9900 ,2.8900 ,22.5000 ,1.7900 ,4.9900 ,14.3600 ,7.9600 ,23.9600 ,11.1600 ,4.7600 ,0.6900 ,3.8900 ,31.4100 ,2.7900 ,5.9900 ),
VO_TOT_RECD_QTY_LY DECIMAL(12,3) DEFAULT 0.000 COMPRESS (0.000 ,8.000 ,6.000 ,4.000 ,3.000 ,24.000 ,12.000 ),
VO_NET_RECD_VAL_LY DECIMAL(16,4) DEFAULT 0.0000 COMPRESS 0.0000 ,
VO_RETURN_QTY_LY DECIMAL(12,3) DEFAULT 0.000 COMPRESS 0.000 ,
VO_RETURN_VAL_LY DECIMAL(16,4) DEFAULT 0.0000 COMPRESS 0.0000 ,
VO_GLOBAL_DISC_QTY_LY DECIMAL(12,3) DEFAULT 0.000 COMPRESS 0.000 ,
VO_GLOBAL_DISC_VAL_LY DECIMAL(16,4) DEFAULT 0.0000 COMPRESS 0.0000 ,
VO_TOT_TAX_VAL_LY DECIMAL(16,4) DEFAULT 0.0000 COMPRESS 0.0000 ,
VO_TOT_RECD_VAL_AT_PP_LY DECIMAL(16,4) DEFAULT 0.0000 COMPRESS 0.0000 ,
VO_TOT_RECD_VAL_AT_WAP_LY DECIMAL(16,4) DEFAULT 0.0000 COMPRESS 0.0000 ,
VO_TOT_RECD_VAL_AT_HOSP_LY DECIMAL(16,4) DEFAULT 0.0000 COMPRESS 0.0000 ,
VO_TOT_RECD_VAL_AT_SP_LY DECIMAL(16,4) DEFAULT 0.0000 COMPRESS 0.0000 ,
INV_TOT_TXN_QTY_LY DECIMAL(12,3) DEFAULT 0.000 COMPRESS 0.000 ,
INV_TOT_TXN_VAL_AT_PP_LY DECIMAL(16,4) DEFAULT 0.0000 COMPRESS 0.0000 ,
INV_TOT_TXN_VAL_AT_WAP_LY DECIMAL(16,4) DEFAULT 0.0000 COMPRESS 0.0000 ,
INV_TOT_TXN_VAL_AT_HOSP_LY DECIMAL(16,4) DEFAULT 0.0000 COMPRESS 0.0000 ,
INV_TOT_TXN_VAL_AT_SP_LY DECIMAL(16,4) DEFAULT 0.0000 COMPRESS 0.0000 ,
SALE_TOT_ITEM_COUNT INTEGER COMPRESS (0 ,1 ,2 ,3 ,4 ,5 ,6 ,7 ,8 ,9 ,10 ,11 ,12 ,13 ),
SALE_TOT_ITEM_COUNT_LY INTEGER COMPRESS (0 ,1 ,2 ,3 ,4 ,5 ,6 ,7 ,8 ,9 ,10 ,11 ,12 ,13 ))
PRIMARY INDEX ( DATE_KEY ,STORE_KEY ,PRODUCT_KEY )
PARTITION BY RANGE_N(DATE_KEY BETWEEN 1 AND 2000 EACH 1 )
INDEX XIF1AGG_DLY_STR_Prod ( DATE_KEY )
INDEX XIF2AGG_DLY_STR_PROD ( STORE_KEY )
INDEX XIF3AGG_DLY_STR_PROD ( PRODUCT_KEY );

2) CREATE SET TABLE INC_ETL_CUR_DAYS ,NO FALLBACK ,
NO BEFORE JOURNAL,
NO AFTER JOURNAL,
CHECKSUM = DEFAULT
(
DATE_KEY INTEGER NOT NULL,
DATE_FLD DATE FORMAT 'YYYY-MM-DD' NOT NULL,
ARC_DATE DATE FORMAT 'YYYY-MM-DD' NOT NULL,
DATE_KEY_LY INTEGER,
DATE_FLD_LY DATE FORMAT 'YYYY-MM-DD')
PRIMARY INDEX ( DATE_KEY );

Can you please tell me how to collect the table demographic information?

Yeah.it is taking almost 19 mintues...when i introduced NUSI for the columns store_code,product_code,product_detail_code in the aggregate table.
Senior Apprentice

Re: DELETE query is taking too long time in the production....

Some remarks:

According to your post the PK of tables seems to be:
AGG_DLY_STR_PROD - ( DATE_KEY ,STORE_KEY ,PRODUCT_KEY )
INC_ETL_CUR_DAYS - (DATE_KEY)

But it's defined as NUPI instead of UPI?

Why there's a DATE_KEY instead of using the DATE_FLD directly?
And when using DATE_KEY why it's an INT instead of a SMALLINT?

Why that INDEX XIF1AGG_DLY_STR_Prod ( DATE_KEY ) when the table is already partitioned by DATE_KEY?
I never thought this would be possible at all.
Maintaining a NUSI is costly (especially when there's a small number of distinct values) so you should drop it.

Try to rewrite the query to use an IN on hard-coded values instead of the join to INC_ETL_CUR_DAYS, this should result in a single working step without spool.

How many days are stored and deleted within AGG_DLY_STR_PROD?
Drop/recreate the NUSIs might help, too.

Demographics is just info about number of rows, the easiest way is the output of a HELP STATS.

Dieter