{"id":1087,"date":"2017-07-03T22:35:18","date_gmt":"2017-07-04T01:35:18","guid":{"rendered":"http:\/\/dbtimewizard.com.br\/blog\/?p=1087"},"modified":"2019-11-11T18:51:07","modified_gmt":"2019-11-11T21:51:07","slug":"3-maneiras-incomuns-de-utilizacao-de-indices","status":"publish","type":"post","link":"http:\/\/dbtimewizard.com.br\/blog\/3-maneiras-incomuns-de-utilizacao-de-indices\/","title":{"rendered":"3 maneiras incomuns de utiliza\u00e7\u00e3o de \u00edndices"},"content":{"rendered":"

A utiliza\u00e7\u00e3o do \u00edndice B-TREE com uma\u00a0coluna ou com um grupo de colunas \u00e9 muito comum nas aplica\u00e7\u00f5es, estas colunas s\u00e3o referenciadas na cl\u00e1usula WHERE das instru\u00e7\u00f5es SQL e possibilitam uma r\u00e1pida recupera\u00e7\u00e3o dos dados que o usu\u00e1rio deseja, mas existem outras formas de utilizar os \u00edndices que s\u00e3o pouco empregadas no dia a dia, mas que podem melhorar muito o desempenho da\u00a0instru\u00e7\u00e3o SQL. \u00a0Neste artigo vamos abordar 3 destas situa\u00e7\u00f5es e mostrar sua efic\u00e1cia atrav\u00e9s de exemplos:<\/span><\/p>\n

 <\/p>\n

1) Como eliminar o acesso a tabela, recuperando os dados somente de \u00edndices atrav\u00e9s da jun\u00e7\u00e3o de \u00edndices.<\/span>
\n2) Como utilizar um \u00edndice numa coluna onde o filtro especificado para essa coluna \u00e9 um valor nulo.<\/span>
\n3) Como utilizar um \u00edndice em colunas que s\u00e3o referenciadas na cl\u00e1usula WHERE como uma fun\u00e7\u00e3o.<\/span><\/p>\n

1) Eliminando o acesso a tabela<\/span><\/h2>\n

Para demonstrar esta situa\u00e7\u00e3o vamos utilizar uma consulta na tabela CUSTOMERS do esquema SH, na primeira execu\u00e7\u00e3o o Otimizador vai escolher o \u00edndice CUSTOMERS_PK que indexa a coluna CUST_ID e vai selecionar os ROWID dos registros que ser\u00e3o acessados\u00a0na tabela.<\/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
<\/div><\/td>
SQL ><\/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
\n1<\/span> linha selecionada.<\/span>
\n
\nSQL ><\/span> create index sh.<\/span>CUST_NAME_IDX on sh.<\/span>CUSTOMERS(<\/span>CUST_FIRST_NAME,<\/span> CUST_LAST_NAME)<\/span>;<\/span>
\n
\n\u00cdndice criado.<\/span>
\n
\nSQL ><\/span> ALTER SESSION SET statistics_level=<\/span>ALL;<\/span>
\n
\nSess\u00e3o alterada.<\/span>
\n
\nSQL ><\/span> ALTER SESSION SET current_schema=<\/span>SH;<\/span>
\n
\nSess\u00e3o alterada.<\/span>
\n
\nSQL ><\/span>
\nSQL ><\/span> SELECT \/* DBTW001 *\/<\/span>
\n  2<\/span>         c.<\/span>cust_id,<\/span> c.<\/span>cust_first_name,<\/span> c.<\/span>cust_last_name
\n  3<\/span>    FROM sh.<\/span>customers c
\n  4<\/span>   WHERE c.<\/span>cust_id >=<\/span>100<\/span>
\n  5<\/span>     AND c.<\/span>cust_id <=<\/span>400<\/span>
\n  6<\/span>     AND c.<\/span>cust_last_name like 'Her%'<\/span>;<\/span>
\n
\n   CUST_ID CUST_FIRST_NAME      CUST_LAST_NAME
\n----------<\/span> --------------------<\/span> ----------------------------------------<\/span>
\n       161<\/span> Linda                Hermann
\n       167<\/span> Eve                  Herd
\n
\n2<\/span> linhas selecionadas.<\/span>
\n
\nSQL ><\/span>
\nSQL ><\/span> column sql_id new_value m_sql_id
\nSQL ><\/span> column child_number new_value m_child_no
\nSQL ><\/span> SELECT sql_id,<\/span> child_number
\n  2<\/span>    FROM v$sql<\/span>
\n  3<\/span>   WHERE sql_text LIKE '%DBTW001%'<\/span>
\n  4<\/span>     AND sql_text NOT LIKE '%v$sql%'<\/span>;<\/span>
\n
\nSQL_ID           CHILD_NUMBER
\n----------------<\/span> ------------<\/span>
\n0y7sa312c8y0q               0<\/span>
\n
\n1<\/span> linha selecionada.<\/span>
\n
\nSQL ><\/span>
\nSQL ><\/span> SELECT *<\/span>
\n  2<\/span>    FROM TABLE (<\/span>dbms_xplan.<\/span>display_cursor (<\/span>'&m_sql_id'<\/span>,&<\/span>m_child_no,<\/span>'typical iostats last'<\/span>)<\/span>)<\/span>;<\/span>
\n
\nPLAN_TABLE_OUTPUT
\n------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------<\/span>
\nSQL_ID  0y7sa312c8y0q,<\/span> child number 0<\/span>
\n-------------------------------------<\/span>
\nSELECT \/* DBTW001 *\/<\/span>        c.<\/span>cust_id,<\/span> c.<\/span>cust_first_name,<\/span>
\nc.<\/span>cust_last_name   FROM sh.<\/span>customers c  WHERE c.<\/span>cust_id >=:<\/span>"SYS_B_0"<\/span>
\nAND c.<\/span>cust_id <=:<\/span>"SYS_B_1"<\/span>    AND c.<\/span>cust_last_name like :<\/span>"SYS_B_2"<\/span>
\n
\nPlan hash<\/span><\/a> value:<\/span> 2754890837<\/span>
\n
\n---------------------------------------------------------------------------------------------------------------------------------------<\/span>
\n|<\/span> Id  |<\/span> Operation                    |<\/span> Name         |<\/span> Starts |<\/span> E-<\/span>Rows |<\/span>E-<\/span>Bytes|<\/span> Cost (<\/span>%<\/span>CPU)<\/span>|<\/span> E-<\/span>Time<\/span><\/a>   |<\/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>       |<\/span>   160<\/span> (<\/span>100<\/span>)<\/span>|<\/span>          |<\/span>      2<\/span> |<\/span>00<\/span>:<\/span>00<\/span>:<\/span>00.01<\/span> |<\/span>     294<\/span> |<\/span>
\n|*<\/span>  1<\/span> |<\/span>  FILTER                      |<\/span>              |<\/span>      1<\/span> |<\/span>        |<\/span>       |<\/span>            |<\/span>          |<\/span>      2<\/span> |<\/span>00<\/span>:<\/span>00<\/span>:<\/span>00.01<\/span> |<\/span>     294<\/span> |<\/span>
\n|*<\/span>  2<\/span> |<\/span>   TABLE ACCESS BY INDEX ROWID|<\/span> CUSTOMERS    |<\/span>      1<\/span> |<\/span>      1<\/span> |<\/span>    20<\/span> |<\/span>   160<\/span>   (<\/span>0<\/span>)<\/span>|<\/span> 00<\/span>:<\/span>00<\/span>:<\/span>02<\/span> |<\/span>      2<\/span> |<\/span>00<\/span>:<\/span>00<\/span>:<\/span>00.01<\/span> |<\/span>     294<\/span> |<\/span>
\n|*<\/span>  3<\/span> |<\/span>    INDEX RANGE<\/span><\/a> SCAN          |<\/span> CUSTOMERS_PK |<\/span>      1<\/span> |<\/span>    161<\/span> |<\/span>       |<\/span>     2<\/span>   (<\/span>0<\/span>)<\/span>|<\/span> 00<\/span>:<\/span>00<\/span>:<\/span>01<\/span> |<\/span>    301<\/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   1<\/span> -<\/span> filter(<\/span>:<\/span>SYS_B_1>=:<\/span>SYS_B_0)<\/span>
\n   2<\/span> -<\/span> filter(<\/span>"C"<\/span>.<\/span>"CUST_LAST_NAME"<\/span> LIKE :<\/span>SYS_B_2)<\/span>
\n   3<\/span> -<\/span> access(<\/span>"C"<\/span>.<\/span>"CUST_ID"<\/span>>=:<\/span>SYS_B_0 AND "C"<\/span>.<\/span>"CUST_ID"<\/span><=:<\/span>SYS_B_1)<\/span>
\n
\n
\n24<\/span> linhas selecionadas.<\/span>
\n
\nSQL ><\/span><\/div><\/td><\/tr><\/tbody><\/table><\/div>\n

 <\/p>\n

Analisando a consulta verificamos que as colunas referenciadas na cl\u00e1usula WHERE e a colunas que v\u00e3o ser apresentadas como resultado da consulta est\u00e3o presentes em dois \u00edndices da tabela CUSTOMERS, os \u00edndices CUSTOMERS_PK e CUST_NAME_IDX. Assim podemos for\u00e7ar o Otimizador atrav\u00e9s do “HINT INDEX_JOIN” a recuperar todas as informa\u00e7\u00f5es necess\u00e1rias dos \u00edndices, n\u00e3o sendo necess\u00e1rio o acesso a tabela, com isso a quantidade de blocos de dados acessados ser\u00e1 menor.<\/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
<\/div><\/td>
SQL ><\/span> set tab off;<\/span>
\nSQL ><\/span>
\nSQL ><\/span> ALTER SESSION SET statistics_level=<\/span>ALL;<\/span>
\n
\nSess\u00e3o alterada.<\/span>
\n
\nSQL ><\/span> ALTER SESSION SET current_schema=<\/span>SH;<\/span>
\n
\nSess\u00e3o alterada.<\/span>
\n
\nSQL ><\/span>
\nSQL ><\/span> SELECT \/*+ index_join(c) *\/<\/span> \/* DBTW002 *\/<\/span>
\n  2<\/span>         c.<\/span>cust_id,<\/span> c.<\/span>cust_first_name,<\/span> c.<\/span>cust_last_name
\n  3<\/span>    FROM sh.<\/span>customers c
\n  4<\/span>   WHERE c.<\/span>cust_id >=<\/span>100<\/span>
\n  5<\/span>     AND c.<\/span>cust_id <=<\/span>400<\/span>
\n  6<\/span>     AND c.<\/span>cust_last_name like 'Her%'<\/span>;<\/span>
\n
\n   CUST_ID CUST_FIRST_NAME      CUST_LAST_NAME
\n----------<\/span> --------------------<\/span> ----------------------------------------<\/span>
\n       167<\/span> Eve                  Herd
\n       161<\/span> Linda                Hermann
\n
\n2<\/span> linhas selecionadas.<\/span>
\n
\nSQL ><\/span>
\nSQL ><\/span> column sql_id new_value m_sql_id
\nSQL ><\/span> column child_number new_value m_child_no
\nSQL ><\/span> SELECT sql_id,<\/span> child_number
\n  2<\/span>    FROM v$sql<\/span>
\n  3<\/span>   WHERE sql_text LIKE '%DBTW002%'<\/span>
\n  4<\/span>     AND sql_text NOT LIKE '%v$sql%'<\/span>;<\/span>
\n
\nSQL_ID           CHILD_NUMBER
\n----------------<\/span> ------------<\/span>
\nba75mduk0kz6q               0<\/span>
\n
\n1<\/span> linha selecionada.<\/span>
\n
\nSQL ><\/span>
\nSQL ><\/span> SELECT *<\/span>
\n  2<\/span>    FROM TABLE (<\/span>dbms_xplan.<\/span>display_cursor (<\/span>'&m_sql_id'<\/span>,&<\/span>m_child_no,<\/span>'typical iostats last'<\/span>)<\/span>)<\/span>;<\/span>
\n
\nPLAN_TABLE_OUTPUT
\n------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------<\/span>
\nSQL_ID  ba75mduk0kz6q,<\/span> child number 0<\/span>
\n-------------------------------------<\/span>
\nSELECT \/*+ index_join(c) *\/<\/span> \/* DBTW002 *\/<\/span>        c.<\/span>cust_id,<\/span>
\nc.<\/span>cust_first_name,<\/span> c.<\/span>cust_last_name   FROM sh.<\/span>customers c  WHERE
\nc.<\/span>cust_id >=:<\/span>"SYS_B_0"<\/span>    AND c.<\/span>cust_id <=:<\/span>"SYS_B_1"<\/span>    AND
\nc.<\/span>cust_last_name like :<\/span>"SYS_B_2"<\/span>
\n
\nPlan hash<\/span><\/a> value:<\/span> 1080046246<\/span>
\n
\n--------------------------------------------------------------------------------------------------------------------------------------<\/span>
\n|<\/span> Id  |<\/span> Operation               |<\/span> Name             |<\/span> Starts |<\/span> E-<\/span>Rows |<\/span>E-<\/span>Bytes|<\/span> Cost (<\/span>%<\/span>CPU)<\/span>|<\/span> E-<\/span>Time<\/span><\/a>   |<\/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>       |<\/span>   197<\/span> (<\/span>100<\/span>)<\/span>|<\/span>          |<\/span>      2<\/span> |<\/span>00<\/span>:<\/span>00<\/span>:<\/span>00.01<\/span> |<\/span>     203<\/span> |<\/span>
\n|*<\/span>  1<\/span> |<\/span>  FILTER                 |<\/span>                  |<\/span>      1<\/span> |<\/span>        |<\/span>       |<\/span>            |<\/span>          |<\/span>      2<\/span> |<\/span>00<\/span>:<\/span>00<\/span>:<\/span>00.01<\/span> |<\/span>     203<\/span> |<\/span>
\n|*<\/span>  2<\/span> |<\/span>   VIEW                  |<\/span> index$_join<\/span>$_001<\/span> |<\/span>      1<\/span> |<\/span>      1<\/span> |<\/span>    20<\/span> |<\/span>   197<\/span>   (<\/span>1<\/span>)<\/span>|<\/span> 00<\/span>:<\/span>00<\/span>:<\/span>03<\/span> |<\/span>      2<\/span> |<\/span>00<\/span>:<\/span>00<\/span>:<\/span>00.01<\/span> |<\/span>     203<\/span> |<\/span>
\n|*<\/span>  3<\/span> |<\/span>    HASH<\/span><\/a> JOIN<\/span><\/a>            |<\/span>                  |<\/span>      1<\/span> |<\/span>        |<\/span>       |<\/span>            |<\/span>          |<\/span>      2<\/span> |<\/span>00<\/span>:<\/span>00<\/span>:<\/span>00.01<\/span> |<\/span>     203<\/span> |<\/span>
\n|*<\/span>  4<\/span> |<\/span>     INDEX RANGE<\/span><\/a> SCAN    |<\/span> CUSTOMERS_PK     |<\/span>      1<\/span> |<\/span>      1<\/span> |<\/span>    20<\/span> |<\/span>     2<\/span>   (<\/span>0<\/span>)<\/span>|<\/span> 00<\/span>:<\/span>00<\/span>:<\/span>01<\/span> |<\/span>    301<\/span> |<\/span>00<\/span>:<\/span>00<\/span>:<\/span>00.01<\/span> |<\/span>       2<\/span> |<\/span>
\n|*<\/span>  5<\/span> |<\/span>     INDEX FAST FULL SCAN|<\/span> CUST_NAME_IDX    |<\/span>      1<\/span> |<\/span>      1<\/span> |<\/span>    20<\/span> |<\/span>   243<\/span>   (<\/span>0<\/span>)<\/span>|<\/span> 00<\/span>:<\/span>00<\/span>:<\/span>03<\/span> |<\/span>     82<\/span> |<\/span>00<\/span>:<\/span>00<\/span>:<\/span>00.01<\/span> |<\/span>     201<\/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   2<\/span> -<\/span> filter(<\/span>(<\/span>"C"<\/span>.<\/span>"CUST_ID"<\/span><=:<\/span>SYS_B_1 AND "C"<\/span>.<\/span>"CUST_ID"<\/span>>=:<\/span>SYS_B_0)<\/span>)<\/span>
\n   3<\/span> -<\/span> access(<\/span>ROWID=<\/span>ROWID)<\/span>
\n   4<\/span> -<\/span> access(<\/span>"C"<\/span>.<\/span>"CUST_ID"<\/span>>=:<\/span>SYS_B_0 AND "C"<\/span>.<\/span>"CUST_ID"<\/span><=:<\/span>SYS_B_1)<\/span>
\n   5<\/span> -<\/span> filter(<\/span>"C"<\/span>.<\/span>"CUST_LAST_NAME"<\/span> LIKE :<\/span>SYS_B_2)<\/span>
\n
\n
\n29<\/span> linhas selecionadas.<\/span>
\n
\nSQL ><\/span><\/div><\/td><\/tr><\/tbody><\/table><\/div>\n

 <\/p>\n

Comparando os dois planos de execu\u00e7\u00e3o verificamos que na primeira execu\u00e7\u00e3o da consulta a quantidade de buffers acessados foi 294 enquanto na segunda execu\u00e7\u00e3o foram acessados 203 buffers, o plano mostra que os dois \u00edndices foram acessados atrav\u00e9s da opera\u00e7\u00e3o HASH JOIN.<\/span><\/p>\n

2) Utilizando filtros de valor nulo<\/span><\/h2>\n

Quando criamos um \u00edndice em uma coluna da tabela onde existem registros com valores nulos, este \u00edndice ir\u00e1 reconhecer somente os registros ou linhas que n\u00e3o possuem valores nulos. Por esta raz\u00e3o quando executamos uma consulta com o filtro de valores nulos em uma determinada coluna, o plano de execu\u00e7\u00e3o desta consulta utilizar\u00e1 a opera\u00e7\u00e3o TABLE FULL SCAN mesmo que exista um \u00edndice para esta coluna e o numero de registros nulos seja pequeno. Abaixo podemos verificar um exemplo pr\u00e1tico dessa situa\u00e7\u00e3o:<\/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
<\/div><\/td>
SQL ><\/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
\n1<\/span> linha selecionada.<\/span>
\n
\nSQL ><\/span>
\nSQL ><\/span>
\nSQL ><\/span> create index CURSO01.<\/span>DBTW_OBJECTS_ID_IDX on CURSO01.<\/span>DBTW_OBJECTS(<\/span>OBJECT_ID)<\/span>;<\/span>
\n
\n\u00cdndice criado.<\/span>
\n
\nSQL ><\/span>
\nSQL ><\/span> set tab off;<\/span>
\nSQL ><\/span>
\nSQL ><\/span> ALTER SESSION SET statistics_level=<\/span>ALL;<\/span>
\n
\nSess\u00e3o alterada.<\/span>
\n
\nSQL ><\/span> ALTER SESSION SET current_schema=<\/span>CURSO01;<\/span>
\n
\nSess\u00e3o alterada.<\/span>
\n
\nSQL ><\/span>
\nSQL ><\/span> col OBJECT_NAME for<\/span> a30;<\/span>
\nSQL ><\/span> select \/* DBTW101 *\/<\/span> OBJECT_NAME,<\/span>
\n  2<\/span>         OBJECT_TYPE,<\/span>
\n  3<\/span>         CREATED
\n  4<\/span>    from DBTW_OBJECTS
\n  5<\/span>   where OBJECT_ID is null<\/span>;<\/span>
\n
\nOBJECT_NAME                    OBJECT_TYPE         CREATED
\n------------------------------<\/span> -------------------<\/span> --------<\/span>
\nYPKHOMO                        DATABASE LINK<\/span><\/a>       09<\/span>\/<\/span>05<\/span>\/<\/span>15<\/span>
\nAPPL338                        DATABASE LINK<\/span><\/a>       17<\/span>\/<\/span>12<\/span>\/<\/span>15<\/span>
\nAPPL359                        DATABASE LINK<\/span><\/a>       16<\/span>\/<\/span>12<\/span>\/<\/span>15<\/span>
\nXPTO_05                        DATABASE LINK<\/span><\/a>       09<\/span>\/<\/span>05<\/span>\/<\/span>15<\/span>
\nATIVO_YPK                      DATABASE LINK<\/span><\/a>       07<\/span>\/<\/span>10<\/span>\/<\/span>15<\/span>
\nAPPL306                        DATABASE LINK<\/span><\/a>       07<\/span>\/<\/span>10<\/span>\/<\/span>15<\/span>
\nYPK                            DATABASE LINK<\/span><\/a>       09<\/span>\/<\/span>05<\/span>\/<\/span>15<\/span>
\nAPPL308                        DATABASE LINK<\/span><\/a>       09<\/span>\/<\/span>05<\/span>\/<\/span>15<\/span>
\nAPPL302                        DATABASE LINK<\/span><\/a>       09<\/span>\/<\/span>05<\/span>\/<\/span>15<\/span>
\nDESENV_YPK                     DATABASE LINK<\/span><\/a>       25<\/span>\/<\/span>08<\/span>\/<\/span>15<\/span>
\nDESENV_XPTO                    DATABASE LINK<\/span><\/a>       17<\/span>\/<\/span>11<\/span>\/<\/span>15<\/span>
\n
\n11<\/span> linhas selecionadas.<\/span>
\n
\nSQL ><\/span>
\nSQL ><\/span> column sql_id new_value m_sql_id
\nSQL ><\/span> column child_number new_value m_child_no
\nSQL ><\/span> SELECT sql_id,<\/span> child_number
\n  2<\/span>    FROM v$sql<\/span>
\n  3<\/span>   WHERE sql_text LIKE '%DBTW101%'<\/span>
\n  4<\/span>     AND sql_text NOT LIKE '%v$sql%'<\/span>;<\/span>
\n
\nSQL_ID           CHILD_NUMBER
\n----------------<\/span> ------------<\/span>
\n7cqnxfyjkud1r               0<\/span>
\n
\n1<\/span> linha selecionada.<\/span>
\n
\nSQL ><\/span>
\nSQL ><\/span> SELECT *<\/span>
\n  2<\/span>    FROM TABLE (<\/span>dbms_xplan.<\/span>display_cursor (<\/span>'&m_sql_id'<\/span>,&<\/span>m_child_no,<\/span>'typical iostats last'<\/span>)<\/span>)<\/span>;<\/span>
\n
\nPLAN_TABLE_OUTPUT
\n------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------<\/span>
\nSQL_ID  7cqnxfyjkud1r,<\/span> child number 0<\/span>
\n-------------------------------------<\/span>
\nselect \/* DBTW101 *\/<\/span> OBJECT_NAME,<\/span>        OBJECT_TYPE,<\/span>        CREATED
\nfrom DBTW_OBJECTS  where OBJECT_ID is null<\/span>
\n
\nPlan hash<\/span><\/a> value:<\/span> 3768306837<\/span>
\n
\n----------------------------------------------------------------------------------------------------------------------------<\/span>
\n|<\/span> Id  |<\/span> Operation         |<\/span> Name         |<\/span> Starts |<\/span> E-<\/span>Rows |<\/span>E-<\/span>Bytes|<\/span> Cost (<\/span>%<\/span>CPU)<\/span>|<\/span> E-<\/span>Time<\/span><\/a>   |<\/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>       |<\/span>   476<\/span> (<\/span>100<\/span>)<\/span>|<\/span>          |<\/span>     11<\/span> |<\/span>00<\/span>:<\/span>00<\/span>:<\/span>00.01<\/span> |<\/span>    1717<\/span> |<\/span>
\n|*<\/span>  1<\/span> |<\/span>  TABLE ACCESS FULL|<\/span> DBTW_OBJECTS |<\/span>      1<\/span> |<\/span>     11<\/span> |<\/span>   495<\/span> |<\/span>   476<\/span>   (<\/span>1<\/span>)<\/span>|<\/span> 00<\/span>:<\/span>00<\/span>:<\/span>06<\/span> |<\/span>     11<\/span> |<\/span>00<\/span>:<\/span>00<\/span>:<\/span>00.01<\/span> |<\/span>    1717<\/span> |<\/span>
\n----------------------------------------------------------------------------------------------------------------------------<\/span>
\n
\nPredicate Information (<\/span>identified by operation id)<\/span>:<\/span>
\n---------------------------------------------------<\/span>
\n
\n   1<\/span> -<\/span> filter(<\/span>"OBJECT_ID"<\/span> IS NULL<\/span>)<\/span>
\n
\n
\n19<\/span> linhas selecionadas.<\/span>
\n
\nSQL ><\/span><\/div><\/td><\/tr><\/tbody><\/table><\/div>\n

 <\/p>\n

Para permitir que o \u00edndice trate os registros que apresentam valores nulos na coluna especificada, podemos criar um \u00edndice composto onde o primeiro campo \u00e9 a coluna da tabela que queremos indexar e o segundo campo e um valor fixo qualquer. Abaixo recriamos o \u00edndice utilizando esta t\u00e9cnica e executamos a consulta novamente:<\/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
<\/div><\/td>
SQL ><\/span> drop index CURSO01.<\/span>DBTW_OBJECTS_ID_IDX;<\/span>
\n\u00cdndice eliminado.<\/span>
\n
\nSQL ><\/span>
\nSQL ><\/span> create index CURSO01.<\/span>DBTW_OBJECTS_ID_IDX on CURSO01.<\/span>DBTW_OBJECTS(<\/span>OBJECT_ID,<\/span>0<\/span>)<\/span>;<\/span>
\n
\n\u00cdndice criado.<\/span>
\n
\nSQL ><\/span> set tab off;<\/span>
\nSQL ><\/span>
\nSQL ><\/span> ALTER SESSION SET statistics_level=<\/span>ALL;<\/span>
\n
\nSess\u00e3o alterada.<\/span>
\n
\nSQL ><\/span> ALTER SESSION SET current_schema=<\/span>CURSO01;<\/span>
\n
\nSess\u00e3o alterada.<\/span>
\n
\nSQL ><\/span>
\nSQL ><\/span>
\nSQL ><\/span> select \/* DBTW102 *\/<\/span> OBJECT_NAME,<\/span>
\n  2<\/span>         OBJECT_TYPE,<\/span>
\n  3<\/span>         CREATED
\n  4<\/span>    from DBTW_OBJECTS
\n  5<\/span>   where OBJECT_ID is null<\/span>;<\/span>
\n
\nOBJECT_NAME                    OBJECT_TYPE         CREATED
\n------------------------------<\/span> -------------------<\/span> --------<\/span>
\nYPKHOMO                        DATABASE LINK<\/span><\/a>       09<\/span>\/<\/span>05<\/span>\/<\/span>15<\/span>
\nAPPL338                        DATABASE LINK<\/span><\/a>       17<\/span>\/<\/span>12<\/span>\/<\/span>15<\/span>
\nAPPL359                        DATABASE LINK<\/span><\/a>       16<\/span>\/<\/span>12<\/span>\/<\/span>15<\/span>
\nXPTO_05                        DATABASE LINK<\/span><\/a>       09<\/span>\/<\/span>05<\/span>\/<\/span>15<\/span>
\nATIVO_YPK                      DATABASE LINK<\/span><\/a>       07<\/span>\/<\/span>10<\/span>\/<\/span>15<\/span>
\nAPPL306                        DATABASE LINK<\/span><\/a>       07<\/span>\/<\/span>10<\/span>\/<\/span>15<\/span>
\nYPK                            DATABASE LINK<\/span><\/a>       09<\/span>\/<\/span>05<\/span>\/<\/span>15<\/span>
\nAPPL308                        DATABASE LINK<\/span><\/a>       09<\/span>\/<\/span>05<\/span>\/<\/span>15<\/span>
\nAPPL302                        DATABASE LINK<\/span><\/a>       09<\/span>\/<\/span>05<\/span>\/<\/span>15<\/span>
\nDESENV_YPK                     DATABASE LINK<\/span><\/a>       25<\/span>\/<\/span>08<\/span>\/<\/span>15<\/span>
\nDESENV_XPTO                    DATABASE LINK<\/span><\/a>       17<\/span>\/<\/span>11<\/span>\/<\/span>15<\/span>
\n
\n11<\/span> linhas selecionadas.<\/span>
\n
\nSQL ><\/span>
\nSQL ><\/span> column sql_id new_value m_sql_id
\nSQL ><\/span> column child_number new_value m_child_no
\nSQL ><\/span> SELECT sql_id,<\/span> child_number
\n  2<\/span>    FROM v$sql<\/span>
\n  3<\/span>   WHERE sql_text LIKE '%DBTW102%'<\/span>
\n  4<\/span>     AND sql_text NOT LIKE '%v$sql%'<\/span>;<\/span>
\n
\nSQL_ID           CHILD_NUMBER
\n----------------<\/span> ------------<\/span>
\n0x60spb2w32s6               0<\/span>
\n
\n1<\/span> linha selecionada.<\/span>
\n
\nSQL ><\/span>
\nSQL ><\/span> SELECT *<\/span>
\n  2<\/span>    FROM TABLE (<\/span>dbms_xplan.<\/span>display_cursor (<\/span>'&m_sql_id'<\/span>,&<\/span>m_child_no,<\/span>'typical iostats last'<\/span>)<\/span>)<\/span>;<\/span>
\n
\nPLAN_TABLE_OUTPUT
\n------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------<\/span>
\nSQL_ID  0x60spb2w32s6,<\/span> child number 0<\/span>
\n-------------------------------------<\/span>
\nselect \/* DBTW102 *\/<\/span> OBJECT_NAME,<\/span>        OBJECT_TYPE,<\/span>        CREATED
\nfrom DBTW_OBJECTS  where OBJECT_ID is null<\/span>
\n
\nPlan hash<\/span><\/a> value:<\/span> 3646832289<\/span>
\n
\n---------------------------------------------------------------------------------------------------------------------------------------------<\/span>
\n|<\/span> Id  |<\/span> Operation                   |<\/span> Name                |<\/span> Starts |<\/span> E-<\/span>Rows |<\/span>E-<\/span>Bytes|<\/span> Cost (<\/span>%<\/span>CPU)<\/span>|<\/span> E-<\/span>Time<\/span><\/a>   |<\/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>       |<\/span>     3<\/span> (<\/span>100<\/span>)<\/span>|<\/span>          |<\/span>     11<\/span> |<\/span>00<\/span>:<\/span>00<\/span>:<\/span>00.01<\/span> |<\/span>       5<\/span> |<\/span>
\n|<\/span>   1<\/span> |<\/span>  TABLE ACCESS BY INDEX ROWID|<\/span> DBTW_OBJECTS        |<\/span>      1<\/span> |<\/span>     11<\/span> |<\/span>   495<\/span> |<\/span>     3<\/span>   (<\/span>0<\/span>)<\/span>|<\/span> 00<\/span>:<\/span>00<\/span>:<\/span>01<\/span> |<\/span>     11<\/span> |<\/span>00<\/span>:<\/span>00<\/span>:<\/span>00.01<\/span> |<\/span>       5<\/span> |<\/span>
\n|*<\/span>  2<\/span> |<\/span>   INDEX RANGE<\/span><\/a> SCAN          |<\/span> DBTW_OBJECTS_ID_IDX |<\/span>      1<\/span> |<\/span>     11<\/span> |<\/span>       |<\/span>     2<\/span>   (<\/span>0<\/span>)<\/span>|<\/span> 00<\/span>:<\/span>00<\/span>:<\/span>01<\/span> |<\/span>     11<\/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   2<\/span> -<\/span> access(<\/span>"OBJECT_ID"<\/span> IS NULL<\/span>)<\/span>
\n
\n
\n20<\/span> linhas selecionadas.<\/span>
\n
\nSQL ><\/span><\/div><\/td><\/tr><\/tbody><\/table><\/div>\n

 <\/p>\n

Agora podemos observar que no plano de execu\u00e7\u00e3o acima o \u00edndice foi utilizado e o desempenho da consulta foi muito melhor que na primeira execu\u00e7\u00e3o, a quantidade de buffers lidos caiu de 1717 para 5 buffers.<\/span><\/p>\n

3) Utilizando fun\u00e7\u00f5es na cl\u00e1usula WHERE<\/span><\/h2>\n

N\u00e3o \u00e9 incomum encontrarmos instru\u00e7\u00f5es SQL utilizando fun\u00e7\u00f5es como filtro na cl\u00e1usula WHERE e na maioria desses casos verificamos que o plano de execu\u00e7\u00e3o acaba executando uma opera\u00e7\u00e3o de TABLE FULL SCAN na tabela para recuperar os registros que atendam ao filtro da fun\u00e7\u00e3o, como no exemplo abaixo:<\/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
<\/div><\/td>
SQL ><\/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
\n1<\/span> linha selecionada.<\/span>
\n
\nSQL ><\/span>
\nSQL ><\/span>
\nSQL ><\/span> set tab off;<\/span>
\nSQL ><\/span>
\nSQL ><\/span> ALTER SESSION SET statistics_level=<\/span>ALL;<\/span>
\n
\nSess\u00e3o alterada.<\/span>
\n
\nSQL ><\/span> ALTER SESSION SET current_schema=<\/span>SH;<\/span>
\n
\nSess\u00e3o alterada.<\/span>
\n
\nSQL ><\/span>
\nSQL ><\/span>
\nSQL ><\/span> select \/* DBTW301 *\/<\/span> PROD_ID,<\/span>
\n  2<\/span>         (<\/span>quantity_sold *<\/span> amount_sold)<\/span> total_sold
\n  3<\/span>    from sales
\n  4<\/span>   where (<\/span>quantity_sold *<\/span> amount_sold)<\/span> ><\/span> 5000<\/span>;<\/span>
\n
\n   PROD_ID TOTAL_SOLD
\n----------<\/span> ----------<\/span>
\n        13<\/span>   13553<\/span>,<\/span>76<\/span>
\n        13<\/span>   13562<\/span>,<\/span>89<\/span>
\n        13<\/span>   13562<\/span>,<\/span>89<\/span>
\n        13<\/span>   13265<\/span>,<\/span>89<\/span>
\n
\n4<\/span> linhas selecionadas.<\/span>
\n
\nSQL ><\/span>
\nSQL ><\/span> column sql_id new_value m_sql_id
\nSQL ><\/span> column child_number new_value m_child_no
\nSQL ><\/span> SELECT sql_id,<\/span> child_number
\n  2<\/span>    FROM v$sql<\/span>
\n  3<\/span>   WHERE sql_text LIKE '%DBTW301%'<\/span>
\n  4<\/span>     AND sql_text NOT LIKE '%v$sql%'<\/span>;<\/span>
\n
\nSQL_ID           CHILD_NUMBER
\n----------------<\/span> ------------<\/span>
\na4c214bz2rgb0               0<\/span>
\n
\n1<\/span> linha selecionada.<\/span>
\n
\nSQL ><\/span>
\nSQL ><\/span> SELECT *<\/span>
\n  2<\/span>    FROM TABLE (<\/span>dbms_xplan.<\/span>display_cursor (<\/span>'&m_sql_id'<\/span>,&<\/span>m_child_no,<\/span>'typical iostats last'<\/span>)<\/span>)<\/span>;<\/span>
\n
\nPLAN_TABLE_OUTPUT
\n------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------<\/span>
\nSQL_ID  a4c214bz2rgb0,<\/span> child number 0<\/span>
\n-------------------------------------<\/span>
\nselect \/* DBTW301 *\/<\/span> PROD_ID,<\/span>        (<\/span>quantity_sold *<\/span> amount_sold)<\/span>
\ntotal_sold   from sales  where (<\/span>quantity_sold *<\/span> amount_sold)<\/span> ><\/span>
\n:<\/span>"SYS_B_0"<\/span>
\n
\nPlan hash<\/span><\/a> value:<\/span> 1550251865<\/span>
\n
\n------------------------------------------------------------------------------------------------------------------------------------------------<\/span>
\n|<\/span> Id  |<\/span> Operation           |<\/span> Name  |<\/span> Starts |<\/span> E-<\/span>Rows |<\/span>E-<\/span>Bytes|<\/span> Cost (<\/span>%<\/span>CPU)<\/span>|<\/span> E-<\/span>Time<\/span><\/a>   |<\/span> Pstart|<\/span> Pstop |<\/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>       |<\/span>  1292<\/span> (<\/span>100<\/span>)<\/span>|<\/span>          |<\/span>       |<\/span>       |<\/span>      4<\/span> |<\/span>00<\/span>:<\/span>00<\/span>:<\/span>00.87<\/span> |<\/span>    4472<\/span> |<\/span>   4439<\/span> |<\/span>
\n|<\/span>   1<\/span> |<\/span>  PARTITION RANGE<\/span><\/a> ALL|<\/span>       |<\/span>      1<\/span> |<\/span>  45942<\/span> |<\/span>   538K|<\/span>  1292<\/span>   (<\/span>1<\/span>)<\/span>|<\/span> 00<\/span>:<\/span>00<\/span>:<\/span>16<\/span> |<\/span>     1<\/span> |<\/span>    28<\/span> |<\/span>      4<\/span> |<\/span>00<\/span>:<\/span>00<\/span>:<\/span>00.87<\/span> |<\/span>    4472<\/span> |<\/span>   4439<\/span> |<\/span>
\n|*<\/span>  2<\/span> |<\/span>   TABLE ACCESS FULL |<\/span> SALES |<\/span>     28<\/span> |<\/span>  45942<\/span> |<\/span>   538K|<\/span>  1292<\/span>   (<\/span>1<\/span>)<\/span>|<\/span> 00<\/span>:<\/span>00<\/span>:<\/span>16<\/span> |<\/span>     1<\/span> |<\/span>    28<\/span> |<\/span>      4<\/span> |<\/span>00<\/span>:<\/span>00<\/span>:<\/span>00.87<\/span> |<\/span>    4472<\/span> |<\/span>   4439<\/span> |<\/span>
\n------------------------------------------------------------------------------------------------------------------------------------------------<\/span>
\n
\nPredicate Information (<\/span>identified by operation id)<\/span>:<\/span>
\n---------------------------------------------------<\/span>
\n
\n   2<\/span> -<\/span> filter(<\/span>"QUANTITY_SOLD"<\/span>*<\/span>"AMOUNT_SOLD"<\/span>>:<\/span>SYS_B_0)<\/span>
\n
\n
\n21<\/span> linhas selecionadas.<\/span>
\n
\nSQL ><\/span><\/div><\/td><\/tr><\/tbody><\/table><\/div>\n

 <\/p>\n

A boa noticia \u00e9 que podemos criar um \u00edndice para este tipo de situa\u00e7\u00e3o e com ele tornar o desempenho da consulta muito melhor, veja a seguir como podemos criar um Function Base Index (FBI) que torna da execu\u00e7\u00e3o desta consulta muito mais eficiente.<\/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
<\/div><\/td>
SQL ><\/span> create index sh.<\/span>sales_total_IDX on sh.<\/span>sales(<\/span>quantity_sold *<\/span> amount_sold)<\/span>;<\/span>
\n
\n\u00cdndice criado.<\/span>
\n
\nSQL ><\/span>
\nSQL ><\/span> set tab off;<\/span>
\nSQL ><\/span>
\nSQL ><\/span> ALTER SESSION SET statistics_level=<\/span>ALL;<\/span>
\n
\nSess\u00e3o alterada.<\/span>
\n
\nSQL ><\/span> ALTER SESSION SET current_schema=<\/span>SH;<\/span>
\n
\nSess\u00e3o alterada.<\/span>
\n
\nSQL ><\/span>
\nSQL ><\/span>
\nSQL ><\/span> select \/* DBTW302 *\/<\/span> PROD_ID,<\/span>
\n  2<\/span>         (<\/span>quantity_sold *<\/span> amount_sold)<\/span> total_sold
\n  3<\/span>    from sales
\n  4<\/span>   where (<\/span>quantity_sold *<\/span> amount_sold)<\/span> ><\/span> 5000<\/span>;<\/span>
\n
\n   PROD_ID TOTAL_SOLD
\n----------<\/span> ----------<\/span>
\n        13<\/span>   13265<\/span>,<\/span>89<\/span>
\n        13<\/span>   13553<\/span>,<\/span>76<\/span>
\n        13<\/span>   13562<\/span>,<\/span>89<\/span>
\n        13<\/span>   13562<\/span>,<\/span>89<\/span>
\n
\n4<\/span> linhas selecionadas.<\/span>
\n
\nSQL ><\/span>
\nSQL ><\/span> column sql_id new_value m_sql_id
\nSQL ><\/span> column child_number new_value m_child_no
\nSQL ><\/span> SELECT sql_id,<\/span> child_number
\n  2<\/span>    FROM v$sql<\/span>
\n  3<\/span>   WHERE sql_text LIKE '%DBTW302%'<\/span>
\n  4<\/span>     AND sql_text NOT LIKE '%v$sql%'<\/span>;<\/span>
\n
\nSQL_ID           CHILD_NUMBER
\n----------------<\/span> ------------<\/span>
\nb171zy5vbtm1u               0<\/span>
\n
\n1<\/span> linha selecionada.<\/span>
\n
\nSQL ><\/span>
\nSQL ><\/span> SELECT *<\/span>
\n  2<\/span>    FROM TABLE (<\/span>dbms_xplan.<\/span>display_cursor (<\/span>'&m_sql_id'<\/span>,&<\/span>m_child_no,<\/span>'typical iostats last'<\/span>)<\/span>)<\/span>;<\/span>
\n
\nPLAN_TABLE_OUTPUT
\n------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------<\/span>
\nSQL_ID  b171zy5vbtm1u,<\/span> child number 0<\/span>
\n-------------------------------------<\/span>
\nselect \/* DBTW302 *\/<\/span> PROD_ID,<\/span>        (<\/span>quantity_sold *<\/span> amount_sold)<\/span>
\ntotal_sold   from sales  where (<\/span>quantity_sold *<\/span> amount_sold)<\/span> ><\/span>
\n:<\/span>"SYS_B_0"<\/span>
\n
\nPlan hash<\/span><\/a> value:<\/span> 4182791213<\/span>
\n
\n----------------------------------------------------------------------------------------------------------------------------------------------------------------<\/span>
\n|<\/span> Id  |<\/span> Operation                          |<\/span> Name            |<\/span> Starts |<\/span> E-<\/span>Rows |<\/span>E-<\/span>Bytes|<\/span> Cost (<\/span>%<\/span>CPU)<\/span>|<\/span> E-<\/span>Time<\/span><\/a>   |<\/span> Pstart|<\/span> Pstop |<\/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>       |<\/span>   271<\/span> (<\/span>100<\/span>)<\/span>|<\/span>          |<\/span>       |<\/span>       |<\/span>      4<\/span> |<\/span>00<\/span>:<\/span>00<\/span>:<\/span>00.01<\/span> |<\/span>       8<\/span> |<\/span>
\n|<\/span>   1<\/span> |<\/span>  TABLE ACCESS BY GLOBAL<\/span> INDEX ROWID|<\/span> SALES           |<\/span>      1<\/span> |<\/span>  45942<\/span> |<\/span>   762K|<\/span>   271<\/span>   (<\/span>0<\/span>)<\/span>|<\/span> 00<\/span>:<\/span>00<\/span>:<\/span>04<\/span> |<\/span> ROWID |<\/span> ROWID |<\/span>      4<\/span> |<\/span>00<\/span>:<\/span>00<\/span>:<\/span>00.01<\/span> |<\/span>       8<\/span> |<\/span>
\n|*<\/span>  2<\/span> |<\/span>   INDEX RANGE<\/span><\/a> SCAN                 |<\/span> SALES_TOTAL_IDX |<\/span>      1<\/span> |<\/span>   8270<\/span> |<\/span>       |<\/span>    25<\/span>   (<\/span>0<\/span>)<\/span>|<\/span> 00<\/span>:<\/span>00<\/span>:<\/span>01<\/span> |<\/span>       |<\/span>       |<\/span>      4<\/span> |<\/span>00<\/span>:<\/span>00<\/span>:<\/span>00.01<\/span> |<\/span>       5<\/span> |<\/span>
\n----------------------------------------------------------------------------------------------------------------------------------------------------------------<\/span>
\n
\nPredicate Information (<\/span>identified by operation id)<\/span>:<\/span>
\n---------------------------------------------------<\/span>
\n
\n   2<\/span> -<\/span> access(<\/span>"SALES"<\/span>.<\/span>"SYS_NC00009$"<\/span>>:<\/span>SYS_B_0)<\/span>
\n
\n
\n21<\/span> linhas selecionadas.<\/span>
\n
\nSQL ><\/span><\/div><\/td><\/tr><\/tbody><\/table><\/div>\n

 <\/p>\n

Quando examinamos o plano de execu\u00e7\u00e3o verificamos que o \u00edndice criado foi utilizado e o desempenho da consulta foi bem melhor que na primeira execu\u00e7\u00e3o onde o numero de buffers lidos foi 4472 contra 8 buffers lidos no plano que utilizou o \u00edndice.<\/span><\/p>\n

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

https:\/\/docs.oracle.com\/cloud\/latest\/db112\/SQLRF\/statements_5012.htm#SQLRF01209<\/a><\/span><\/strong>
\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":"

A utiliza\u00e7\u00e3o do \u00edndice B-TREE com uma\u00a0coluna ou com um grupo de colunas \u00e9 muito comum nas aplica\u00e7\u00f5es, estas colunas s\u00e3o referenciadas na cl\u00e1usula WHERE das instru\u00e7\u00f5es SQL e possibilitam uma r\u00e1pida recupera\u00e7\u00e3o dos dados que o usu\u00e1rio deseja, mas existem outras formas de utilizar os \u00edndices que s\u00e3o pouco empregadas no dia a dia, mas que podem melhorar muito<\/p>\n","protected":false},"author":2,"featured_media":1103,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[62],"tags":[170,169,171,82,10,29],"class_list":["post-1087","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-indice","tag-function-base-index","tag-index-join","tag-is-null","tag-optimizer","tag-otimizador","tag-performance-tuning"],"aioseo_notices":[],"_links":{"self":[{"href":"http:\/\/dbtimewizard.com.br\/blog\/wp-json\/wp\/v2\/posts\/1087","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=1087"}],"version-history":[{"count":22,"href":"http:\/\/dbtimewizard.com.br\/blog\/wp-json\/wp\/v2\/posts\/1087\/revisions"}],"predecessor-version":[{"id":2084,"href":"http:\/\/dbtimewizard.com.br\/blog\/wp-json\/wp\/v2\/posts\/1087\/revisions\/2084"}],"wp:featuredmedia":[{"embeddable":true,"href":"http:\/\/dbtimewizard.com.br\/blog\/wp-json\/wp\/v2\/media\/1103"}],"wp:attachment":[{"href":"http:\/\/dbtimewizard.com.br\/blog\/wp-json\/wp\/v2\/media?parent=1087"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"http:\/\/dbtimewizard.com.br\/blog\/wp-json\/wp\/v2\/categories?post=1087"},{"taxonomy":"post_tag","embeddable":true,"href":"http:\/\/dbtimewizard.com.br\/blog\/wp-json\/wp\/v2\/tags?post=1087"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}