É comum encontrar aplicações cujas tabelas possuem muitos índices unitários (uma coluna) e nenhum índice composto (duas ou mais colunas), as ultimas versões do banco de dados Oracle já trata essa deficiência de estrutura das aplicações com o uso da operação "BITMAP AND" no plano de execução, mas em alguns…
Índice desordenado! Isto é possível?
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
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
