EXPLAIN e Planos de Acesso — Como o DB2 Executa suas Queries

CREATE TABLE USERID.PLAN_TABLE (
    QUERYNO          INTEGER       NOT NULL,
    QBLOCKNO         SMALLINT      NOT NULL,
    APPLNAME         CHAR(8)       NOT NULL,
    PROGNAME         CHAR(8)       NOT NULL,
    PLANNO           SMALLINT      NOT NULL,
    METHOD           SMALLINT      NOT NULL,
    CREATOR          CHAR(8)       NOT NULL,
    TNAME            CHAR(18)      NOT NULL,
    TABNO            SMALLINT      NOT NULL,
    ACCESSTYPE       CHAR(2)       NOT NULL,
    MATCHCOLS        SMALLINT      NOT NULL,
    ACCESSCREATOR    CHAR(8)       NOT NULL,
    ACCESSNAME       CHAR(18)      NOT NULL,
    INDEXONLY        CHAR(1)       NOT NULL,
    SORTN_UNIQ       CHAR(1)       NOT NULL,
    SORTN_JOIN       CHAR(1)       NOT NULL,
    SORTN_ORDERBY    CHAR(1)       NOT NULL,
    SORTN_GROUPBY    CHAR(1)       NOT NULL,
    SORTC_UNIQ       CHAR(1)       NOT NULL,
    SORTC_JOIN       CHAR(1)       NOT NULL,
    SORTC_ORDERBY    CHAR(1)       NOT NULL,
    SORTC_GROUPBY    CHAR(1)       NOT NULL,
    TSLOCKMODE       CHAR(3)       NOT NULL,
    TIMESTAMP        CHAR(16)      NOT NULL,
    REMARKS          VARCHAR(254)  NOT NULL,
    PREFETCH         CHAR(1)       NOT NULL,
    COLUMN_FN_EVAL   CHAR(1)       NOT NULL,
    MIXOPSEQ         SMALLINT      NOT NULL,
    VERSION          VARCHAR(64)   NOT NULL,
    COLLID           CHAR(18)      NOT NULL,
    ACCESS_DEGREE    SMALLINT,
    JOIN_DEGREE      SMALLINT,
    SORTC_PGROUP_ID  SMALLINT,
    SORTN_PGROUP_ID  SMALLINT,
    PARALLELISM_MODE CHAR(1),
    MERGE_JOIN_COLS  SMALLINT,
    CORRELATION_NAME CHAR(18),
    PAGE_RANGE       CHAR(1)       NOT NULL,
    JOIN_TYPE        CHAR(1)       NOT NULL,
    GROUP_MEMBER     CHAR(8)       NOT NULL,
    IBM_SERVICE_DATA VARCHAR(254)
);

-- QUERYNO identifica o resultado na PLAN_TABLE
EXPLAIN PLAN SET QUERYNO = 1 FOR
SELECT C.NOME, SUM(T.VL_TRANSACAO) AS TOTAL
  FROM CLIENTES C
  JOIN TRANSACOES T ON C.COD_CLIENTE = T.COD_CLIENTE
 WHERE T.DT_TRANSACAO BETWEEN '2026-06-01' AND '2026-06-30'
 GROUP BY C.NOME
 ORDER BY TOTAL DESC
FETCH FIRST 10 ROWS ONLY;

SELECT QUERYNO
     , QBLOCKNO
     , PLANNO
     , TNAME
     , ACCESSTYPE
     , MATCHCOLS
     , ACCESSNAME
     , INDEXONLY
     , PREFETCH
     , SORTN_JOIN
     , SORTC_JOIN
     , SORTN_ORDERBY
     , SORTC_ORDERBY
  FROM USERID.PLAN_TABLE
 WHERE QUERYNO = 1
 ORDER BY PLANNO;

QUERYNO PLANNO  TNAME        ACCESSTYPE  MATCHCOLS  ACCESSNAME    INDEXONLY  PREFETCH  SORTC_JOIN  SORTC_ORDERBY
------- ------  -----------  ----------  ---------  ------------  ---------  --------  ----------  -------------
      1      1  TRANSACOES   I                   1  XTRANS01      N          S         N           N
      1      2  CLIENTES     I                   1  XCLIEN01      N          L         Y           N
      1      3  (sort)       -                   -  -             -          -         -           Y

-- Índice em (COD_AGENCIA, DT_TRANSACAO, VL_TRANSACAO)
-- Query usa apenas essas três colunas → sem acesso à tabela
SELECT COD_AGENCIA
     , COUNT(*)
     , SUM(VL_TRANSACAO)
  FROM TRANSACOES
 WHERE COD_AGENCIA = '001'
   AND DT_TRANSACAO BETWEEN '2026-06-01' AND '2026-06-30'
 GROUP BY COD_AGENCIA;

-- Clustering index em DT_TRANSACAO ASC
-- Esta query elimina o sort de ORDER BY — dados já estão na ordem certa
SELECT COD_CONTA, VL_TRANSACAO
  FROM TRANSACOES
 WHERE DT_TRANSACAO = '2026-06-15'
 ORDER BY DT_TRANSACAO;   -- alinhado ao clustering → SORTC_ORDERBY = N

-- Esta query FORÇA sort — ORDER BY em coluna diferente do clustering
SELECT COD_CONTA, VL_TRANSACAO
  FROM TRANSACOES
 WHERE DT_TRANSACAO = '2026-06-15'
 ORDER BY VL_TRANSACAO;   -- diferente → SORTC_ORDERBY = Y

-- 1. Executar EXPLAIN da query problemática
EXPLAIN PLAN SET QUERYNO = 99 FOR
SELECT ... /* query suspeita */;

-- 2. Consultar resultado
SELECT PLANNO, TNAME, ACCESSTYPE, MATCHCOLS,
       ACCESSNAME, INDEXONLY, PREFETCH,
       SORTC_JOIN, SORTC_ORDERBY
  FROM USERID.PLAN_TABLE
 WHERE QUERYNO = 99
 ORDER BY PLANNO;

-- 3. Verificar quando as estatísticas foram coletadas
SELECT CREATOR, NAME, CARDF, NPAGES, PCTROWCOMP,
       STATSTIME
  FROM SYSIBM.SYSTABLESTATS
 WHERE CREATOR = 'SCHEMA1'
   AND NAME = 'TRANSACOES';

-- 4. Verificar cluster ratio dos índices
SELECT CREATOR, NAME, TBNAME, CLUSTERING,
       CLUSTERRATIO, FULLKEYCARDF, FIRSTKEYCARDF
  FROM SYSIBM.SYSINDEXES
 WHERE TBCREATOR = 'SCHEMA1'
   AND TBNAME    = 'TRANSACOES';

-- 5. Executar RUNSTATS se necessário, depois REBIND
-- 6. Rodar EXPLAIN novamente e comparar