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(RIGH T(ODS_WT_DEAL.B OOK_NM,4))),
( CASE WHEN ( ODS_WT_SCHDL_DE AL.SRC_CD ) NOT IN ('CSWS','PJM') AND
( ODS_WT_SCHDL_DE AL.SINK_CD ) NOT IN ('CSWS','PJM') THEN
'WT-Off'
WHEN ( ODS_WT_SCHDL_DE AL.SRC_CD ) IN ('CSWS','PJM') OR
( ODS_WT_SCHDL_DE AL.SINK_CD ) IN ('CSWS','PJM') THEN
'WT-On'
END ) || ' - ' ||
( Alias_ODS_AEE_B OOK_DESIG_WT_DE AL.BOOK_DESIGNA TION_NAME ),
ODS_WT_DEAL.DEA L_NR,
SUM(ABS(CASE WHEN ( ODS_WT_SCHDL_DE AL.SRC_CD ) IN ('CSWS','PJM')
THEN ODS_WT_SCHDL_EN RGY_PRFL_BLK.GE N_MW_QT
WHEN ( ODS_WT_SCHDL_DE AL.SINK_CD ) IN ('CSWS','PJM') THEN
ODS_WT_SCHDL_EN RGY_PRFL_BLK.LO AD_MW_QT
END)),
Alias_EDW_PERIO D_MWH_WT.CPT_PE RIOD_DT,
ODS_WT_DEAL.TRA N_TYP_CD,
ODS_STLMT_CNTR_ PRTY_XREF.CPRTY _CD,
ODS_WT_DEAL.DEA L_ID,
CHAR(ODS_WT_DEA L.DEAL_NR),
ODS_WT_DEAL.TRD _DT,
rtrim(ltrim(ODS _WT_DEAL.MKT1_N M)),
ODS_WT_DEAL.DEA L_TYP_TX,
RTRIM(ODS_WT_DE AL.TERM_TX),
Alias_EDW_PERIO D_MWH_WT.CPT_PE RIOD_TS
FROM
DB2INSTB.ODS_WT _DEAL ODS_WT_DEAL,
DB2INSTB.ODS_WT _SCHDL_DEAL ODS_WT_SCHDL_DE AL,
DB2INSTB.ODS_AE E_BOOK_DESIGNAT ION Alias_ODS_AEE_B OOK_DESIG_WT_DE AL,
DB2INSTB.ODS_WT _SCHDL_ENRGY_PR FL_BLK ODS_WT_SCHDL_EN RGY_PRFL_BLK,
DB2INSTB.ODS_ED W_PERIOD Alias_EDW_PERIO D_MWH_WT,
DB2INSTB.ODS_ST LMT_CNTR_PRTY_X REF ODS_STLMT_CNTR_ PRTY_XREF,
DB2INSTB.ODS_WT _SCHDL_DEAL_CPR TY ODS_WT_SCHDL_DE AL_CPRTY,
DB2INSTB.ODS_AE E_BOOK Alias_ODS_AEE_B OOK_WT_DEAL
WHERE
( ODS_WT_SCHDL_DE AL.PHYS_DEAL_NR =ODS_WT_SCHDL_D EAL_CPRTY.PHYS_ DEAL_NR )
AND ( ODS_WT_SCHDL_DE AL_CPRTY.DEAL_N R=ODS_WT_DEAL.D EAL_NR )
AND ( ODS_STLMT_CNTR_ PRTY_XREF.SOURC E_CPRTY_CD=ODS_ WT_DEAL.CPRTY_N M
AND ODS_STLMT_CNTR_ PRTY_XREF.SOURC E_SYS_CD = 'WT' )
AND
( Alias_EDW_PERIO D_MWH_WT.GMT_PE RIOD_TS=ODS_WT_ SCHDL_ENRGY_PRF L_BLK.HOUR_ENDI NG_TS )
AND ( RTRIM(CHAR(RIGH T(ODS_WT_DEAL.B OOK_NM,
4)))=Alias_ODS_ AEE_BOOK_WT_DEA L.BOOK_NAME )
AND
( Alias_ODS_AEE_B OOK_WT_DEAL.BOO K_ID=Alias_ODS_ AEE_BOOK_DESIG_ WT_DEAL.BOOK_ID )
AND
( ODS_WT_SCHDL_EN RGY_PRFL_BLK.PH YS_DEAL_NR=ODS_ WT_SCHDL_DEAL_C PRTY.PHYS_DEAL_ NR )
AND (
( ( Alias_EDW_PERIO D_MWH_WT.CPT_PE RIOD_DT ) between '03/01/2007'
and '03/31/2007' AND (Alias_EDW_PERI OD_MWH_WT.CPT_P ERIOD_DT >=
Alias_ODS_AEE_B OOK_DESIG_WT_DE AL.START_DATE AND
Alias_EDW_PERIO D_MWH_WT.CPT_PE RIOD_DT <=
Alias_ODS_AEE_B OOK_DESIG_WT_DE AL.STOP_DATE )
AND
ODS_WT_SCHDL_EN RGY_PRFL_BLK.HO UR_ENDING_DT >= date('03/01/2007') -1
day and ODS_WT_SCHDL_EN RGY_PRFL_BLK.HO UR_ENDING_DT <=
date('03/31/2007') + 1 day
and ( Alias_EDW_PERIO D_MWH_WT.CPT_PE RIOD_DT ) BETWEEN
ODS_STLMT_CNTR_ PRTY_XREF.SOURC E_START_DT AND
ODS_STLMT_CNTR_ PRTY_XREF.SOURC E_END_DT )
AND ( ( ODS_STLMT_CNTR_ PRTY_XREF.CPRTY _CD ) NOT IN
('AEP','AEPGEN' ,'AEPLOAD','AEP TCC','AEPTNC',' PJM
OPRES','ERCOT', 'QFOXYCHEM','', ' ') AND
( ODS_STLMT_CNTR_ PRTY_XREF.CPRTY _CD ) NOT IN '%' )
AND ( CASE WHEN ( ODS_WT_SCHDL_DE AL.SRC_CD ) NOT IN ('CSWS','PJM')
AND ( ODS_WT_SCHDL_DE AL.SINK_CD ) NOT IN ('CSWS','PJM') THEN
'WT-Off'
WHEN ( ODS_WT_SCHDL_DE AL.SRC_CD ) IN ('CSWS','PJM') OR
( ODS_WT_SCHDL_DE AL.SINK_CD ) IN ('CSWS','PJM') THEN
'WT-On'
END ) || ' - ' ||
( Alias_ODS_AEE_B OOK_DESIG_WT_DE AL.BOOK_DESIGNA TION_NAME ) IN ('WT-
On - PE', 'WT-On - PW')
AND ODS_WT_DEAL.STA TUS_CD < 'VOIDED'
AND ODS_WT_SCHDL_DE AL.STAT_CD < 'VOIDED'
AND ODS_WT_DEAL.DEA L_TYP_TX < 'Transmission'
)
GROUP BY
RTRIM(CHAR(RIGH T(ODS_WT_DEAL.B OOK_NM,4))),
( CASE WHEN ( ODS_WT_SCHDL_DE AL.SRC_CD ) NOT IN ('CSWS','PJM') AND
( ODS_WT_SCHDL_DE AL.SINK_CD ) NOT IN ('CSWS','PJM') THEN
'WT-Off'
WHEN ( ODS_WT_SCHDL_DE AL.SRC_CD ) IN ('CSWS','PJM') OR
( ODS_WT_SCHDL_DE AL.SINK_CD ) IN ('CSWS','PJM') THEN
'WT-On'
END ) || ' - ' ||
( Alias_ODS_AEE_B OOK_DESIG_WT_DE AL.BOOK_DESIGNA TION_NAME ),
ODS_WT_DEAL.DEA L_NR,
Alias_EDW_PERIO D_MWH_WT.CPT_PE RIOD_DT,
ODS_WT_DEAL.TRA N_TYP_CD,
ODS_STLMT_CNTR_ PRTY_XREF.CPRTY _CD,
ODS_WT_DEAL.DEA L_ID,
CHAR(ODS_WT_DEA L.DEAL_NR),
ODS_WT_DEAL.TRD _DT,
rtrim(ltrim(ODS _WT_DEAL.MKT1_N M)),
ODS_WT_DEAL.DEA L_TYP_TX,
RTRIM(ODS_WT_DE AL.TERM_TX),
Alias_EDW_PERIO D_MWH_WT.CPT_PE RIOD_TS
UNION
(
SELECT
RTRIM(CHAR(RIGH T(ODS_WT_DEAL.B OOK_NM,4))),
'WT' || ' - ' ||
( Alias_ODS_AEE_B OOK_DESIG_WT_DE AL.BOOK_DESIGNA TION_NAME ),
ODS_WT_DEAL.DEA L_NR,
ABS(ODS_WT_DEAL _ENGRY_PRFL_BLK .EP_MW_QT),
Alias_EDW_PERIO D_Price_WT.CPT_ PERIOD_DT,
ODS_WT_DEAL.TRA N_TYP_CD,
ODS_STLMT_CNTR_ PRTY_XREF.CPRTY _CD,
ODS_WT_DEAL.DEA L_ID,
CHAR(ODS_WT_DEA L.DEAL_NR),
ODS_WT_DEAL.TRD _DT,
rtrim(ltrim(ODS _WT_DEAL.MKT1_N M)),
ODS_WT_DEAL.DEA L_TYP_TX,
RTRIM(ODS_WT_DE AL.TERM_TX),
Alias_EDW_PERIO D_Price_WT.CPT_ PERIOD_TS
FROM
DB2INSTB.ODS_WT _DEAL ODS_WT_DEAL,
DB2INSTB.ODS_AE E_BOOK_DESIGNAT ION Alias_ODS_AEE_B OOK_DESIG_WT_DE AL,
DB2INSTB.ODS_WT _DEAL_ENGRY_PRF L_BLK ODS_WT_DEAL_ENG RY_PRFL_BLK,
DB2INSTB.ODS_ED W_PERIOD Alias_EDW_PERIO D_Price_WT,
DB2INSTB.ODS_ST LMT_CNTR_PRTY_X REF ODS_STLMT_CNTR_ PRTY_XREF,
DB2INSTB.ODS_AE E_BOOK Alias_ODS_AEE_B OOK_WT_DEAL
WHERE
( ODS_STLMT_CNTR_ PRTY_XREF.SOURC E_CPRTY_CD=ODS_ WT_DEAL.CPRTY_N M AND
ODS_STLMT_CNTR_ PRTY_XREF.SOURC E_SYS_CD = 'WT' )
AND
( Alias_EDW_PERIO D_Price_WT.GMT_ PERIOD_TS=ODS_W T_DEAL_ENGRY_PR FL_BLK.HOUR_END ING_TS )
AND ( RTRIM(CHAR(RIGH T(ODS_WT_DEAL.B OOK_NM,
4)))=Alias_ODS_ AEE_BOOK_WT_DEA L.BOOK_NAME )
AND
( Alias_ODS_AEE_B OOK_WT_DEAL.BOO K_ID=Alias_ODS_ AEE_BOOK_DESIG_ WT_DEAL.BOOK_ID )
AND ( ODS_WT_DEAL_ENG RY_PRFL_BLK.DEA L_NR=ODS_WT_DEA L.DEAL_NR )
AND (
( ( Alias_EDW_PERIO D_Price_WT.CPT_ PERIOD_DT ) between '03/01/2007'
and '03/31/2007' AND (( Alias_EDW_PERIO D_Price_WT.CPT_ PERIOD_DT ) >=
Alias_ODS_AEE_B OOK_DESIG_WT_DE AL.START_DATE AND
( Alias_EDW_PERIO D_Price_WT.CPT_ PERIOD_DT ) <=
Alias_ODS_AEE_B OOK_DESIG_WT_DE AL.STOP_DATE )
AND
ODS_WT_DEAL_ENG RY_PRFL_BLK.HOU R_ENDING_DT >= date('03/01/2007') -1
day and ODS_WT_DEAL_ENG RY_PRFL_BLK.HOU R_ENDING_DT <=
date('03/31/2007') + 1 day
and ( Alias_EDW_PERIO D_Price_WT.CPT_ PERIOD_DT ) BETWEEN
ODS_STLMT_CNTR_ PRTY_XREF.SOURC E_START_DT AND
ODS_STLMT_CNTR_ PRTY_XREF.SOURC E_END_DT )
AND ( ( ODS_STLMT_CNTR_ PRTY_XREF.CPRTY _CD ) NOT IN
('AEP','AEPGEN' ,'AEPLOAD','AEP TCC','AEPTNC',' PJM
OPRES','ERCOT', 'QFOXYCHEM','', ' ') AND
( ODS_STLMT_CNTR_ PRTY_XREF.CPRTY _CD ) NOT IN '%' )
AND 'WT' || ' - ' ||
( Alias_ODS_AEE_B OOK_DESIG_WT_DE AL.BOOK_DESIGNA TION_NAME ) IN ('WT -
PE', 'WT - PW')
AND ODS_WT_DEAL.DEA L_TYP_TX < 'Transmission'
AND ODS_WT_DEAL.STA TUS_CD < 'VOIDED'
)
UNION
SELECT
RTRIM(CHAR(RIGH T(ODS_WT_DEAL.B OOK_NM,4))),
CASE WHEN Alias_ODS_AEE_B OOK_DESIG_WT_DE AL.BOOK_DESIGNA TION_NAME =
'PW'
AND ( ODS_WT_SCHDL_DE AL.SRC_CD ) IN ('CSWS','PJM') THEN 'WT-On - PW'
WHEN Alias_ODS_AEE_B OOK_DESIG_WT_DE AL.BOOK_DESIGNA TION_NAME = 'PE'
AND ( ODS_WT_SCHDL_DE AL.SRC_CD ) IN ('CSWS','PJM') THEN 'WT-On - PE'
END,
ODS_WT_SCHDL_DE AL.PHYS_DEAL_NR ,
SUM(ODS_WT_SCHD L_LPRFL_BLK.BLK _MW*-1),
ODS_WT_SCHDL_LP RFL_BLK.CPT_PER IOD_DT,
ODS_WT_DEAL.TRA N_TYP_CD,
ODS_STLMT_CNTR_ PRTY_XREF.CPRTY _CD,
ODS_WT_DEAL.DEA L_ID,
CHAR(ODS_WT_DEA L.DEAL_NR),
ODS_WT_DEAL.TRD _DT,
rtrim(ltrim(ODS _WT_DEAL.MKT1_N M)),
ODS_WT_DEAL.DEA L_TYP_TX,
RTRIM(ODS_WT_DE AL.TERM_TX),
ODS_WT_SCHDL_LP RFL_BLK.CPT_PER IOD_TS
FROM
DB2INSTB.ODS_WT _DEAL ODS_WT_DEAL,
DB2INSTB.ODS_AE E_BOOK_DESIGNAT ION Alias_ODS_AEE_B OOK_DESIG_WT_DE AL,
DB2INSTB.ODS_WT _SCHDL_DEAL ODS_WT_SCHDL_DE AL,
DB2INSTB.ODS_WT _SCHDL_LPRFL_BL K_VW ODS_WT_SCHDL_LP RFL_BLK,
DB2INSTB.ODS_ST LMT_CNTR_PRTY_X REF ODS_STLMT_CNTR_ PRTY_XREF,
DB2INSTB.ODS_WT _SCHDL_TPRFL_RS RVN ODS_WT_SCHDL_TP RFL_RSRVN,
DB2INSTB.ODS_AE E_BOOK Alias_ODS_AEE_B OOK_WT_DEAL,
DB2INSTB.ODS_WT _SCHDL_DEAL_CPR TY ODS_WT_SCHDL_DE AL_CPRTY,
DB2INSTB.ODS_WT _SCHDL_DEAL_LPR FL ODS_WT_SCHDL_DE AL_LPRFL,
DB2INSTB.ODS_WT _SCHDL_DEAL_TPR FL ODS_WT_SCHDL_DE AL_TPRFL
WHERE
( ODS_WT_SCHDL_DE AL.PHYS_DEAL_NR =ODS_WT_SCHDL_D EAL_CPRTY.PHYS_ DEAL_NR )
AND ( ODS_WT_SCHDL_DE AL_CPRTY.DEAL_N R=ODS_WT_DEAL.D EAL_NR )
AND ( ODS_STLMT_CNTR_ PRTY_XREF.SOURC E_CPRTY_CD=ODS_ WT_DEAL.CPRTY_N M
AND ODS_STLMT_CNTR_ PRTY_XREF.SOURC E_SYS_CD = 'WT' )
AND
( ODS_WT_SCHDL_DE AL_LPRFL.PHYS_D EAL_NR=ODS_WT_S CHDL_DEAL.PHYS_ DEAL_NR )
AND
( ODS_WT_SCHDL_DE AL_LPRFL.PHYS_D EAL_NR=ODS_WT_S CHDL_LPRFL_BLK. PHYS_DEAL_NR
and
ODS_WT_SCHDL_DE AL_LPRFL.LPRFL_ ID=ODS_WT_SCHDL _LPRFL_BLK.LPRF L_ID )
AND
( ODS_WT_SCHDL_DE AL_LPRFL.PHYS_D EAL_NR=ODS_WT_S CHDL_DEAL_TPRFL .PHYS_DEAL_NR )
AND
( ODS_WT_SCHDL_DE AL_LPRFL.TRNMT_ PRVDR_NM=ODS_WT _SCHDL_DEAL_TPR FL.TRNMT_PRVDR_ NM )
AND
( ODS_WT_SCHDL_DE AL_TPRFL.PHYS_D EAL_NR=ODS_WT_S CHDL_TPRFL_RSRV N.PHYS_DEAL_NR )
AND
( ODS_WT_SCHDL_DE AL_TPRFL.SCHDL_ DEAL_PRFL_ID=OD S_WT_SCHDL_TPRF L_RSRVN.SCHDL_D EAL_PRFL_ID )
AND
( ODS_WT_SCHDL_DE AL_TPRFL.TRANS_ PRFL_ID=ODS_WT_ SCHDL_TPRFL_RSR VN.TRANS_PRFL_I D )
AND ( RTRIM(CHAR(RIGH T(ODS_WT_DEAL.B OOK_NM,
4)))=Alias_ODS_ AEE_BOOK_WT_DEA L.BOOK_NAME )
AND
( Alias_ODS_AEE_B OOK_WT_DEAL.BOO K_ID=Alias_ODS_ AEE_BOOK_DESIG_ WT_DEAL.BOOK_ID )
AND (
( ( ODS_STLMT_CNTR_ PRTY_XREF.CPRTY _CD ) NOT IN
('AEP','AEPGEN' ,'AEPLOAD','AEP TCC','AEPTNC',' PJM
OPRES','ERCOT', 'QFOXYCHEM','', ' ') AND
( ODS_STLMT_CNTR_ PRTY_XREF.CPRTY _CD ) NOT IN '%' )
AND ( ( ODS_WT_SCHDL_LP RFL_BLK.CPT_PER IOD_DT ) between '03/01/2007'
and '03/31/2007' AND (ODS_WT_SCHDL_L PRFL_BLK.CPT_PE RIOD_DT >=
Alias_ODS_AEE_B OOK_DESIG_WT_DE AL.START_DATE AND
ODS_WT_SCHDL_LP RFL_BLK.CPT_PER IOD_DT <=
Alias_ODS_AEE_B OOK_DESIG_WT_DE AL.STOP_DATE) and
ODS_WT_SCHDL_LP RFL_BLK.HOUR_EN DING_DT >= date('03/01/2007') -1 day
and ODS_WT_SCHDL_LP RFL_BLK.HOUR_EN DING_DT<= date('03/31/2007') + 1
day
and ( Alias_EDW_PERIO D_WT_LPrfl.CPT_ PERIOD_DT ) BETWEEN
ODS_STLMT_CNTR_ PRTY_XREF.SOURC E_START_DT AND
ODS_STLMT_CNTR_ PRTY_XREF.SOURC E_END_DT )
AND ODS_WT_SCHDL_DE AL.STAT_CD < 'VOIDED'
AND CASE WHEN
Alias_ODS_AEE_B OOK_DESIG_WT_DE AL.BOOK_DESIGNA TION_NAME = 'PW'
AND ( ODS_WT_SCHDL_DE AL.SRC_CD ) IN ('CSWS','PJM') THEN 'WT-On - PW'
WHEN Alias_ODS_AEE_B OOK_DESIG_WT_DE AL.BOOK_DESIGNA TION_NAME = 'PE'
AND ( ODS_WT_SCHDL_DE AL.SRC_CD ) IN ('CSWS','PJM') THEN 'WT-On - PE'
END IN ('WT-On - PE', 'WT-On - PW')
AND ODS_WT_DEAL.DEA L_TYP_TX < 'Transmission'
AND ODS_WT_DEAL.STA TUS_CD < 'VOIDED'
AND ODS_WT_SCHDL_TP RFL_RSRVN.OWNER _NM IN ('AEPMEA', 'CSWMEO')
)
GROUP BY
RTRIM(CHAR(RIGH T(ODS_WT_DEAL.B OOK_NM,4))),
CASE WHEN Alias_ODS_AEE_B OOK_DESIG_WT_DE AL.BOOK_DESIGNA TION_NAME =
'PW'
AND ( ODS_WT_SCHDL_DE AL.SRC_CD ) IN ('CSWS','PJM') THEN 'WT-On - PW'
WHEN Alias_ODS_AEE_B OOK_DESIG_WT_DE AL.BOOK_DESIGNA TION_NAME = 'PE'
AND ( ODS_WT_SCHDL_DE AL.SRC_CD ) IN ('CSWS','PJM') THEN 'WT-On - PE'
END,
ODS_WT_SCHDL_DE AL.PHYS_DEAL_NR ,
ODS_WT_SCHDL_LP RFL_BLK.CPT_PER IOD_DT,
ODS_WT_DEAL.TRA N_TYP_CD,
ODS_STLMT_CNTR_ PRTY_XREF.CPRTY _CD,
ODS_WT_DEAL.DEA L_ID,
CHAR(ODS_WT_DEA L.DEAL_NR),
ODS_WT_DEAL.TRD _DT,
rtrim(ltrim(ODS _WT_DEAL.MKT1_N M)),
ODS_WT_DEAL.DEA L_TYP_TX,
RTRIM(ODS_WT_DE AL.TERM_TX),
ODS_WT_SCHDL_LP RFL_BLK.CPT_PER IOD_TS
)
);
2  2191 
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(RIGH T(ODS_WT_DEAL.B OOK_NM,4))),
( CASE WHEN ( ODS_WT_SCHDL_DE AL.SRC_CD ) NOT IN ('CSWS','PJM') AND
( ODS_WT_SCHDL_DE AL.SINK_CD ) NOT IN ('CSWS','PJM') THEN
'WT-Off'
WHEN ( ODS_WT_SCHDL_DE AL.SRC_CD ) IN ('CSWS','PJM') OR
( ODS_WT_SCHDL_DE AL.SINK_CD ) IN ('CSWS','PJM') THEN
'WT-On'
END ) || ' - ' ||
( Alias_ODS_AEE_B OOK_DESIG_WT_DE AL.BOOK_DESIGNA TION_NAME ),
ODS_WT_DEAL.DEA L_NR,
SUM(ABS(CASE WHEN ( ODS_WT_SCHDL_DE AL.SRC_CD ) IN ('CSWS','PJM')
THEN ODS_WT_SCHDL_EN RGY_PRFL_BLK.GE N_MW_QT
WHEN ( ODS_WT_SCHDL_DE AL.SINK_CD ) IN ('CSWS','PJM') THEN
ODS_WT_SCHDL_EN RGY_PRFL_BLK.LO AD_MW_QT
END)),
Alias_EDW_PERIO D_MWH_WT.CPT_PE RIOD_DT,
ODS_WT_DEAL.TRA N_TYP_CD,
ODS_STLMT_CNTR_ PRTY_XREF.CPRTY _CD,
ODS_WT_DEAL.DEA L_ID,
CHAR(ODS_WT_DEA L.DEAL_NR),
ODS_WT_DEAL.TRD _DT,
rtrim(ltrim(ODS _WT_DEAL.MKT1_N M)),
ODS_WT_DEAL.DEA L_TYP_TX,
RTRIM(ODS_WT_DE AL.TERM_TX),
Alias_EDW_PERIO D_MWH_WT.CPT_PE RIOD_TS
FROM
DB2INSTB.ODS_WT _DEAL ODS_WT_DEAL,
DB2INSTB.ODS_WT _SCHDL_DEAL ODS_WT_SCHDL_DE AL,
DB2INSTB.ODS_AE E_BOOK_DESIGNAT ION Alias_ODS_AEE_B OOK_DESIG_WT_DE AL,
DB2INSTB.ODS_WT _SCHDL_ENRGY_PR FL_BLK ODS_WT_SCHDL_EN RGY_PRFL_BLK,
DB2INSTB.ODS_ED W_PERIOD Alias_EDW_PERIO D_MWH_WT,
DB2INSTB.ODS_ST LMT_CNTR_PRTY_X REF ODS_STLMT_CNTR_ PRTY_XREF,
DB2INSTB.ODS_WT _SCHDL_DEAL_CPR TY ODS_WT_SCHDL_DE AL_CPRTY,
DB2INSTB.ODS_AE E_BOOK Alias_ODS_AEE_B OOK_WT_DEAL
WHERE
( ODS_WT_SCHDL_DE AL.PHYS_DEAL_NR =ODS_WT_SCHDL_D EAL_CPRTY.PHYS_ DEAL_NR )
AND ( ODS_WT_SCHDL_DE AL_CPRTY.DEAL_N R=ODS_WT_DEAL.D EAL_NR )
AND ( ODS_STLMT_CNTR_ PRTY_XREF.SOURC E_CPRTY_CD=ODS_ WT_DEAL.CPRTY_N M
AND ODS_STLMT_CNTR_ PRTY_XREF.SOURC E_SYS_CD = 'WT' )
AND
( Alias_EDW_PERIO D_MWH_WT.GMT_PE RIOD_TS=ODS_WT_ SCHDL_ENRGY_PRF L_BLK.HOUR_ENDI NG_TS )
AND ( RTRIM(CHAR(RIGH T(ODS_WT_DEAL.B OOK_NM,
4)))=Alias_ODS_ AEE_BOOK_WT_DEA L.BOOK_NAME )
AND
( Alias_ODS_AEE_B OOK_WT_DEAL.BOO K_ID=Alias_ODS_ AEE_BOOK_DESIG_ WT_DEAL.BOOK_ID )
AND
( ODS_WT_SCHDL_EN RGY_PRFL_BLK.PH YS_DEAL_NR=ODS_ WT_SCHDL_DEAL_C PRTY.PHYS_DEAL_ NR )
AND (
( ( Alias_EDW_PERIO D_MWH_WT.CPT_PE RIOD_DT ) between '03/01/2007'
and '03/31/2007' AND (Alias_EDW_PERI OD_MWH_WT.CPT_P ERIOD_DT >=
Alias_ODS_AEE_B OOK_DESIG_WT_DE AL.START_DATE AND
Alias_EDW_PERIO D_MWH_WT.CPT_PE RIOD_DT <=
Alias_ODS_AEE_B OOK_DESIG_WT_DE AL.STOP_DATE )
AND
ODS_WT_SCHDL_EN RGY_PRFL_BLK.HO UR_ENDING_DT >= date('03/01/2007') -1
day and ODS_WT_SCHDL_EN RGY_PRFL_BLK.HO UR_ENDING_DT <=
date('03/31/2007') + 1 day
and ( Alias_EDW_PERIO D_MWH_WT.CPT_PE RIOD_DT ) BETWEEN
ODS_STLMT_CNTR_ PRTY_XREF.SOURC E_START_DT AND
ODS_STLMT_CNTR_ PRTY_XREF.SOURC E_END_DT )
AND ( ( ODS_STLMT_CNTR_ PRTY_XREF.CPRTY _CD ) NOT IN
('AEP','AEPGEN' ,'AEPLOAD','AEP TCC','AEPTNC',' PJM
OPRES','ERCOT', 'QFOXYCHEM','', ' ') AND
( ODS_STLMT_CNTR_ PRTY_XREF.CPRTY _CD ) NOT IN '%' )
AND ( CASE WHEN ( ODS_WT_SCHDL_DE AL.SRC_CD ) NOT IN ('CSWS','PJM')
AND ( ODS_WT_SCHDL_DE AL.SINK_CD ) NOT IN ('CSWS','PJM') THEN
'WT-Off'
WHEN ( ODS_WT_SCHDL_DE AL.SRC_CD ) IN ('CSWS','PJM') OR
( ODS_WT_SCHDL_DE AL.SINK_CD ) IN ('CSWS','PJM') THEN
'WT-On'
END ) || ' - ' ||
( Alias_ODS_AEE_B OOK_DESIG_WT_DE AL.BOOK_DESIGNA TION_NAME ) IN ('WT-
On - PE', 'WT-On - PW')
AND ODS_WT_DEAL.STA TUS_CD < 'VOIDED'
AND ODS_WT_SCHDL_DE AL.STAT_CD < 'VOIDED'
AND ODS_WT_DEAL.DEA L_TYP_TX < 'Transmission'
)
GROUP BY
RTRIM(CHAR(RIGH T(ODS_WT_DEAL.B OOK_NM,4))),
( CASE WHEN ( ODS_WT_SCHDL_DE AL.SRC_CD ) NOT IN ('CSWS','PJM') AND
( ODS_WT_SCHDL_DE AL.SINK_CD ) NOT IN ('CSWS','PJM') THEN
'WT-Off'
WHEN ( ODS_WT_SCHDL_DE AL.SRC_CD ) IN ('CSWS','PJM') OR
( ODS_WT_SCHDL_DE AL.SINK_CD ) IN ('CSWS','PJM') THEN
'WT-On'
END ) || ' - ' ||
( Alias_ODS_AEE_B OOK_DESIG_WT_DE AL.BOOK_DESIGNA TION_NAME ),
ODS_WT_DEAL.DEA L_NR,
Alias_EDW_PERIO D_MWH_WT.CPT_PE RIOD_DT,
ODS_WT_DEAL.TRA N_TYP_CD,
ODS_STLMT_CNTR_ PRTY_XREF.CPRTY _CD,
ODS_WT_DEAL.DEA L_ID,
CHAR(ODS_WT_DEA L.DEAL_NR),
ODS_WT_DEAL.TRD _DT,
rtrim(ltrim(ODS _WT_DEAL.MKT1_N M)),
ODS_WT_DEAL.DEA L_TYP_TX,
RTRIM(ODS_WT_DE AL.TERM_TX),
Alias_EDW_PERIO D_MWH_WT.CPT_PE RIOD_TS
UNION
(
SELECT
RTRIM(CHAR(RIGH T(ODS_WT_DEAL.B OOK_NM,4))),
'WT' || ' - ' ||
( Alias_ODS_AEE_B OOK_DESIG_WT_DE AL.BOOK_DESIGNA TION_NAME ),
ODS_WT_DEAL.DEA L_NR,
ABS(ODS_WT_DEAL _ENGRY_PRFL_BLK .EP_MW_QT),
Alias_EDW_PERIO D_Price_WT.CPT_ PERIOD_DT,
ODS_WT_DEAL.TRA N_TYP_CD,
ODS_STLMT_CNTR_ PRTY_XREF.CPRTY _CD,
ODS_WT_DEAL.DEA L_ID,
CHAR(ODS_WT_DEA L.DEAL_NR),
ODS_WT_DEAL.TRD _DT,
rtrim(ltrim(ODS _WT_DEAL.MKT1_N M)),
ODS_WT_DEAL.DEA L_TYP_TX,
RTRIM(ODS_WT_DE AL.TERM_TX),
Alias_EDW_PERIO D_Price_WT.CPT_ PERIOD_TS
FROM
DB2INSTB.ODS_WT _DEAL ODS_WT_DEAL,
DB2INSTB.ODS_AE E_BOOK_DESIGNAT ION Alias_ODS_AEE_B OOK_DESIG_WT_DE AL,
DB2INSTB.ODS_WT _DEAL_ENGRY_PRF L_BLK ODS_WT_DEAL_ENG RY_PRFL_BLK,
DB2INSTB.ODS_ED W_PERIOD Alias_EDW_PERIO D_Price_WT,
DB2INSTB.ODS_ST LMT_CNTR_PRTY_X REF ODS_STLMT_CNTR_ PRTY_XREF,
DB2INSTB.ODS_AE E_BOOK Alias_ODS_AEE_B OOK_WT_DEAL
WHERE
( ODS_STLMT_CNTR_ PRTY_XREF.SOURC E_CPRTY_CD=ODS_ WT_DEAL.CPRTY_N M AND
ODS_STLMT_CNTR_ PRTY_XREF.SOURC E_SYS_CD = 'WT' )
AND
( Alias_EDW_PERIO D_Price_WT.GMT_ PERIOD_TS=ODS_W T_DEAL_ENGRY_PR FL_BLK.HOUR_END ING_TS )
AND ( RTRIM(CHAR(RIGH T(ODS_WT_DEAL.B OOK_NM,
4)))=Alias_ODS_ AEE_BOOK_WT_DEA L.BOOK_NAME )
AND
( Alias_ODS_AEE_B OOK_WT_DEAL.BOO K_ID=Alias_ODS_ AEE_BOOK_DESIG_ WT_DEAL.BOOK_ID )
AND ( ODS_WT_DEAL_ENG RY_PRFL_BLK.DEA L_NR=ODS_WT_DEA L.DEAL_NR )
AND (
( ( Alias_EDW_PERIO D_Price_WT.CPT_ PERIOD_DT ) between '03/01/2007'
and '03/31/2007' AND (( Alias_EDW_PERIO D_Price_WT.CPT_ PERIOD_DT ) >=
Alias_ODS_AEE_B OOK_DESIG_WT_DE AL.START_DATE AND
( Alias_EDW_PERIO D_Price_WT.CPT_ PERIOD_DT ) <=
Alias_ODS_AEE_B OOK_DESIG_WT_DE AL.STOP_DATE )
AND
ODS_WT_DEAL_ENG RY_PRFL_BLK.HOU R_ENDING_DT >= date('03/01/2007') -1
day and ODS_WT_DEAL_ENG RY_PRFL_BLK.HOU R_ENDING_DT <=
date('03/31/2007') + 1 day
and ( Alias_EDW_PERIO D_Price_WT.CPT_ PERIOD_DT ) BETWEEN
ODS_STLMT_CNTR_ PRTY_XREF.SOURC E_START_DT AND
ODS_STLMT_CNTR_ PRTY_XREF.SOURC E_END_DT )
AND ( ( ODS_STLMT_CNTR_ PRTY_XREF.CPRTY _CD ) NOT IN
('AEP','AEPGEN' ,'AEPLOAD','AEP TCC','AEPTNC',' PJM
OPRES','ERCOT', 'QFOXYCHEM','', ' ') AND
( ODS_STLMT_CNTR_ PRTY_XREF.CPRTY _CD ) NOT IN '%' )
AND 'WT' || ' - ' ||
( Alias_ODS_AEE_B OOK_DESIG_WT_DE AL.BOOK_DESIGNA TION_NAME ) IN ('WT -
PE', 'WT - PW')
AND ODS_WT_DEAL.DEA L_TYP_TX < 'Transmission'
AND ODS_WT_DEAL.STA TUS_CD < 'VOIDED'
)
UNION
SELECT
RTRIM(CHAR(RIGH T(ODS_WT_DEAL.B OOK_NM,4))),
CASE WHEN Alias_ODS_AEE_B OOK_DESIG_WT_DE AL.BOOK_DESIGNA TION_NAME =
'PW'
AND ( ODS_WT_SCHDL_DE AL.SRC_CD ) IN ('CSWS','PJM') THEN 'WT-On - PW'
WHEN Alias_ODS_AEE_B OOK_DESIG_WT_DE AL.BOOK_DESIGNA TION_NAME = 'PE'
AND ( ODS_WT_SCHDL_DE AL.SRC_CD ) IN ('CSWS','PJM') THEN 'WT-On - PE'
END,
ODS_WT_SCHDL_DE AL.PHYS_DEAL_NR ,
SUM(ODS_WT_SCHD L_LPRFL_BLK.BLK _MW*-1),
ODS_WT_SCHDL_LP RFL_BLK.CPT_PER IOD_DT,
ODS_WT_DEAL.TRA N_TYP_CD,
ODS_STLMT_CNTR_ PRTY_XREF.CPRTY _CD,
ODS_WT_DEAL.DEA L_ID,
CHAR(ODS_WT_DEA L.DEAL_NR),
ODS_WT_DEAL.TRD _DT,
rtrim(ltrim(ODS _WT_DEAL.MKT1_N M)),
ODS_WT_DEAL.DEA L_TYP_TX,
RTRIM(ODS_WT_DE AL.TERM_TX),
ODS_WT_SCHDL_LP RFL_BLK.CPT_PER IOD_TS
FROM
DB2INSTB.ODS_WT _DEAL ODS_WT_DEAL,
DB2INSTB.ODS_AE E_BOOK_DESIGNAT ION Alias_ODS_AEE_B OOK_DESIG_WT_DE AL,
DB2INSTB.ODS_WT _SCHDL_DEAL ODS_WT_SCHDL_DE AL,
DB2INSTB.ODS_WT _SCHDL_LPRFL_BL K_VW ODS_WT_SCHDL_LP RFL_BLK,
DB2INSTB.ODS_ST LMT_CNTR_PRTY_X REF ODS_STLMT_CNTR_ PRTY_XREF,
DB2INSTB.ODS_WT _SCHDL_TPRFL_RS RVN ODS_WT_SCHDL_TP RFL_RSRVN,
DB2INSTB.ODS_AE E_BOOK Alias_ODS_AEE_B OOK_WT_DEAL,
DB2INSTB.ODS_WT _SCHDL_DEAL_CPR TY ODS_WT_SCHDL_DE AL_CPRTY,
DB2INSTB.ODS_WT _SCHDL_DEAL_LPR FL ODS_WT_SCHDL_DE AL_LPRFL,
DB2INSTB.ODS_WT _SCHDL_DEAL_TPR FL ODS_WT_SCHDL_DE AL_TPRFL
WHERE
( ODS_WT_SCHDL_DE AL.PHYS_DEAL_NR =ODS_WT_SCHDL_D EAL_CPRTY.PHYS_ DEAL_NR )
AND ( ODS_WT_SCHDL_DE AL_CPRTY.DEAL_N R=ODS_WT_DEAL.D EAL_NR )
AND ( ODS_STLMT_CNTR_ PRTY_XREF.SOURC E_CPRTY_CD=ODS_ WT_DEAL.CPRTY_N M
AND ODS_STLMT_CNTR_ PRTY_XREF.SOURC E_SYS_CD = 'WT' )
AND
( ODS_WT_SCHDL_DE AL_LPRFL.PHYS_D EAL_NR=ODS_WT_S CHDL_DEAL.PHYS_ DEAL_NR )
AND
( ODS_WT_SCHDL_DE AL_LPRFL.PHYS_D EAL_NR=ODS_WT_S CHDL_LPRFL_BLK. PHYS_DEAL_NR
and
ODS_WT_SCHDL_DE AL_LPRFL.LPRFL_ ID=ODS_WT_SCHDL _LPRFL_BLK.LPRF L_ID )
AND
( ODS_WT_SCHDL_DE AL_LPRFL.PHYS_D EAL_NR=ODS_WT_S CHDL_DEAL_TPRFL .PHYS_DEAL_NR )
AND
( ODS_WT_SCHDL_DE AL_LPRFL.TRNMT_ PRVDR_NM=ODS_WT _SCHDL_DEAL_TPR FL.TRNMT_PRVDR_ NM )
AND
( ODS_WT_SCHDL_DE AL_TPRFL.PHYS_D EAL_NR=ODS_WT_S CHDL_TPRFL_RSRV N.PHYS_DEAL_NR )
AND
( ODS_WT_SCHDL_DE AL_TPRFL.SCHDL_ DEAL_PRFL_ID=OD S_WT_SCHDL_TPRF L_RSRVN.SCHDL_D EAL_PRFL_ID )
AND
( ODS_WT_SCHDL_DE AL_TPRFL.TRANS_ PRFL_ID=ODS_WT_ SCHDL_TPRFL_RSR VN.TRANS_PRFL_I D )
AND ( RTRIM(CHAR(RIGH T(ODS_WT_DEAL.B OOK_NM,
4)))=Alias_ODS_ AEE_BOOK_WT_DEA L.BOOK_NAME )
AND
( Alias_ODS_AEE_B OOK_WT_DEAL.BOO K_ID=Alias_ODS_ AEE_BOOK_DESIG_ WT_DEAL.BOOK_ID )
AND (
( ( ODS_STLMT_CNTR_ PRTY_XREF.CPRTY _CD ) NOT IN
('AEP','AEPGEN' ,'AEPLOAD','AEP TCC','AEPTNC',' PJM
OPRES','ERCOT', 'QFOXYCHEM','', ' ') AND
( ODS_STLMT_CNTR_ PRTY_XREF.CPRTY _CD ) NOT IN '%' )
AND ( ( ODS_WT_SCHDL_LP RFL_BLK.CPT_PER IOD_DT ) between '03/01/2007'
and '03/31/2007' AND (ODS_WT_SCHDL_L PRFL_BLK.CPT_PE RIOD_DT >=
Alias_ODS_AEE_B OOK_DESIG_WT_DE AL.START_DATE AND
ODS_WT_SCHDL_LP RFL_BLK.CPT_PER IOD_DT <=
Alias_ODS_AEE_B OOK_DESIG_WT_DE AL.STOP_DATE) and
ODS_WT_SCHDL_LP RFL_BLK.HOUR_EN DING_DT >= date('03/01/2007') -1 day
and ODS_WT_SCHDL_LP RFL_BLK.HOUR_EN DING_DT<= date('03/31/2007') + 1
day
and ( Alias_EDW_PERIO D_WT_LPrfl.CPT_ PERIOD_DT ) BETWEEN
ODS_STLMT_CNTR_ PRTY_XREF.SOURC E_START_DT AND
ODS_STLMT_CNTR_ PRTY_XREF.SOURC E_END_DT )
AND ODS_WT_SCHDL_DE AL.STAT_CD < 'VOIDED'
AND CASE WHEN
Alias_ODS_AEE_B OOK_DESIG_WT_DE AL.BOOK_DESIGNA TION_NAME = 'PW'
AND ( ODS_WT_SCHDL_DE AL.SRC_CD ) IN ('CSWS','PJM') THEN 'WT-On - PW'
WHEN Alias_ODS_AEE_B OOK_DESIG_WT_DE AL.BOOK_DESIGNA TION_NAME = 'PE'
AND ( ODS_WT_SCHDL_DE AL.SRC_CD ) IN ('CSWS','PJM') THEN 'WT-On - PE'
END IN ('WT-On - PE', 'WT-On - PW')
AND ODS_WT_DEAL.DEA L_TYP_TX < 'Transmission'
AND ODS_WT_DEAL.STA TUS_CD < 'VOIDED'
AND ODS_WT_SCHDL_TP RFL_RSRVN.OWNER _NM IN ('AEPMEA', 'CSWMEO')
)
GROUP BY
RTRIM(CHAR(RIGH T(ODS_WT_DEAL.B OOK_NM,4))),
CASE WHEN Alias_ODS_AEE_B OOK_DESIG_WT_DE AL.BOOK_DESIGNA TION_NAME =
'PW'
AND ( ODS_WT_SCHDL_DE AL.SRC_CD ) IN ('CSWS','PJM') THEN 'WT-On - PW'
WHEN Alias_ODS_AEE_B OOK_DESIG_WT_DE AL.BOOK_DESIGNA TION_NAME = 'PE'
AND ( ODS_WT_SCHDL_DE AL.SRC_CD ) IN ('CSWS','PJM') THEN 'WT-On - PE'
END,
ODS_WT_SCHDL_DE AL.PHYS_DEAL_NR ,
ODS_WT_SCHDL_LP RFL_BLK.CPT_PER IOD_DT,
ODS_WT_DEAL.TRA N_TYP_CD,
ODS_STLMT_CNTR_ PRTY_XREF.CPRTY _CD,
ODS_WT_DEAL.DEA L_ID,
CHAR(ODS_WT_DEA L.DEAL_NR),
ODS_WT_DEAL.TRD _DT,
rtrim(ltrim(ODS _WT_DEAL.MKT1_N M)),
ODS_WT_DEAL.DEA L_TYP_TX,
RTRIM(ODS_WT_DE AL.TERM_TX),
ODS_WT_SCHDL_LP RFL_BLK.CPT_PER IOD_TS
)
);
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.
This thread has been closed and replies have been disabled. Please start a new discussion. Similar topics | |
by: Joachim Klassen |
last post by:
Hi all,
first apologies if this question looks the same as another one I recently
posted - its a different thing but for the same szenario:-).
We are having performance problems when inserting/deleting rows from a large
table.
My scenario:
Table (lets call it FACT1) with 1000 million rows distributed on 12
|
by: sherkozmo |
last post by:
SQL2000 - AccessXP
I built an adp file with a stored procedure from SQL as follows:
SELECT * FROM
Z_mis_sjk_job_code_access
WHERE job_code=@JobCode
UNION ALL
SELECT * FROM
Z_mis_sjk_job_code_access_mkey
WHERE job_code=@JobCode
ORDER BY app_only, submenu_number, menu_routine_number,
|
by: Salad |
last post by:
Every now and then I see ads that state something like "Experience with
Large Databases ...multi-gig...blah-de-blah" And I have to laugh.
What's the difference between a large or small database? A table is a
table, a record is a record, a field is a field. All you are doing is
manipulating data in tables. I wouldn't think it'd make much...
|
by: Peter Hickman |
last post by:
I have a program that requires x strings all of y length. x will be in the range
of 100-10000 whereas the strings will all be < 200 each.
This does not need to be grown once it has been created.
Should I allocate x strings of y length or should I allocate a single string x *
y long? Which would be more efficient and / or portable?
Thank...
|
by: ranjeet.gupta |
last post by:
Dear ALL
As we know that when we declare the union then we have the size of the
union which is the size of the highest data type as in the below case
the
size should be 4 (For my case and compiler), and it is,
what I conclude from the below code
union data_type {
| | |
by: Ken Allen |
last post by:
I have some code from C/C++ that I am attempting to port to C#. I have come
across an interesting problem that is quite common in complex C/C++ code:
the us of UNION in structure definitions to permit the same piece of memory
to be referenced as different data types. This is often used to save space
and permit a single piece of memory to...
|
by: PaulR |
last post by:
Hi,
(re: DB2 LUW v8.1 fp8 optimisation Level2)
Can anyone explain why the following difference have wildly different
plans - this seems very fundamental)
select p.part_number,
sp.supplier_part_number
from jabs.supplier_part sp
inner join jabs.part p on p.oid = sp.part_oid
|
by: Yevgen Muntyan |
last post by:
Hey,
Why is it legal to do
union U {unsigned char u; int a;};
union U u;
u.a = 1;
u.u;
I tried to find it in the standard, but I only found that
|
by: wugon.net |
last post by:
question: db2 LUW V8 UNION ALL with table function month() have bad
query performance
Env:
db2 LUW V8 + FP14
Problem :
We have history data from 2005/01/01 ~ 2007/05/xx in single big
table,
we try separate this big table into twelve tables and create a view
|
by: marktang |
last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main...
|
by: Hystou |
last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it.
First, let's disable language...
| | |
by: Oralloy |
last post by:
Hello folks,
I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>".
The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed.
This is as boiled down as I can make it. ...
|
by: jinu1996 |
last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that...
|
by: Hystou |
last post by:
Overview:
Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For...
|
by: agi2029 |
last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then...
|
by: conductexam |
last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one.
At the time of converting from word file to html my equations which are in the word document file was convert...
|
by: adsilva |
last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
| | |
by: muto222 |
last post by:
How can i add a mobile payment intergratation into php mysql website.
| |
