Once more into the breach, dear friends! Yes, we are doing a very quick EXPLAIN update again … This is one topic that never ever ever gets old or cold! This month it was triggered by a DBA colleague who emailed me for help as he tried to use the CTE Opthints that I wrote about way way back in 2021 and hit trouble …
The Story begins …
Nearly all good stories start with a misdirected email … Then it was finally forwarded to me, and I saw the SQL attempt that he was making. Now it is a bit odd that he *wants* multiple index access because that is like Hybrid Join … That’s not really “up there” with desired access paths!!! However, he made the case very well in this one very, very special situation, and with host variables, that the data skew was so bad that multiple index access worked best.
How did it look?
Here’s the DDL and SQL, redacted to protect one and all so you can try this one at home!
Table:
CREATE TABLE CTETEST.CTETABTEST
(
COL1 TIMESTAMP NOT NULL
,COL2 TIMESTAMP NOT NULL
,COL3 TIMESTAMP NOT NULL WITH DEFAULT
'9999-12-31-24.00.00.000000'
,COL4 TIMESTAMP NOT NULL
,COL5 CHAR(24) NOT NULL WITH DEFAULT
,COL6 TIMESTAMP NOT NULL WITH DEFAULT
'0001-01-01-00.00.00.000000'
,COL7 TIMESTAMP NOT NULL WITH DEFAULT
'0001-01-01-00.00.00.000000'
,COL8 TIMESTAMP NOT NULL WITH DEFAULT
'0001-01-01-00.00.00.000000'
,COL9 TIMESTAMP NOT NULL WITH DEFAULT
'0001-01-01-00.00.00.000000'
)
CCSID EBCDIC
;
All Indexes:
CREATE UNIQUE INDEX CTETEST.INDEX000
ON CTETEST.CTETABTEST
(
COL1 ASC
,COL2 ASC
,COL3 ASC
)
USING STOGROUP SYSDEFLT
PRIQTY -1
SECQTY -1
CLUSTER
BUFFERPOOL BP0
;
CREATE INDEX CTETEST.INDEX001
ON CTETEST.CTETABTEST
(
COL9 ASC
)
USING STOGROUP SYSDEFLT
PRIQTY -1
SECQTY -1
NOT CLUSTER
BUFFERPOOL BP0
;
CREATE INDEX CTETEST.INDEX002
ON CTETEST.CTETABTEST
(
COL1 ASC
,COL4 ASC
,COL3 ASC
)
USING STOGROUP SYSDEFLT
PRIQTY -1
SECQTY -1
NOT CLUSTER
BUFFERPOOL BP0
;
CREATE INDEX CTETEST.INDEX003
ON CTETEST.CTETABTEST
(
COL2 ASC
)
USING STOGROUP SYSDEFLT
PRIQTY -1
SECQTY -1
NOT CLUSTER
BUFFERPOOL BP0
;
CREATE INDEX CTETEST.INDEX004
ON CTETEST.CTETABTEST
(
COL5 ASC
,COL3 ASC
)
USING STOGROUP SYSDEFLT
PRIQTY -1
SECQTY -1
NOT CLUSTER
BUFFERPOOL BP0
;
CREATE INDEX CTETEST.INDEX005
ON CTETEST.CTETABTEST
(
COL4 ASC
)
USING STOGROUP SYSDEFLT
PRIQTY -1
SECQTY -1
NOT CLUSTER
BUFFERPOOL BP0
;
CREATE INDEX CTETEST.INDEX006
ON CTETEST.CTETABTEST
(
COL6 ASC
)
USING STOGROUP SYSDEFLT
PRIQTY -1
SECQTY -1
NOT CLUSTER
BUFFERPOOL BP0
;
CREATE INDEX CTETEST.INDEX007
ON CTETEST.CTETABTEST
(
COL7 ASC
)
USING STOGROUP SYSDEFLT
PRIQTY -1
SECQTY -1
NOT CLUSTER
BUFFERPOOL BP0
;
CREATE INDEX CTETEST.INDEX008
ON CTETEST.CTETABTEST
(
COL8 ASC
)
USING STOGROUP SYSDEFLT
PRIQTY -1
SECQTY -1
NOT CLUSTER
BUFFERPOOL BP0
;
COMMIT ;
Feel free to only use the last four for any tests you want to run!
The Code?
The SQL looked like:
SELECT COL1
, COL2
, COL3
, COL4
, COL6
, COL7
, COL8
FROM CTETEST.CTETABTEST
WHERE COL3 + 0 DAYS > CURRENT TIMESTAMP
AND ( COL4 = ?
OR COL6 = ?
OR COL7 = ?
OR COL8 = ? )
; And the Aim of the Game was?
He wanted a multiple index access path on all four of the OR predicates. If you EXPLAIN the above query “asis” without data or RUNSTATS:
SELECT ACCESSTYPE
FROM PLAN_TABLE
WHERE QUERYNO = 1
;
---------+---------+---------+--------
ACCESSTYPE
---------+---------+---------+--------
R
DSNE610I NUMBER OF ROWS DISPLAYED IS 1
Surprise, surprise! You get a Tablespace scan (R for Relational scan in the Jargon).
First up!
First attempted SQL code looked like:
-- FIRST ATTEMPT AT GETTING MULTI-INDEX ACCESS
EXPLAIN ALL SET QUERYNO = 2 FOR
WITH DSN_INLINE_OPT_HINT
(QBLOCKNO,TABLE_NAME, ACCESS_TYPE, ACCESS_NAME) AS
( VALUES (1, 'CTETABTEST' , 'MULTI_INDEX' ),
(2, 'CTETABTEST' , 'INDEX', 'INDEX005' ),
(3, 'CTETABTEST' , 'INDEX', 'INDEX006' ),
(4, 'CTETABTEST' , 'INDEX', 'INDEX007' ),
(5, 'CTETABTEST' , 'INDEX', 'INDEX008' ))
SELECT COL1
, COL2
, COL3
, COL4
, COL6
, COL7
, COL8
FROM CTETEST.CTETABTEST
WHERE COL3 + 0 DAYS > CURRENT TIMESTAMP
AND ( COL4 = ?
OR COL6 = ?
OR COL7 = ?
OR COL8 = ? )
;
---------+---------+---------+---------+---------+-------
DSNE616I STATEMENT EXECUTION WAS SUCCESSFUL, SQLCODE IS 0
And???
Things to note: SQLCODE IS 0 should never be output for an inline CTE Opthint. If you read my old Newsletter, you will see you should only get a +394 or +395. In this case it simply ignored the CTE completely!
The result of the above EXPLAIN:
SELECT MIXOPSEQ, ACCESSTYPE, ACCESSNAME
FROM PLAN_TABLE
WHERE QUERYNO = 2
;
---------+---------+---------+---------
MIXOPSEQ ACCESSTYPE ACCESSNAME
---------+---------+---------+---------
0 R
DSNE610I NUMBER OF ROWS DISPLAYED IS 1
No change here!
Next up!
My try:
-- SECOND ATTEMPT AT GETTING MULTI-INDEX ACCESS
EXPLAIN ALL SET QUERYNO = 3 FOR
WITH DSN_INLINE_OPT_HINT
( ACCESS_TYPE, ACCESS_NAME) AS
( VALUES ( 'MULTI_INDEX', 'INDEX005' ),
( 'MULTI_INDEX', 'INDEX006' ),
( 'MULTI_INDEX', 'INDEX007' ),
( 'MULTI_INDEX', 'INDEX008' ))
SELECT COL1
, COL2
, COL3
, COL4
, COL6
, COL7
, COL8
FROM CTETEST.CTETABTEST
WHERE COL3 + 0 DAYS > CURRENT TIMESTAMP
AND ( COL4 = ?
OR COL6 = ?
OR COL7 = ?
OR COL8 = ? )
;
---------+---------+---------+---------+---------+---------+-+------
DSNT404I SQLCODE = 394, WARNING: USER SPECIFIED OPTIMIZATION HINTS USED
DURING ACCESS PATH SELECTION
DSNT418I SQLSTATE = 01629 SQLSTATE RETURN CODE
DSNT415I SQLERRP = DSNXOPCO SQL PROCEDURE DETECTING ERROR
DSNT416I SQLERRD = 20 0 502 1115050228 0 0 SQL DIAGNOSTIC INFORMATION
DSNT416I SQLERRD = X'00000014' X'00000000' X'000001F6' X'427650F4'
X'00000000' X'00000000' SQL DIAGNOSTIC INFORMATION
DSNE616I STATEMENT EXECUTION WAS SUCCESSFUL, SQLCODE IS 0
The result of this EXPLAIN:
SELECT MIXOPSEQ, ACCESSTYPE, ACCESSNAME
FROM PLAN_TABLE
WHERE QUERYNO = 3
;
---------+---------+---------+---------
MIXOPSEQ ACCESSTYPE ACCESSNAME
---------+---------+---------+---------
0 M
1 MX INDEX005
2 MX INDEX006
3 MU
4 MX INDEX007
5 MU
6 MX INDEX008
7 MU
DSNE610I NUMBER OF ROWS DISPLAYED IS 8
BINGO!
As you can see, I reduced the input to the CTE to the absolute minimum just telling the Db2 for z/OS Optimizer the four index names I wanted it to use in a MULTI_INDEX access type and Voila! It worked!
Really?
I emailed my test results to the DBA and he confirmed that my changes work in production and he is now a very happy bunny!
First Time for Everything!
This was the first time I have tried to force multiple index access for a query but you never know if this might be of use to some other DBA fighting the fight and struggling with dodgy access path decisions!
TTFN
Roy Boxwell