Oracle Index Access Paths
Há um mito, ao se falar em performance, que basta criar/utilizar um index e seu SELECT será otimizado. Com os exemplos abaixo, iremos verificar que muitas vezes, quando mal utilizados, um index pode na verdade, causar sérios impactos negativos, aumentando o tempo de resposta e o custo para a operação.
Para facilitar a visualização, montaremos um cenário, criando 3 tabelas, com index do tipo Unique(constraints Primary Key) e non-unique, em diferentes colunas.
CREATE TABLE GPO_ORDENADA AS
(SELECT LEVEL COD,
ROUND(DBMS_RANDOM.VALUE(1111111111, 99999999999)) A,
DBMS_RANDOM.STRING('A', 7) B,
ROUND(DBMS_RANDOM.VALUE(1, 10)) C,
ROUND(DBMS_RANDOM.VALUE(1, 10)) D,
ROUND(DBMS_RANDOM.VALUE(1, 10)) E,
ROUND(DBMS_RANDOM.VALUE(1, 10)) F,
'3' CONST,
ROUND(DBMS_RANDOM.VALUE(1, 10)) G,
DBMS_RANDOM.STRING('A', 50) H,
TO_DATE(ROUND(DBMS_RANDOM.VALUE(1, 28)) || '/' ||
ROUND(DBMS_RANDOM.VALUE(01, 12)) || '/' ||
ROUND(DBMS_RANDOM.VALUE(2012, 2013)) || ' ' ||
ROUND(DBMS_RANDOM.VALUE(01, 23)) || ':' ||
ROUND(DBMS_RANDOM.VALUE(01, 59)) || ':' ||
ROUND(DBMS_RANDOM.VALUE(01, 59)),
'DD/MM/YYYY HH24:MI:SS') I FROM DUAL
CONNECT BY LEVEL < 10000);
ALTER TABLE GPO_ORDENADA ADD CONSTRAINT ORDENADA_PK PRIMARY KEY (COD) USING INDEX;
CREATE TABLE GPO_DESORDENADA AS
(SELECT LEVEL COD,
ROUND(DBMS_RANDOM.VALUE(1111111111, 99999999999)) A,
DBMS_RANDOM.STRING('A', 7) B,
ROUND(DBMS_RANDOM.VALUE(1, 10)) C,
ROUND(DBMS_RANDOM.VALUE(1, 10)) D,
ROUND(DBMS_RANDOM.VALUE(1, 10)) E,
ROUND(DBMS_RANDOM.VALUE(1, 10)) F,
'3' CONST,
ROUND(DBMS_RANDOM.VALUE(1, 10)) G,
DBMS_RANDOM.STRING('A', 50) H,
TO_DATE(ROUND(DBMS_RANDOM.VALUE(1, 28)) || '/' ||
ROUND(DBMS_RANDOM.VALUE(01, 12)) || '/' ||
ROUND(DBMS_RANDOM.VALUE(2012, 2013)) || ' ' ||
ROUND(DBMS_RANDOM.VALUE(01, 23)) || ':' ||
ROUND(DBMS_RANDOM.VALUE(01, 59)) || ':' ||
ROUND(DBMS_RANDOM.VALUE(01, 59)),
'DD/MM/YYYY HH24:MI:SS') I FROM DUAL
CONNECTBYLEVEL < 10000);
ALTER TABLE GPO_DESORDENADA ADD CONSTRAINT DESORDENADA_PK PRIMARY KEY (A) USING INDEX;
CREATE INDEX DESORDENADA_IDX ON GPO_DESORDENADA (COD, A,B,C);
CREATE TABLE GPO_SKIP
( COD NUMBER,
A VARCHAR2(10),
B NUMBER,
C VARCHAR2(10),
D NUMBER,
E VARCHAR2(10));
CREATE INDEX GPO_SKIP_IDX ON GPO_SKIP (COD, A,B,C);
INSERT INTO GPO_SKIP VALUES (1 , DBMS_RANDOM.STRING('A', 6),
ROUND(DBMS_RANDOM.VALUE(1, 4)),
DBMS_RANDOM.STRING('A', 6),
ROUND(DBMS_RANDOM.VALUE(1, 4)),
DBMS_RANDOM.STRING('A', 6));
INSERT INTO GPO_SKIP VALUES (1 , DBMS_RANDOM.STRING('A', 6),
ROUND(DBMS_RANDOM.VALUE(1, 4)),
DBMS_RANDOM.STRING('A', 6),
ROUND(DBMS_RANDOM.VALUE(1, 4)),
DBMS_RANDOM.STRING('A', 6));
INSERT INTO GPO_SKIP VALUES (1 , DBMS_RANDOM.STRING('A', 6),
ROUND(DBMS_RANDOM.VALUE(1, 4)),
DBMS_RANDOM.STRING('A', 6),
ROUND(DBMS_RANDOM.VALUE(1, 4)),
DBMS_RANDOM.STRING('A', 6));
INSERT INTO GPO_SKIP VALUES (1 , DBMS_RANDOM.STRING('A', 6),
ROUND(DBMS_RANDOM.VALUE(1, 4)),
DBMS_RANDOM.STRING('A', 6),
ROUND(DBMS_RANDOM.VALUE(1, 4)),
DBMS_RANDOM.STRING('A', 6));
INSERT INTO GPO_SKIP VALUES (1 , DBMS_RANDOM.STRING('A', 6),
ROUND(DBMS_RANDOM.VALUE(1, 4)),
DBMS_RANDOM.STRING('A', 6),
ROUND(DBMS_RANDOM.VALUE(1, 4)),
DBMS_RANDOM.STRING('A', 6));
COMMIT;Após a criação, vamos atualizar as estatísticas das tabelas:
EXEC DBMS_STATS.GATHER_TABLE_STATS('<YOUR OWNER>','GPO_ORDENADA');
EXEC DBMS_STATS.GATHER_TABLE_STATS('<YOUR OWNER>','GPO_DESORDENADA');
EXEC DBMS_STATS.GATHER_TABLE_STATS('<YOUR OWNER>','GPO_SKIP');Em seguida, vamos utilizar os seguintes comandos para permitir a visualização de nossos planos de execução:
SET ARRAYSIZE 50;
SET TIMING ON;
SET LINESIZE 150;
SET AUTOTRACE TRACEONLY EXPLAIN STATISTICS;– Tipos de Index Access Paths:
Com nosso cenário montado, vamos iniciar nossa análise:
Index Fast Full Scan: Neste tipo de acesso, todas as colunas utilizadas na query (uma ou mais, para index concatenados) fazem parte do index. Aqui, nenhum tipo de acesso aos blocos da tabela será necessário. Todos os dados serão buscados nos blocos pertencentes ao index em si, através de leitura múltipla de blocos (multiblock read), sem ordem definida, onde o número de blocos lidos é definido pelo parâmetro DB_FILE_MULTIBLOCK_READ_COUNT. Este é o único tipo de acesso ao index que possui esta funcionalidade – Os demais acessos são realizados através de single-block read(um bloco de cada vez).
Nota: A partir da versão 10g release 2, a Oracle recomenda que este parâmetro não seja estipulado, permitindo que a instância de banco de dados determine qual o valor utilizado(tunning automático).
SQL> SELECT COD FROM GPO_ORDENADA;
Perceba a diferença de custo ao buscar os dados nos blocos do index (plano de execução acima) e ao buscar nos blocos da tabela (plano de execução abaixo), também utilizando multiblock read (Table Full Scan), para retornar o mesmo número de registros:
SQL> SELECT/*+ FULL (GPO_ORDENADA) */ COD FROM GPO_ORDENADA;
Index Full Scan: Ocorre quando o Oracle varre todo o index(root, branch e leafs), lendo todos os seus blocos. Após levantar os dados no index, acessamos a tabela pelo rowid.
SQL> SELECT/*+ INDEX (GPO_ORDENADA ORDENADA_PK) */ COD,A,B,I FROM GPO_ORDENADA;
Index Unique Scan: O Oracle utiliza este tipo de acesso quando nossa query retorna um único registro, restringindo através da cláusula WHERE a linha que queremos acessar, mencionando todas as colunas pertencentes a um index do tipo UNIQUE ou as colunas de uma PRIMARY KEY.
SQL> SELECT * FROM GPO_ORDENADA WHERE COD = 150;
Index Range Scan: Temos este tipo de acesso aos dados quando em nossa query, possuímos uma restrição na cláusula WHERE que utiliza condições do tipo maior (>), menor (<), igual (=) e quando usamos wild-cards, desde que o mesmo não esteja no início da expressão. Exemplo:
WHERE MY_INDEXED_COLUMN LIKE ‘ABC%’;=> Possibilidade de utilizar acesso do tipo Index Range Scan.
WHERE MY_INDEXED_COLUMN LIKE ‘%ABC’;=> Sem possibilidades.
SQL> SELECT COD, A FROM GPO_ORDENADA WHERE COD < 1000;
Index Skip Scan: Esta opção foi implementada na versão 9i. Anteriormente, quando não se utilizava a primeira coluna(leading column) de um index composto na cláusula WHERE, o index era ignorado. Atualmente, mesmo ao não utilizar a coluna líder, o Oracle nos permite continuar utilizando o index, através do seguinte mecanismo: Cada valor distinto da coluna líder é considerado como um ponto de partida para uma procura nas colunas subsequentes. É como se particionássemos o index anterior em diversos sub-indexes, permitindo assim que possamos buscar os dados através dele.
SQL> SELECT A,B,C FROM GPO_SKIP WHERE A = 'JJJ'AND B = 2;
Index Join: Este tipo de acesso ocorre quando todas as colunas de nossa query estão contidas em 2 ou mais indexes. Através de uma JOIN dos indexes, podemos selecionar os dados sem utilizar os blocos da tabela:
SQL> SELECT/*+ INDEX_JOIN (DES DESORDENADA_IDX ORDENADA_PK) */ ORD.COD, DES.A
FROM GPO_DESORDENADA DES, GPO_ORDENADA ORD
WHERE DES.COD = ORD.COD;
– CLUSTERING FACTOR:
Agora que temos um entendimento de como acessamos os dados através do index, vamos analisar um dos fatores de porque um index, criado sobre um mesmo número de linhas, pode apresentar diferenças grandes de eficácia quando utilizado. Para isso, vamos observar o output de alguns SELECTs, efetuado sobre nosso cenário:
SQL> SELECT/*+ INDEX (GPO_ORDENADA ORDENADA_PK) */ COD
FROM GPO_ORDENADA
WHERE ROWNUM < 10;
SQL> SELECT/*+ FULL (GPO_ORDENADA) */ COD
FROM GPO_ORDENADA
WHERE ROWNUM < 10;
SQL> SELECT/*+ INDEX (GPO_DESORDENADA DESORDENADA_PK) */ A
FROM GPO_DESORDENADA
WHERE ROWNUM < 10;
SQL> SELECT/*+ FULL (GPO_DESORDENADA) */ A
FROM GPO_DESORDENADA
WHERE ROWNUM < 10;
Olhando para os resultados, notamos que houve uma diferença no output da query, ao utilizar o index e ao fazer um acesso direto(Full Table Scan) para a tabela GPO_DESORDENADA. Analisando os DDLs de cada tabela, vemos que o index na primeira (GPO_ORDENADA) foi criado numa coluna sequencial e no segundo caso, em uma coluna criada com valores aleatórios.
– Qual o Impacto que isto pode ter no index?
Na view USER_INDEXES (ALL_INDEXES/DBA_INDEXES), há uma coluna chamada CLUSTERING_FACTOR, que está diretamente ligada a nossa pergunta:
SQL> SELECT TAB.BLOCKS, IND.CLUSTERING_FACTOR
FROM USER_TABLES TAB, USER_INDEXES IND
WHERE TAB.TABLE_NAME = IND.TABLE_NAME
AND IND.INDEX_NAME = 'ORDENADA_PK';
SQL> SELECT TAB.BLOCKS, IND.CLUSTERING_FACTOR
FROM USER_TABLES TAB, USER_INDEXES IND
WHERE TAB.TABLE_NAME = IND.TABLE_NAME
AND IND.INDEX_NAME = 'DESORDENADA_PK';
Esta coluna é utilizada pelo Otimizador para verificar a eficiência do index se tivermos que ler todas as linhas da tabela através dele – O valor representa uma estimativa de I/Os necessários para a operação. Porém, fica a dúvida: porque há uma diferença tão grande nos valores para os indexes, sendo que o número de linhas/blocos da tabela é o mesmo?
Olhando para o output anterior de nossas queries, vimos que ao utilizar um index, os dados são acessados na ordem (Sort operation) do index – Exceção para o Index Fast Full Scan. Se a forma como as linhas estão distribuídas nos blocos da tabela é muito diferente da organização no index, mais I/Os serão necessários para buscar os dados – Inclusive com possibilidade de que o mesmo bloco da tabela seja acessado várias vezes. Vamos ver como isso se reflete, analisando o plano de execução:
SQL> SELECT/*+ INDEX(GPO_DESORDENADA DESORDENADA_PK) */ *
FROM GPO_DESORDENADA WHERE COD < 3000;
SQL> SELECT/*+ INDEX(GPO_ORDENADA ORDENADA_PK) */ * FROM GPO_ORDENADA WHERE COD < 3000;
Mesmo utilizando a mesma query, retornando o mesmo número de linhas, em tabelas com o mesmo número de blocos, foram gerados tipos distintos de acesso ao index e uma diferença grande de custo, quantidade de gets e physical reads. Ao utilizar o Index para acessar os dados da tabela, tivemos que trazê-los na ordem presente no Index – Como a diferença entre os 2 é grande, ocorre a situação que observamos acima.
Quanto mais próximo ao número de blocos da tabela o CLUSTERING FACTOR estiver, maior será a efetividade do index, pois menos I/Os serão necessários para acessar os dados. Caso esteja mais próximo do número de linhas da tabela, provavelmente este index não será de muita serventia durante a execução de nossas queries em muitas situações.
Conclusão
Bom pessoal, este artigo termina por aqui. Espero poder ter ajudado a ter um entendimento melhor sobre indexes e a quebrar o mito de que “basta criar um index que a consulta ficará boa”. Bons estudos a todos e até a próxima.
Referências