Cenário: Melhorar estimativas de cardinalidade usando visualizações estatísticas

Considere a seguinte consulta que computa a receita anual de vendas para tacos de golfe vendidos durante julho de cada ano:

   select  sum(f.sales_price), d2.year
     from  product d1, period d2, daily_sales f
     where  d1.prodkey = f.prodkey
       and  d2.perkey = f.perkey
       and  d1.item_desc = 'golf club'
       and  d2.month = 'JUL'
     group by  d2.year

Considere um banco de dados a partir de um armazém de dados típico, onde STORE, CLIENTE, PRODUTO, PROMOÇÃO e PERÍODO são as tabelas de dimensões, e DAILY_SALES é a tabela de fatos. As tabelas a seguir fornecem as definições para estas tabelas.

A tabela indica que os descontos para clientes devolvidos representam apenas 2.86% dos 35 tipos de promoções que foram oferecidos.

A consulta a seguir retorna uma contagem de 12889514:

   select  count(*)
     from  store d1, promotion d2, daily_sales f
     where  d1.storekey = f.storekey
       and  d2.promokey = f.promokey
       and  d1.store_number = '01'
       and  d2.promotype = 1

Esta consulta executa de acordo com o seguinte plano que é gerado pelo otimizador. Em cada nó desse diagrama, a primeira linha é a estimativa de cardinalidade, a segunda linha é o tipo de operador, e a terceira linha (o número entre parênteses) é o ID do operador.

                                          6.15567e+06
                                             IXAND
                                             (   8)
                             /------------------+------------------\
                      2.15448e+07                                2.15448e+08
                        NLJOIN                                     NLJOIN
                        (   9)                                     (  13)
                 /---------+--------\                       /---------+--------\
               1                  2.15448e+07            18                1.19694e+07
            FETCH                   IXSCAN             FETCH                   IXSCAN
            (  10)                  (  12)             (  14)                  (  16)
           /---+---\                  |               /---+---\                  |
         35         35           7.54069e+08        18         63          7.54069e+08
      IXSCAN  TABLE: DB2DBA  INDEX: DB2DBA       IXSCAN   TABLE: DB2DBA       INDEX: DB2DBA
      (  11)       PROMOTION     PROMO_FK_IDX     (  15)         STORE             STORE_FK_IDX
        |                                          |
        35                                         63
  INDEX: DB2DBA                                INDEX: DB2DBA
 PROMOTION_PK_IDX                              STOREX1

Na junção de loop aninhado (número 9), o otimizador estima que cerca de 2.86% do produto vendido resultou de clientes retornarem para comprar os mesmos produtos a um preço descontado (2.15448e+07 ÷ 7.54069e+08 ≈ 0.0286). Note que este é o mesmo valor antes e depois de ingressá-lo na tabela PROMOÇÃO com a tabela DAILY_SALES. Tabela 8 resume as estimativas de cardinalidade e sua porcentagem (o efeito filtrante) antes e depois da junção.

O que faz com que o otimizador só estique metade do número real de registros que satisfazem os dois predicados? A loja '01' representa cerca de 28.57% de todas as lojas E se outras lojas tivessem mais vendas do que a loja '01' (menos de 28.57%)? Ou se a loja '01' realmente vendeu a maior parte do produto (mais de 28.57%)? Da mesma forma, 2.86% dos produtos vendidos usando o tipo de promoção 1 mostrado na Tabela 8 podem ser enganosos. A porcentagem real em DAILY_SALES poderia muito bem ser uma figura diferente da projetada.

Você pode usar visualizações estatísticas para ajudar o otimizador a corrigir suas estimativas. Primeiro, você precisa criar duas visualizações estatísticas representando cada semi-join na consulta anterior. A primeira visão estatística fornece a distribuição de lojas para todas as vendas diárias. A segunda visão estatística representa a distribuição de tipos de promoção para todas as vendas diárias. Observe que cada visualização estatística pode fornecer as informações de distribuição para qualquer número de loja ou tipo de promoção em particular. Neste exemplo, você usa uma taxa de amostra de 10% para recuperar os registros em DAILY_SALES para as respectivas visualizações e salvá-los em tabelas temporárias globais. Em seguida, é possível consultar essas tabelas para coletar as estatísticas necessárias para atualizar as duas visões estatísticas.

1. Crie uma view representando a junção de STORE com DAILY_SALES.

      create view sv_store_dailysales as
        (select s.*
          from store s, daily_sales ds
          where s.storekey = ds.storekey)

2. Crie uma view representando a junção da PROMOÇÃO com DAILY_SALES.

      create view sv_promotion_dailysales as
        (select p.*
          from promotion.p, daily_sales ds
          where p.promokey = ds.promokey)

3. Faça as visualizações estatísticas de visualizações, ativando-as para otimização de consulta:

      alter view sv_store_dailysales enable query optimization
      alter view sv_promotion_dailysales enable query optimization

4. Execute o comando RUNSTATS para coletar estatísticas sobre as visualizações:

      runstats on table db2dba.sv_store_dailysales with distribution
      runstats on table db2dba.sv_promotion_dailysales with distribution

5. Execute a consulta novamente para que ele possa ser re-otimizado. Mediante a reotimização, o otimizador corresponderá SV_STORE_DAILYSALES e SV_PROMOTION_DAILYSALES com a consulta, e utilizará as estatísticas da visualização para ajustar a estimativa de cardinalidade das semi-joias entre as tabelas de fato e dimensão, causando uma inversão da ordem original das semi-joias escolhidas sem essas estatísticas. O novo plano é o seguinte:

                                             1.04627e+07
                                               IXAND
                                               (   8)
                               /------------------+------------------\
                        6.99152e+07                                1.12845e+08
                          NLJOIN                                     NLJOIN
                          (   9)                                     (  13)
                   /---------+--------\                       /---------+--------\
                18                 3.88418e+06             1                  1.12845e+08
              FETCH                   IXSCAN             FETCH                   IXSCAN
              (  10)                  (  12)             (  14)                  (  16)
             /---+---\                  |               /---+---\                  |
          18           63           7.54069e+08      35           35          7.54069e+08
        IXSCAN   TABLE:DB2DBA     INDEX: DB2DBA   IXSCAN    TABLE: DB2DBA    INDEX: DB2DBA DB2DBA
       (  11)      STORE            STORE_FK_IDX    (  15)       PROMOTION        PROMO_FK_IDX
          |                                          |
          63                                         35
      INDEX: DB2DBA                              INDEX: DB2DBA
      STOREX1                                    PROMOTION_PK_IDX

A tabela 9 resume as estimativas de cardinalidade e sua porcentagem (o efeito de filtragem) antes e depois da junção para cada semi-join.

Observe que, desta vez, a semi-junção entre STORE e DAILY_SALES é realizada na perna externa do plano ANDing do índice. Isso porque as duas visões estatísticas dizem essencialmente ao otimizador que o predicado store_number = '01' filtrará mais linhas do que promotype = 1. Desta vez, o otimizador estima que existam aproximadamente 10462700 produtos vendidos. Esta estimativa é desativada por um fator de 1.23 (12 889 514 ÷ 10 462 700 ≈ 1.23), que é uma melhoria significativa sobre a estimativa sem visualizações estatísticas (na Tabela 8).