468,525 Members | 2,164 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 468,525 developers. It's quick & easy.

large Union and Access path.

I have a large union.

If I break it into its individual parts they all run quick. The
longest is the last select it takes 2 minutes to fetch all rows. When
I run the query below it does not come back for quite some time. 20
minutes. There are very few duplicates. When I run the sql's
individually the first two give no warning but the third
does(SQL0437W Performance of this complex query may be sub-optimal.
Reason code:"1". SQLSTATE=01602). If I run the first two by them
selves they work quick in 2 minutes. But if I run any of the first
SQL's with the last one it takes 20 minutes again. I have changed
the STMTHEAP to 25600 pages(100MB) but it caused other SQL's to become
bad and take upwards of 5 minutes to explain( I did not think this was
a viable solution since the third SQL with the bad access path by
itself it ran in 2 minutes). This one does as well. If I take the
individual third SQL statement it takes 4 minutes to prepare and then
runs in 3 seconds(Finally with the right access path, in my eyes).

Is there something that the optimizer does to look for like parts of
the SQL when there is a union do those like parts once?

Any help would be appreciated. Thanks,

(
SELECT
RTRIM(CHAR(RIGHT(ODS_WT_DEAL.BOOK_NM,4))),
( CASE WHEN ( ODS_WT_SCHDL_DEAL.SRC_CD ) NOT IN ('CSWS','PJM') AND
( ODS_WT_SCHDL_DEAL.SINK_CD ) NOT IN ('CSWS','PJM') THEN
'WT-Off'
WHEN ( ODS_WT_SCHDL_DEAL.SRC_CD ) IN ('CSWS','PJM') OR
( ODS_WT_SCHDL_DEAL.SINK_CD ) IN ('CSWS','PJM') THEN
'WT-On'
END ) || ' - ' ||
( Alias_ODS_AEE_BOOK_DESIG_WT_DEAL.BOOK_DESIGNATION_ NAME ),
ODS_WT_DEAL.DEAL_NR,
SUM(ABS(CASE WHEN ( ODS_WT_SCHDL_DEAL.SRC_CD ) IN ('CSWS','PJM')
THEN ODS_WT_SCHDL_ENRGY_PRFL_BLK.GEN_MW_QT
WHEN ( ODS_WT_SCHDL_DEAL.SINK_CD ) IN ('CSWS','PJM') THEN
ODS_WT_SCHDL_ENRGY_PRFL_BLK.LOAD_MW_QT
END)),
Alias_EDW_PERIOD_MWH_WT.CPT_PERIOD_DT,
ODS_WT_DEAL.TRAN_TYP_CD,
ODS_STLMT_CNTR_PRTY_XREF.CPRTY_CD,
ODS_WT_DEAL.DEAL_ID,
CHAR(ODS_WT_DEAL.DEAL_NR),
ODS_WT_DEAL.TRD_DT,
rtrim(ltrim(ODS_WT_DEAL.MKT1_NM)),
ODS_WT_DEAL.DEAL_TYP_TX,
RTRIM(ODS_WT_DEAL.TERM_TX),
Alias_EDW_PERIOD_MWH_WT.CPT_PERIOD_TS
FROM
DB2INSTB.ODS_WT_DEAL ODS_WT_DEAL,
DB2INSTB.ODS_WT_SCHDL_DEAL ODS_WT_SCHDL_DEAL,
DB2INSTB.ODS_AEE_BOOK_DESIGNATION Alias_ODS_AEE_BOOK_DESIG_WT_DEAL,
DB2INSTB.ODS_WT_SCHDL_ENRGY_PRFL_BLK ODS_WT_SCHDL_ENRGY_PRFL_BLK,
DB2INSTB.ODS_EDW_PERIOD Alias_EDW_PERIOD_MWH_WT,
DB2INSTB.ODS_STLMT_CNTR_PRTY_XREF ODS_STLMT_CNTR_PRTY_XREF,
DB2INSTB.ODS_WT_SCHDL_DEAL_CPRTY ODS_WT_SCHDL_DEAL_CPRTY,
DB2INSTB.ODS_AEE_BOOK Alias_ODS_AEE_BOOK_WT_DEAL
WHERE

( ODS_WT_SCHDL_DEAL.PHYS_DEAL_NR=ODS_WT_SCHDL_DEAL_C PRTY.PHYS_DEAL_NR )
AND ( ODS_WT_SCHDL_DEAL_CPRTY.DEAL_NR=ODS_WT_DEAL.DEAL_N R )
AND ( ODS_STLMT_CNTR_PRTY_XREF.SOURCE_CPRTY_CD=ODS_WT_DE AL.CPRTY_NM
AND ODS_STLMT_CNTR_PRTY_XREF.SOURCE_SYS_CD = 'WT' )
AND
( Alias_EDW_PERIOD_MWH_WT.GMT_PERIOD_TS=ODS_WT_SCHDL _ENRGY_PRFL_BLK.HOUR_ENDING_TS )
AND ( RTRIM(CHAR(RIGHT(ODS_WT_DEAL.BOOK_NM,
4)))=Alias_ODS_AEE_BOOK_WT_DEAL.BOOK_NAME )
AND
( Alias_ODS_AEE_BOOK_WT_DEAL.BOOK_ID=Alias_ODS_AEE_B OOK_DESIG_WT_DEAL.BOOK_ID )
AND
( ODS_WT_SCHDL_ENRGY_PRFL_BLK.PHYS_DEAL_NR=ODS_WT_SC HDL_DEAL_CPRTY.PHYS_DEAL_NR )
AND (
( ( Alias_EDW_PERIOD_MWH_WT.CPT_PERIOD_DT ) between '03/01/2007'
and '03/31/2007' AND (Alias_EDW_PERIOD_MWH_WT.CPT_PERIOD_DT >=
Alias_ODS_AEE_BOOK_DESIG_WT_DEAL.START_DATE AND
Alias_EDW_PERIOD_MWH_WT.CPT_PERIOD_DT <=
Alias_ODS_AEE_BOOK_DESIG_WT_DEAL.STOP_DATE )
AND
ODS_WT_SCHDL_ENRGY_PRFL_BLK.HOUR_ENDING_DT >= date('03/01/2007') -1
day and ODS_WT_SCHDL_ENRGY_PRFL_BLK.HOUR_ENDING_DT <=
date('03/31/2007') + 1 day
and ( Alias_EDW_PERIOD_MWH_WT.CPT_PERIOD_DT ) BETWEEN
ODS_STLMT_CNTR_PRTY_XREF.SOURCE_START_DT AND
ODS_STLMT_CNTR_PRTY_XREF.SOURCE_END_DT )
AND ( ( ODS_STLMT_CNTR_PRTY_XREF.CPRTY_CD ) NOT IN
('AEP','AEPGEN','AEPLOAD','AEPTCC','AEPTNC','PJM
OPRES','ERCOT','QFOXYCHEM','',' ') AND
( ODS_STLMT_CNTR_PRTY_XREF.CPRTY_CD ) NOT IN '%' )
AND ( CASE WHEN ( ODS_WT_SCHDL_DEAL.SRC_CD ) NOT IN ('CSWS','PJM')
AND ( ODS_WT_SCHDL_DEAL.SINK_CD ) NOT IN ('CSWS','PJM') THEN
'WT-Off'
WHEN ( ODS_WT_SCHDL_DEAL.SRC_CD ) IN ('CSWS','PJM') OR
( ODS_WT_SCHDL_DEAL.SINK_CD ) IN ('CSWS','PJM') THEN
'WT-On'
END ) || ' - ' ||
( Alias_ODS_AEE_BOOK_DESIG_WT_DEAL.BOOK_DESIGNATION_ NAME ) IN ('WT-
On - PE', 'WT-On - PW')
AND ODS_WT_DEAL.STATUS_CD < 'VOIDED'
AND ODS_WT_SCHDL_DEAL.STAT_CD < 'VOIDED'
AND ODS_WT_DEAL.DEAL_TYP_TX < 'Transmission'
)
GROUP BY
RTRIM(CHAR(RIGHT(ODS_WT_DEAL.BOOK_NM,4))),
( CASE WHEN ( ODS_WT_SCHDL_DEAL.SRC_CD ) NOT IN ('CSWS','PJM') AND
( ODS_WT_SCHDL_DEAL.SINK_CD ) NOT IN ('CSWS','PJM') THEN
'WT-Off'
WHEN ( ODS_WT_SCHDL_DEAL.SRC_CD ) IN ('CSWS','PJM') OR
( ODS_WT_SCHDL_DEAL.SINK_CD ) IN ('CSWS','PJM') THEN
'WT-On'
END ) || ' - ' ||
( Alias_ODS_AEE_BOOK_DESIG_WT_DEAL.BOOK_DESIGNATION_ NAME ),
ODS_WT_DEAL.DEAL_NR,
Alias_EDW_PERIOD_MWH_WT.CPT_PERIOD_DT,
ODS_WT_DEAL.TRAN_TYP_CD,
ODS_STLMT_CNTR_PRTY_XREF.CPRTY_CD,
ODS_WT_DEAL.DEAL_ID,
CHAR(ODS_WT_DEAL.DEAL_NR),
ODS_WT_DEAL.TRD_DT,
rtrim(ltrim(ODS_WT_DEAL.MKT1_NM)),
ODS_WT_DEAL.DEAL_TYP_TX,
RTRIM(ODS_WT_DEAL.TERM_TX),
Alias_EDW_PERIOD_MWH_WT.CPT_PERIOD_TS
UNION
(
SELECT
RTRIM(CHAR(RIGHT(ODS_WT_DEAL.BOOK_NM,4))),
'WT' || ' - ' ||
( Alias_ODS_AEE_BOOK_DESIG_WT_DEAL.BOOK_DESIGNATION_ NAME ),
ODS_WT_DEAL.DEAL_NR,
ABS(ODS_WT_DEAL_ENGRY_PRFL_BLK.EP_MW_QT),
Alias_EDW_PERIOD_Price_WT.CPT_PERIOD_DT,
ODS_WT_DEAL.TRAN_TYP_CD,
ODS_STLMT_CNTR_PRTY_XREF.CPRTY_CD,
ODS_WT_DEAL.DEAL_ID,
CHAR(ODS_WT_DEAL.DEAL_NR),
ODS_WT_DEAL.TRD_DT,
rtrim(ltrim(ODS_WT_DEAL.MKT1_NM)),
ODS_WT_DEAL.DEAL_TYP_TX,
RTRIM(ODS_WT_DEAL.TERM_TX),
Alias_EDW_PERIOD_Price_WT.CPT_PERIOD_TS
FROM
DB2INSTB.ODS_WT_DEAL ODS_WT_DEAL,
DB2INSTB.ODS_AEE_BOOK_DESIGNATION Alias_ODS_AEE_BOOK_DESIG_WT_DEAL,
DB2INSTB.ODS_WT_DEAL_ENGRY_PRFL_BLK ODS_WT_DEAL_ENGRY_PRFL_BLK,
DB2INSTB.ODS_EDW_PERIOD Alias_EDW_PERIOD_Price_WT,
DB2INSTB.ODS_STLMT_CNTR_PRTY_XREF ODS_STLMT_CNTR_PRTY_XREF,
DB2INSTB.ODS_AEE_BOOK Alias_ODS_AEE_BOOK_WT_DEAL
WHERE
( ODS_STLMT_CNTR_PRTY_XREF.SOURCE_CPRTY_CD=ODS_WT_DE AL.CPRTY_NM AND
ODS_STLMT_CNTR_PRTY_XREF.SOURCE_SYS_CD = 'WT' )
AND
( Alias_EDW_PERIOD_Price_WT.GMT_PERIOD_TS=ODS_WT_DEA L_ENGRY_PRFL_BLK.HOUR_ENDING_TS )
AND ( RTRIM(CHAR(RIGHT(ODS_WT_DEAL.BOOK_NM,
4)))=Alias_ODS_AEE_BOOK_WT_DEAL.BOOK_NAME )
AND
( Alias_ODS_AEE_BOOK_WT_DEAL.BOOK_ID=Alias_ODS_AEE_B OOK_DESIG_WT_DEAL.BOOK_ID )
AND ( ODS_WT_DEAL_ENGRY_PRFL_BLK.DEAL_NR=ODS_WT_DEAL.DEA L_NR )
AND (
( ( Alias_EDW_PERIOD_Price_WT.CPT_PERIOD_DT ) between '03/01/2007'
and '03/31/2007' AND (( Alias_EDW_PERIOD_Price_WT.CPT_PERIOD_DT ) >=
Alias_ODS_AEE_BOOK_DESIG_WT_DEAL.START_DATE AND
( Alias_EDW_PERIOD_Price_WT.CPT_PERIOD_DT ) <=
Alias_ODS_AEE_BOOK_DESIG_WT_DEAL.STOP_DATE )
AND
ODS_WT_DEAL_ENGRY_PRFL_BLK.HOUR_ENDING_DT >= date('03/01/2007') -1
day and ODS_WT_DEAL_ENGRY_PRFL_BLK.HOUR_ENDING_DT <=
date('03/31/2007') + 1 day
and ( Alias_EDW_PERIOD_Price_WT.CPT_PERIOD_DT ) BETWEEN
ODS_STLMT_CNTR_PRTY_XREF.SOURCE_START_DT AND
ODS_STLMT_CNTR_PRTY_XREF.SOURCE_END_DT )
AND ( ( ODS_STLMT_CNTR_PRTY_XREF.CPRTY_CD ) NOT IN
('AEP','AEPGEN','AEPLOAD','AEPTCC','AEPTNC','PJM
OPRES','ERCOT','QFOXYCHEM','',' ') AND
( ODS_STLMT_CNTR_PRTY_XREF.CPRTY_CD ) NOT IN '%' )
AND 'WT' || ' - ' ||
( Alias_ODS_AEE_BOOK_DESIG_WT_DEAL.BOOK_DESIGNATION_ NAME ) IN ('WT -
PE', 'WT - PW')
AND ODS_WT_DEAL.DEAL_TYP_TX < 'Transmission'
AND ODS_WT_DEAL.STATUS_CD < 'VOIDED'
)
UNION
SELECT
RTRIM(CHAR(RIGHT(ODS_WT_DEAL.BOOK_NM,4))),
CASE WHEN Alias_ODS_AEE_BOOK_DESIG_WT_DEAL.BOOK_DESIGNATION_ NAME =
'PW'
AND ( ODS_WT_SCHDL_DEAL.SRC_CD ) IN ('CSWS','PJM') THEN 'WT-On - PW'
WHEN Alias_ODS_AEE_BOOK_DESIG_WT_DEAL.BOOK_DESIGNATION_ NAME = 'PE'
AND ( ODS_WT_SCHDL_DEAL.SRC_CD ) IN ('CSWS','PJM') THEN 'WT-On - PE'
END,
ODS_WT_SCHDL_DEAL.PHYS_DEAL_NR,
SUM(ODS_WT_SCHDL_LPRFL_BLK.BLK_MW*-1),
ODS_WT_SCHDL_LPRFL_BLK.CPT_PERIOD_DT,
ODS_WT_DEAL.TRAN_TYP_CD,
ODS_STLMT_CNTR_PRTY_XREF.CPRTY_CD,
ODS_WT_DEAL.DEAL_ID,
CHAR(ODS_WT_DEAL.DEAL_NR),
ODS_WT_DEAL.TRD_DT,
rtrim(ltrim(ODS_WT_DEAL.MKT1_NM)),
ODS_WT_DEAL.DEAL_TYP_TX,
RTRIM(ODS_WT_DEAL.TERM_TX),
ODS_WT_SCHDL_LPRFL_BLK.CPT_PERIOD_TS
FROM
DB2INSTB.ODS_WT_DEAL ODS_WT_DEAL,
DB2INSTB.ODS_AEE_BOOK_DESIGNATION Alias_ODS_AEE_BOOK_DESIG_WT_DEAL,
DB2INSTB.ODS_WT_SCHDL_DEAL ODS_WT_SCHDL_DEAL,
DB2INSTB.ODS_WT_SCHDL_LPRFL_BLK_VW ODS_WT_SCHDL_LPRFL_BLK,
DB2INSTB.ODS_STLMT_CNTR_PRTY_XREF ODS_STLMT_CNTR_PRTY_XREF,
DB2INSTB.ODS_WT_SCHDL_TPRFL_RSRVN ODS_WT_SCHDL_TPRFL_RSRVN,
DB2INSTB.ODS_AEE_BOOK Alias_ODS_AEE_BOOK_WT_DEAL,
DB2INSTB.ODS_WT_SCHDL_DEAL_CPRTY ODS_WT_SCHDL_DEAL_CPRTY,
DB2INSTB.ODS_WT_SCHDL_DEAL_LPRFL ODS_WT_SCHDL_DEAL_LPRFL,
DB2INSTB.ODS_WT_SCHDL_DEAL_TPRFL ODS_WT_SCHDL_DEAL_TPRFL
WHERE

( ODS_WT_SCHDL_DEAL.PHYS_DEAL_NR=ODS_WT_SCHDL_DEAL_C PRTY.PHYS_DEAL_NR )
AND ( ODS_WT_SCHDL_DEAL_CPRTY.DEAL_NR=ODS_WT_DEAL.DEAL_N R )
AND ( ODS_STLMT_CNTR_PRTY_XREF.SOURCE_CPRTY_CD=ODS_WT_DE AL.CPRTY_NM
AND ODS_STLMT_CNTR_PRTY_XREF.SOURCE_SYS_CD = 'WT' )
AND
( ODS_WT_SCHDL_DEAL_LPRFL.PHYS_DEAL_NR=ODS_WT_SCHDL_ DEAL.PHYS_DEAL_NR )
AND
( ODS_WT_SCHDL_DEAL_LPRFL.PHYS_DEAL_NR=ODS_WT_SCHDL_ LPRFL_BLK.PHYS_DEAL_NR
and
ODS_WT_SCHDL_DEAL_LPRFL.LPRFL_ID=ODS_WT_SCHDL_LPRF L_BLK.LPRFL_ID )
AND
( ODS_WT_SCHDL_DEAL_LPRFL.PHYS_DEAL_NR=ODS_WT_SCHDL_ DEAL_TPRFL.PHYS_DEAL_NR )
AND
( ODS_WT_SCHDL_DEAL_LPRFL.TRNMT_PRVDR_NM=ODS_WT_SCHD L_DEAL_TPRFL.TRNMT_PRVDR_NM )
AND
( ODS_WT_SCHDL_DEAL_TPRFL.PHYS_DEAL_NR=ODS_WT_SCHDL_ TPRFL_RSRVN.PHYS_DEAL_NR )
AND
( ODS_WT_SCHDL_DEAL_TPRFL.SCHDL_DEAL_PRFL_ID=ODS_WT_ SCHDL_TPRFL_RSRVN.SCHDL_DEAL_PRFL_ID )
AND
( ODS_WT_SCHDL_DEAL_TPRFL.TRANS_PRFL_ID=ODS_WT_SCHDL _TPRFL_RSRVN.TRANS_PRFL_ID )
AND ( RTRIM(CHAR(RIGHT(ODS_WT_DEAL.BOOK_NM,
4)))=Alias_ODS_AEE_BOOK_WT_DEAL.BOOK_NAME )
AND
( Alias_ODS_AEE_BOOK_WT_DEAL.BOOK_ID=Alias_ODS_AEE_B OOK_DESIG_WT_DEAL.BOOK_ID )
AND (
( ( ODS_STLMT_CNTR_PRTY_XREF.CPRTY_CD ) NOT IN
('AEP','AEPGEN','AEPLOAD','AEPTCC','AEPTNC','PJM
OPRES','ERCOT','QFOXYCHEM','',' ') AND
( ODS_STLMT_CNTR_PRTY_XREF.CPRTY_CD ) NOT IN '%' )
AND ( ( ODS_WT_SCHDL_LPRFL_BLK.CPT_PERIOD_DT ) between '03/01/2007'
and '03/31/2007' AND (ODS_WT_SCHDL_LPRFL_BLK.CPT_PERIOD_DT >=
Alias_ODS_AEE_BOOK_DESIG_WT_DEAL.START_DATE AND
ODS_WT_SCHDL_LPRFL_BLK.CPT_PERIOD_DT <=
Alias_ODS_AEE_BOOK_DESIG_WT_DEAL.STOP_DATE) and
ODS_WT_SCHDL_LPRFL_BLK.HOUR_ENDING_DT >= date('03/01/2007') -1 day
and ODS_WT_SCHDL_LPRFL_BLK.HOUR_ENDING_DT<= date('03/31/2007') + 1
day
and ( Alias_EDW_PERIOD_WT_LPrfl.CPT_PERIOD_DT ) BETWEEN
ODS_STLMT_CNTR_PRTY_XREF.SOURCE_START_DT AND
ODS_STLMT_CNTR_PRTY_XREF.SOURCE_END_DT )
AND ODS_WT_SCHDL_DEAL.STAT_CD < 'VOIDED'
AND CASE WHEN
Alias_ODS_AEE_BOOK_DESIG_WT_DEAL.BOOK_DESIGNATION_ NAME = 'PW'
AND ( ODS_WT_SCHDL_DEAL.SRC_CD ) IN ('CSWS','PJM') THEN 'WT-On - PW'
WHEN Alias_ODS_AEE_BOOK_DESIG_WT_DEAL.BOOK_DESIGNATION_ NAME = 'PE'
AND ( ODS_WT_SCHDL_DEAL.SRC_CD ) IN ('CSWS','PJM') THEN 'WT-On - PE'
END IN ('WT-On - PE', 'WT-On - PW')
AND ODS_WT_DEAL.DEAL_TYP_TX < 'Transmission'
AND ODS_WT_DEAL.STATUS_CD < 'VOIDED'
AND ODS_WT_SCHDL_TPRFL_RSRVN.OWNER_NM IN ('AEPMEA', 'CSWMEO')
)
GROUP BY
RTRIM(CHAR(RIGHT(ODS_WT_DEAL.BOOK_NM,4))),
CASE WHEN Alias_ODS_AEE_BOOK_DESIG_WT_DEAL.BOOK_DESIGNATION_ NAME =
'PW'
AND ( ODS_WT_SCHDL_DEAL.SRC_CD ) IN ('CSWS','PJM') THEN 'WT-On - PW'
WHEN Alias_ODS_AEE_BOOK_DESIG_WT_DEAL.BOOK_DESIGNATION_ NAME = 'PE'
AND ( ODS_WT_SCHDL_DEAL.SRC_CD ) IN ('CSWS','PJM') THEN 'WT-On - PE'
END,
ODS_WT_SCHDL_DEAL.PHYS_DEAL_NR,
ODS_WT_SCHDL_LPRFL_BLK.CPT_PERIOD_DT,
ODS_WT_DEAL.TRAN_TYP_CD,
ODS_STLMT_CNTR_PRTY_XREF.CPRTY_CD,
ODS_WT_DEAL.DEAL_ID,
CHAR(ODS_WT_DEAL.DEAL_NR),
ODS_WT_DEAL.TRD_DT,
rtrim(ltrim(ODS_WT_DEAL.MKT1_NM)),
ODS_WT_DEAL.DEAL_TYP_TX,
RTRIM(ODS_WT_DEAL.TERM_TX),
ODS_WT_SCHDL_LPRFL_BLK.CPT_PERIOD_TS
)
);

Apr 10 '07 #1
2 1934
You are using UNIONs (vs UNION ALL) to tie the three parts together
which helps make this a very large complicated statement to the
optimizer. Taking four minutes to optimize a query that can run in two
minutes probably isn't cost effective either.

Without trying to analyze your SQL, there are two simple things to try:

1. Try adjusting the optimization level for the statement. You'll need
to strike a balance between the time spent optimizing and the execution
time.

2. Create a temporary table that matches your SELECT columns and
populate it using three independent SELECT statements. Do a SELECT
DISTINCT from the temporary table to get your final results then delete
the contents of it. Performance can be improved by eliminating logging
by setting up the temporary table as "not logged initially". You'll have
to do all of the work on the table in a single transaction to make "not
logged initially" work.

Phil Sherman
ja*********@aep.com wrote:
I have a large union.

If I break it into its individual parts they all run quick. The
longest is the last select it takes 2 minutes to fetch all rows. When
I run the query below it does not come back for quite some time. 20
minutes. There are very few duplicates. When I run the sql's
individually the first two give no warning but the third
does(SQL0437W Performance of this complex query may be sub-optimal.
Reason code:"1". SQLSTATE=01602). If I run the first two by them
selves they work quick in 2 minutes. But if I run any of the first
SQL's with the last one it takes 20 minutes again. I have changed
the STMTHEAP to 25600 pages(100MB) but it caused other SQL's to become
bad and take upwards of 5 minutes to explain( I did not think this was
a viable solution since the third SQL with the bad access path by
itself it ran in 2 minutes). This one does as well. If I take the
individual third SQL statement it takes 4 minutes to prepare and then
runs in 3 seconds(Finally with the right access path, in my eyes).

Is there something that the optimizer does to look for like parts of
the SQL when there is a union do those like parts once?

Any help would be appreciated. Thanks,

(
SELECT
RTRIM(CHAR(RIGHT(ODS_WT_DEAL.BOOK_NM,4))),
( CASE WHEN ( ODS_WT_SCHDL_DEAL.SRC_CD ) NOT IN ('CSWS','PJM') AND
( ODS_WT_SCHDL_DEAL.SINK_CD ) NOT IN ('CSWS','PJM') THEN
'WT-Off'
WHEN ( ODS_WT_SCHDL_DEAL.SRC_CD ) IN ('CSWS','PJM') OR
( ODS_WT_SCHDL_DEAL.SINK_CD ) IN ('CSWS','PJM') THEN
'WT-On'
END ) || ' - ' ||
( Alias_ODS_AEE_BOOK_DESIG_WT_DEAL.BOOK_DESIGNATION_ NAME ),
ODS_WT_DEAL.DEAL_NR,
SUM(ABS(CASE WHEN ( ODS_WT_SCHDL_DEAL.SRC_CD ) IN ('CSWS','PJM')
THEN ODS_WT_SCHDL_ENRGY_PRFL_BLK.GEN_MW_QT
WHEN ( ODS_WT_SCHDL_DEAL.SINK_CD ) IN ('CSWS','PJM') THEN
ODS_WT_SCHDL_ENRGY_PRFL_BLK.LOAD_MW_QT
END)),
Alias_EDW_PERIOD_MWH_WT.CPT_PERIOD_DT,
ODS_WT_DEAL.TRAN_TYP_CD,
ODS_STLMT_CNTR_PRTY_XREF.CPRTY_CD,
ODS_WT_DEAL.DEAL_ID,
CHAR(ODS_WT_DEAL.DEAL_NR),
ODS_WT_DEAL.TRD_DT,
rtrim(ltrim(ODS_WT_DEAL.MKT1_NM)),
ODS_WT_DEAL.DEAL_TYP_TX,
RTRIM(ODS_WT_DEAL.TERM_TX),
Alias_EDW_PERIOD_MWH_WT.CPT_PERIOD_TS
FROM
DB2INSTB.ODS_WT_DEAL ODS_WT_DEAL,
DB2INSTB.ODS_WT_SCHDL_DEAL ODS_WT_SCHDL_DEAL,
DB2INSTB.ODS_AEE_BOOK_DESIGNATION Alias_ODS_AEE_BOOK_DESIG_WT_DEAL,
DB2INSTB.ODS_WT_SCHDL_ENRGY_PRFL_BLK ODS_WT_SCHDL_ENRGY_PRFL_BLK,
DB2INSTB.ODS_EDW_PERIOD Alias_EDW_PERIOD_MWH_WT,
DB2INSTB.ODS_STLMT_CNTR_PRTY_XREF ODS_STLMT_CNTR_PRTY_XREF,
DB2INSTB.ODS_WT_SCHDL_DEAL_CPRTY ODS_WT_SCHDL_DEAL_CPRTY,
DB2INSTB.ODS_AEE_BOOK Alias_ODS_AEE_BOOK_WT_DEAL
WHERE

( ODS_WT_SCHDL_DEAL.PHYS_DEAL_NR=ODS_WT_SCHDL_DEAL_C PRTY.PHYS_DEAL_NR )
AND ( ODS_WT_SCHDL_DEAL_CPRTY.DEAL_NR=ODS_WT_DEAL.DEAL_N R )
AND ( ODS_STLMT_CNTR_PRTY_XREF.SOURCE_CPRTY_CD=ODS_WT_DE AL.CPRTY_NM
AND ODS_STLMT_CNTR_PRTY_XREF.SOURCE_SYS_CD = 'WT' )
AND
( Alias_EDW_PERIOD_MWH_WT.GMT_PERIOD_TS=ODS_WT_SCHDL _ENRGY_PRFL_BLK.HOUR_ENDING_TS )
AND ( RTRIM(CHAR(RIGHT(ODS_WT_DEAL.BOOK_NM,
4)))=Alias_ODS_AEE_BOOK_WT_DEAL.BOOK_NAME )
AND
( Alias_ODS_AEE_BOOK_WT_DEAL.BOOK_ID=Alias_ODS_AEE_B OOK_DESIG_WT_DEAL.BOOK_ID )
AND
( ODS_WT_SCHDL_ENRGY_PRFL_BLK.PHYS_DEAL_NR=ODS_WT_SC HDL_DEAL_CPRTY.PHYS_DEAL_NR )
AND (
( ( Alias_EDW_PERIOD_MWH_WT.CPT_PERIOD_DT ) between '03/01/2007'
and '03/31/2007' AND (Alias_EDW_PERIOD_MWH_WT.CPT_PERIOD_DT >=
Alias_ODS_AEE_BOOK_DESIG_WT_DEAL.START_DATE AND
Alias_EDW_PERIOD_MWH_WT.CPT_PERIOD_DT <=
Alias_ODS_AEE_BOOK_DESIG_WT_DEAL.STOP_DATE )
AND
ODS_WT_SCHDL_ENRGY_PRFL_BLK.HOUR_ENDING_DT >= date('03/01/2007') -1
day and ODS_WT_SCHDL_ENRGY_PRFL_BLK.HOUR_ENDING_DT <=
date('03/31/2007') + 1 day
and ( Alias_EDW_PERIOD_MWH_WT.CPT_PERIOD_DT ) BETWEEN
ODS_STLMT_CNTR_PRTY_XREF.SOURCE_START_DT AND
ODS_STLMT_CNTR_PRTY_XREF.SOURCE_END_DT )
AND ( ( ODS_STLMT_CNTR_PRTY_XREF.CPRTY_CD ) NOT IN
('AEP','AEPGEN','AEPLOAD','AEPTCC','AEPTNC','PJM
OPRES','ERCOT','QFOXYCHEM','',' ') AND
( ODS_STLMT_CNTR_PRTY_XREF.CPRTY_CD ) NOT IN '%' )
AND ( CASE WHEN ( ODS_WT_SCHDL_DEAL.SRC_CD ) NOT IN ('CSWS','PJM')
AND ( ODS_WT_SCHDL_DEAL.SINK_CD ) NOT IN ('CSWS','PJM') THEN
'WT-Off'
WHEN ( ODS_WT_SCHDL_DEAL.SRC_CD ) IN ('CSWS','PJM') OR
( ODS_WT_SCHDL_DEAL.SINK_CD ) IN ('CSWS','PJM') THEN
'WT-On'
END ) || ' - ' ||
( Alias_ODS_AEE_BOOK_DESIG_WT_DEAL.BOOK_DESIGNATION_ NAME ) IN ('WT-
On - PE', 'WT-On - PW')
AND ODS_WT_DEAL.STATUS_CD < 'VOIDED'
AND ODS_WT_SCHDL_DEAL.STAT_CD < 'VOIDED'
AND ODS_WT_DEAL.DEAL_TYP_TX < 'Transmission'
)
GROUP BY
RTRIM(CHAR(RIGHT(ODS_WT_DEAL.BOOK_NM,4))),
( CASE WHEN ( ODS_WT_SCHDL_DEAL.SRC_CD ) NOT IN ('CSWS','PJM') AND
( ODS_WT_SCHDL_DEAL.SINK_CD ) NOT IN ('CSWS','PJM') THEN
'WT-Off'
WHEN ( ODS_WT_SCHDL_DEAL.SRC_CD ) IN ('CSWS','PJM') OR
( ODS_WT_SCHDL_DEAL.SINK_CD ) IN ('CSWS','PJM') THEN
'WT-On'
END ) || ' - ' ||
( Alias_ODS_AEE_BOOK_DESIG_WT_DEAL.BOOK_DESIGNATION_ NAME ),
ODS_WT_DEAL.DEAL_NR,
Alias_EDW_PERIOD_MWH_WT.CPT_PERIOD_DT,
ODS_WT_DEAL.TRAN_TYP_CD,
ODS_STLMT_CNTR_PRTY_XREF.CPRTY_CD,
ODS_WT_DEAL.DEAL_ID,
CHAR(ODS_WT_DEAL.DEAL_NR),
ODS_WT_DEAL.TRD_DT,
rtrim(ltrim(ODS_WT_DEAL.MKT1_NM)),
ODS_WT_DEAL.DEAL_TYP_TX,
RTRIM(ODS_WT_DEAL.TERM_TX),
Alias_EDW_PERIOD_MWH_WT.CPT_PERIOD_TS
UNION
(
SELECT
RTRIM(CHAR(RIGHT(ODS_WT_DEAL.BOOK_NM,4))),
'WT' || ' - ' ||
( Alias_ODS_AEE_BOOK_DESIG_WT_DEAL.BOOK_DESIGNATION_ NAME ),
ODS_WT_DEAL.DEAL_NR,
ABS(ODS_WT_DEAL_ENGRY_PRFL_BLK.EP_MW_QT),
Alias_EDW_PERIOD_Price_WT.CPT_PERIOD_DT,
ODS_WT_DEAL.TRAN_TYP_CD,
ODS_STLMT_CNTR_PRTY_XREF.CPRTY_CD,
ODS_WT_DEAL.DEAL_ID,
CHAR(ODS_WT_DEAL.DEAL_NR),
ODS_WT_DEAL.TRD_DT,
rtrim(ltrim(ODS_WT_DEAL.MKT1_NM)),
ODS_WT_DEAL.DEAL_TYP_TX,
RTRIM(ODS_WT_DEAL.TERM_TX),
Alias_EDW_PERIOD_Price_WT.CPT_PERIOD_TS
FROM
DB2INSTB.ODS_WT_DEAL ODS_WT_DEAL,
DB2INSTB.ODS_AEE_BOOK_DESIGNATION Alias_ODS_AEE_BOOK_DESIG_WT_DEAL,
DB2INSTB.ODS_WT_DEAL_ENGRY_PRFL_BLK ODS_WT_DEAL_ENGRY_PRFL_BLK,
DB2INSTB.ODS_EDW_PERIOD Alias_EDW_PERIOD_Price_WT,
DB2INSTB.ODS_STLMT_CNTR_PRTY_XREF ODS_STLMT_CNTR_PRTY_XREF,
DB2INSTB.ODS_AEE_BOOK Alias_ODS_AEE_BOOK_WT_DEAL
WHERE
( ODS_STLMT_CNTR_PRTY_XREF.SOURCE_CPRTY_CD=ODS_WT_DE AL.CPRTY_NM AND
ODS_STLMT_CNTR_PRTY_XREF.SOURCE_SYS_CD = 'WT' )
AND
( Alias_EDW_PERIOD_Price_WT.GMT_PERIOD_TS=ODS_WT_DEA L_ENGRY_PRFL_BLK.HOUR_ENDING_TS )
AND ( RTRIM(CHAR(RIGHT(ODS_WT_DEAL.BOOK_NM,
4)))=Alias_ODS_AEE_BOOK_WT_DEAL.BOOK_NAME )
AND
( Alias_ODS_AEE_BOOK_WT_DEAL.BOOK_ID=Alias_ODS_AEE_B OOK_DESIG_WT_DEAL.BOOK_ID )
AND ( ODS_WT_DEAL_ENGRY_PRFL_BLK.DEAL_NR=ODS_WT_DEAL.DEA L_NR )
AND (
( ( Alias_EDW_PERIOD_Price_WT.CPT_PERIOD_DT ) between '03/01/2007'
and '03/31/2007' AND (( Alias_EDW_PERIOD_Price_WT.CPT_PERIOD_DT ) >=
Alias_ODS_AEE_BOOK_DESIG_WT_DEAL.START_DATE AND
( Alias_EDW_PERIOD_Price_WT.CPT_PERIOD_DT ) <=
Alias_ODS_AEE_BOOK_DESIG_WT_DEAL.STOP_DATE )
AND
ODS_WT_DEAL_ENGRY_PRFL_BLK.HOUR_ENDING_DT >= date('03/01/2007') -1
day and ODS_WT_DEAL_ENGRY_PRFL_BLK.HOUR_ENDING_DT <=
date('03/31/2007') + 1 day
and ( Alias_EDW_PERIOD_Price_WT.CPT_PERIOD_DT ) BETWEEN
ODS_STLMT_CNTR_PRTY_XREF.SOURCE_START_DT AND
ODS_STLMT_CNTR_PRTY_XREF.SOURCE_END_DT )
AND ( ( ODS_STLMT_CNTR_PRTY_XREF.CPRTY_CD ) NOT IN
('AEP','AEPGEN','AEPLOAD','AEPTCC','AEPTNC','PJM
OPRES','ERCOT','QFOXYCHEM','',' ') AND
( ODS_STLMT_CNTR_PRTY_XREF.CPRTY_CD ) NOT IN '%' )
AND 'WT' || ' - ' ||
( Alias_ODS_AEE_BOOK_DESIG_WT_DEAL.BOOK_DESIGNATION_ NAME ) IN ('WT -
PE', 'WT - PW')
AND ODS_WT_DEAL.DEAL_TYP_TX < 'Transmission'
AND ODS_WT_DEAL.STATUS_CD < 'VOIDED'
)
UNION
SELECT
RTRIM(CHAR(RIGHT(ODS_WT_DEAL.BOOK_NM,4))),
CASE WHEN Alias_ODS_AEE_BOOK_DESIG_WT_DEAL.BOOK_DESIGNATION_ NAME =
'PW'
AND ( ODS_WT_SCHDL_DEAL.SRC_CD ) IN ('CSWS','PJM') THEN 'WT-On - PW'
WHEN Alias_ODS_AEE_BOOK_DESIG_WT_DEAL.BOOK_DESIGNATION_ NAME = 'PE'
AND ( ODS_WT_SCHDL_DEAL.SRC_CD ) IN ('CSWS','PJM') THEN 'WT-On - PE'
END,
ODS_WT_SCHDL_DEAL.PHYS_DEAL_NR,
SUM(ODS_WT_SCHDL_LPRFL_BLK.BLK_MW*-1),
ODS_WT_SCHDL_LPRFL_BLK.CPT_PERIOD_DT,
ODS_WT_DEAL.TRAN_TYP_CD,
ODS_STLMT_CNTR_PRTY_XREF.CPRTY_CD,
ODS_WT_DEAL.DEAL_ID,
CHAR(ODS_WT_DEAL.DEAL_NR),
ODS_WT_DEAL.TRD_DT,
rtrim(ltrim(ODS_WT_DEAL.MKT1_NM)),
ODS_WT_DEAL.DEAL_TYP_TX,
RTRIM(ODS_WT_DEAL.TERM_TX),
ODS_WT_SCHDL_LPRFL_BLK.CPT_PERIOD_TS
FROM
DB2INSTB.ODS_WT_DEAL ODS_WT_DEAL,
DB2INSTB.ODS_AEE_BOOK_DESIGNATION Alias_ODS_AEE_BOOK_DESIG_WT_DEAL,
DB2INSTB.ODS_WT_SCHDL_DEAL ODS_WT_SCHDL_DEAL,
DB2INSTB.ODS_WT_SCHDL_LPRFL_BLK_VW ODS_WT_SCHDL_LPRFL_BLK,
DB2INSTB.ODS_STLMT_CNTR_PRTY_XREF ODS_STLMT_CNTR_PRTY_XREF,
DB2INSTB.ODS_WT_SCHDL_TPRFL_RSRVN ODS_WT_SCHDL_TPRFL_RSRVN,
DB2INSTB.ODS_AEE_BOOK Alias_ODS_AEE_BOOK_WT_DEAL,
DB2INSTB.ODS_WT_SCHDL_DEAL_CPRTY ODS_WT_SCHDL_DEAL_CPRTY,
DB2INSTB.ODS_WT_SCHDL_DEAL_LPRFL ODS_WT_SCHDL_DEAL_LPRFL,
DB2INSTB.ODS_WT_SCHDL_DEAL_TPRFL ODS_WT_SCHDL_DEAL_TPRFL
WHERE

( ODS_WT_SCHDL_DEAL.PHYS_DEAL_NR=ODS_WT_SCHDL_DEAL_C PRTY.PHYS_DEAL_NR )
AND ( ODS_WT_SCHDL_DEAL_CPRTY.DEAL_NR=ODS_WT_DEAL.DEAL_N R )
AND ( ODS_STLMT_CNTR_PRTY_XREF.SOURCE_CPRTY_CD=ODS_WT_DE AL.CPRTY_NM
AND ODS_STLMT_CNTR_PRTY_XREF.SOURCE_SYS_CD = 'WT' )
AND
( ODS_WT_SCHDL_DEAL_LPRFL.PHYS_DEAL_NR=ODS_WT_SCHDL_ DEAL.PHYS_DEAL_NR )
AND
( ODS_WT_SCHDL_DEAL_LPRFL.PHYS_DEAL_NR=ODS_WT_SCHDL_ LPRFL_BLK.PHYS_DEAL_NR
and
ODS_WT_SCHDL_DEAL_LPRFL.LPRFL_ID=ODS_WT_SCHDL_LPRF L_BLK.LPRFL_ID )
AND
( ODS_WT_SCHDL_DEAL_LPRFL.PHYS_DEAL_NR=ODS_WT_SCHDL_ DEAL_TPRFL.PHYS_DEAL_NR )
AND
( ODS_WT_SCHDL_DEAL_LPRFL.TRNMT_PRVDR_NM=ODS_WT_SCHD L_DEAL_TPRFL.TRNMT_PRVDR_NM )
AND
( ODS_WT_SCHDL_DEAL_TPRFL.PHYS_DEAL_NR=ODS_WT_SCHDL_ TPRFL_RSRVN.PHYS_DEAL_NR )
AND
( ODS_WT_SCHDL_DEAL_TPRFL.SCHDL_DEAL_PRFL_ID=ODS_WT_ SCHDL_TPRFL_RSRVN.SCHDL_DEAL_PRFL_ID )
AND
( ODS_WT_SCHDL_DEAL_TPRFL.TRANS_PRFL_ID=ODS_WT_SCHDL _TPRFL_RSRVN.TRANS_PRFL_ID )
AND ( RTRIM(CHAR(RIGHT(ODS_WT_DEAL.BOOK_NM,
4)))=Alias_ODS_AEE_BOOK_WT_DEAL.BOOK_NAME )
AND
( Alias_ODS_AEE_BOOK_WT_DEAL.BOOK_ID=Alias_ODS_AEE_B OOK_DESIG_WT_DEAL.BOOK_ID )
AND (
( ( ODS_STLMT_CNTR_PRTY_XREF.CPRTY_CD ) NOT IN
('AEP','AEPGEN','AEPLOAD','AEPTCC','AEPTNC','PJM
OPRES','ERCOT','QFOXYCHEM','',' ') AND
( ODS_STLMT_CNTR_PRTY_XREF.CPRTY_CD ) NOT IN '%' )
AND ( ( ODS_WT_SCHDL_LPRFL_BLK.CPT_PERIOD_DT ) between '03/01/2007'
and '03/31/2007' AND (ODS_WT_SCHDL_LPRFL_BLK.CPT_PERIOD_DT >=
Alias_ODS_AEE_BOOK_DESIG_WT_DEAL.START_DATE AND
ODS_WT_SCHDL_LPRFL_BLK.CPT_PERIOD_DT <=
Alias_ODS_AEE_BOOK_DESIG_WT_DEAL.STOP_DATE) and
ODS_WT_SCHDL_LPRFL_BLK.HOUR_ENDING_DT >= date('03/01/2007') -1 day
and ODS_WT_SCHDL_LPRFL_BLK.HOUR_ENDING_DT<= date('03/31/2007') + 1
day
and ( Alias_EDW_PERIOD_WT_LPrfl.CPT_PERIOD_DT ) BETWEEN
ODS_STLMT_CNTR_PRTY_XREF.SOURCE_START_DT AND
ODS_STLMT_CNTR_PRTY_XREF.SOURCE_END_DT )
AND ODS_WT_SCHDL_DEAL.STAT_CD < 'VOIDED'
AND CASE WHEN
Alias_ODS_AEE_BOOK_DESIG_WT_DEAL.BOOK_DESIGNATION_ NAME = 'PW'
AND ( ODS_WT_SCHDL_DEAL.SRC_CD ) IN ('CSWS','PJM') THEN 'WT-On - PW'
WHEN Alias_ODS_AEE_BOOK_DESIG_WT_DEAL.BOOK_DESIGNATION_ NAME = 'PE'
AND ( ODS_WT_SCHDL_DEAL.SRC_CD ) IN ('CSWS','PJM') THEN 'WT-On - PE'
END IN ('WT-On - PE', 'WT-On - PW')
AND ODS_WT_DEAL.DEAL_TYP_TX < 'Transmission'
AND ODS_WT_DEAL.STATUS_CD < 'VOIDED'
AND ODS_WT_SCHDL_TPRFL_RSRVN.OWNER_NM IN ('AEPMEA', 'CSWMEO')
)
GROUP BY
RTRIM(CHAR(RIGHT(ODS_WT_DEAL.BOOK_NM,4))),
CASE WHEN Alias_ODS_AEE_BOOK_DESIG_WT_DEAL.BOOK_DESIGNATION_ NAME =
'PW'
AND ( ODS_WT_SCHDL_DEAL.SRC_CD ) IN ('CSWS','PJM') THEN 'WT-On - PW'
WHEN Alias_ODS_AEE_BOOK_DESIG_WT_DEAL.BOOK_DESIGNATION_ NAME = 'PE'
AND ( ODS_WT_SCHDL_DEAL.SRC_CD ) IN ('CSWS','PJM') THEN 'WT-On - PE'
END,
ODS_WT_SCHDL_DEAL.PHYS_DEAL_NR,
ODS_WT_SCHDL_LPRFL_BLK.CPT_PERIOD_DT,
ODS_WT_DEAL.TRAN_TYP_CD,
ODS_STLMT_CNTR_PRTY_XREF.CPRTY_CD,
ODS_WT_DEAL.DEAL_ID,
CHAR(ODS_WT_DEAL.DEAL_NR),
ODS_WT_DEAL.TRD_DT,
rtrim(ltrim(ODS_WT_DEAL.MKT1_NM)),
ODS_WT_DEAL.DEAL_TYP_TX,
RTRIM(ODS_WT_DEAL.TERM_TX),
ODS_WT_SCHDL_LPRFL_BLK.CPT_PERIOD_TS
)
);
Apr 10 '07 #2
Phil,

Thanks for the reply.

I have changed the optimization level of the database using
update db cfg using dft_queryopt 3
This was the best level that it had performed at.

Since this is coming from a Business Objects Universe. It is really
hard to do what you said with a temporary table.

I have experimented with MDC's to see if I can fix the access path
with them. I also tried Views. Because these influence the optimizer
also.

Thanks again for the reply.

Apr 11 '07 #3

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

3 posts views Thread by Joachim Klassen | last post: by
6 posts views Thread by Peter Hickman | last post: by
18 posts views Thread by ranjeet.gupta | last post: by
15 posts views Thread by Ken Allen | last post: by
30 posts views Thread by Yevgen Muntyan | last post: by
reply views Thread by NPC403 | last post: by
1 post views Thread by fmendoza | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.