{"id":1503,"date":"2018-04-15T21:30:31","date_gmt":"2018-04-16T00:30:31","guid":{"rendered":"http:\/\/dbtimewizard.com.br\/blog\/?p=1503"},"modified":"2019-11-11T18:46:08","modified_gmt":"2019-11-11T21:46:08","slug":"indice-desordenado-isto-e-possivel","status":"publish","type":"post","link":"http:\/\/dbtimewizard.com.br\/blog\/indice-desordenado-isto-e-possivel\/","title":{"rendered":"\u00cdndice desordenado! Isto \u00e9 poss\u00edvel?"},"content":{"rendered":"

Quando executamos uma consulta no banco de dados Oracle e no seu plano de execu\u00e7\u00e3o aparece a opera\u00e7\u00e3o INDEX RANGE SCAN, os dados recuperados da tabela deveriam estar ordernados pela chave do \u00edndice, mas esta afirma\u00e7\u00e3o encontrada inclusive no manual da Oracle nem sempre \u00e9 verdadeira. Esta premissa \u00e9 muito importante pois a utiliza\u00e7\u00e3o da cla\u00fasula ORDER BY na consulta for\u00e7a o plano de execu\u00e7\u00e3o a recuperar as linhas da tabela de forma ordenada e se o \u00edndice n\u00e3o garantir essa ordena\u00e7\u00e3o o plano de execu\u00e7\u00e3o vai realizar uma opera\u00e7\u00e3o adicional de SORT, tornando o plano de execu\u00e7\u00e3o mais oneroso.<\/span><\/p>\n

Neste artigo vamos ver em que situa\u00e7\u00e3o a opera\u00e7\u00e3o INDEX RANGE SCAN recupera as linhas fora de ordem e o que \u00e9 necess\u00e1rio fazer para garantir que a linhas sejam recuperadas em ordem, evitando uma opera\u00e7\u00e3o adicional no plano de execu\u00e7\u00e3o para ordenar a linhas.<\/span><\/p>\n

Para realizar esta simula\u00e7\u00e3o vamos:<\/span><\/p>\n

1) Criar 1 tabela com um indice composto<\/span>
\n2) Executar a consulta e verificar o plano de execu\u00e7\u00e3o<\/span>
\n3) Verificar porque as linhas recuperadas pelo \u00edndice n\u00e3o est\u00e3o em ordem<\/span>
\n4) Executar a consulta novamente e verificar se a opera\u00e7\u00e3o SORT foi eliminada do plano de execu\u00e7\u00e3o<\/span><\/p>\n

1) Criar a tabela da simula\u00e7\u00e3o<\/span><\/h2>\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
<\/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
\nSQL ><\/span>
\nSQL ><\/span> -----------------------<\/span>
\nSQL ><\/span> --<\/span> Defini\u00e7\u00e3o da tabela
\nSQL ><\/span> -----------------------<\/span>
\nSQL ><\/span>
\nSQL ><\/span> CREATE TABLE dbtw_list (<\/span>
\n  2<\/span>     Id          NUMBER        NOT NULL<\/span>,<\/span>
\n  3<\/span>     First_Name  VARCHAR2(<\/span>100<\/span>)<\/span> NOT NULL<\/span>,<\/span>
\n  4<\/span>     Last_Name   VARCHAR2(<\/span>100<\/span>)<\/span> NOT NULL<\/span>,<\/span>
\n  5<\/span>     Description VARCHAR2(<\/span>100<\/span>)<\/span> NULL<\/span>
\n  6<\/span>  )<\/span>;<\/span>
\n
\nTabela criada.<\/span>
\n
\nSQL ><\/span>
\nSQL ><\/span> ---------------------------<\/span>
\nSQL ><\/span> --<\/span> Carga de dados na tabela
\nSQL ><\/span> ---------------------------<\/span>
\nSQL ><\/span>
\nSQL ><\/span> BEGIN
\n  2<\/span>     FOR<\/span> counter IN 1<\/span> ..<\/span> 10000<\/span> LOOP
\n  3<\/span>        INSERT INTO dbtw_list (<\/span>Id,<\/span> First_Name,<\/span> Last_Name,<\/span> Description)<\/span>
\n  4<\/span>           VALUES (<\/span>counter,<\/span>
\n  5<\/span>                   DBMS_RANDOM.<\/span>STRING(<\/span>'A'<\/span>,<\/span> 10<\/span>)<\/span>,<\/span>
\n  6<\/span>                   DBMS_RANDOM.<\/span>STRING(<\/span>'A'<\/span>,<\/span> 20<\/span>)<\/span>,<\/span>
\n  7<\/span>                   DBMS_RANDOM.<\/span>STRING(<\/span>'A'<\/span>,<\/span> 100<\/span>)<\/span>)<\/span>;<\/span>
\n  8<\/span>     END<\/span><\/a> LOOP;<\/span>
\n  9<\/span>  END<\/span><\/a>;<\/span>
\n 10<\/span>  \/<\/span>
\n
\nProcedimento PL\/<\/span>SQL conclu\u00eddo<\/span> com sucesso.<\/span>
\n
\nSQL ><\/span> ---------------------<\/span>
\nSQL ><\/span> --<\/span> Cria\u00e7\u00e3o do<\/span> \u00edndice
\nSQL ><\/span> ---------------------<\/span>
\nSQL ><\/span>
\nSQL ><\/span> CREATE INDEX dbtw_list_idx ON dbtw_list (<\/span>Id,<\/span> First_Name)<\/span>;<\/span>
\n
\n\u00cdndice criado.<\/span>
\n
\nSQL ><\/span>
\nSQL ><\/span> -------------------------<\/span>
\nSQL ><\/span> --<\/span> Coleta de estat\u00edsticas
\nSQL ><\/span> -------------------------<\/span>
\nSQL ><\/span>
\nSQL ><\/span> exec<\/span><\/a> dbms_stats.<\/span>gather_table_stats(<\/span>ownname =><\/span> USER,<\/span>tabname =><\/span> 'DBTW_LIST'<\/span>,<\/span>method_opt=><\/span>'for all columns size auto'<\/span>,<\/span>cascade=><\/span>true<\/span>,<\/span>force=><\/span>true<\/span>,<\/span>degree=><\/span>16<\/span>)<\/span>;<\/span>
\n
\nProcedimento PL\/<\/span>SQL conclu\u00eddo<\/span> com sucesso.<\/span>
\n
\nSQL ><\/span><\/div><\/td><\/tr><\/tbody><\/table><\/div>\n

 <\/p>\n

2) Executar a consulta e verificar o plano de execu\u00e7\u00e3o<\/span><\/h2>\n

Vamos realizar uma consulta na tabela incluindo no filtro da cl\u00e1usula WHERE a primeira coluna do \u00edndice criado.<\/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
82
<\/div><\/td>
SQL ><\/span> ALTER SESSION SET statistics_level =<\/span> ALL;<\/span>
\n
\nSess\u00e3o alterada.<\/span>
\n
\nSQL ><\/span> ALTER SESSION SET optimizer_mode =<\/span> FIRST_ROWS_10;<\/span>
\n
\nSess\u00e3o alterada.<\/span>
\n
\nSQL ><\/span>
\nSQL ><\/span> set arraysize 100<\/span>
\nSQL ><\/span> col First_Name for<\/span> a20
\nSQL ><\/span> col Last_Name  for<\/span> a30
\nSQL ><\/span>
\nSQL ><\/span> SELECT \/* DBTW101 *\/<\/span> id,<\/span> First_Name,<\/span> Last_Name
\n  2<\/span>    FROM dbtw_list
\n  3<\/span>   WHERE id BETWEEN 9500<\/span> AND 9600<\/span>
\n  4<\/span>   ORDER BY Id,<\/span> First_Name;<\/span>
\n
\n        ID FIRST_NAME           LAST_NAME
\n----------<\/span> --------------------<\/span> ------------------------------<\/span>
\n      9500<\/span> YgXpCiWvyd           RuOdUtvJkKZKFmCbDXiC
\n      9501<\/span> zggUcAYKFV           UefhYFwDzrOcLquPlBek
\n      9502<\/span> ZpehlGozsl           lrXlhagateiceAAjihMo
\n      .<\/span>
\n      .<\/span>
\n      .<\/span>
\n      9598<\/span> yCGMWLTsrT           OLzOZMZDxyNSoyRZQmYo
\n      9599<\/span> zlwuHPyIWY           vrzhHzjeiApdWRujSFze
\n      9600<\/span> GVCDosOoqE           LKUXKEaSZctofrMrDljI
\n
\n101<\/span> linhas selecionadas.<\/span>
\n
\nSQL ><\/span>
\nSQL ><\/span>
\nSQL ><\/span> --<\/span> Recupera o SQL_ID e o CHILD_NUMBER  da consulta executada
\nSQL ><\/span> column sql_id new_value m_sql_id
\nSQL ><\/span> column child_number new_value m_child_no
\nSQL ><\/span>
\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>
\nb9yvxn9d08gd8            0<\/span>
\n
\nSQL ><\/span>
\nSQL ><\/span> --<\/span> Gera o relat\u00f3rio do<\/span> plano de execu\u00e7\u00e3o da consulta
\nSQL ><\/span> SELECT *<\/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>
\nantigo   1<\/span>:<\/span> SELECT *<\/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>
\nnovo   1<\/span>:<\/span> SELECT *<\/span> FROM TABLE (<\/span>dbms_xplan.<\/span>display_cursor (<\/span>'b9yvxn9d08gd8'<\/span>,<\/span>         0<\/span>,<\/span>'typical iostats last'<\/span>)<\/span>)<\/span>
\n
\nPLAN_TABLE_OUTPUT
\n-----------------------------------------------------------------------------------------------------------------------------------------<\/span>
\nSQL_ID  b9yvxn9d08gd8,<\/span> child number 0<\/span>
\n-------------------------------------<\/span>
\nSELECT \/* DBTW101 *\/<\/span> id,<\/span> First_Name,<\/span> Last_Name   FROM dbtw_list  WHERE
\nid BETWEEN :<\/span>"SYS_B_0"<\/span> AND :<\/span>"SYS_B_1"<\/span>  ORDER BY Id,<\/span> First_Name
\n
\nPlan hash<\/span><\/a> value:<\/span> 2931546093<\/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>     6<\/span> (<\/span>100<\/span>)<\/span>|<\/span>          |<\/span>    101<\/span> |<\/span>00<\/span>:<\/span>00<\/span>:<\/span>00.01<\/span> |<\/span>       5<\/span> |<\/span>
\n|<\/span>   1<\/span> |<\/span>  SORT<\/span><\/a> ORDER BY                |<\/span>               |<\/span>      1<\/span> |<\/span>    102<\/span> |<\/span>  3672<\/span> |<\/span>     6<\/span>  (<\/span>17<\/span>)<\/span>|<\/span> 00<\/span>:<\/span>00<\/span>:<\/span>01<\/span> |<\/span>    101<\/span> |<\/span>00<\/span>:<\/span>00<\/span>:<\/span>00.01<\/span> |<\/span>       5<\/span> |<\/span>
\n|*<\/span>  2<\/span> |<\/span>   FILTER                      |<\/span>               |<\/span>      1<\/span> |<\/span>        |<\/span>       |<\/span>            |<\/span>          |<\/span>    101<\/span> |<\/span>00<\/span>:<\/span>00<\/span>:<\/span>00.01<\/span> |<\/span>       5<\/span> |<\/span>
\n|<\/span>   3<\/span> |<\/span>    TABLE ACCESS BY INDEX ROWID|<\/span> DBTW_LIST     |<\/span>      1<\/span> |<\/span>    102<\/span> |<\/span>  3672<\/span> |<\/span>     5<\/span>   (<\/span>0<\/span>)<\/span>|<\/span> 00<\/span>:<\/span>00<\/span>:<\/span>01<\/span> |<\/span>    101<\/span> |<\/span>00<\/span>:<\/span>00<\/span>:<\/span>00.01<\/span> |<\/span>       5<\/span> |<\/span>
\n|*<\/span>  4<\/span> |<\/span>     INDEX RANGE<\/span><\/a> SCAN          |<\/span> DBTW_LIST_IDX |<\/span>      1<\/span> |<\/span>    102<\/span> |<\/span>       |<\/span>     2<\/span>   (<\/span>0<\/span>)<\/span>|<\/span> 00<\/span>:<\/span>00<\/span>:<\/span>01<\/span> |<\/span>    101<\/span> |<\/span>00<\/span>:<\/span>00<\/span>:<\/span>00.01<\/span> |<\/span>       2<\/span> |<\/span>
\n-----------------------------------------------------------------------------------------------------------------------------------------<\/span>
\n
\nPredicate Information (<\/span>identified by operation id)<\/span>:<\/span>
\n---------------------------------------------------<\/span>
\n
\n   2<\/span> -<\/span> filter(<\/span>:<\/span>SYS_B_1>=:<\/span>SYS_B_0)<\/span>
\n   4<\/span> -<\/span> access(<\/span>"ID"<\/span>>=:<\/span>SYS_B_0 AND "ID"<\/span><=:<\/span>SYS_B_1)<\/span>
\n
\n
\n23<\/span> linhas selecionadas.<\/span>
\n
\nSQL ><\/span><\/div><\/td><\/tr><\/tbody><\/table><\/div>\n

 <\/p>\n

Verificando o plano de execu\u00e7\u00e3o acima constatamos que as linhas foram recuperadas da tabela utilizando a opera\u00e7\u00e3o INDEX RANGE SCAN no \u00edndice que criamos na etapa 1, mas o Otimizador incluiu no plano de execu\u00e7\u00e3o a opera\u00e7\u00e3o SORT ORDER BY pois considerou que as linhas recuperadas pelo \u00edndice n\u00e3o estavam na ordem adequada.<\/span><\/p>\n

3) Verificar porque as linhas n\u00e3o estavam em ordem<\/span><\/h2>\n

O par\u00e2metro NLS_SORT influencia o Otimizador na cria\u00e7\u00e3o do plano de execu\u00e7\u00e3o pois ele determina o crit\u00e9rio de ordem que a linhas devem ser retornadas para o usu\u00e1rio. O \u00edndice normal \u00e9 ordenado pelo valor bin\u00e1rio da chave ou das chaves que ele foi criado e quando o par\u00e2metro NLS_SORT esta definido numa l\u00edngua especifica a ordem das linhas no \u00edndice n\u00e3o ser\u00e1 correspondente a ordem lingu\u00edstica definida no par\u00e2metro NLS_SORT.<\/span><\/p>\n

Portanto para evitarmos que o Otimizador utilize no plano de execu\u00e7\u00e3o a opera\u00e7\u00e3o SORT ORDER BY precisamos que crit\u00e9rio de ordena\u00e7\u00e3o do \u00edndice seja compat\u00edvel com o par\u00e2metro NLS_SORT:<\/span><\/p>\n

1) Para um \u00edndice normal o par\u00e2metro NLS_SORT deve ser definido como BINARY<\/span>
\n2) Quando utilizamos o par\u00e2metro NLS_SORT para ajustar a ordem dos dados a uma l\u00edngua especifica precisamos criar um FUNCTION BASE INDEX utilizando o par\u00e2metro NLS_SORT para garantir que o \u00edndice ser\u00e1 ordenado conforme defini\u00e7\u00e3o lingu\u00edstica deste par\u00e2metro.<\/span><\/p>\n

 <\/p>\n

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
<\/div><\/td>
SQL ><\/span> show parameters nls_sort
\n
\nNAME                                 TYPE                             VALUE
\n------------------------------------<\/span> --------------------------------<\/span> ------------------------------<\/span>
\nnls_sort                             string                           WEST_EUROPEAN
\nSQL ><\/span>
\nSQL ><\/span> alter session set nls_sort=<\/span>BINARY;<\/span>
\n
\nSess\u00e3o alterada.<\/span>
\n
\nSQL ><\/span>
\nSQL ><\/span> show parameters nls_sort
\n
\nNAME                                 TYPE                             VALUE
\n------------------------------------<\/span> --------------------------------<\/span> ------------------------------<\/span>
\nnls_sort                             string                           BINARY
\nSQL ><\/span><\/div><\/td><\/tr><\/tbody><\/table><\/div>\n

 <\/p>\n

Observando os comandos acima podemos identificar porque o Otimizador incluiu uma opera\u00e7\u00e3o SORT ORDER BY no plano de execu\u00e7\u00e3o apresentado na etapa 2, o par\u00e2metro NLS_SORT estava definido como WEST_EUROPEAN e o \u00edndice utilizado era um \u00edndice normal. Para eliminar a necessidade da opera\u00e7\u00e3o SORT ORDER BY precisamos alterar o\u00a0 par\u00e2metro NLS_SORT para BINARY.<\/span><\/p>\n

4) Executar a consulta novamente e verificar o plano de execu\u00e7\u00e3o<\/span><\/h2>\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
<\/div><\/td>
SQL ><\/span> ALTER SESSION SET statistics_level =<\/span> ALL;<\/span>
\n
\nSess\u00e3o alterada.<\/span>
\n
\nSQL ><\/span> ALTER SESSION SET optimizer_mode =<\/span> FIRST_ROWS_10;<\/span>
\n
\nSess\u00e3o alterada.<\/span>
\n
\nSQL ><\/span>
\nSQL ><\/span> set arraysize 100<\/span>
\nSQL ><\/span> col First_Name for<\/span> a20
\nSQL ><\/span> col Last_Name  for<\/span> a30
\nSQL ><\/span>
\nSQL ><\/span> SELECT \/* DBTW102 *\/<\/span> id,<\/span> First_Name,<\/span> Last_Name
\n  2<\/span>    FROM dbtw_list
\n  3<\/span>   WHERE id BETWEEN 9500<\/span> AND 9600<\/span>
\n  4<\/span>   ORDER BY Id,<\/span> First_Name;<\/span>
\n
\n        ID FIRST_NAME           LAST_NAME
\n----------<\/span> --------------------<\/span> ------------------------------<\/span>
\n      9500<\/span> YgXpCiWvyd           RuOdUtvJkKZKFmCbDXiC
\n      9501<\/span> zggUcAYKFV           UefhYFwDzrOcLquPlBek
\n      9502<\/span> ZpehlGozsl           lrXlhagateiceAAjihMo
\n      .<\/span>
\n      .<\/span>
\n      .<\/span>
\n      9600<\/span> GVCDosOoqE           LKUXKEaSZctofrMrDljI
\n
\n101<\/span> linhas selecionadas.<\/span>
\n
\nSQL ><\/span>
\nSQL ><\/span>
\nSQL ><\/span> --<\/span> Recupera o SQL_ID e o CHILD_NUMBER  da consulta executada
\nSQL ><\/span> column sql_id new_value m_sql_id
\nSQL ><\/span> column child_number new_value m_child_no
\nSQL ><\/span>
\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>
\n25j6bnk571k1k            0<\/span>
\n
\nSQL ><\/span>
\nSQL ><\/span> --<\/span> Gera o relat\u00f3rio do<\/span> plano de execu\u00e7\u00e3o da consulta
\nSQL ><\/span> SELECT *<\/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>
\nantigo   1<\/span>:<\/span> SELECT *<\/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>
\nnovo   1<\/span>:<\/span> SELECT *<\/span> FROM TABLE (<\/span>dbms_xplan.<\/span>display_cursor (<\/span>'25j6bnk571k1k'<\/span>,<\/span>         0<\/span>,<\/span>'typical iostats last'<\/span>)<\/span>)<\/span>
\n
\nPLAN_TABLE_OUTPUT
\n----------------------------------------------------------------------------------------------------------------------------------------<\/span>
\nSQL_ID  25j6bnk571k1k,<\/span> child number 0<\/span>
\n-------------------------------------<\/span>
\nSELECT \/* DBTW102 *\/<\/span> id,<\/span> First_Name,<\/span> Last_Name   FROM dbtw_list  WHERE
\nid BETWEEN :<\/span>"SYS_B_0"<\/span> AND :<\/span>"SYS_B_1"<\/span>  ORDER BY Id,<\/span> First_Name
\n
\nPlan hash<\/span><\/a> value:<\/span> 2088179210<\/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>    101<\/span> |<\/span>00<\/span>:<\/span>00<\/span>:<\/span>00.01<\/span> |<\/span>       8<\/span> |<\/span>
\n|*<\/span>  1<\/span> |<\/span>  FILTER                      |<\/span>               |<\/span>      1<\/span> |<\/span>        |<\/span>       |<\/span>            |<\/span>          |<\/span>    101<\/span> |<\/span>00<\/span>:<\/span>00<\/span>:<\/span>00.01<\/span> |<\/span>       8<\/span> |<\/span>
\n|<\/span>   2<\/span> |<\/span>   TABLE ACCESS BY INDEX ROWID|<\/span> DBTW_LIST     |<\/span>      1<\/span> |<\/span>     13<\/span> |<\/span>   468<\/span> |<\/span>     3<\/span>   (<\/span>0<\/span>)<\/span>|<\/span> 00<\/span>:<\/span>00<\/span>:<\/span>01<\/span> |<\/span>    101<\/span> |<\/span>00<\/span>:<\/span>00<\/span>:<\/span>00.01<\/span> |<\/span>       8<\/span> |<\/span>
\n|*<\/span>  3<\/span> |<\/span>    INDEX RANGE<\/span><\/a> SCAN          |<\/span> DBTW_LIST_IDX |<\/span>      1<\/span> |<\/span>    102<\/span> |<\/span>       |<\/span>     2<\/span>   (<\/span>0<\/span>)<\/span>|<\/span> 00<\/span>:<\/span>00<\/span>:<\/span>01<\/span> |<\/span>    101<\/span> |<\/span>00<\/span>:<\/span>00<\/span>:<\/span>00.01<\/span> |<\/span>       4<\/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>"ID"<\/span>>=:<\/span>SYS_B_0 AND "ID"<\/span><=:<\/span>SYS_B_1)<\/span>
\n
\n
\n22<\/span> linhas selecionadas.<\/span>
\n
\nSQL ><\/span><\/div><\/td><\/tr><\/tbody><\/table><\/div>\n

 <\/p>\n

Analisando o plano de execu\u00e7\u00e3o na consulta acima, podemos constatar que a opera\u00e7\u00e3o SORT ORDER BY foi exclu\u00edda do plano. Caso fosse necess\u00e1rio manter o par\u00e2metro NLS_SORT com a defini\u00e7\u00e3o lingu\u00edstica existente, dever\u00edamos criar um FUNCTION BASE INDEX para garantir a compatibilidade entre o par\u00e2metro NLS_SORT e a ordem das chaves no \u00edndice, evitando assim a utiliza\u00e7\u00e3o de uma opera\u00e7\u00e3o adicional SORT no plano de execu\u00e7\u00e3o.<\/span><\/p>\n

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

https:\/\/docs.oracle.com\/cloud\/latest\/db112<\/a><\/span><\/strong><\/p>\n

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

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

Quando executamos uma consulta no banco de dados Oracle e no seu plano de execu\u00e7\u00e3o aparece a opera\u00e7\u00e3o INDEX RANGE SCAN, os dados recuperados da tabela deveriam estar ordernados pela chave do \u00edndice, mas esta afirma\u00e7\u00e3o encontrada inclusive no manual da Oracle nem sempre \u00e9 verdadeira. Esta premissa \u00e9 muito importante pois a utiliza\u00e7\u00e3o da cla\u00fasula ORDER BY na consulta<\/p>\n","protected":false},"author":2,"featured_media":1502,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[62],"tags":[36,195,11,196],"class_list":["post-1503","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-indice","tag-execution-plan","tag-nls_sort","tag-plano-de-execucao","tag-sort-order-by"],"aioseo_notices":[],"_links":{"self":[{"href":"http:\/\/dbtimewizard.com.br\/blog\/wp-json\/wp\/v2\/posts\/1503","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=1503"}],"version-history":[{"count":20,"href":"http:\/\/dbtimewizard.com.br\/blog\/wp-json\/wp\/v2\/posts\/1503\/revisions"}],"predecessor-version":[{"id":2077,"href":"http:\/\/dbtimewizard.com.br\/blog\/wp-json\/wp\/v2\/posts\/1503\/revisions\/2077"}],"wp:featuredmedia":[{"embeddable":true,"href":"http:\/\/dbtimewizard.com.br\/blog\/wp-json\/wp\/v2\/media\/1502"}],"wp:attachment":[{"href":"http:\/\/dbtimewizard.com.br\/blog\/wp-json\/wp\/v2\/media?parent=1503"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"http:\/\/dbtimewizard.com.br\/blog\/wp-json\/wp\/v2\/categories?post=1503"},{"taxonomy":"post_tag","embeddable":true,"href":"http:\/\/dbtimewizard.com.br\/blog\/wp-json\/wp\/v2\/tags?post=1503"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}