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$','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.


    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$','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.

  • Technorati Marcas: ,,,