zOs/WK/MF160ALX
--- temporary explain --------------------------------------------------
set current sqlid = 'A540769';
delete from A540769.plan_table;
delete from A540769.DSN_STATEMNT_TABLE;
delete from A540769.DSN_DetCost_TABLE ;
delete from A540769.dsn_filter_Table ;
delete from A540769.dsn_predicat_table;
explain plan set queryno = 3 for
SELECT 00023300
HEX(MF150001), MF150022, MF150023, MF150064, SUBSTR(MF150066,9,7), 00023400
MF150004, MF150059, MF150013, CHAR(MF150083), CHAR(MF150084), 00023500
CHAR(MF150085), MF150011, MF150030, MF150031, MF150028, MF150029, 00023600
MF150079, MF150017, MF150044, MF150009, ' ', MF150042, CHAR(MF150043), 00023700
MF150016, CHAR(MF150018), CHAR(MF150019), MF150055, CHAR(MF150053), 00023800
MF150035, MF150036, MF150038, MF150041, MF150040, MF150014, MF150015, 00023900
' ', MF150001, MF150003, MF150008, MF150084, MF150085 00024000
FROM OA1P.TMF150A1 00024100
WHERE MF150013 BETWEEN ? AND ? 00024200
AND MF150022 BETWEEN ? AND ? 00024210
AND MF150023 BETWEEN ? AND ? 00024220
AND MF150085 BETWEEN 0 AND 90 00024230
AND NOT (MF150029 = '130' 00024300
OR MF150028 IN ('680', '681', '682', '700', '701', 00024400
'702', '710', '711', '712', '720', '721', 00024500
'722', '730', '731', '732', '938', '939')) 00024600
AND MF150019 <> 0 00024900
AND MF150001 NOT IN 00025000
(SELECT UUID FROM OA1P.TMF401A1) 00025100
UNION ALL 00025200
SELECT 00025300
HEX(MF150001), MF150022, MF150023, MF150064, SUBSTR(MF150066,9,7), 00025400
MF150004, MF150059, MF150013, CHAR(MF150083), CHAR(MF150084), 00025500
CHAR(MF150085), MF150011, MF150030, MF150031, MF150028, MF150029, 00025600
MF150079, MF150017, MF150044, MF150009, ' ', MF150042, CHAR(MF150043), 00025700
MF150016, CHAR(MF150018), CHAR(MF150019), MF150055, CHAR(MF150053), 00025800
MF150035, MF150036, MF150038, MF150041, MF150040, MF150014, MF150015, 00025900
' ', MF150001, MF150003, MF150008, MF150084, MF150085 00026000
FROM OA1P.TMF150A1 00026100
WHERE 00026200
NOT (MF150029 = '130' 00026300
OR MF150028 IN ('680', '681', '682', '700', '701', 00026400
'702', '710', '711', '712', '720', '721', 00026500
'722', '730', '731', '732', '938', '939')) 00026600
AND MF150022 BETWEEN ? AND ? 00026810
AND MF150023 BETWEEN ? AND ? 00026820
AND MF150085 BETWEEN 0 AND 90 00026830
AND MF150001 IN 00026900
(SELECT UUID FROM OA1P.TMF401A1 00027000
WHERE BEWERTUNGSDATUM BETWEEN ? AND ? ) 00027100
WITH UR 00027200
;
explain plan set queryno = 7 for
SELECT
HEX(MF150001), MF150022, MF150023, MF150064, SUBSTR(MF150066,9,7),
MF150004, MF150059, MF150013, CHAR(MF150083), CHAR(MF150084),
CHAR(MF150085), MF150011, MF150030, MF150031, MF150028, MF150029,
MF150079, MF150017, MF150044, MF150009, ' ', MF150042, CHAR(MF150043),
MF150016, CHAR(MF150018), CHAR(MF150019), MF150055, CHAR(MF150053),
MF150035, MF150036, MF150038, MF150041, MF150040, MF150014, MF150015,
' ', MF150001, MF150003, MF150008, MF150084, MF150085
FROM OA1P.TMF150A1
WHERE MF150013 BETWEEN ? AND ?
AND MF150022 BETWEEN ? AND ?
AND MF150023 BETWEEN ? AND ?
AND MF150085 BETWEEN 0 AND 90
AND MF150029 <> '130'
and MF150028 not IN ('680', '681', '682', '700', '701',
'702', '710', '711', '712', '720', '721',
'722', '730', '731', '732', '938', '939')
AND (( MF150019 <> 0
AND not exists (select 1 FROM OA1P.TMF401A1
where mf150001 = UUID)
) or exists (SELECT 1 FROM OA1P.TMF401A1
where mf150001 = UUID
and BEWERTUNGSDATUM BETWEEN ? AND ? )
)
WITH UR
;
select * from plan_view1
order by -- collid, progName, version, bind_time,
queryNo, qblockno, planno, mixOpSeq
;
select * from plan_view2
order by -- collid, progName, version, bind_time,
queryNo, qblockno, planno, mixOpSeq
;
select * from plan_view2det
order by -- collid, progName, version, bind_time,
queryNo, qblockno, planno, mixOpSeq
;
select *
from plan_viewPred
order by --collid, progName, applName, explain_time,
queryNo, qBlockNo, planno,
stage, predNo
with ur
;
rollback
;;;;