Quando devemos criar um índice composto?

Índice composto

É comum encontrar aplicações cujas tabelas possuem muitos índices unitários (uma coluna) e nenhum índice composto (duas ou mais colunas), as ultimas versões do banco de dados Oracle já trata essa deficiência de estrutura das aplicações com o uso da operação “BITMAP AND” no plano de execução, mas em alguns casos a utilização do índice composto continua sendo a melhor solução para uma instrução SQL que esteja causando impacto de performance para os usuários da aplicação.

Considere a criação de um índice composto em colunas que são freqüentemente usadas juntas na cláusula “WHERE” combinadas com o operador “AND”, principalmente se a seletividade das colunas combinadas é melhor do que a seletividade de qualquer uma delas individualmente.

Seletividade de um índice

A seletividade de um índice pode variar de 0 a 1, sendo 1 ou 100% a seletividade ideal, normalmente índices “Primary Key” ou “Unique Key” possuem seletividade 1. Para calcular a seletividade de um índice de uma coluna utilizamos a seguinte formula:

seletividade = Número de valores diferentes da coluna / Numero de registros da tabela

Por exemplo, uma tabela com 10.000 registros e um índice unitário dessa tabela que tem 8.700 valores diferentes, a seletividade será 0,87 ou 87% (seletividade=8700/10000).

Podemos calcular a seletividade de um índice de forma rápida executando uma consulta na visão “DBA_INDEXES”:

 

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
appuser@LAB11 > set tab off
appuser@LAB11 > col "Percentual" for a10
appuser@LAB11 > select index_name "Nome do índice",
  2         num_rows "Total linhas",
  3         distinct_keys "Qtde linhas diferentes",
  4         round(distinct_keys/num_rows,2) "Seletividade",
  5         LPAD(round(distinct_keys/num_rows,2)*100||' %',10,' ') "Percentual"
  6  from dba_indexes
  7  where table_name = 'TAB01'
  8  order by index_name;

Nome do índice                 Total linhas Qtde linhas diferentes Seletividade Percentual
------------------------------ ------------ ---------------------- ------------ ----------
IDX_CATEGORIA                        100000                   1000          .01        1 %
IDX_CODVAR                           100000                    100            0        0 %
IDX_COMPOSTO                         100000                  63098          .63       63 %

Elapsed: 00:00:00.05
appuser@LAB11 >

 

Demonstração do índice composto

 

Vamos fazer uma simulação para verificar se há alguma vantagem em utilizar um índice composto, em nossa simulação vamos utilizar uma consulta em uma tabela com dois filtros na cláusula WHERE, inicialmente vamos executar a consulta com dois índices criados para cada uma das colunas referenciadas na cláusula WHERE. Na sequência vamos criar um índice composto com essas duas colunas e executar a consulta novamente para avaliar o resultado.

Caso tenha alguma dúvida na leitura do plano de execução gerado pelo pacote DBMS_XPLAN consulte a série de artigos que vai ajuda-lo a interpretar essas informações:

1. Como verificar a ordem que as operações são realizadas
2. Como interpretar os valores estatísticos estimados
3. Como interpretar os valores estatísticos coletados durante a execução
4. Como interpretar as seções não estatísticas do plano de execução

 

Criando a Tabela e seus Índices

Para esta simulação vamos criar uma tabela e inserir nela 100.000 linhas, criar dois índices e gerar suas estatísticas.

 

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
appuser@LAB11 > SELECT * FROM V$VERSION where rownum < 2;

BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production

Elapsed: 00:00:00.19
appuser@LAB11 >
appuser@LAB11 > create table tab01
  2  as
  3  with contador as (
  4      select rownum id
  5        from dual
  6     connect by rownum <= 100000
  7  )
  8  select rownum                                      id,
  9         mod(rownum,100)                         codvar,
 10         trunc(dbms_random.value(0,1000))  categoria,
 11         lpad(rownum,10,'0')                criterio,
 12         rpad('x',60,'x')                      texto
 13    from contador   v1,
 14         contador   v2
 15   where rownum <= 100000;

Table created.

Elapsed: 00:00:01.27
appuser@LAB11 >
appuser@LAB11 >
appuser@LAB11 >
appuser@LAB11 > create index idx_id on tab01(id);

Index created.

Elapsed: 00:00:00.18
appuser@LAB11 >
appuser@LAB11 > create index idx_categoria on tab01(categoria);

Index created.

Elapsed: 00:00:00.16
appuser@LAB11 >
appuser@LAB11 > exec dbms_stats.GATHER_TABLE_STATS(ownname =>'APPUSER',tabname =>'TAB01',method_opt=>'FOR ALL INDEXED COLUMNS SIZE 254',cascade=>true);

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.46
appuser@LAB11 >

 

Consulta com índices unitários

 
Agora vamos executar a primeira consulta e verificar se os índices são utilizados no plano de execução:
 

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
appuser@LAB11 > set echo off;
appuser@LAB11 > set tab off;
appuser@LAB11 > set lines 300 pages 100;
appuser@LAB11 >
appuser@LAB11 > ALTER SESSION SET statistics_level=ALL;

Session altered.

Elapsed: 00:00:00.00
appuser@LAB11 >
appuser@LAB11 > select /* tst401 */ * from tab01
  2   where id between 3000 and 7000
  3     and categoria = 500;

        ID     CODVAR  CATEGORIA CRITERIO   TEXTO
---------- ---------- ---------- ---------- ------------------------------------------------------------
      3211         11        500 0000003211 xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
      4295         95        500 0000004295 xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
      4391         91        500 0000004391 xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
      5091         91        500 0000005091 xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
      6146         46        500 0000006146 xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx

Elapsed: 00:00:00.01
appuser@LAB11 >
appuser@LAB11 >
appuser@LAB11 > column sql_id new_value m_sql_id
appuser@LAB11 > column child_number new_value m_child_no
appuser@LAB11 >
appuser@LAB11 > SELECT sql_id, child_number
  2    FROM v$sql
  3   WHERE sql_text LIKE '%tst401%'
  4     AND sql_text NOT LIKE '%v$sql%';

SQL_ID        CHILD_NUMBER
------------- ------------
2mxjd0f4wnyj6            0

Elapsed: 00:00:00.05
appuser@LAB11 >
appuser@LAB11 >
appuser@LAB11 > SELECT *
  2    FROM TABLE (dbms_xplan.display_cursor ('&m_sql_id',&m_child_no,'basic iostats last'));
old   2:   FROM TABLE (dbms_xplan.display_cursor ('&m_sql_id',&m_child_no,'basic iostats last'))
new   2:   FROM TABLE (dbms_xplan.display_cursor ('2mxjd0f4wnyj6',         0,'basic iostats last'))

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
EXPLAINED SQL STATEMENT:
------------------------
select /* tst401 */ * from tab01  where id between :"SYS_B_0" and
:"SYS_B_1"    and categoria = :"SYS_B_2"

Plan hash value: 2854783063

-------------------------------------------------------------------------------------------------------------
| Id  | Operation                         | Name          | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
-------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                  |               |      1 |        |      5 |00:00:00.01 |      17 |
|*  1 |  FILTER                           |               |      1 |        |      5 |00:00:00.01 |      17 |
|   2 |   TABLE ACCESS BY INDEX ROWID     | TAB01         |      1 |      4 |      5 |00:00:00.01 |      17 |
|   3 |    BITMAP CONVERSION TO ROWIDS    |               |      1 |        |      5 |00:00:00.01 |      12 |
|   4 |     BITMAP AND                    |               |      1 |        |      1 |00:00:00.01 |      12 |
|   5 |      BITMAP CONVERSION FROM ROWIDS|               |      1 |        |      1 |00:00:00.01 |       2 |
|*  6 |       INDEX RANGE SCAN            | IDX_CATEGORIA |      1 |   3739 |    101 |00:00:00.01 |       2 |
|   7 |      BITMAP CONVERSION FROM ROWIDS|               |      1 |        |      1 |00:00:00.01 |      10 |
|   8 |       SORT ORDER BY               |               |      1 |        |   4001 |00:00:00.01 |      10 |
|*  9 |        INDEX RANGE SCAN           | IDX_ID        |      1 |   3739 |   4001 |00:00:00.01 |      10 |
-------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter(:SYS_B_1>=:SYS_B_0)
   6 - access("CATEGORIA"=:SYS_B_2)
   9 - access("ID">=:SYS_B_0 AND "ID"<=:SYS_B_1)


29 rows selected.

Elapsed: 00:00:00.03
appuser@LAB11 >

 

Verificando no plano de execução acima, podemos observar que o Otimizador escolheu um plano que utiliza os dois índices (IDX_CATEGORIA,IDX_ID) através da operação “BITMAP AND”. Esse plano é executado em milésimos de segundo, porem o campo “A-TIME” registra que o tempo de execução da consulta foi 1 centésimo de segundo que é o resultado do arredondamento do tempo real, devido a limitação do campo “A-TIME”, por essa razão vamos utilizar a quantidade de “Buffers” lidos para comparar a eficiência do plano de execução que no caso dessa consulta foram 17 Buffers.

 

Consulta com índice composto

 

Na sequência vamos criar um índice composto com as duas colunas utilizadas no filtro da cláusula WHERE (CATEGORIA,ID) e executar novamente a mesma consulta para verificar se o Otimizador vai utilizar este índice e qual o plano de execução que ele vai escolher:

 

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
ppuser@LAB11 > create index idx_composto on tab01(categoria,id);

Index created.

Elapsed: 00:00:00.26
appuser@LAB11 >
appuser@LAB11 >
appuser@LAB11 > select /* tst402 */ * from tab01
  2   where id between 3000 and 7000
  3     and categoria = 500;

        ID     CODVAR  CATEGORIA CRITERIO   TEXTO
---------- ---------- ---------- ---------- ------------------------------------------------------------
      3211         11        500 0000003211 xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
      4295         95        500 0000004295 xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
      4391         91        500 0000004391 xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
      5091         91        500 0000005091 xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
      6146         46        500 0000006146 xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx

Elapsed: 00:00:00.00
appuser@LAB11 >
appuser@LAB11 >
appuser@LAB11 > column sql_id new_value m_sql_id
appuser@LAB11 > column child_number new_value m_child_no
appuser@LAB11 >
appuser@LAB11 > SELECT sql_id, child_number
  2    FROM v$sql
  3   WHERE sql_text LIKE '%tst402%'
  4     AND sql_text NOT LIKE '%v$sql%';

SQL_ID        CHILD_NUMBER
------------- ------------
gd8whz92pvq8x            0

Elapsed: 00:00:00.04
appuser@LAB11 >
appuser@LAB11 >
appuser@LAB11 > SELECT *
  2    FROM TABLE (dbms_xplan.display_cursor ('&m_sql_id',&m_child_no,'basic iostats last'));
old   2:   FROM TABLE (dbms_xplan.display_cursor ('&m_sql_id',&m_child_no,'basic iostats last'))
new   2:   FROM TABLE (dbms_xplan.display_cursor ('gd8whz92pvq8x',         0,'basic iostats last'))

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
EXPLAINED SQL STATEMENT:
------------------------
select /* tst402 */ * from tab01  where id between :"SYS_B_0" and
:"SYS_B_1"    and categoria = :"SYS_B_2"

Plan hash value: 1105024155

----------------------------------------------------------------------------------------------------------------
| Id  | Operation                    | Name         | Starts | E-Rows | A-Rows |   A-Time   | Buffers | Reads  |
----------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |              |      1 |        |      5 |00:00:00.01 |       8 |      1 |
|*  1 |  FILTER                      |              |      1 |        |      5 |00:00:00.01 |       8 |      1 |
|   2 |   TABLE ACCESS BY INDEX ROWID| TAB01        |      1 |      4 |      5 |00:00:00.01 |       8 |      1 |
|*  3 |    INDEX RANGE SCAN          | IDX_COMPOSTO |      1 |      4 |      5 |00:00:00.01 |       3 |      1 |
----------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter(:SYS_B_1>=:SYS_B_0)
   3 - access("CATEGORIA"=:SYS_B_2 AND "ID">=:SYS_B_0 AND "ID"<=:SYS_B_1)


22 rows selected.

Elapsed: 00:00:00.02
appuser@LAB11 >

 

Analisando o plano de execução acima notamos que o índice composto criado foi utilizado pelo Otimizador e que a eficiência do plano melhorou pois o numero de Buffers diminuiu de 17 para 8, uma redução de 53% do numero de Buffers visitados.

 

Conclusão

O Objetivo dessa simulação era verificar se a utilização de um índice composto proporciona alguma melhoria no plano de execução de uma consulta que possui mais que um filtro da mesma tabela na cláusula WHERE. Conforme pudemos constatar mesmo com as melhorias implementadas no Otimizador na ultimas versões do banco de dados Oracle, ainda continua sendo uma opção de ajuste de desempenho a utilização de índices compostos que na nossa simulação foi com duas colunas, mas poderiam ser três ou mais.

 

Referências

https://docs.oracle.com/cd/E11882_01/server.112/e41573/data_acc.htm#PFGRF94776

http://www.akadia.com/services/ora_index_selectivity.html
 
 

Mídia social

 

Deixe uma resposta

O seu endereço de email não será publicado. Campos obrigatórios são marcados com *