Í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

Title of the document

Ajudo DBAs e analistas de sistema a se destacarem em suas empresas

e obter um crescimento mais acelerado em suas carreiras, quer saber mais click no link abaixo:

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 um comentário

O seu endereço de e-mail não será publicado.