Como Criar Índices no Oracle para Otimizar a Performance do Banco de Dados – Index na prática

Index na prática

Existe no Oracle diversos mecanismos para otimizar a performance do banco de dados, uma dessas formas, é a possibilidade de criar index em tabelas.

Antes de fazermos nosso exemplo prático, deixarei algumas diretrizes que a Oracle recomenda em relação a index. Devemos sempre prestar atenção nessas recomendações afim de realizar um trabalho eficiente. Fique atento com a criação deles, se algum index for mal construído, poderá até afetar a performance do banco dados.

Veja algumas:

Quando criar index

• Quando a coluna contém um grande range de valores.
• A coluna contém vários valores null.
• A tabela deve ser grande.
• Quando a maioria das consultas, espera recuperar menos do que 2% a 4% das linhas da tabela.
• Frequentemente, a(s) coluna(s) é usada em WHERE cláusula ou em condições de join.

Quando não criar index

• Quando a coluna não for usada em condições na consulta.
• A tabela é atualizada frequentemente.
• Quando tabela for pequena.
• A maioria das consultas expera recuperar mais do que 2% a 4% das linhas da tabela.

Vamos iniciar nosso teste criando um ambiente para ele, execute o seguinte comando para criar a tabela T com os registros da tabela all_objects.

SQL> create table t as select object_name name, object_id id from all_objects;

Tabela criada.

Vamos dar um pouco mais de carga nessa tabela.

SQL> insert into t select * from t; 49871 linhas criadas.

SQL> select count(*) from t; COUNT(*) ---------- 99742

Agora com a tabela criada, vamos executar a seguinte query observando seu tempo:

SQL> set timing on

SQL> select name 2 from t 3 where upper(substr(name,5,2)) in 4 ('AA','V

O select acima, executou a consulta em 4 segundos e 32 milisegundos. Agora vamos executar a query novamente, mais antes disso, criaremos um index baseado em função na tabela T, e veremos novamente o tempo gasto para retornar a mesma consulta.

SQL> create index nameidxt
2  on T
3  (upper(substr(name, 5,2)));

Índice criado.

Agora esvaziamos o BUFFER_CACHE, para que o Oracle não apresse a consulta já executada anteriormente.

SQL> alter system flush buffer_cache;

Sistema alterado.

Executamos a mesma consulta, mais agora com o index criado na tabela, compare o tempo gasto da primeira vez que executamos, com a segunda vez.

SQL> select name
2          from t
3          where upper(substr(name,5,2)) in
4          ('AA','V

Se compararmos o tempo gasto nas duas execuções veremos uma diminuição enorme no tempo, de 4.32 para 0.70, ou seja a mesma query executada apenas com a inclusão de um index baseado em função, nos permite estar em um nível maior de performance.

Apesar do index fornecer para esse tipo de situação, e para outros, notórios tempos de melhorias, é importante saber aonde criar cada um deles, observar atentamente as recomendações citadas acima, para que possa atender a sua real necessidade.

,'AR','OE','HS','AO','KO','NT','ZO','GQ',
5           'XH','JO','BS','ML','AL','WW','PX','XX','IU','AO',
6           'CZ','JX','CQ','CW','CE','CR','CT','CY','CU','CI',
7           'MT','AH','LV','EG','BB','MO','MY','BQ','AE','AT')
8          /

 ...

NAME
------------------------------
INVENTORY_LIST_TYP
REJECTION_T
SUBCATEGORY_REF_LIST_TYP
SUBCATEGORY_REF_LIST_NESTEDTAB
CAL_MONTH_SALES_MV

2018 linhas selecionadas.

 Decorrido: 00:00:04.32

O select acima, executou a consulta em 4 segundos e 32 milisegundos. Agora vamos executar a query novamente, mais antes disso, criaremos um index baseado em função na tabela T, e veremos novamente o tempo gasto para retornar a mesma consulta.

Executamos a mesma consulta, mais agora com o index criado na tabela, compare o tempo gasto da primeira vez que executamos, com a segunda vez.

Se compararmos o tempo gasto nas duas execuções veremos uma diminuição enorme no tempo, de 4.32 para 0.70, ou seja a mesma query executada apenas com a inclusão de um index baseado em função, nos permite estar em um nível maior de performance.

Apesar do index fornecer para esse tipo de situação, e para outros, notórios tempos de melhorias, é importante saber aonde criar cada um deles, observar atentamente as recomendações citadas acima, para que possa atender a sua real necessidade.

,'AR','OE','HS','AO','KO','NT','ZO','GQ', 5 'XH','JO','BS','ML','AL','WW','PX','XX','IU','AO', 6 'CZ','JX','CQ','CW','CE','CR','CT','CY','CU','CI', 7 'MT','AH','LV','EG','BB','MO','MY','BQ','AE','AT') 8 / ... NAME ------------------------------ INVENTORY_LIST_TYP REJECTION_T SUBCATEGORY_REF_LIST_TYP SUBCATEGORY_REF_LIST_NESTEDTAB CAL_MONTH_SALES_MV 2018 linhas selecionadas. Decorrido: 00:00:00.70

Se compararmos o tempo gasto nas duas execuções veremos uma diminuição enorme no tempo, de 4.32 para 0.70, ou seja a mesma query executada apenas com a inclusão de um index baseado em função, nos permite estar em um nível maior de performance.

Apesar do index fornecer para esse tipo de situação, e para outros, notórios tempos de melhorias, é importante saber aonde criar cada um deles, observar atentamente as recomendações citadas acima, para que possa atender a sua real necessidade.

,'AR','OE','HS','AO','KO','NT','ZO','GQ', 5 'XH','JO','BS','ML','AL','WW','PX','XX','IU','AO', 6 'CZ','JX','CQ','CW','CE','CR','CT','CY','CU','CI', 7 'MT','AH','LV','EG','BB','MO','MY','BQ','AE','AT') 8 / ... NAME ------------------------------ INVENTORY_LIST_TYP REJECTION_T SUBCATEGORY_REF_LIST_TYP SUBCATEGORY_REF_LIST_NESTEDTAB CAL_MONTH_SALES_MV 2018 linhas selecionadas. Decorrido: 00:00:04.32

O select acima, executou a consulta em 4 segundos e 32 milisegundos. Agora vamos executar a query novamente, mais antes disso, criaremos um index baseado em função na tabela T, e veremos novamente o tempo gasto para retornar a mesma consulta.

Executamos a mesma consulta, mais agora com o index criado na tabela, compare o tempo gasto da primeira vez que executamos, com a segunda vez.

Se compararmos o tempo gasto nas duas execuções veremos uma diminuição enorme no tempo, de 4.32 para 0.70, ou seja a mesma query executada apenas com a inclusão de um index baseado em função, nos permite estar em um nível maior de performance.

Apesar do index fornecer para esse tipo de situação, e para outros, notórios tempos de melhorias, é importante saber aonde criar cada um deles, observar atentamente as recomendações citadas acima, para que possa atender a sua real necessidade.