Início / dba / Perguntas / 138028
Accepted
Joishi Bodio
Asked: 2016-05-10 09:28:29 +0800 CST

Consulta na tabela de histórico não usando índice (Oracle 12c)

  • 772

Eu tenho uma tabela de histórico que possui uma coluna 'ID' e 'TIMESTAMP' como tal

CREATE TABLE hist (
  HIST_ID INTEGER,
  HIST_TIMESTAMP TIMESTAMP,
  ID INTEGER, -- this is the id of the table that is being tracked
  --OTHER COLS
);

Eu também tenho um índice nesta tabela como tal

CREATE INDEX hist_ix ON hist (ID, HIST_TIMESTAMP);

Esta tabela tem muitas inserções contra ela e atualmente tem cerca de 30 milhões de linhas nela.

Quando tento executar a seguinte consulta, o oracle faz uma varredura completa da tabela em vez de usar o índice (que .. pelo menos eu acredito .. ele deveria ser capaz de usar).

SELECT ID, MAX(HIST_TIMESTAMP) FROM hist WHERE HIST_TIMESTAMP <= <<A TIMESTAMP>> GROUP BY ID;

Parece-me que o Oracle deve ser capaz de usar o índice para identificar rapidamente qual par id/timestamp está logo à "esquerda" de um ponto específico no tempo rapidamente, observando o índice id/timestamp em um id-by-id base, mas está insistindo em uma varredura completa da tabela.

Qualquer ajuda seria apreciada para obter esta consulta mais rápida.

Eu executei o seguinte para garantir que as estatísticas estavam atualizadas

EXEC DBMS_STATS.GATHER_TABLE_STATS('<meh>','hist');

Além disso, existem cerca de 1.000 valores de ID distintos na tabela hist.

Com relação à distribuição de dados. Dos ~1k IDs, 50 têm menos de 100 entradas na tabela, 70 têm entre 100 e 1.000 entradas, 146 têm entre 1.000 e 10.000 entradas e o restante varia de 10.000 a 60.000 entradas. Mais da metade das entradas tem pelo menos 30 mil registros.

oracle oracle-12c

2 respostas

  1. Best Answer

    O uso do índice é obviamente possível, mas opcional.

    CREATE TABLE hist (
  HIST_ID INTEGER,
  HIST_TIMESTAMP TIMESTAMP,
  ID INTEGER -- this is the id of the table that is being tracked
  --OTHER COLS
);

CREATE INDEX hist_ix ON hist (ID, HIST_TIMESTAMP);

explain plan for SELECT ID, MAX(HIST_TIMESTAMP) FROM hist WHERE HIST_TIMESTAMP <= :B1 GROUP BY ID;
select * from table(dbms_xplan.display);


Plan hash value: 1027924405

--------------------------------------------------------------------------------
| Id  | Operation            | Name    | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------
|   0 | SELECT STATEMENT     |         |     1 |    26 |     1   (0)| 00:00:01 |
|   1 |  SORT GROUP BY NOSORT|         |     1 |    26 |     1   (0)| 00:00:01 |
|*  2 |   INDEX FULL SCAN    | HIST_IX |     1 |    26 |     1   (0)| 00:00:01 |
--------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("HIST_TIMESTAMP"<=TO_TIMESTAMP(:B1))
       filter("HIST_TIMESTAMP"<=TO_TIMESTAMP(:B1))

Note
-----
   - dynamic statistics used: dynamic sampling (level=2)

    Não é verdade que um índice pode ser usado apenas para as colunas principais:

    explain plan for SELECT /*+ INDEX_SS(hist hist_ix) */ ID, MAX(HIST_TIMESTAMP) FROM hist WHERE HIST_TIMESTAMP <= :B1 GROUP BY ID;
select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 2669193891

--------------------------------------------------------------------------------
| Id  | Operation            | Name    | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------
|   0 | SELECT STATEMENT     |         |     1 |    26 |     1   (0)| 00:00:01 |
|   1 |  SORT GROUP BY NOSORT|         |     1 |    26 |     1   (0)| 00:00:01 |
|*  2 |   INDEX SKIP SCAN    | HIST_IX |     1 |    26 |     1   (0)| 00:00:01 |
--------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("HIST_TIMESTAMP"<=TO_TIMESTAMP(:B1))
       filter("HIST_TIMESTAMP"<=TO_TIMESTAMP(:B1))

Note
-----
   - dynamic statistics used: dynamic sampling (level=2)

    Outro método seria uma varredura completa rápida do índice (dica INDEX_FFS).

    Se você forçar o uso de seu índice com dicas, compare o custo do plano com verificação completa da tabela e o plano com caminho de acesso ao índice. É simplesmente uma decisão baseada em custo com um exemplo simples como este.

    Se você não pode nem forçar o uso do seu índice, eu procuraria o problema em outro lugar. Por exemplo seu índice está no UNUSABLEestado (check USER_INDEXES.STATUS) ou foi feito INVISIBLE( USER_INDEXES.VISIBILITY).

    • 1

  2. Seu índice é um índice composto em hist_id E hist_timestamp. Como hist_id é a parte inicial desse índice, o índice não pode ser usado quando seu predicado WHERE está apenas na parte final de hist_timestamp.

    • 0

relate perguntas