Início / dba / Perguntas / 47462
Accepted
gavenkoa
Asked: 2013-08-03 01:45:48 +0800 CST

Índice para coluna de data para consulta de intervalo no Oracle?

  • 772

Nós temos muito:

select count(*) from TBL where XDATE between ? and ? and FLD = ?;

Esperamos aumentar o desempenho ajustando o índice na coluna XDATE (que tem o tipo DATE ).

Que tipo de índices mais adequado para o tipo DATE e entre a condição?

oracle index

2 respostas

  1. Intervalos - especialmente intervalos de datas - podem ser difíceis de ajustar. Há uma palavra de diferença entre isso ...

    where xdate between date '2013-07-01' and date '2013-07-31'

    ... e isto ...

    where xdate between date '2003-07-01' and date '2013-07-31'

    Você desejaria caminhos de acesso radicalmente diferentes para essas duas consultas. Um índice ajudará com o primeiro, mas provavelmente será desastroso para o segundo (dependendo dos volumes de dados envolvidos).

    O outro problema com datas de indexação diz respeito ao elemento de tempo. Se xdatecontiver o elemento de tempo, potencialmente você terá 86.400 valores diferentes para cada dia do calendário. Na maioria dos casos, quando fazemos uma pesquisa de intervalo de datas, queremos todos os resultados do dia, independentemente da hora.

    Não podemos dar uma recomendação absoluta, porque você não forneceu detalhes suficientes, mas meu conselho geral seria:

    1. Construa um índice baseado em função, removendo o elemento time: create index tbl_xdate_idx on tbl(trunc(xdate)).
    2. Investigue se a compactação de índice lhe dará uma economia de espaço. Índices compactados geralmente têm um desempenho melhor também. Mas provavelmente só vale a pena se as datas não incluírem horários, outro benefício da abordagem do FBI. Ah, e você precisa da Enterprise Edition.
    3. Lembre-se de que as pesquisas que abrangem longos intervalos podem precisar ser tratadas de maneira diferente. Como você faz isso depende das peculiaridades do seu aplicativo.
    • 7
  2. Best Answer

    Com os detalhes fornecidos, o índice de duas colunas pode ser sensato:

    CREATE INDEX tbl_index ON tbl(fld,xdate);

    Mas é claro que tudo depende dos seus dados.

    • se sua consulta retornar > 1% da varredura completa da tabela, provavelmente será mais rápido. Portanto, não há necessidade de criar índice
    • se o FLD tiver uma seletividade ruim, o índice em (xdate) poderá obter resultados semelhantes
    • cada índice adiciona sobrecarga às operações DML. Além de alterar os dados o Oracle também tem que alterar todos os índices e também registrar tudo nos redo logs.

    Para coletar dados basta executar:

    select count(*) from TBL;
select count(*) from TBL where XDATE between ? and ?;
select count(*) from TBL where FLD = ?;
select count(*) from TBL where XDATE between ? and ? and FLD = ?;

    E compare os resultados. Ou edite sua pergunta com esses resultados.

    • 4

relate perguntas