{"id":272,"date":"2016-01-31T21:45:38","date_gmt":"2016-01-31T23:45:38","guid":{"rendered":"http:\/\/dbtimewizard.com.br\/blog\/?p=272"},"modified":"2019-10-10T22:57:10","modified_gmt":"2019-10-11T01:57:10","slug":"a-ordem-das-colunas-num-indice-composto-faz-diferenca","status":"publish","type":"post","link":"http:\/\/dbtimewizard.com.br\/blog\/a-ordem-das-colunas-num-indice-composto-faz-diferenca\/","title":{"rendered":"A ordem das colunas num \u00edndice composto faz diferen\u00e7a?"},"content":{"rendered":"

Em um artigo anterior<\/a><\/strong> j\u00e1 mostramos a import\u00e2ncia do \u00edndice composto para melhorar o desempenho de uma instru\u00e7\u00e3o SQL, agora vamos estudar um pouco como a ordem das colunas no \u00edndice composto pode impactar o desempenho de uma instru\u00e7\u00e3o SQL.<\/p>\n

A ordem das colunas no \u00edndice composto \u00e9 importante, no entanto para definir qual a melhor ordem a ser adotada precisamos entender como a aplica\u00e7\u00e3o utilizar\u00e1 esse \u00edndice nas suas instru\u00e7\u00f5es SQL mais cr\u00edticas, entender como os dados ser\u00e3o acessados e a partir da\u00ed determinar qual a melhor ordem para as colunas.<\/span><\/p>\n

Quando o filtro da cl\u00e1usula WHERE contem somente uma das colunas de um \u00edndice composto, a ordem dessa coluna no \u00edndice vai afetar o desempenho da instru\u00e7\u00e3o SQL. A partir da vers\u00e3o 9i a Oracle introduziu a opera\u00e7\u00e3o INDEX SKIP SCAN que permitiu que um \u00edndice composto seja utilizado mesmo que a coluna presente no filtro da cl\u00e1usula WHERE n\u00e3o seja a primeira no \u00edndice composto.<\/span><\/p>\n

Descri\u00e7\u00e3o da simula\u00e7\u00e3o<\/span><\/h2>\n

Para facilitar o entendimento desses conceitos vamos fazer algumas simula\u00e7\u00f5es utilizando o “SAMPLE SCHEMA SH”, para isso vamos criar dois \u00edndices compostos com duas colunas da tabela “SALES” em ordem alternada e tornar invis\u00edvel os dois \u00edndices unit\u00e1rios das colunas que foram utilizadas para criar os \u00edndices compostos. Nas duas primeiras simula\u00e7\u00f5es vamos utilizar as duas colunas no filtro da cl\u00e1usula WHERE e para cada uma das simula\u00e7\u00f5es vamos for\u00e7ar a utiliza\u00e7\u00e3o de um dos \u00edndices compostos deixando o outro \u00edndice composto invis\u00edvel.<\/span>
\nNas duas ultimas simula\u00e7\u00f5es vamos utilizar somente uma coluna no filtro da cl\u00e1usula WHERE e para cada uma das simula\u00e7\u00f5es vamos for\u00e7ar novamente a utiliza\u00e7\u00e3o alternada dos \u00edndices compostos utilizando o mesmo procedimento.<\/span><\/p>\n

Caso tenha alguma d\u00favida na leitura do plano de execu\u00e7\u00e3o gerado pelo pacote DBMS_XPLAN consulte a s\u00e9rie de artigos que vai ajuda-lo a interpretar essas informa\u00e7\u00f5es:<\/span><\/p>\n

1. Como verificar a ordem que as opera\u00e7\u00f5es s\u00e3o realizadas<\/a><\/strong><\/span>
\n2. Como interpretar os valores estat\u00edsticos estimados<\/a><\/strong><\/span>
\n3. Como interpretar os valores estat\u00edsticos coletados durante a execu\u00e7\u00e3o<\/a><\/strong><\/span>
\n4. Como interpretar as se\u00e7\u00f5es n\u00e3o estat\u00edsticas do plano de execu\u00e7\u00e3o<\/a><\/strong><\/span><\/p>\n

Criando os \u00edndices compostos<\/span><\/h2>\n

O primeiro passo em nossa simula\u00e7\u00e3o \u00e9 a cria\u00e7\u00e3o de dois \u00edndices compostos utilizando duas colunas da tabela “SALES” com a ordem das colunas alternadas e tornar invis\u00edvel os \u00edndices unit\u00e1rios dessas duas colunas.<\/span><\/p>\n

 <\/p>\n

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
<\/div><\/td>
sh@<\/span>LAB11 ><\/span> SELECT *<\/span> FROM V$VERSION<\/span> where rownum <<\/span> 2<\/span>;<\/span>
\n
\nBANNER
\n--------------------------------------------------------------------------------<\/span>
\nOracle Database 11g Enterprise Edition Release 11.<\/span>2.<\/span>0.<\/span>4.<\/span>0 -<\/span> Production
\n
\nsh@<\/span>LAB11 ><\/span>
\nsh@<\/span>LAB11 ><\/span> create index sales_cust_prod_bix on sales(<\/span>cust_id,<\/span>prod_id)<\/span>;<\/span>
\n
\n\u00edndice criado.<\/span>
\n
\nsh@<\/span>LAB11 ><\/span>
\nsh@<\/span>LAB11 ><\/span> create index sales_prod_cust_bix on sales(<\/span>prod_id,<\/span>cust_id)<\/span>;<\/span>
\n
\n\u00edndice criado.<\/span>
\n
\nsh@<\/span>LAB11 ><\/span>
\nsh@<\/span>LAB11 ><\/span> alter index SALES_CUST_BIX invisible;<\/span>
\n
\n\u00edndice alterado.<\/span>
\n
\nsh@<\/span>LAB11 ><\/span>
\nsh@<\/span>LAB11 ><\/span> alter index SALES_PROD_BIX invisible;<\/span>
\n
\n\u00edndice alterado.<\/span>
\n
\nsh@<\/span>LAB11 ><\/span>
\nsh@<\/span>LAB11 ><\/span> SELECT index_name,<\/span>
\n  2<\/span>         num_rows,<\/span>
\n  3<\/span>         distinct_keys,<\/span>
\n  4<\/span>         visibility,<\/span>
\n  5<\/span>         last_analyzed
\n  6<\/span>    FROM user_indexes
\n  7<\/span>   WHERE table_name =<\/span> 'SALES'<\/span>
\n  8<\/span>   ORDER BY index_name;<\/span>
\n
\nINDEX_NAME                       NUM_ROWS DISTINCT_KEYS VISIBILIT LAST_ANA
\n------------------------------<\/span> ----------<\/span> -------------<\/span> ---------<\/span> --------<\/span>
\nSALES_CHANNEL_BIX                      92<\/span>             4<\/span> VISIBLE   18<\/span>\/<\/span>01<\/span>\/<\/span>16<\/span>
\nSALES_CUST_BIX                      35808<\/span>          7059<\/span> INVISIBLE 18<\/span>\/<\/span>01<\/span>\/<\/span>16<\/span>
\nSALES_CUST_PROD_BIX                918843<\/span>        279954<\/span> VISIBLE   18<\/span>\/<\/span>01<\/span>\/<\/span>16<\/span>
\nSALES_PROD_BIX                       1074<\/span>            72<\/span> INVISIBLE 18<\/span>\/<\/span>01<\/span>\/<\/span>16<\/span>
\nSALES_PROD_CUST_BIX                918843<\/span>        279954<\/span> VISIBLE   18<\/span>\/<\/span>01<\/span>\/<\/span>16<\/span>
\nSALES_PROMO_BIX                        54<\/span>             4<\/span> VISIBLE   18<\/span>\/<\/span>01<\/span>\/<\/span>16<\/span>
\nSALES_TIME_BIX                       1460<\/span>          1460<\/span> VISIBLE   18<\/span>\/<\/span>01<\/span>\/<\/span>16<\/span>
\n
\n7<\/span> linhas selecionadas.<\/span>
\n
\nsh@<\/span>LAB11 ><\/span><\/div><\/td><\/tr><\/tbody><\/table><\/div>\n

 <\/p>\n

Consulta com dois filtros na cl\u00e1usula WHERE<\/span><\/h2>\n

Agora vamos executar duas vezes uma consulta com dois filtros na cl\u00e1usula WHERE, um para cada uma das colunas presentes no \u00edndice composto, sendo que em cada uma das execu\u00e7\u00f5es vamos for\u00e7ar a utiliza\u00e7\u00e3o de um\u00a0\u00edndice composto diferente e vamos observar qual ser\u00e1 o efeito desses \u00edndices no desempenho da consulta.<\/span><\/p>\n

 <\/p>\n

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
<\/div><\/td>
sh@<\/span>LAB11 ><\/span> set echo<\/span> off;<\/span>
\nsh@<\/span>LAB11 ><\/span> set tab off;<\/span>
\nsh@<\/span>LAB11 ><\/span> set lines 300<\/span> pages 100<\/span>;<\/span>
\nsh@<\/span>LAB11 ><\/span>
\nsh@<\/span>LAB11 ><\/span> ALTER SESSION SET statistics_level=<\/span>ALL;<\/span>
\n
\nSess\u00e3o alterada.<\/span>
\n
\nsh@<\/span>LAB11 ><\/span> alter index SALES_CUST_PROD_BIX invisible;<\/span>
\n
\n\u00edndice alterado.<\/span>
\n
\nsh@<\/span>LAB11 ><\/span>
\nsh@<\/span>LAB11 ><\/span> SELECT \/* tst101 *\/<\/span> sum(<\/span>amount_sold)<\/span> amount_sold
\n  2<\/span>    FROM sales
\n  3<\/span>   WHERE cust_id =<\/span> 100872<\/span>
\n  4<\/span>     AND prod_id =<\/span> 147<\/span>;<\/span>
\n
\nAMOUNT_SOLD
\n-----------<\/span>
\n       7<\/span>,<\/span>99<\/span>
\n
\nsh@<\/span>LAB11 ><\/span>
\nsh@<\/span>LAB11 ><\/span>
\nsh@<\/span>LAB11 ><\/span> column sql_id new_value m_sql_id
\nsh@<\/span>LAB11 ><\/span> column child_number new_value m_child_no
\nsh@<\/span>LAB11 ><\/span>
\nsh@<\/span>LAB11 ><\/span> SELECT sql_id,<\/span> child_number
\n  2<\/span>    FROM v$sql<\/span>
\n  3<\/span>   WHERE sql_text LIKE '%tst101%'<\/span>
\n  4<\/span>     AND sql_text NOT LIKE '%v$sql%'<\/span>;<\/span>
\n
\nSQL_ID        CHILD_NUMBER
\n-------------<\/span> ------------<\/span>
\n8jubrg8qvyzn2            0<\/span>
\n
\nsh@<\/span>LAB11 ><\/span>
\nsh@<\/span>LAB11 ><\/span>
\nsh@<\/span>LAB11 ><\/span> SELECT *<\/span>
\n  2<\/span>    FROM TABLE (<\/span>dbms_xplan.<\/span>display_cursor (<\/span>'&m_sql_id'<\/span>,&<\/span>m_child_no,<\/span>'basic iostats last'<\/span>)<\/span>)<\/span>;<\/span>
\nantigo   2<\/span>:<\/span>   FROM TABLE (<\/span>dbms_xplan.<\/span>display_cursor (<\/span>'&m_sql_id'<\/span>,&<\/span>m_child_no,<\/span>'basic iostats last'<\/span>)<\/span>)<\/span>
\nnovo   2<\/span>:<\/span>   FROM TABLE (<\/span>dbms_xplan.<\/span>display_cursor (<\/span>'8jubrg8qvyzn2'<\/span>,<\/span>         0<\/span>,<\/span>'basic iostats last'<\/span>)<\/span>)<\/span>
\n
\nPLAN_TABLE_OUTPUT
\n-----------------------------------------------------------------------------------------------------------------------------------<\/span>
\n----------------------------------------------------------------------------------------------------<\/span>
\nEXPLAINED SQL STATEMENT:<\/span>
\n------------------------<\/span>
\nSELECT \/* tst101 *\/<\/span> sum(<\/span>amount_sold)<\/span> amount_sold   FROM sales  WHERE
\ncust_id =<\/span> 100872<\/span>    AND prod_id =<\/span> 147<\/span>
\n
\nPlan hash<\/span><\/a> value:<\/span> 1854317342<\/span>
\n
\n------------------------------------------------------------------------------------------------------------------------------<\/span>
\n|<\/span> Id  |<\/span> Operation                           |<\/span> Name                |<\/span> Starts |<\/span> E-<\/span>Rows |<\/span> A-<\/span>Rows |<\/span>   A-<\/span>Time<\/span><\/a>   |<\/span> Buffers |<\/span> Reads  |<\/span>
\n------------------------------------------------------------------------------------------------------------------------------<\/span>
\n|<\/span>   0<\/span> |<\/span> SELECT STATEMENT                    |<\/span>                     |<\/span>      1<\/span> |<\/span>        |<\/span>      1<\/span> |<\/span>00<\/span>:<\/span>00<\/span>:<\/span>00.01<\/span> |<\/span>       4<\/span> |<\/span>      9<\/span> |<\/span>
\n|<\/span>   1<\/span> |<\/span>  SORT<\/span><\/a> AGGREGATE<\/span><\/a>                     |<\/span>                     |<\/span>      1<\/span> |<\/span>      1<\/span> |<\/span>      1<\/span> |<\/span>00<\/span>:<\/span>00<\/span>:<\/span>00.01<\/span> |<\/span>       4<\/span> |<\/span>      9<\/span> |<\/span>
\n|<\/span>   2<\/span> |<\/span>   TABLE ACCESS BY GLOBAL<\/span> INDEX ROWID|<\/span> SALES               |<\/span>      1<\/span> |<\/span>      1<\/span> |<\/span>      1<\/span> |<\/span>00<\/span>:<\/span>00<\/span>:<\/span>00.01<\/span> |<\/span>       4<\/span> |<\/span>      9<\/span> |<\/span>
\n|*<\/span>  3<\/span> |<\/span>    INDEX RANGE<\/span><\/a> SCAN                 |<\/span> SALES_PROD_CUST_BIX |<\/span>      1<\/span> |<\/span>      1<\/span> |<\/span>      1<\/span> |<\/span>00<\/span>:<\/span>00<\/span>:<\/span>00.01<\/span> |<\/span>       3<\/span> |<\/span>      9<\/span> |<\/span>
\n------------------------------------------------------------------------------------------------------------------------------<\/span>
\n
\nPredicate Information (<\/span>identified by operation id)<\/span>:<\/span>
\n---------------------------------------------------<\/span>
\n
\n   3<\/span> -<\/span> access(<\/span>"PROD_ID"<\/span>=<\/span>147<\/span> AND "CUST_ID"<\/span>=<\/span>100872<\/span>)<\/span>
\n
\n
\n21<\/span> linhas selecionadas.<\/span>
\n
\nsh@<\/span>LAB11 ><\/span>
\nsh@<\/span>LAB11 ><\/span> set echo<\/span> off;<\/span>
\nsh@<\/span>LAB11 ><\/span> set tab off;<\/span>
\nsh@<\/span>LAB11 ><\/span> set lines 300<\/span> pages 100<\/span>;<\/span>
\nsh@<\/span>LAB11 ><\/span>
\nsh@<\/span>LAB11 ><\/span> ALTER SESSION SET statistics_level=<\/span>ALL;<\/span>
\n
\nSess\u00e3o alterada.<\/span>
\n
\nsh@<\/span>LAB11 ><\/span> alter index SALES_CUST_PROD_BIX visible;<\/span>
\n
\n\u00edndice alterado.<\/span>
\n
\nsh@<\/span>LAB11 ><\/span> alter index SALES_PROD_CUST_BIX invisible;<\/span>
\n
\n\u00edndice alterado.<\/span>
\n
\nsh@<\/span>LAB11 ><\/span>
\nsh@<\/span>LAB11 ><\/span> SELECT \/* tst103 *\/<\/span> sum(<\/span>amount_sold)<\/span> amount_sold
\n  2<\/span>    FROM sales
\n  3<\/span>   WHERE cust_id =<\/span> 100872<\/span>
\n  4<\/span>     AND prod_id =<\/span> 147<\/span>;<\/span>
\n
\nAMOUNT_SOLD
\n-----------<\/span>
\n       7<\/span>,<\/span>99<\/span>
\n
\nsh@<\/span>LAB11 ><\/span>
\nsh@<\/span>LAB11 ><\/span>
\nsh@<\/span>LAB11 ><\/span> column sql_id new_value m_sql_id
\nsh@<\/span>LAB11 ><\/span> column child_number new_value m_child_no
\nsh@<\/span>LAB11 ><\/span>
\nsh@<\/span>LAB11 ><\/span> SELECT sql_id,<\/span> child_number
\n  2<\/span>    FROM v$sql<\/span>
\n  3<\/span>   WHERE sql_text LIKE '%tst103%'<\/span>
\n  4<\/span>     AND sql_text NOT LIKE '%v$sql%'<\/span>;<\/span>
\n
\nSQL_ID        CHILD_NUMBER
\n-------------<\/span> ------------<\/span>
\n3grctx7p1ukta            0<\/span>
\n
\nsh@<\/span>LAB11 ><\/span>
\nsh@<\/span>LAB11 ><\/span>
\nsh@<\/span>LAB11 ><\/span> SELECT *<\/span>
\n  2<\/span>    FROM TABLE (<\/span>dbms_xplan.<\/span>display_cursor (<\/span>'&m_sql_id'<\/span>,&<\/span>m_child_no,<\/span>'basic iostats last'<\/span>)<\/span>)<\/span>;<\/span>
\nantigo   2<\/span>:<\/span>   FROM TABLE (<\/span>dbms_xplan.<\/span>display_cursor (<\/span>'&m_sql_id'<\/span>,&<\/span>m_child_no,<\/span>'basic iostats last'<\/span>)<\/span>)<\/span>
\nnovo   2<\/span>:<\/span>   FROM TABLE (<\/span>dbms_xplan.<\/span>display_cursor (<\/span>'3grctx7p1ukta'<\/span>,<\/span>         0<\/span>,<\/span>'basic iostats last'<\/span>)<\/span>)<\/span>
\n
\nPLAN_TABLE_OUTPUT
\n---------------------------------------------------------------------------------------------------------------------------------------------<\/span>
\n----------------------------------------------------------------------------------------------------<\/span>
\nEXPLAINED SQL STATEMENT:<\/span>
\n------------------------<\/span>
\nSELECT \/* tst103 *\/<\/span> sum(<\/span>amount_sold)<\/span> amount_sold   FROM sales  WHERE
\ncust_id =<\/span> 100872<\/span>    AND prod_id =<\/span> 147<\/span>
\n
\nPlan hash<\/span><\/a> value:<\/span> 1856095833<\/span>
\n
\n------------------------------------------------------------------------------------------------------------------------------<\/span>
\n|<\/span> Id  |<\/span> Operation                           |<\/span> Name                |<\/span> Starts |<\/span> E-<\/span>Rows |<\/span> A-<\/span>Rows |<\/span>   A-<\/span>Time<\/span><\/a>   |<\/span> Buffers |<\/span> Reads  |<\/span>
\n------------------------------------------------------------------------------------------------------------------------------<\/span>
\n|<\/span>   0<\/span> |<\/span> SELECT STATEMENT                    |<\/span>                     |<\/span>      1<\/span> |<\/span>        |<\/span>      1<\/span> |<\/span>00<\/span>:<\/span>00<\/span>:<\/span>00.01<\/span> |<\/span>       4<\/span> |<\/span>      8<\/span> |<\/span>
\n|<\/span>   1<\/span> |<\/span>  SORT<\/span><\/a> AGGREGATE<\/span><\/a>                     |<\/span>                     |<\/span>      1<\/span> |<\/span>      1<\/span> |<\/span>      1<\/span> |<\/span>00<\/span>:<\/span>00<\/span>:<\/span>00.01<\/span> |<\/span>       4<\/span> |<\/span>      8<\/span> |<\/span>
\n|<\/span>   2<\/span> |<\/span>   TABLE ACCESS BY GLOBAL<\/span> INDEX ROWID|<\/span> SALES               |<\/span>      1<\/span> |<\/span>      1<\/span> |<\/span>      1<\/span> |<\/span>00<\/span>:<\/span>00<\/span>:<\/span>00.01<\/span> |<\/span>       4<\/span> |<\/span>      8<\/span> |<\/span>
\n|*<\/span>  3<\/span> |<\/span>    INDEX RANGE<\/span><\/a> SCAN                 |<\/span> SALES_CUST_PROD_BIX |<\/span>      1<\/span> |<\/span>      1<\/span> |<\/span>      1<\/span> |<\/span>00<\/span>:<\/span>00<\/span>:<\/span>00.01<\/span> |<\/span>       3<\/span> |<\/span>      8<\/span> |<\/span>
\n------------------------------------------------------------------------------------------------------------------------------<\/span>
\n
\nPredicate Information (<\/span>identified by operation id)<\/span>:<\/span>
\n---------------------------------------------------<\/span>
\n
\n   3<\/span> -<\/span> access(<\/span>"CUST_ID"<\/span>=<\/span>100872<\/span> AND "PROD_ID"<\/span>=<\/span>147<\/span>)<\/span>
\n
\n
\n21<\/span> linhas selecionadas.<\/span>
\n
\nsh@<\/span>LAB11 ><\/span><\/div><\/td><\/tr><\/tbody><\/table><\/div>\n

 <\/p>\n

Verificando os planos de execu\u00e7\u00e3o acima, observamos que o Otimizador utilizou os dois \u00edndices 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.<\/span><\/p>\n

Consulta com um filtro na cl\u00e1usula WHERE<\/span><\/h2>\n

Na sequ\u00eancia vamos executar duas vezes uma consulta com um filtro na cl\u00e1usula WHERE alternando novamente a utiliza\u00e7\u00e3o dos \u00edndices compostos e verificar o desempenho dessas execu\u00e7\u00f5es.<\/span><\/p>\n

 <\/p>\n

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
<\/div><\/td>
sh@<\/span>LAB11 ><\/span> set echo<\/span> off;<\/span>
\nsh@<\/span>LAB11 ><\/span> set tab off;<\/span>
\nsh@<\/span>LAB11 ><\/span> set lines 300<\/span> pages 100<\/span>;<\/span>
\nsh@<\/span>LAB11 ><\/span>
\nsh@<\/span>LAB11 ><\/span> ALTER SESSION SET statistics_level=<\/span>ALL;<\/span>
\n
\nSess\u00e3o alterada.<\/span>
\n
\nsh@<\/span>LAB11 ><\/span>
\nsh@<\/span>LAB11 ><\/span> alter index SALES_PROD_CUST_BIX visible;<\/span>
\n
\n\u00edndice alterado.<\/span>
\n
\nsh@<\/span>LAB11 ><\/span> alter index SALES_CUST_PROD_BIX invisible;<\/span>
\n
\n\u00edndice alterado.<\/span>
\n
\nsh@<\/span>LAB11 ><\/span>
\nsh@<\/span>LAB11 ><\/span> SELECT \/* tst102 *\/<\/span> sum(<\/span>amount_sold)<\/span> amount_sold
\n  2<\/span>    FROM sales
\n  3<\/span>   WHERE cust_id =<\/span> 100872<\/span>;<\/span>
\n
\nAMOUNT_SOLD
\n-----------<\/span>
\n     907<\/span>,<\/span>98<\/span>
\n
\nsh@<\/span>LAB11 ><\/span>
\nsh@<\/span>LAB11 ><\/span>
\nsh@<\/span>LAB11 ><\/span> column sql_id new_value m_sql_id
\nsh@<\/span>LAB11 ><\/span> column child_number new_value m_child_no
\nsh@<\/span>LAB11 ><\/span>
\nsh@<\/span>LAB11 ><\/span> SELECT sql_id,<\/span> child_number
\n  2<\/span>    FROM v$sql<\/span>
\n  3<\/span>   WHERE sql_text LIKE '%tst102%'<\/span>
\n  4<\/span>     AND sql_text NOT LIKE '%v$sql%'<\/span>;<\/span>
\n
\nSQL_ID        CHILD_NUMBER
\n-------------<\/span> ------------<\/span>
\nf5ycnuyrttt4a            0<\/span>
\n
\nsh@<\/span>LAB11 ><\/span>
\nsh@<\/span>LAB11 ><\/span>
\nsh@<\/span>LAB11 ><\/span> SELECT *<\/span>
\n  2<\/span>    FROM TABLE (<\/span>dbms_xplan.<\/span>display_cursor (<\/span>'&m_sql_id'<\/span>,&<\/span>m_child_no,<\/span>'basic iostats last'<\/span>)<\/span>)<\/span>;<\/span>
\nantigo   2<\/span>:<\/span>   FROM TABLE (<\/span>dbms_xplan.<\/span>display_cursor (<\/span>'&m_sql_id'<\/span>,&<\/span>m_child_no,<\/span>'basic iostats last'<\/span>)<\/span>)<\/span>
\nnovo   2<\/span>:<\/span>   FROM TABLE (<\/span>dbms_xplan.<\/span>display_cursor (<\/span>'f5ycnuyrttt4a'<\/span>,<\/span>         0<\/span>,<\/span>'basic iostats last'<\/span>)<\/span>)<\/span>
\n
\nPLAN_TABLE_OUTPUT
\n---------------------------------------------------------------------------------------------------------------------------------<\/span>
\n----------------------------------------------------------------------------------------------------<\/span>
\nEXPLAINED SQL STATEMENT:<\/span>
\n------------------------<\/span>
\nSELECT \/* tst102 *\/<\/span> sum(<\/span>amount_sold)<\/span> amount_sold   FROM sales  WHERE
\ncust_id =<\/span> 100872<\/span>
\n
\nPlan hash<\/span><\/a> value:<\/span> 1413306053<\/span>
\n
\n---------------------------------------------------------------------------------------------------------------------<\/span>
\n|<\/span> Id  |<\/span> Operation                           |<\/span> Name                |<\/span> Starts |<\/span> E-<\/span>Rows |<\/span> A-<\/span>Rows |<\/span>   A-<\/span>Time<\/span><\/a>   |<\/span> Buffers |<\/span>
\n---------------------------------------------------------------------------------------------------------------------<\/span>
\n|<\/span>   0<\/span> |<\/span> SELECT STATEMENT                    |<\/span>                     |<\/span>      1<\/span> |<\/span>        |<\/span>      1<\/span> |<\/span>00<\/span>:<\/span>00<\/span>:<\/span>00.01<\/span> |<\/span>      85<\/span> |<\/span>
\n|<\/span>   1<\/span> |<\/span>  SORT<\/span><\/a> AGGREGATE<\/span><\/a>                     |<\/span>                     |<\/span>      1<\/span> |<\/span>      1<\/span> |<\/span>      1<\/span> |<\/span>00<\/span>:<\/span>00<\/span>:<\/span>00.01<\/span> |<\/span>      85<\/span> |<\/span>
\n|<\/span>   2<\/span> |<\/span>   TABLE ACCESS BY GLOBAL<\/span> INDEX ROWID|<\/span> SALES               |<\/span>      1<\/span> |<\/span>    130<\/span> |<\/span>      2<\/span> |<\/span>00<\/span>:<\/span>00<\/span>:<\/span>00.01<\/span> |<\/span>      85<\/span> |<\/span>
\n|*<\/span>  3<\/span> |<\/span>    INDEX SKIP SCAN                  |<\/span> SALES_PROD_CUST_BIX |<\/span>      1<\/span> |<\/span>    130<\/span> |<\/span>      2<\/span> |<\/span>00<\/span>:<\/span>00<\/span>:<\/span>00.01<\/span> |<\/span>      84<\/span> |<\/span>
\n---------------------------------------------------------------------------------------------------------------------<\/span>
\n
\nPredicate Information (<\/span>identified by operation id)<\/span>:<\/span>
\n---------------------------------------------------<\/span>
\n
\n   3<\/span> -<\/span> access(<\/span>"CUST_ID"<\/span>=<\/span>100872<\/span>)<\/span>
\n       filter(<\/span>"CUST_ID"<\/span>=<\/span>100872<\/span>)<\/span>
\n
\n
\n22<\/span> linhas selecionadas.<\/span>
\n
\nsh@<\/span>LAB11 ><\/span>
\nsh@<\/span>LAB11 ><\/span> set echo<\/span> off;<\/span>
\nsh@<\/span>LAB11 ><\/span> set tab off;<\/span>
\nsh@<\/span>LAB11 ><\/span> set lines 300<\/span> pages 100<\/span>;<\/span>
\nsh@<\/span>LAB11 ><\/span>
\nsh@<\/span>LAB11 ><\/span> ALTER SESSION SET statistics_level=<\/span>ALL;<\/span>
\n
\nSess\u00e3o alterada.<\/span>
\n
\nsh@<\/span>LAB11 ><\/span>
\nsh@<\/span>LAB11 ><\/span> alter index SALES_CUST_PROD_BIX visible;<\/span>
\n
\n\u00edndice alterado.<\/span>
\n
\nsh@<\/span>LAB11 ><\/span> alter index SALES_PROD_CUST_BIX invisible;<\/span>
\n
\n\u00edndice alterado.<\/span>
\n
\nsh@<\/span>LAB11 ><\/span>
\nsh@<\/span>LAB11 ><\/span> SELECT \/* tst104 *\/<\/span> sum(<\/span>amount_sold)<\/span> amount_sold
\n  2<\/span>    FROM sales
\n  3<\/span>   WHERE cust_id =<\/span> 100872<\/span>;<\/span>
\n
\nAMOUNT_SOLD
\n-----------<\/span>
\n     907<\/span>,<\/span>98<\/span>
\n
\nsh@<\/span>LAB11 ><\/span>
\nsh@<\/span>LAB11 ><\/span>
\nsh@<\/span>LAB11 ><\/span> column sql_id new_value m_sql_id
\nsh@<\/span>LAB11 ><\/span> column child_number new_value m_child_no
\nsh@<\/span>LAB11 ><\/span>
\nsh@<\/span>LAB11 ><\/span> SELECT sql_id,<\/span> child_number
\n  2<\/span>    FROM v$sql<\/span>
\n  3<\/span>   WHERE sql_text LIKE '%tst104%'<\/span>
\n  4<\/span>     AND sql_text NOT LIKE '%v$sql%'<\/span>;<\/span>
\n
\nSQL_ID        CHILD_NUMBER
\n-------------<\/span> ------------<\/span>
\natucc5mxmju9n            0<\/span>
\n
\nsh@<\/span>LAB11 ><\/span>
\nsh@<\/span>LAB11 ><\/span>
\nsh@<\/span>LAB11 ><\/span> SELECT *<\/span>
\n  2<\/span>    FROM TABLE (<\/span>dbms_xplan.<\/span>display_cursor (<\/span>'&m_sql_id'<\/span>,&<\/span>m_child_no,<\/span>'basic iostats last'<\/span>)<\/span>)<\/span>;<\/span>
\nantigo   2<\/span>:<\/span>   FROM TABLE (<\/span>dbms_xplan.<\/span>display_cursor (<\/span>'&m_sql_id'<\/span>,&<\/span>m_child_no,<\/span>'basic iostats last'<\/span>)<\/span>)<\/span>
\nnovo   2<\/span>:<\/span>   FROM TABLE (<\/span>dbms_xplan.<\/span>display_cursor (<\/span>'atucc5mxmju9n'<\/span>,<\/span>         0<\/span>,<\/span>'basic iostats last'<\/span>)<\/span>)<\/span>
\n
\nPLAN_TABLE_OUTPUT
\n----------------------------------------------------------------------------------------------------------------------------------------------------<\/span>
\n----------------------------------------------------------------------------------------------------<\/span>
\nEXPLAINED SQL STATEMENT:<\/span>
\n------------------------<\/span>
\nSELECT \/* tst104 *\/<\/span> sum(<\/span>amount_sold)<\/span> amount_sold   FROM sales  WHERE
\ncust_id =<\/span> 100872<\/span>
\n
\nPlan hash<\/span><\/a> value:<\/span> 1856095833<\/span>
\n
\n---------------------------------------------------------------------------------------------------------------------<\/span>
\n|<\/span> Id  |<\/span> Operation                           |<\/span> Name                |<\/span> Starts |<\/span> E-<\/span>Rows |<\/span> A-<\/span>Rows |<\/span>   A-<\/span>Time<\/span><\/a>   |<\/span> Buffers |<\/span>
\n---------------------------------------------------------------------------------------------------------------------<\/span>
\n|<\/span>   0<\/span> |<\/span> SELECT STATEMENT                    |<\/span>                     |<\/span>      1<\/span> |<\/span>        |<\/span>      1<\/span> |<\/span>00<\/span>:<\/span>00<\/span>:<\/span>00.01<\/span> |<\/span>       4<\/span> |<\/span>
\n|<\/span>   1<\/span> |<\/span>  SORT<\/span><\/a> AGGREGATE<\/span><\/a>                     |<\/span>                     |<\/span>      1<\/span> |<\/span>      1<\/span> |<\/span>      1<\/span> |<\/span>00<\/span>:<\/span>00<\/span>:<\/span>00.01<\/span> |<\/span>       4<\/span> |<\/span>
\n|<\/span>   2<\/span> |<\/span>   TABLE ACCESS BY GLOBAL<\/span> INDEX ROWID|<\/span> SALES               |<\/span>      1<\/span> |<\/span>    130<\/span> |<\/span>      2<\/span> |<\/span>00<\/span>:<\/span>00<\/span>:<\/span>00.01<\/span> |<\/span>       4<\/span> |<\/span>
\n|*<\/span>  3<\/span> |<\/span>    INDEX RANGE<\/span><\/a> SCAN                 |<\/span> SALES_CUST_PROD_BIX |<\/span>      1<\/span> |<\/span>    130<\/span> |<\/span>      2<\/span> |<\/span>00<\/span>:<\/span>00<\/span>:<\/span>00.01<\/span> |<\/span>       3<\/span> |<\/span>
\n---------------------------------------------------------------------------------------------------------------------<\/span>
\n
\nPredicate Information (<\/span>identified by operation id)<\/span>:<\/span>
\n---------------------------------------------------<\/span>
\n
\n   3<\/span> -<\/span> access(<\/span>"CUST_ID"<\/span>=<\/span>100872<\/span>)<\/span>
\n
\n
\n21<\/span> linhas selecionadas.<\/span>
\n
\nsh@<\/span>LAB11 ><\/span><\/div><\/td><\/tr><\/tbody><\/table><\/div>\n

 <\/p>\n

Analisando os planos de execu\u00e7\u00e3o acima notamos que na primeira execu\u00e7\u00e3o a opera\u00e7\u00e3o utilizada para acessar o \u00edndice foi “INDEX SKIP SCAN”, pois a coluna “CUST_ID” utilizada como filtro na cl\u00e1usula WHERE \u00e9 a segunda coluna no \u00edndice composto “SALES_PROD_CUST_BIX” e a opera\u00e7\u00e3o visitou 84 Buffers para encontrar os registros que correspondem\u00a0ao filtro especificado. J\u00e1 na segunda execu\u00e7\u00e3o a opera\u00e7\u00e3o utilizada foi “INDEX RANGE SCAN” pois a coluna “CUST_ID” \u00e9 a primeira coluna no \u00edndice composto “SALES_CUST_PROD_BIX” e a quantidade de Buffers visitados foram 3.<\/span><\/p>\n

Comparando o desempenho das duas execu\u00e7\u00f5es podemos afirmar que a segunda execu\u00e7\u00e3o que utilizou o \u00edndice “SALES_CUST_PROD_BIX” foi melhor que a primeira.<\/span><\/p>\n

 <\/p>\n

Conclus\u00e3o<\/span><\/h2>\n

Quando utilizamos \u00edndices compostos temos que avaliar as instru\u00e7\u00f5es SQL da nossa aplica\u00e7\u00e3o e verificar se nossos \u00edndices v\u00e3o proporcionar um bom desempenho para as instru\u00e7\u00f5es SQL mais cr\u00edticas, precisamos observar se essas instru\u00e7\u00f5es possuem equival\u00eancia do numero de filtros na cl\u00e1usula WHERE com o numero de colunas dos \u00edndices compostos, pois se n\u00e3o houver essa equival\u00eancia essas instru\u00e7\u00f5es podem ter um desempenho abaixo do esperado devido \u00e0 ordem das colunas no \u00edndice.<\/span><\/p>\n

Refer\u00eancias<\/span><\/h2>\n

https:\/\/asktom.oracle.com\/pls\/asktom\/f?p=100:11:0::::P11_QUESTION_ID:5671539468597<\/a><\/strong><\/p>\n

https:\/\/docs.oracle.com\/cd\/E11882_01\/server.112\/e41573\/data_acc.htm#PFGRF94776<\/a><\/strong><\/p>\n

 
\n 
\n\"Promo-D75L\"<\/a><\/p>\n

\"video-02\"<\/a>
\n 
\n 
\n 
\n 
\n 
\n 
\n 
\n 
\n 
\n 
\n 
\n 
\n 
\n <\/p>\n","protected":false},"excerpt":{"rendered":"

Em um artigo anterior j\u00e1 mostramos a import\u00e2ncia do \u00edndice composto para melhorar o desempenho de uma instru\u00e7\u00e3o SQL, agora vamos estudar um pouco como a ordem das colunas no \u00edndice composto pode impactar o desempenho de uma instru\u00e7\u00e3o SQL. A ordem das colunas no \u00edndice composto \u00e9 importante, no entanto para definir qual a melhor ordem a ser adotada<\/p>\n","protected":false},"author":2,"featured_media":273,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[62],"tags":[65,6,63,42,36,78,77,64,79,10,29,11,67,66,45,61],"class_list":["post-272","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-indice","tag-ajuste-de-desempenho","tag-cbo","tag-composite-index","tag-cost-base-optimizer","tag-execution-plan","tag-index-column-order","tag-index-skip-scan","tag-indice-composto","tag-ordem-das-colunas-no-indice","tag-otimizador","tag-performance-tuning","tag-plano-de-execucao","tag-selectivity","tag-seletividade","tag-sql-statement-analysis","tag-tempo-de-reposta"],"aioseo_notices":[],"_links":{"self":[{"href":"http:\/\/dbtimewizard.com.br\/blog\/wp-json\/wp\/v2\/posts\/272","targetHints":{"allow":["GET"]}}],"collection":[{"href":"http:\/\/dbtimewizard.com.br\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"http:\/\/dbtimewizard.com.br\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"http:\/\/dbtimewizard.com.br\/blog\/wp-json\/wp\/v2\/users\/2"}],"replies":[{"embeddable":true,"href":"http:\/\/dbtimewizard.com.br\/blog\/wp-json\/wp\/v2\/comments?post=272"}],"version-history":[{"count":24,"href":"http:\/\/dbtimewizard.com.br\/blog\/wp-json\/wp\/v2\/posts\/272\/revisions"}],"predecessor-version":[{"id":2052,"href":"http:\/\/dbtimewizard.com.br\/blog\/wp-json\/wp\/v2\/posts\/272\/revisions\/2052"}],"wp:featuredmedia":[{"embeddable":true,"href":"http:\/\/dbtimewizard.com.br\/blog\/wp-json\/wp\/v2\/media\/273"}],"wp:attachment":[{"href":"http:\/\/dbtimewizard.com.br\/blog\/wp-json\/wp\/v2\/media?parent=272"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"http:\/\/dbtimewizard.com.br\/blog\/wp-json\/wp\/v2\/categories?post=272"},{"taxonomy":"post_tag","embeddable":true,"href":"http:\/\/dbtimewizard.com.br\/blog\/wp-json\/wp\/v2\/tags?post=272"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}