A ordem das colunas num índice composto faz diferença?

índice composto

Em um artigo anterior já mostramos a importância do índice composto para melhorar o desempenho de uma instrução SQL, agora vamos estudar um pouco como a ordem das colunas no índice composto pode impactar o desempenho de uma instrução SQL.

A ordem das colunas no índice composto é importante, no entanto para definir qual a melhor ordem a ser adotada precisamos entender como a aplicação utilizará esse índice nas suas instruções SQL mais críticas, entender como os dados serão acessados e a partir daí determinar qual a melhor ordem para as colunas.

Quando o filtro da cláusula WHERE contem somente uma das colunas de um índice composto, a ordem dessa coluna no índice vai afetar o desempenho da instrução SQL. A partir da versão 9i a Oracle introduziu a operação INDEX SKIP SCAN que permitiu que um índice composto seja utilizado mesmo que a coluna presente no filtro da cláusula WHERE não seja a primeira no índice composto.

Descrição da simulação

Para facilitar o entendimento desses conceitos vamos fazer algumas simulações utilizando o “SAMPLE SCHEMA SH”, para isso vamos criar dois índices compostos com duas colunas da tabela “SALES” em ordem alternada e tornar invisível os dois índices unitários das colunas que foram utilizadas para criar os índices compostos. Nas duas primeiras simulações vamos utilizar as duas colunas no filtro da cláusula WHERE e para cada uma das simulações vamos forçar a utilização de um dos índices compostos deixando o outro índice composto invisível.
Nas duas ultimas simulações vamos utilizar somente uma coluna no filtro da cláusula WHERE e para cada uma das simulações vamos forçar novamente a utilização alternada dos índices compostos utilizando o mesmo procedimento.

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 os índices compostos

O primeiro passo em nossa simulação é a criação de dois índices compostos utilizando duas colunas da tabela “SALES” com a ordem das colunas alternadas e tornar invisível os índices unitários dessas duas colunas.

 

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
sh@LAB11 > SELECT * FROM V$VERSION where rownum < 2;

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

sh@LAB11 >
sh@LAB11 > create index sales_cust_prod_bix on sales(cust_id,prod_id);

índice criado.

sh@LAB11 >
sh@LAB11 > create index sales_prod_cust_bix on sales(prod_id,cust_id);

índice criado.

sh@LAB11 >
sh@LAB11 > alter index SALES_CUST_BIX invisible;

índice alterado.

sh@LAB11 >
sh@LAB11 > alter index SALES_PROD_BIX invisible;

índice alterado.

sh@LAB11 >
sh@LAB11 > SELECT index_name,
  2         num_rows,
  3         distinct_keys,
  4         visibility,
  5         last_analyzed
  6    FROM user_indexes
  7   WHERE table_name = 'SALES'
  8   ORDER BY index_name;

INDEX_NAME                       NUM_ROWS DISTINCT_KEYS VISIBILIT LAST_ANA
------------------------------ ---------- ------------- --------- --------
SALES_CHANNEL_BIX                      92             4 VISIBLE   18/01/16
SALES_CUST_BIX                      35808          7059 INVISIBLE 18/01/16
SALES_CUST_PROD_BIX                918843        279954 VISIBLE   18/01/16
SALES_PROD_BIX                       1074            72 INVISIBLE 18/01/16
SALES_PROD_CUST_BIX                918843        279954 VISIBLE   18/01/16
SALES_PROMO_BIX                        54             4 VISIBLE   18/01/16
SALES_TIME_BIX                       1460          1460 VISIBLE   18/01/16

7 linhas selecionadas.

sh@LAB11 >

 

Consulta com dois filtros na cláusula WHERE

Title of the document

Ajudo DBAs e analistas de sistema a se destacarem em suas empresas

e obter um crescimento mais acelerado em suas carreiras, quer saber mais click no link abaixo:

Agora vamos executar duas vezes uma consulta com dois filtros na cláusula WHERE, um para cada uma das colunas presentes no índice composto, sendo que em cada uma das execuções vamos forçar a utilização de um índice composto diferente e vamos observar qual será o efeito desses índices no desempenho da consulta.

 

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
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
sh@LAB11 > set echo off;
sh@LAB11 > set tab off;
sh@LAB11 > set lines 300 pages 100;
sh@LAB11 >
sh@LAB11 > ALTER SESSION SET statistics_level=ALL;

Sessão alterada.

sh@LAB11 > alter index SALES_CUST_PROD_BIX invisible;

índice alterado.

sh@LAB11 >
sh@LAB11 > SELECT /* tst101 */ sum(amount_sold) amount_sold
  2    FROM sales
  3   WHERE cust_id = 100872
  4     AND prod_id = 147;

AMOUNT_SOLD
-----------
       7,99

sh@LAB11 >
sh@LAB11 >
sh@LAB11 > column sql_id new_value m_sql_id
sh@LAB11 > column child_number new_value m_child_no
sh@LAB11 >
sh@LAB11 > SELECT sql_id, child_number
  2    FROM v$sql
  3   WHERE sql_text LIKE '%tst101%'
  4     AND sql_text NOT LIKE '%v$sql%';

SQL_ID        CHILD_NUMBER
------------- ------------
8jubrg8qvyzn2            0

sh@LAB11 >
sh@LAB11 >
sh@LAB11 > SELECT *
  2    FROM TABLE (dbms_xplan.display_cursor ('&m_sql_id',&m_child_no,'basic iostats last'));
antigo   2:   FROM TABLE (dbms_xplan.display_cursor ('&m_sql_id',&m_child_no,'basic iostats last'))
novo   2:   FROM TABLE (dbms_xplan.display_cursor ('8jubrg8qvyzn2',         0,'basic iostats last'))

PLAN_TABLE_OUTPUT
-----------------------------------------------------------------------------------------------------------------------------------
----------------------------------------------------------------------------------------------------
EXPLAINED SQL STATEMENT:
------------------------
SELECT /* tst101 */ sum(amount_sold) amount_sold   FROM sales  WHERE
cust_id = 100872    AND prod_id = 147

Plan hash value: 1854317342

------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                           | Name                | Starts | E-Rows | A-Rows |   A-Time   | Buffers | Reads  |
------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                    |                     |      1 |        |      1 |00:00:00.01 |       4 |      9 |
|   1 |  SORT AGGREGATE                     |                     |      1 |      1 |      1 |00:00:00.01 |       4 |      9 |
|   2 |   TABLE ACCESS BY GLOBAL INDEX ROWID| SALES               |      1 |      1 |      1 |00:00:00.01 |       4 |      9 |
|*  3 |    INDEX RANGE SCAN                 | SALES_PROD_CUST_BIX |      1 |      1 |      1 |00:00:00.01 |       3 |      9 |
------------------------------------------------------------------------------------------------------------------------------

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

   3 - access("PROD_ID"=147 AND "CUST_ID"=100872)


21 linhas selecionadas.

sh@LAB11 >
sh@LAB11 > set echo off;
sh@LAB11 > set tab off;
sh@LAB11 > set lines 300 pages 100;
sh@LAB11 >
sh@LAB11 > ALTER SESSION SET statistics_level=ALL;

Sessão alterada.

sh@LAB11 > alter index SALES_CUST_PROD_BIX visible;

índice alterado.

sh@LAB11 > alter index SALES_PROD_CUST_BIX invisible;

índice alterado.

sh@LAB11 >
sh@LAB11 > SELECT /* tst103 */ sum(amount_sold) amount_sold
  2    FROM sales
  3   WHERE cust_id = 100872
  4     AND prod_id = 147;

AMOUNT_SOLD
-----------
       7,99

sh@LAB11 >
sh@LAB11 >
sh@LAB11 > column sql_id new_value m_sql_id
sh@LAB11 > column child_number new_value m_child_no
sh@LAB11 >
sh@LAB11 > SELECT sql_id, child_number
  2    FROM v$sql
  3   WHERE sql_text LIKE '%tst103%'
  4     AND sql_text NOT LIKE '%v$sql%';

SQL_ID        CHILD_NUMBER
------------- ------------
3grctx7p1ukta            0

sh@LAB11 >
sh@LAB11 >
sh@LAB11 > SELECT *
  2    FROM TABLE (dbms_xplan.display_cursor ('&m_sql_id',&m_child_no,'basic iostats last'));
antigo   2:   FROM TABLE (dbms_xplan.display_cursor ('&m_sql_id',&m_child_no,'basic iostats last'))
novo   2:   FROM TABLE (dbms_xplan.display_cursor ('3grctx7p1ukta',         0,'basic iostats last'))

PLAN_TABLE_OUTPUT
---------------------------------------------------------------------------------------------------------------------------------------------
----------------------------------------------------------------------------------------------------
EXPLAINED SQL STATEMENT:
------------------------
SELECT /* tst103 */ sum(amount_sold) amount_sold   FROM sales  WHERE
cust_id = 100872    AND prod_id = 147

Plan hash value: 1856095833

------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                           | Name                | Starts | E-Rows | A-Rows |   A-Time   | Buffers | Reads  |
------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                    |                     |      1 |        |      1 |00:00:00.01 |       4 |      8 |
|   1 |  SORT AGGREGATE                     |                     |      1 |      1 |      1 |00:00:00.01 |       4 |      8 |
|   2 |   TABLE ACCESS BY GLOBAL INDEX ROWID| SALES               |      1 |      1 |      1 |00:00:00.01 |       4 |      8 |
|*  3 |    INDEX RANGE SCAN                 | SALES_CUST_PROD_BIX |      1 |      1 |      1 |00:00:00.01 |       3 |      8 |
------------------------------------------------------------------------------------------------------------------------------

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

   3 - access("CUST_ID"=100872 AND "PROD_ID"=147)


21 linhas selecionadas.

sh@LAB11 >

 

Verificando os planos de execução acima, observamos que o Otimizador utilizou os dois índices compostos com a ordem das colunas invertidas e a quantidade de Buffers lidos foi a mesma, podemos afirmar que o desempenho das duas consulta foi semelhante.

Consulta com um filtro na cláusula WHERE

Na sequência vamos executar duas vezes uma consulta com um filtro na cláusula WHERE alternando novamente a utilização dos índices compostos e verificar o desempenho dessas execuções.

 

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
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
sh@LAB11 > set echo off;
sh@LAB11 > set tab off;
sh@LAB11 > set lines 300 pages 100;
sh@LAB11 >
sh@LAB11 > ALTER SESSION SET statistics_level=ALL;

Sessão alterada.

sh@LAB11 >
sh@LAB11 > alter index SALES_PROD_CUST_BIX visible;

índice alterado.

sh@LAB11 > alter index SALES_CUST_PROD_BIX invisible;

índice alterado.

sh@LAB11 >
sh@LAB11 > SELECT /* tst102 */ sum(amount_sold) amount_sold
  2    FROM sales
  3   WHERE cust_id = 100872;

AMOUNT_SOLD
-----------
     907,98

sh@LAB11 >
sh@LAB11 >
sh@LAB11 > column sql_id new_value m_sql_id
sh@LAB11 > column child_number new_value m_child_no
sh@LAB11 >
sh@LAB11 > SELECT sql_id, child_number
  2    FROM v$sql
  3   WHERE sql_text LIKE '%tst102%'
  4     AND sql_text NOT LIKE '%v$sql%';

SQL_ID        CHILD_NUMBER
------------- ------------
f5ycnuyrttt4a            0

sh@LAB11 >
sh@LAB11 >
sh@LAB11 > SELECT *
  2    FROM TABLE (dbms_xplan.display_cursor ('&m_sql_id',&m_child_no,'basic iostats last'));
antigo   2:   FROM TABLE (dbms_xplan.display_cursor ('&m_sql_id',&m_child_no,'basic iostats last'))
novo   2:   FROM TABLE (dbms_xplan.display_cursor ('f5ycnuyrttt4a',         0,'basic iostats last'))

PLAN_TABLE_OUTPUT
---------------------------------------------------------------------------------------------------------------------------------
----------------------------------------------------------------------------------------------------
EXPLAINED SQL STATEMENT:
------------------------
SELECT /* tst102 */ sum(amount_sold) amount_sold   FROM sales  WHERE
cust_id = 100872

Plan hash value: 1413306053

---------------------------------------------------------------------------------------------------------------------
| Id  | Operation                           | Name                | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
---------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                    |                     |      1 |        |      1 |00:00:00.01 |      85 |
|   1 |  SORT AGGREGATE                     |                     |      1 |      1 |      1 |00:00:00.01 |      85 |
|   2 |   TABLE ACCESS BY GLOBAL INDEX ROWID| SALES               |      1 |    130 |      2 |00:00:00.01 |      85 |
|*  3 |    INDEX SKIP SCAN                  | SALES_PROD_CUST_BIX |      1 |    130 |      2 |00:00:00.01 |      84 |
---------------------------------------------------------------------------------------------------------------------

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

   3 - access("CUST_ID"=100872)
       filter("CUST_ID"=100872)


22 linhas selecionadas.

sh@LAB11 >
sh@LAB11 > set echo off;
sh@LAB11 > set tab off;
sh@LAB11 > set lines 300 pages 100;
sh@LAB11 >
sh@LAB11 > ALTER SESSION SET statistics_level=ALL;

Sessão alterada.

sh@LAB11 >
sh@LAB11 > alter index SALES_CUST_PROD_BIX visible;

índice alterado.

sh@LAB11 > alter index SALES_PROD_CUST_BIX invisible;

índice alterado.

sh@LAB11 >
sh@LAB11 > SELECT /* tst104 */ sum(amount_sold) amount_sold
  2    FROM sales
  3   WHERE cust_id = 100872;

AMOUNT_SOLD
-----------
     907,98

sh@LAB11 >
sh@LAB11 >
sh@LAB11 > column sql_id new_value m_sql_id
sh@LAB11 > column child_number new_value m_child_no
sh@LAB11 >
sh@LAB11 > SELECT sql_id, child_number
  2    FROM v$sql
  3   WHERE sql_text LIKE '%tst104%'
  4     AND sql_text NOT LIKE '%v$sql%';

SQL_ID        CHILD_NUMBER
------------- ------------
atucc5mxmju9n            0

sh@LAB11 >
sh@LAB11 >
sh@LAB11 > SELECT *
  2    FROM TABLE (dbms_xplan.display_cursor ('&m_sql_id',&m_child_no,'basic iostats last'));
antigo   2:   FROM TABLE (dbms_xplan.display_cursor ('&m_sql_id',&m_child_no,'basic iostats last'))
novo   2:   FROM TABLE (dbms_xplan.display_cursor ('atucc5mxmju9n',         0,'basic iostats last'))

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------------------------------------------------------
----------------------------------------------------------------------------------------------------
EXPLAINED SQL STATEMENT:
------------------------
SELECT /* tst104 */ sum(amount_sold) amount_sold   FROM sales  WHERE
cust_id = 100872

Plan hash value: 1856095833

---------------------------------------------------------------------------------------------------------------------
| Id  | Operation                           | Name                | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
---------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                    |                     |      1 |        |      1 |00:00:00.01 |       4 |
|   1 |  SORT AGGREGATE                     |                     |      1 |      1 |      1 |00:00:00.01 |       4 |
|   2 |   TABLE ACCESS BY GLOBAL INDEX ROWID| SALES               |      1 |    130 |      2 |00:00:00.01 |       4 |
|*  3 |    INDEX RANGE SCAN                 | SALES_CUST_PROD_BIX |      1 |    130 |      2 |00:00:00.01 |       3 |
---------------------------------------------------------------------------------------------------------------------

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

   3 - access("CUST_ID"=100872)


21 linhas selecionadas.

sh@LAB11 >

 

Analisando os planos de execução acima notamos que na primeira execução a operação utilizada para acessar o índice foi “INDEX SKIP SCAN”, pois a coluna “CUST_ID” utilizada como filtro na cláusula WHERE é a segunda coluna no índice composto “SALES_PROD_CUST_BIX” e a operação visitou 84 Buffers para encontrar os registros que correspondem ao filtro especificado. Já na segunda execução a operação utilizada foi “INDEX RANGE SCAN” pois a coluna “CUST_ID” é a primeira coluna no índice composto “SALES_CUST_PROD_BIX” e a quantidade de Buffers visitados foram 3.

Comparando o desempenho das duas execuções podemos afirmar que a segunda execução que utilizou o índice “SALES_CUST_PROD_BIX” foi melhor que a primeira.

 

Conclusão

Quando utilizamos índices compostos temos que avaliar as instruções SQL da nossa aplicação e verificar se nossos índices vão proporcionar um bom desempenho para as instruções SQL mais críticas, precisamos observar se essas instruções possuem equivalência do numero de filtros na cláusula WHERE com o numero de colunas dos índices compostos, pois se não houver essa equivalência essas instruções podem ter um desempenho abaixo do esperado devido à ordem das colunas no índice.

Referências

https://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:5671539468597

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

 
 
Promo-D75L

video-02
 
 
 
 
 
 
 
 
 
 
 
 
 
 

2 comments

  • Cristiano

    olá Valter, sou seu aluno no curso Oracle SQL Tuning Índices(Udemy). Quero parabenizar sua didática, sem duvida conhecimento compartilhado, fará grande diferença em muito profissionais. abraço e obrigado pela atitude em compartilhar todo essa bagagem.

Deixe um comentário para Cristiano Cancelar resposta

O seu endereço de e-mail não será publicado.