Índice desordenado! Isto é possível?

indice desordenado

Quando executamos uma consulta no banco de dados Oracle e no seu plano de execução aparece a operação INDEX RANGE SCAN, os dados recuperados da tabela deveriam estar ordernados pela chave do índice, mas esta afirmação encontrada inclusive no manual da Oracle nem sempre é verdadeira. Esta premissa é muito importante pois a utilização da claúsula ORDER BY na consulta força o plano de execução a recuperar as linhas da tabela de forma ordenada e se o índice não garantir essa ordenação o plano de execução vai realizar uma operação adicional de SORT, tornando o plano de execução mais oneroso.

Neste artigo vamos ver em que situação a operação INDEX RANGE SCAN recupera as linhas fora de ordem e o que é necessário fazer para garantir que a linhas sejam recuperadas em ordem, evitando uma operação adicional no plano de execução para ordenar a linhas.

Para realizar esta simulação vamos:

1) Criar 1 tabela com um indice composto
2) Executar a consulta e verificar o plano de execução
3) Verificar porque as linhas recuperadas pelo índice não estão em ordem
4) Executar a consulta novamente e verificar se a operação SORT foi eliminada do plano de execução

1) Criar a tabela da simulação

 

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
SQL > SELECT * FROM V$VERSION where rownum < 2;

BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production

SQL >
SQL > -----------------------
SQL > -- Definição da tabela
SQL > -----------------------
SQL >
SQL > CREATE TABLE dbtw_list (
  2     Id          NUMBER        NOT NULL,
  3     First_Name  VARCHAR2(100) NOT NULL,
  4     Last_Name   VARCHAR2(100) NOT NULL,
  5     Description VARCHAR2(100) NULL
  6  );

Tabela criada.

SQL >
SQL > ---------------------------
SQL > -- Carga de dados na tabela
SQL > ---------------------------
SQL >
SQL > BEGIN
  2     FOR counter IN 1 .. 10000 LOOP
  3        INSERT INTO dbtw_list (Id, First_Name, Last_Name, Description)
  4           VALUES (counter,
  5                   DBMS_RANDOM.STRING('A', 10),
  6                   DBMS_RANDOM.STRING('A', 20),
  7                   DBMS_RANDOM.STRING('A', 100));
  8     END LOOP;
  9  END;
 10  /

Procedimento PL/SQL concluído com sucesso.

SQL > ---------------------
SQL > -- Criação do índice
SQL > ---------------------
SQL >
SQL > CREATE INDEX dbtw_list_idx ON dbtw_list (Id, First_Name);

Índice criado.

SQL >
SQL > -------------------------
SQL > -- Coleta de estatísticas
SQL > -------------------------
SQL >
SQL > exec dbms_stats.gather_table_stats(ownname => USER,tabname => 'DBTW_LIST',method_opt=>'for all columns size auto',cascade=>true,force=>true,degree=>16);

Procedimento PL/SQL concluído com sucesso.

SQL >

 

2) Executar a consulta e verificar o plano de execução

Vamos realizar uma consulta na tabela incluindo no filtro da cláusula WHERE a primeira coluna do índice criado.
 

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
SQL > ALTER SESSION SET statistics_level = ALL;

Sessão alterada.

SQL > ALTER SESSION SET optimizer_mode = FIRST_ROWS_10;

Sessão alterada.

SQL >
SQL > set arraysize 100
SQL > col First_Name for a20
SQL > col Last_Name  for a30
SQL >
SQL > SELECT /* DBTW101 */ id, First_Name, Last_Name
  2    FROM dbtw_list
  3   WHERE id BETWEEN 9500 AND 9600
  4   ORDER BY Id, First_Name;

        ID FIRST_NAME           LAST_NAME
---------- -------------------- ------------------------------
      9500 YgXpCiWvyd           RuOdUtvJkKZKFmCbDXiC
      9501 zggUcAYKFV           UefhYFwDzrOcLquPlBek
      9502 ZpehlGozsl           lrXlhagateiceAAjihMo
      .
      .
      .
      9598 yCGMWLTsrT           OLzOZMZDxyNSoyRZQmYo
      9599 zlwuHPyIWY           vrzhHzjeiApdWRujSFze
      9600 GVCDosOoqE           LKUXKEaSZctofrMrDljI

101 linhas selecionadas.

SQL >
SQL >
SQL > -- Recupera o SQL_ID e o CHILD_NUMBER  da consulta executada
SQL > column sql_id new_value m_sql_id
SQL > column child_number new_value m_child_no
SQL >
SQL > SELECT sql_id, child_number
  2    FROM v$sql
  3   WHERE sql_text LIKE '%DBTW101%'
  4     AND sql_text NOT LIKE '%v$sql%';

SQL_ID        CHILD_NUMBER
------------- ------------
b9yvxn9d08gd8            0

SQL >
SQL > -- Gera o relatório do plano de execução da consulta
SQL > SELECT * FROM TABLE (dbms_xplan.display_cursor ('&m_sql_id',&m_child_no,'typical iostats last'));
antigo   1: SELECT * FROM TABLE (dbms_xplan.display_cursor ('&m_sql_id',&m_child_no,'typical iostats last'))
novo   1: SELECT * FROM TABLE (dbms_xplan.display_cursor ('b9yvxn9d08gd8',         0,'typical iostats last'))

PLAN_TABLE_OUTPUT
-----------------------------------------------------------------------------------------------------------------------------------------
SQL_ID  b9yvxn9d08gd8, child number 0
-------------------------------------
SELECT /* DBTW101 */ id, First_Name, Last_Name   FROM dbtw_list  WHERE
id BETWEEN :"SYS_B_0" AND :"SYS_B_1"  ORDER BY Id, First_Name

Plan hash value: 2931546093

-----------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                     | Name          | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time   | A-Rows |   A-Time   | Buffers |
-----------------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT              |               |      1 |        |       |     6 (100)|          |    101 |00:00:00.01 |       5 |
|   1 |  SORT ORDER BY                |               |      1 |    102 |  3672 |     6  (17)| 00:00:01 |    101 |00:00:00.01 |       5 |
|*  2 |   FILTER                      |               |      1 |        |       |            |          |    101 |00:00:00.01 |       5 |
|   3 |    TABLE ACCESS BY INDEX ROWID| DBTW_LIST     |      1 |    102 |  3672 |     5   (0)| 00:00:01 |    101 |00:00:00.01 |       5 |
|*  4 |     INDEX RANGE SCAN          | DBTW_LIST_IDX |      1 |    102 |       |     2   (0)| 00:00:01 |    101 |00:00:00.01 |       2 |
-----------------------------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - filter(:SYS_B_1>=:SYS_B_0)
   4 - access("ID">=:SYS_B_0 AND "ID"<=:SYS_B_1)


23 linhas selecionadas.

SQL >

 

Verificando o plano de execução acima constatamos que as linhas foram recuperadas da tabela utilizando a operação INDEX RANGE SCAN no índice que criamos na etapa 1, mas o Otimizador incluiu no plano de execução a operação SORT ORDER BY pois considerou que as linhas recuperadas pelo índice não estavam na ordem adequada.

3) Verificar porque as linhas não estavam em ordem

O parâmetro NLS_SORT influencia o Otimizador na criação do plano de execução pois ele determina o critério de ordem que a linhas devem ser retornadas para o usuário. O índice normal é ordenado pelo valor binário da chave ou das chaves que ele foi criado e quando o parâmetro NLS_SORT esta definido numa língua especifica a ordem das linhas no índice não será correspondente a ordem linguística definida no parâmetro NLS_SORT.

Portanto para evitarmos que o Otimizador utilize no plano de execução a operação SORT ORDER BY precisamos que critério de ordenação do índice seja compatível com o parâmetro NLS_SORT:

1) Para um índice normal o parâmetro NLS_SORT deve ser definido como BINARY
2) Quando utilizamos o parâmetro NLS_SORT para ajustar a ordem dos dados a uma língua especifica precisamos criar um FUNCTION BASE INDEX utilizando o parâmetro NLS_SORT para garantir que o índice será ordenado conforme definição linguística deste parâmetro.

 

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
SQL > show parameters nls_sort

NAME                                 TYPE                             VALUE
------------------------------------ -------------------------------- ------------------------------
nls_sort                             string                           WEST_EUROPEAN
SQL >
SQL > alter session set nls_sort=BINARY;

Sessão alterada.

SQL >
SQL > show parameters nls_sort

NAME                                 TYPE                             VALUE
------------------------------------ -------------------------------- ------------------------------
nls_sort                             string                           BINARY
SQL >

 

Observando os comandos acima podemos identificar porque o Otimizador incluiu uma operação SORT ORDER BY no plano de execução apresentado na etapa 2, o parâmetro NLS_SORT estava definido como WEST_EUROPEAN e o índice utilizado era um índice normal. Para eliminar a necessidade da operação SORT ORDER BY precisamos alterar o  parâmetro NLS_SORT para BINARY.

4) Executar a consulta novamente e verificar o plano de execução

 

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
SQL > ALTER SESSION SET statistics_level = ALL;

Sessão alterada.

SQL > ALTER SESSION SET optimizer_mode = FIRST_ROWS_10;

Sessão alterada.

SQL >
SQL > set arraysize 100
SQL > col First_Name for a20
SQL > col Last_Name  for a30
SQL >
SQL > SELECT /* DBTW102 */ id, First_Name, Last_Name
  2    FROM dbtw_list
  3   WHERE id BETWEEN 9500 AND 9600
  4   ORDER BY Id, First_Name;

        ID FIRST_NAME           LAST_NAME
---------- -------------------- ------------------------------
      9500 YgXpCiWvyd           RuOdUtvJkKZKFmCbDXiC
      9501 zggUcAYKFV           UefhYFwDzrOcLquPlBek
      9502 ZpehlGozsl           lrXlhagateiceAAjihMo
      .
      .
      .
      9600 GVCDosOoqE           LKUXKEaSZctofrMrDljI

101 linhas selecionadas.

SQL >
SQL >
SQL > -- Recupera o SQL_ID e o CHILD_NUMBER  da consulta executada
SQL > column sql_id new_value m_sql_id
SQL > column child_number new_value m_child_no
SQL >
SQL > SELECT sql_id, child_number
  2    FROM v$sql
  3   WHERE sql_text LIKE '%DBTW102%'
  4     AND sql_text NOT LIKE '%v$sql%';

SQL_ID        CHILD_NUMBER
------------- ------------
25j6bnk571k1k            0

SQL >
SQL > -- Gera o relatório do plano de execução da consulta
SQL > SELECT * FROM TABLE (dbms_xplan.display_cursor ('&m_sql_id',&m_child_no,'typical iostats last'));
antigo   1: SELECT * FROM TABLE (dbms_xplan.display_cursor ('&m_sql_id',&m_child_no,'typical iostats last'))
novo   1: SELECT * FROM TABLE (dbms_xplan.display_cursor ('25j6bnk571k1k',         0,'typical iostats last'))

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------------------------------------------
SQL_ID  25j6bnk571k1k, child number 0
-------------------------------------
SELECT /* DBTW102 */ id, First_Name, Last_Name   FROM dbtw_list  WHERE
id BETWEEN :"SYS_B_0" AND :"SYS_B_1"  ORDER BY Id, First_Name

Plan hash value: 2088179210

----------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                    | Name          | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time   | A-Rows |   A-Time   | Buffers |
----------------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |               |      1 |        |       |     3 (100)|          |    101 |00:00:00.01 |       8 |
|*  1 |  FILTER                      |               |      1 |        |       |            |          |    101 |00:00:00.01 |       8 |
|   2 |   TABLE ACCESS BY INDEX ROWID| DBTW_LIST     |      1 |     13 |   468 |     3   (0)| 00:00:01 |    101 |00:00:00.01 |       8 |
|*  3 |    INDEX RANGE SCAN          | DBTW_LIST_IDX |      1 |    102 |       |     2   (0)| 00:00:01 |    101 |00:00:00.01 |       4 |
----------------------------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter(:SYS_B_1>=:SYS_B_0)
   3 - access("ID">=:SYS_B_0 AND "ID"<=:SYS_B_1)


22 linhas selecionadas.

SQL >

 

Analisando o plano de execução na consulta acima, podemos constatar que a operação SORT ORDER BY foi excluída do plano. Caso fosse necessário manter o parâmetro NLS_SORT com a definição linguística existente, deveríamos criar um FUNCTION BASE INDEX para garantir a compatibilidade entre o parâmetro NLS_SORT e a ordem das chaves no índice, evitando assim a utilização de uma operação adicional SORT no plano de execução.

Referência:

https://docs.oracle.com/cloud/latest/db112

 
 
 
Promo-D75L

video-02
 
 
 
 
 
 
 
 
 
 
 
 

Deixe uma resposta

O seu endereço de email não será publicado. Campos obrigatórios são marcados com *