Used in 10.5:
******************** EXPLAIN INSTANCE ********************
DB2_VERSION: 10.05.A
FORMATTED ON DB: SAMPLE
SOURCE_NAME: SQLC2K26
SOURCE_SCHEMA: NULLID
SOURCE_VERSION:
EXPLAIN_TIME: 2020-04-21-15.51.15.269964
EXPLAIN_REQUESTER: DB2INST1
Original Statement:
------------------
SELECT
T1.ID,
T1.DATA,
T1.ID
FROM
db2inst1.TABLE1 T1
WHERE
(T1.ID = 'test')
FOR FETCH ONLY
OPTIMIZE FOR 1 ROW
Optimized Statement:
-------------------
SELECT
$INTERNAL_XMLTOSQL$(Q1.$C0) AS "ID",
$INTERNAL_XMLTOSQL$(Q1.$C1) AS "DATA",
$INTERNAL_XMLTOSQL$(Q1.$C0) AS "ID"
FROM
$INTERNAL_FOR$ ((TABLE ($INTERNAL_XPATH$
('($INTERNAL_XMLTOXML_NIEO$(Q2.XMLDATA))/estimate/{ID/./(.[((.
>= $INTERNAL_SQLTOXML$(RTRIM(:L0))) and (. <
$INTERNAL_FUNC$()))])(:-->$C0:),EventInfo/AssignmentEvent/(Inspe
ctionDateTime)(:-->$C1:)}'))) AS Q1),
db2inst1.TABLE2 AS Q2
WHERE
($INTERNAL_XMLTOSQL$(Q1.$C0) = :L0)
Access Plan:
-----------
Total Cost: 7.7278e+06
Query Degree: 1
Rows
RETURN
( 1)
Cost
I/O
|
1.11147e-05
NLJOIN
( 2)
7.7278e+06
1.13528e+06
/--+--
167249 6.64561e-11
FETCH XSCAN
( 3) ( 7)
132621 45.4123
131788 6
/---+----
167249 1.19344e+06
RIDSCN TABLE: db2inst1
( 4) TABLE2
1050.71 Q2
123
|
167249
SORT
( 5)
1050.71
123
|
167249
XISCAN
( 6)
941.878
123
|
1.19344e+06
XMLIN: db2inst1
TABLE2_D4
Q2
But not in 11.5:
******************** EXPLAIN INSTANCE ********************
DB2_VERSION: 11.05.0
FORMATTED ON DB: SAMPLE
SOURCE_NAME: SQLC2O29
SOURCE_SCHEMA: NULLID
SOURCE_VERSION:
EXPLAIN_TIME: 2020-04-21-15.45.24.238406
EXPLAIN_REQUESTER: DB2INST1
Original Statement:
------------------
SELECT
T1.ID,
T1.DATA,
T1.ID
FROM
db2inst1.TABLE1 T1
WHERE
(T1.ID = 'test')
FOR FETCH ONLY
OPTIMIZE FOR 1 ROW
Optimized Statement:
-------------------
SELECT
$INTERNAL_XMLTOSQL$(Q1.$C1) AS "ID",
$INTERNAL_XMLTOSQL$(Q1.$C0) AS "DATA",
$INTERNAL_XMLTOSQL$(Q1.$C1) AS "ID"
FROM
$INTERNAL_FOR$ ((TABLE ($INTERNAL_XPATH$
('($INTERNAL_XMLTOXML_NIEO$(Q2.XMLDATA))/estimate/{EventInfo/Ass
ignmentEvent/(InspectionDateTime)(:-->$C0:),(ID)(:-->$C1:)}')))
AS Q1),
db2inst1.TABLE2 AS Q2
WHERE
($INTERNAL_XMLTOSQL$(Q1.$C1) = :L0)
Access Plan:
-----------
Total Cost: 3.24676e+07
Query Degree: 1
Rows
RETURN
( 1)
Cost
I/O
|
15204.1
NLJOIN
( 2)
3.24676e+07
4.6992e+06
/-+--
844914 0.0179949
TBSCAN XSCAN
( 3) ( 4)
474844 37.8651
474628 5
|
844914
TABLE: db2inst1
TABLE2
Q2 |
