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…
Quando devemos criar um í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
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:
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
