{"id":222,"date":"2016-01-03T23:16:49","date_gmt":"2016-01-04T01:16:49","guid":{"rendered":"http:\/\/dbtimewizard.com.br\/blog\/?p=222"},"modified":"2019-10-10T22:52:00","modified_gmt":"2019-10-11T01:52:00","slug":"quando-devemos-criar-um-indice-composto","status":"publish","type":"post","link":"http:\/\/dbtimewizard.com.br\/blog\/quando-devemos-criar-um-indice-composto\/","title":{"rendered":"Quando devemos criar um \u00edndice composto?"},"content":{"rendered":"

\u00c9 comum encontrar aplica\u00e7\u00f5es cujas tabelas possuem muitos \u00edndices unit\u00e1rios (uma coluna) e nenhum \u00edndice composto (duas ou mais colunas), as ultimas vers\u00f5es do banco de dados Oracle j\u00e1 trata essa defici\u00eancia de estrutura das aplica\u00e7\u00f5es com o uso da opera\u00e7\u00e3o “BITMAP AND” no plano de execu\u00e7\u00e3o, mas em alguns casos a utiliza\u00e7\u00e3o do \u00edndice composto continua sendo a melhor solu\u00e7\u00e3o para uma instru\u00e7\u00e3o SQL que esteja causando impacto de performance para os usu\u00e1rios da aplica\u00e7\u00e3o.<\/span><\/p>\n

Considere a cria\u00e7\u00e3o de um \u00edndice composto em colunas que s\u00e3o freq\u00fcentemente usadas juntas na cl\u00e1usula “WHERE” combinadas com o operador “AND”, principalmente se a seletividade das colunas combinadas \u00e9 melhor do que a seletividade de qualquer uma delas individualmente.<\/span><\/p>\n

Seletividade de um \u00edndice<\/span><\/h2>\n

A seletividade de um \u00edndice pode variar de 0 a 1, sendo 1 ou 100% a seletividade ideal, normalmente \u00edndices “Primary Key” ou “Unique Key” possuem seletividade 1. Para calcular a seletividade de um \u00edndice de uma coluna utilizamos a seguinte formula:<\/span><\/p>\n

seletividade = N\u00famero de valores diferentes da coluna \/ Numero de registros da tabela<\/span><\/p>\n

Por exemplo, uma tabela com 10.000 registros e um \u00edndice unit\u00e1rio dessa tabela\u00a0que tem 8.700 valores diferentes, a seletividade ser\u00e1 0,87 ou 87% (seletividade=8700\/10000).<\/span><\/p>\n

Podemos calcular a seletividade de um \u00edndice de forma r\u00e1pida executando uma consulta na vis\u00e3o “DBA_INDEXES”:<\/span><\/p>\n

 <\/p>\n

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
<\/div><\/td>
appuser@<\/span>LAB11 ><\/span> set tab off
\nappuser@<\/span>LAB11 ><\/span> col "Percentual"<\/span> for<\/span> a10
\nappuser@<\/span>LAB11 ><\/span> select index_name "Nome do \u00edndice"<\/span>,<\/span>
\n  2<\/span>         num_rows "Total linhas"<\/span>,<\/span>
\n  3<\/span>         distinct_keys "Qtde linhas diferentes"<\/span>,<\/span>
\n  4<\/span>         round<\/span><\/a>(<\/span>distinct_keys\/<\/span>num_rows,<\/span>2<\/span>)<\/span> "Seletividade"<\/span>,<\/span>
\n  5<\/span>         LPAD(<\/span>round<\/span><\/a>(<\/span>distinct_keys\/<\/span>num_rows,<\/span>2<\/span>)<\/span>*<\/span>100<\/span>||<\/span>' %'<\/span>,<\/span>10<\/span>,<\/span>' '<\/span>)<\/span> "Percentual"<\/span>
\n  6<\/span>  from dba_indexes
\n  7<\/span>  where table_name =<\/span> 'TAB01'<\/span>
\n  8<\/span>  order by index_name;<\/span>
\n
\nNome do<\/span> \u00edndice                 Total linhas Qtde linhas diferentes Seletividade Percentual
\n------------------------------<\/span> ------------<\/span> ----------------------<\/span> ------------<\/span> ----------<\/span>
\nIDX_CATEGORIA                        100000<\/span>                   1000<\/span>          .01<\/span>        1<\/span> %<\/span>
\nIDX_CODVAR                           100000<\/span>                    100<\/span>            0<\/span>        0<\/span> %<\/span>
\nIDX_COMPOSTO                         100000<\/span>                  63098<\/span>          .63<\/span>       63<\/span> %<\/span>
\n
\nElapsed:<\/span> 00<\/span>:<\/span>00<\/span>:<\/span>00.05<\/span>
\nappuser@<\/span>LAB11 ><\/span><\/div><\/td><\/tr><\/tbody><\/table><\/div>\n

 <\/p>\n

Demonstra\u00e7\u00e3o do \u00edndice composto<\/span><\/h2>\n

 <\/p>\n

Vamos fazer uma simula\u00e7\u00e3o para verificar se h\u00e1 alguma vantagem em utilizar um \u00edndice composto, em nossa simula\u00e7\u00e3o vamos utilizar uma consulta em uma tabela com dois filtros na cl\u00e1usula WHERE, inicialmente vamos executar a consulta com dois \u00edndices criados para cada uma das colunas referenciadas na cl\u00e1usula WHERE. Na sequ\u00eancia vamos criar um \u00edndice composto com essas duas colunas e executar a consulta novamente para avaliar o resultado.<\/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><\/span><\/strong>
\n2. Como interpretar os valores estat\u00edsticos estimados<\/a><\/span><\/strong>
\n3. Como interpretar os valores estat\u00edsticos coletados durante a execu\u00e7\u00e3o<\/a><\/span><\/strong>
\n4. Como interpretar as se\u00e7\u00f5es n\u00e3o estat\u00edsticas do plano de execu\u00e7\u00e3o<\/a><\/span><\/strong><\/p>\n

 <\/p>\n

Criando a Tabela e seus \u00cdndices<\/span><\/h3>\n

Para esta simula\u00e7\u00e3o vamos criar uma tabela e inserir nela 100.000 linhas, criar dois \u00edndices e gerar suas estat\u00edsticas.<\/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
<\/div><\/td>
appuser@<\/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> 64bit Production
\n
\nElapsed:<\/span> 00<\/span>:<\/span>00<\/span>:<\/span>00.19<\/span>
\nappuser@<\/span>LAB11 ><\/span>
\nappuser@<\/span>LAB11 ><\/span> create table tab01
\n  2<\/span>  as<\/span>
\n  3<\/span>  with contador as<\/span> (<\/span>
\n  4<\/span>      select rownum id
\n  5<\/span>        from dual
\n  6<\/span>     connect by rownum <=<\/span> 100000<\/span>
\n  7<\/span>  )<\/span>
\n  8<\/span>  select rownum                                      id,<\/span>
\n  9<\/span>         mod(<\/span>rownum,<\/span>100<\/span>)<\/span>                         codvar,<\/span>
\n 10<\/span>         trunc(<\/span>dbms_random.<\/span>value(<\/span>0<\/span>,<\/span>1000<\/span>)<\/span>)<\/span>  categoria,<\/span>
\n 11<\/span>         lpad(<\/span>rownum,<\/span>10<\/span>,<\/span>'0'<\/span>)<\/span>                criterio,<\/span>
\n 12<\/span>         rpad(<\/span>'x'<\/span>,<\/span>60<\/span>,<\/span>'x'<\/span>)<\/span>                      texto
\n 13<\/span>    from contador   v1,<\/span>
\n 14<\/span>         contador   v2
\n 15<\/span>   where rownum <=<\/span> 100000<\/span>;<\/span>
\n
\nTable created.<\/span>
\n
\nElapsed:<\/span> 00<\/span>:<\/span>00<\/span>:<\/span>01.27<\/span>
\nappuser@<\/span>LAB11 ><\/span>
\nappuser@<\/span>LAB11 ><\/span>
\nappuser@<\/span>LAB11 ><\/span>
\nappuser@<\/span>LAB11 ><\/span> create index idx_id on tab01(<\/span>id)<\/span>;<\/span>
\n
\nIndex created.<\/span>
\n
\nElapsed:<\/span> 00<\/span>:<\/span>00<\/span>:<\/span>00.18<\/span>
\nappuser@<\/span>LAB11 ><\/span>
\nappuser@<\/span>LAB11 ><\/span> create index idx_categoria on tab01(<\/span>categoria)<\/span>;<\/span>
\n
\nIndex created.<\/span>
\n
\nElapsed:<\/span> 00<\/span>:<\/span>00<\/span>:<\/span>00.16<\/span>
\nappuser@<\/span>LAB11 ><\/span>
\nappuser@<\/span>LAB11 ><\/span> exec<\/span><\/a> dbms_stats.<\/span>GATHER_TABLE_STATS(<\/span>ownname =><\/span>'APPUSER'<\/span>,<\/span>tabname =><\/span>'TAB01'<\/span>,<\/span>method_opt=><\/span>'FOR ALL INDEXED COLUMNS SIZE 254'<\/span>,<\/span>cascade=><\/span>true<\/span>)<\/span>;<\/span>
\n
\nPL\/<\/span>SQL procedure successfully completed.<\/span>
\n
\nElapsed:<\/span> 00<\/span>:<\/span>00<\/span>:<\/span>00.46<\/span>
\nappuser@<\/span>LAB11 ><\/span><\/div><\/td><\/tr><\/tbody><\/table><\/div>\n

 <\/p>\n

Consulta com \u00edndices unit\u00e1rios<\/span><\/h3>\n

 
\nAgora vamos executar a primeira consulta e verificar se os \u00edndices s\u00e3o utilizados no plano de execu\u00e7\u00e3o:<\/span>
\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
<\/div><\/td>
appuser@<\/span>LAB11 ><\/span> set echo<\/span> off;<\/span>
\nappuser@<\/span>LAB11 ><\/span> set tab off;<\/span>
\nappuser@<\/span>LAB11 ><\/span> set lines 300<\/span> pages 100<\/span>;<\/span>
\nappuser@<\/span>LAB11 ><\/span>
\nappuser@<\/span>LAB11 ><\/span> ALTER SESSION SET statistics_level=<\/span>ALL;<\/span>
\n
\nSession altered.<\/span>
\n
\nElapsed:<\/span> 00<\/span>:<\/span>00<\/span>:<\/span>00.00<\/span>
\nappuser@<\/span>LAB11 ><\/span>
\nappuser@<\/span>LAB11 ><\/span> select \/* tst401 *\/<\/span> *<\/span> from tab01
\n  2<\/span>   where id between 3000<\/span> and 7000<\/span>
\n  3<\/span>     and categoria =<\/span> 500<\/span>;<\/span>
\n
\n        ID     CODVAR  CATEGORIA CRITERIO   TEXTO
\n----------<\/span> ----------<\/span> ----------<\/span> ----------<\/span> ------------------------------------------------------------<\/span>
\n      3211<\/span>         11<\/span>        500<\/span> 0000003211<\/span> xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
\n      4295<\/span>         95<\/span>        500<\/span> 0000004295<\/span> xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
\n      4391<\/span>         91<\/span>        500<\/span> 0000004391<\/span> xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
\n      5091<\/span>         91<\/span>        500<\/span> 0000005091<\/span> xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
\n      6146<\/span>         46<\/span>        500<\/span> 0000006146<\/span> xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
\n
\nElapsed:<\/span> 00<\/span>:<\/span>00<\/span>:<\/span>00.01<\/span>
\nappuser@<\/span>LAB11 ><\/span>
\nappuser@<\/span>LAB11 ><\/span>
\nappuser@<\/span>LAB11 ><\/span> column sql_id new_value m_sql_id
\nappuser@<\/span>LAB11 ><\/span> column child_number new_value m_child_no
\nappuser@<\/span>LAB11 ><\/span>
\nappuser@<\/span>LAB11 ><\/span> SELECT sql_id,<\/span> child_number
\n  2<\/span>    FROM v$sql<\/span>
\n  3<\/span>   WHERE sql_text LIKE '%tst401%'<\/span>
\n  4<\/span>     AND sql_text NOT LIKE '%v$sql%'<\/span>;<\/span>
\n
\nSQL_ID        CHILD_NUMBER
\n-------------<\/span> ------------<\/span>
\n2mxjd0f4wnyj6            0<\/span>
\n
\nElapsed:<\/span> 00<\/span>:<\/span>00<\/span>:<\/span>00.05<\/span>
\nappuser@<\/span>LAB11 ><\/span>
\nappuser@<\/span>LAB11 ><\/span>
\nappuser@<\/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>
\nold   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>
\nnew<\/span>   2<\/span>:<\/span>   FROM TABLE (<\/span>dbms_xplan.<\/span>display_cursor (<\/span>'2mxjd0f4wnyj6'<\/span>,<\/span>         0<\/span>,<\/span>'basic iostats last'<\/span>)<\/span>)<\/span>
\n
\nPLAN_TABLE_OUTPUT
\n----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------<\/span>
\nEXPLAINED SQL STATEMENT:<\/span>
\n------------------------<\/span>
\nselect \/* tst401 *\/<\/span> *<\/span> from tab01  where id between :<\/span>"SYS_B_0"<\/span> and
\n:<\/span>"SYS_B_1"<\/span>    and categoria =<\/span> :<\/span>"SYS_B_2"<\/span>
\n
\nPlan hash<\/span><\/a> value:<\/span> 2854783063<\/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>      5<\/span> |<\/span>00<\/span>:<\/span>00<\/span>:<\/span>00.01<\/span> |<\/span>      17<\/span> |<\/span>
\n|*<\/span>  1<\/span> |<\/span>  FILTER                           |<\/span>               |<\/span>      1<\/span> |<\/span>        |<\/span>      5<\/span> |<\/span>00<\/span>:<\/span>00<\/span>:<\/span>00.01<\/span> |<\/span>      17<\/span> |<\/span>
\n|<\/span>   2<\/span> |<\/span>   TABLE ACCESS BY INDEX ROWID     |<\/span> TAB01         |<\/span>      1<\/span> |<\/span>      4<\/span> |<\/span>      5<\/span> |<\/span>00<\/span>:<\/span>00<\/span>:<\/span>00.01<\/span> |<\/span>      17<\/span> |<\/span>
\n|<\/span>   3<\/span> |<\/span>    BITMAP CONVERSION TO ROWIDS    |<\/span>               |<\/span>      1<\/span> |<\/span>        |<\/span>      5<\/span> |<\/span>00<\/span>:<\/span>00<\/span>:<\/span>00.01<\/span> |<\/span>      12<\/span> |<\/span>
\n|<\/span>   4<\/span> |<\/span>     BITMAP AND                    |<\/span>               |<\/span>      1<\/span> |<\/span>        |<\/span>      1<\/span> |<\/span>00<\/span>:<\/span>00<\/span>:<\/span>00.01<\/span> |<\/span>      12<\/span> |<\/span>
\n|<\/span>   5<\/span> |<\/span>      BITMAP CONVERSION FROM ROWIDS|<\/span>               |<\/span>      1<\/span> |<\/span>        |<\/span>      1<\/span> |<\/span>00<\/span>:<\/span>00<\/span>:<\/span>00.01<\/span> |<\/span>       2<\/span> |<\/span>
\n|*<\/span>  6<\/span> |<\/span>       INDEX RANGE<\/span><\/a> SCAN            |<\/span> IDX_CATEGORIA |<\/span>      1<\/span> |<\/span>   3739<\/span> |<\/span>    101<\/span> |<\/span>00<\/span>:<\/span>00<\/span>:<\/span>00.01<\/span> |<\/span>       2<\/span> |<\/span>
\n|<\/span>   7<\/span> |<\/span>      BITMAP CONVERSION FROM ROWIDS|<\/span>               |<\/span>      1<\/span> |<\/span>        |<\/span>      1<\/span> |<\/span>00<\/span>:<\/span>00<\/span>:<\/span>00.01<\/span> |<\/span>      10<\/span> |<\/span>
\n|<\/span>   8<\/span> |<\/span>       SORT<\/span><\/a> ORDER BY               |<\/span>               |<\/span>      1<\/span> |<\/span>        |<\/span>   4001<\/span> |<\/span>00<\/span>:<\/span>00<\/span>:<\/span>00.01<\/span> |<\/span>      10<\/span> |<\/span>
\n|*<\/span>  9<\/span> |<\/span>        INDEX RANGE<\/span><\/a> SCAN           |<\/span> IDX_ID        |<\/span>      1<\/span> |<\/span>   3739<\/span> |<\/span>   4001<\/span> |<\/span>00<\/span>:<\/span>00<\/span>:<\/span>00.01<\/span> |<\/span>      10<\/span> |<\/span>
\n-------------------------------------------------------------------------------------------------------------<\/span>
\n
\nPredicate Information (<\/span>identified by operation id)<\/span>:<\/span>
\n---------------------------------------------------<\/span>
\n
\n   1<\/span> -<\/span> filter(<\/span>:<\/span>SYS_B_1>=:<\/span>SYS_B_0)<\/span>
\n   6<\/span> -<\/span> access(<\/span>"CATEGORIA"<\/span>=:<\/span>SYS_B_2)<\/span>
\n   9<\/span> -<\/span> access(<\/span>"ID"<\/span>>=:<\/span>SYS_B_0 AND "ID"<\/span><=:<\/span>SYS_B_1)<\/span>
\n
\n
\n29<\/span> rows selected.<\/span>
\n
\nElapsed:<\/span> 00<\/span>:<\/span>00<\/span>:<\/span>00.03<\/span>
\nappuser@<\/span>LAB11 ><\/span><\/div><\/td><\/tr><\/tbody><\/table><\/div>\n

 <\/p>\n

Verificando no plano de execu\u00e7\u00e3o acima, podemos observar\u00a0que o Otimizador escolheu um plano que utiliza os dois \u00edndices (IDX_CATEGORIA,IDX_ID) atrav\u00e9s da opera\u00e7\u00e3o “BITMAP AND”. Esse plano \u00e9 executado em mil\u00e9simos de segundo, porem o campo “A-TIME” registra que o tempo de execu\u00e7\u00e3o da consulta foi 1 cent\u00e9simo de segundo\u00a0que \u00e9 o resultado do arredondamento do\u00a0tempo\u00a0real,\u00a0devido a limita\u00e7\u00e3o do campo “A-TIME”, por essa raz\u00e3o vamos utilizar a quantidade de “Buffers” lidos para comparar a efici\u00eancia do plano de execu\u00e7\u00e3o que no caso dessa consulta foram 17 Buffers.<\/span><\/p>\n

 <\/p>\n

Consulta com \u00edndice composto<\/span><\/h3>\n

 <\/p>\n

Na sequ\u00eancia vamos criar um \u00edndice composto com as duas colunas utilizadas no filtro da cl\u00e1usula WHERE (CATEGORIA,ID) e executar novamente a mesma consulta para verificar se o Otimizador vai utilizar este \u00edndice e qual o plano de execu\u00e7\u00e3o que ele vai escolher:<\/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
<\/div><\/td>
ppuser@<\/span>LAB11 ><\/span> create index idx_composto on tab01(<\/span>categoria,<\/span>id)<\/span>;<\/span>
\n
\nIndex created.<\/span>
\n
\nElapsed:<\/span> 00<\/span>:<\/span>00<\/span>:<\/span>00.26<\/span>
\nappuser@<\/span>LAB11 ><\/span>
\nappuser@<\/span>LAB11 ><\/span>
\nappuser@<\/span>LAB11 ><\/span> select \/* tst402 *\/<\/span> *<\/span> from tab01
\n  2<\/span>   where id between 3000<\/span> and 7000<\/span>
\n  3<\/span>     and categoria =<\/span> 500<\/span>;<\/span>
\n
\n        ID     CODVAR  CATEGORIA CRITERIO   TEXTO
\n----------<\/span> ----------<\/span> ----------<\/span> ----------<\/span> ------------------------------------------------------------<\/span>
\n      3211<\/span>         11<\/span>        500<\/span> 0000003211<\/span> xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
\n      4295<\/span>         95<\/span>        500<\/span> 0000004295<\/span> xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
\n      4391<\/span>         91<\/span>        500<\/span> 0000004391<\/span> xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
\n      5091<\/span>         91<\/span>        500<\/span> 0000005091<\/span> xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
\n      6146<\/span>         46<\/span>        500<\/span> 0000006146<\/span> xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
\n
\nElapsed:<\/span> 00<\/span>:<\/span>00<\/span>:<\/span>00.00<\/span>
\nappuser@<\/span>LAB11 ><\/span>
\nappuser@<\/span>LAB11 ><\/span>
\nappuser@<\/span>LAB11 ><\/span> column sql_id new_value m_sql_id
\nappuser@<\/span>LAB11 ><\/span> column child_number new_value m_child_no
\nappuser@<\/span>LAB11 ><\/span>
\nappuser@<\/span>LAB11 ><\/span> SELECT sql_id,<\/span> child_number
\n  2<\/span>    FROM v$sql<\/span>
\n  3<\/span>   WHERE sql_text LIKE '%tst402%'<\/span>
\n  4<\/span>     AND sql_text NOT LIKE '%v$sql%'<\/span>;<\/span>
\n
\nSQL_ID        CHILD_NUMBER
\n-------------<\/span> ------------<\/span>
\ngd8whz92pvq8x            0<\/span>
\n
\nElapsed:<\/span> 00<\/span>:<\/span>00<\/span>:<\/span>00.04<\/span>
\nappuser@<\/span>LAB11 ><\/span>
\nappuser@<\/span>LAB11 ><\/span>
\nappuser@<\/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>
\nold   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>
\nnew<\/span>   2<\/span>:<\/span>   FROM TABLE (<\/span>dbms_xplan.<\/span>display_cursor (<\/span>'gd8whz92pvq8x'<\/span>,<\/span>         0<\/span>,<\/span>'basic iostats last'<\/span>)<\/span>)<\/span>
\n
\nPLAN_TABLE_OUTPUT
\n----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------<\/span>
\nEXPLAINED SQL STATEMENT:<\/span>
\n------------------------<\/span>
\nselect \/* tst402 *\/<\/span> *<\/span> from tab01  where id between :<\/span>"SYS_B_0"<\/span> and
\n:<\/span>"SYS_B_1"<\/span>    and categoria =<\/span> :<\/span>"SYS_B_2"<\/span>
\n
\nPlan hash<\/span><\/a> value:<\/span> 1105024155<\/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>      5<\/span> |<\/span>00<\/span>:<\/span>00<\/span>:<\/span>00.01<\/span> |<\/span>       8<\/span> |<\/span>      1<\/span> |<\/span>
\n|*<\/span>  1<\/span> |<\/span>  FILTER                      |<\/span>              |<\/span>      1<\/span> |<\/span>        |<\/span>      5<\/span> |<\/span>00<\/span>:<\/span>00<\/span>:<\/span>00.01<\/span> |<\/span>       8<\/span> |<\/span>      1<\/span> |<\/span>
\n|<\/span>   2<\/span> |<\/span>   TABLE ACCESS BY INDEX ROWID|<\/span> TAB01        |<\/span>      1<\/span> |<\/span>      4<\/span> |<\/span>      5<\/span> |<\/span>00<\/span>:<\/span>00<\/span>:<\/span>00.01<\/span> |<\/span>       8<\/span> |<\/span>      1<\/span> |<\/span>
\n|*<\/span>  3<\/span> |<\/span>    INDEX RANGE<\/span><\/a> SCAN          |<\/span> IDX_COMPOSTO |<\/span>      1<\/span> |<\/span>      4<\/span> |<\/span>      5<\/span> |<\/span>00<\/span>:<\/span>00<\/span>:<\/span>00.01<\/span> |<\/span>       3<\/span> |<\/span>      1<\/span> |<\/span>
\n----------------------------------------------------------------------------------------------------------------<\/span>
\n
\nPredicate Information (<\/span>identified by operation id)<\/span>:<\/span>
\n---------------------------------------------------<\/span>
\n
\n   1<\/span> -<\/span> filter(<\/span>:<\/span>SYS_B_1>=:<\/span>SYS_B_0)<\/span>
\n   3<\/span> -<\/span> access(<\/span>"CATEGORIA"<\/span>=:<\/span>SYS_B_2 AND "ID"<\/span>>=:<\/span>SYS_B_0 AND "ID"<\/span><=:<\/span>SYS_B_1)<\/span>
\n
\n
\n22<\/span> rows selected.<\/span>
\n
\nElapsed:<\/span> 00<\/span>:<\/span>00<\/span>:<\/span>00.02<\/span>
\nappuser@<\/span>LAB11 ><\/span><\/div><\/td><\/tr><\/tbody><\/table><\/div>\n

 <\/p>\n

Analisando o plano de execu\u00e7\u00e3o acima notamos que o \u00edndice composto criado foi utilizado pelo Otimizador e que a efici\u00eancia do plano melhorou pois o numero de Buffers diminuiu de 17 para 8, uma redu\u00e7\u00e3o de 53% do numero de Buffers visitados.<\/span><\/p>\n

 <\/p>\n

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

O Objetivo dessa simula\u00e7\u00e3o era verificar se a utiliza\u00e7\u00e3o de um \u00edndice composto proporciona alguma melhoria no plano de execu\u00e7\u00e3o de uma consulta que possui mais que um filtro da mesma tabela na cl\u00e1usula WHERE. Conforme pudemos constatar mesmo com as melhorias implementadas no Otimizador na ultimas vers\u00f5es do banco de dados Oracle, ainda continua sendo uma op\u00e7\u00e3o de ajuste de desempenho a utiliza\u00e7\u00e3o de \u00edndices compostos que na nossa simula\u00e7\u00e3o foi com duas colunas, mas poderiam ser tr\u00eas ou mais.<\/span><\/p>\n

 <\/p>\n

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

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

http:\/\/www.akadia.com\/services\/ora_index_selectivity.html<\/a>
\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":"

\u00c9 comum encontrar aplica\u00e7\u00f5es cujas tabelas possuem muitos \u00edndices unit\u00e1rios (uma coluna) e nenhum \u00edndice composto (duas ou mais colunas), as ultimas vers\u00f5es do banco de dados Oracle j\u00e1 trata essa defici\u00eancia de estrutura das aplica\u00e7\u00f5es com o uso da opera\u00e7\u00e3o “BITMAP AND” no plano de execu\u00e7\u00e3o, mas em alguns casos a utiliza\u00e7\u00e3o do \u00edndice composto continua sendo a melhor<\/p>\n","protected":false},"author":2,"featured_media":223,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[62],"tags":[65,6,63,42,64,10,29,67,66,45],"class_list":["post-222","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-indice-composto","tag-otimizador","tag-performance-tuning","tag-selectivity","tag-seletividade","tag-sql-statement-analysis"],"aioseo_notices":[],"_links":{"self":[{"href":"http:\/\/dbtimewizard.com.br\/blog\/wp-json\/wp\/v2\/posts\/222","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=222"}],"version-history":[{"count":25,"href":"http:\/\/dbtimewizard.com.br\/blog\/wp-json\/wp\/v2\/posts\/222\/revisions"}],"predecessor-version":[{"id":2049,"href":"http:\/\/dbtimewizard.com.br\/blog\/wp-json\/wp\/v2\/posts\/222\/revisions\/2049"}],"wp:featuredmedia":[{"embeddable":true,"href":"http:\/\/dbtimewizard.com.br\/blog\/wp-json\/wp\/v2\/media\/223"}],"wp:attachment":[{"href":"http:\/\/dbtimewizard.com.br\/blog\/wp-json\/wp\/v2\/media?parent=222"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"http:\/\/dbtimewizard.com.br\/blog\/wp-json\/wp\/v2\/categories?post=222"},{"taxonomy":"post_tag","embeddable":true,"href":"http:\/\/dbtimewizard.com.br\/blog\/wp-json\/wp\/v2\/tags?post=222"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}