Executando verificação de segurança...
Em resposta a Dúvida sincera: Como escalar consultas em Oracle com múltiplos filtros e alto volume (90M+ registros)?
1
Oletros

Meus 2 cents,

Sem conhecer melhor a estrutura fica complicado dar direcoes mais exatas, mas vamos no basico:

  1. paginacao

SELECT * FROM (
SELECT
idpedido, data_pedido, cliente, status
FROM
pedidos
WHERE
data_pedido BETWEEN :inicio AND :fim
AND idpedido > :ultimo_id_recebido
ORDER BY
idpedido ASC
)
WHERE ROWNUM <= 50;

A vantagem desta abordagem eh que se voce tiver um indice por idpedido, o oracle faz um range scan e o rownum forca a parar a partir do 50 registros - isso agiliza um bocado (mas so faz sentido se voce usar a paginacao baseada em um indice conhecido)

  1. Hints

O oracle tem um negocio chamado 'hints', o que permite forcar algum comportamento durante a query, p.ex. forcar o uso de um campo/indice ou mesmo criar um indice que fica desabilitado para o dia-a-dia exceto naquele uso especifico.

de uma olhada em Invisible Indexes e /*+ INDEX(tabela invisible_index_x) */

  1. Partition Range

No oracle eh possivel dividir uma tabela internamente baseado em um campo. Talvez particionar por data seja interessante.

  1. Materialized View

Sao views pre-processadas, se voce tem um "padrao" de pesquisas pode agilizar o processo

  1. Uso do OpenSearch (ou Elasticsearch dependendo do caso)

Dependendo do caso o OpenSearch pode ser uma alternativa, mas implica em infra mais complexa

  1. Cache usando MySQL/MariaDB, Redis, Cassandra, PostgreSQL

Uma alternativa ao OpenSearch/Elastisearch seria usar um banco "comum" para cache apenas de leitura - neste caso precisa entender como funcionam os dados para escolher a alternativa mais interessante.

A vantagem eh que sendo bancos "read-only" voce pode otimizar tudo no full, sem se preocupar em corrupcao de dados por queda - se der merda, so reconstroi a partir do oracle.

O OpenSearch/Elasticsearch eh a melhor alternativa (antes dele existir ja usei cache com bancos "read-only", e tambem funcionou OK) - mas tem o problema da replicacao de dados, onde o ideal eh fazer isso em batch, mas novamente, depende muito de como seus dados sao organizados.

Nao esqueca de compartilhar os caminhos seguidos e o resultado

Saude e Sucesso !

Carregando publicação patrocinada...
1
sonnic

Muito obrigado pela sua colaboração!

Vou trazer um pouco mais de detalhes...

Atualmente tenho alguns indices como disse e dois deles são, um pelo IDPEDIDO e outro pelos campos (IDPEDIDO, DATAPEDIDO), os registros usam partition range pela data do pedido.

Gostaria que o ROWNUM <= 50 funcionasse seria uma mão na roda, mas infelizmente no select interno tenho alguns filtros pela data identificador do parceiro e outros filtros que o parceiro pode adicionar como refrencia e etc...

Com o order by idpedido no select interno ele acaba fazendo um index full scan que degrada a performance da consulta.

EXPLAIN PLAN FOR
SELECT *
FROM (
    SELECT *
    FROM PEDIDO
    WHERE IDPARCEIRO = 20
      AND DTTRANSACAO BETWEEN TO_DATE('01/03/2006','DD/MM/YYYY')
                          AND TO_DATE('26/03/2026','DD/MM/YYYY')
      AND IDGATEWAY IN (1,2,4,10)
      AND SITUACAO IN ('P')
    ORDER BY IDPEDIDO
)
WHERE ROWNUM <= 50;

Resultado do explain

--------------------------------------------------------------------------------------------------------------------
| Id  | Operation                            | Name        | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
--------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                     |             |    50 |   157K|   271   (0)| 00:00:01 |       |       |
|*  1 |  COUNT STOPKEY                       |             |       |       |            |          |       |       |
|   2 |   VIEW                               |             |    51 |   160K|   271   (0)| 00:00:01 |       |       |
|*  3 |    TABLE ACCESS BY GLOBAL INDEX ROWID| PEDIDO      |    83M|    27G|   271   (0)| 00:00:01 | ROWID | ROWID |
|   4 |     INDEX FULL SCAN                  | PKPEDIDO    |  4387 |       |    19   (0)| 00:00:01 |       |       |
--------------------------------------------------------------------------------------------------------------------

Sobre os indices invisiveis ainda preciso aprofundar se eles afetariam o insert e update dos dados, pelo que vi ele fica invisível apenas ao otimizador de consultas.

Olhando para um banco "read-only" ou OpenSearch/Elasticsearch é uma ideia que ainda não fiz nenhuma tentativa. Se puder compartilhar como utilizou a replicação dos dados o que deu certo e errado seria de grande valia.

Hoje meus dados estão organizados em tabelas particionadas, mas tenho vários tenant de banco de dados com dados de um ou vários parceiros.

Obrigado!

1
Oletros
  • Autor
    Autor

Meus 2 cents extendidos,

Minha situacao foi um projeto de IoT onde dispositivos mandavam telemetria e a pesquisa poderia acontecer de diversas formas.

Como resolvi:

  1. Separar tabela de persistencia de tabela de consulta

A de persistencia nao tinha indices, exceto pela PK (indices sao um problema serio em tabelas de alto volume)

Eu tinha uma tabela simples separada que mantinha a data e a PK (em alguns casos - sem PK so a Heap), assim se precisasse por alguma razao consultar os registros inseridos no dia X, consultava a tabela simples, pela qual sabia o range de ID que tinha de usar para a primera tabela.

Cheguei a usar um ID como BigInt e numeracao que comecava em ANOMESDIASEQ, p.ex., 260328000000001 e uma cron que resetava isso para o dia seguinte (260329000000000) mas no final das contas comecei a usar a tabela com ID normal e a outra so indicando o ID inicial e a data dele.

  1. Quando chegava um registro de telemetria, ele ia para 2 filas separadas

Fila 1: insert na tabela de persistencia

Fila 2: insert na tabela de consulta, totalmente assincrona

  1. Na tabela de consulta, joguei sujo como podia

3.1. As vezes nao era apenas uma tabela de consulta, as vezes tinha mais de uma com campos e normalizacao diferentes para diferentes tipos de consulta

3.2. Mandei a 3FN as favas - se um JOIN me complicava a vida (latencia), era eliminado com o campo equivalente sendo de-normalizado direto na tabela de consulta. Eh de matar DBA de desgosto e comia espaco em disco, mas agilidade contava mais

3.3. Em alguns BD se voce criar um indice que seja igual a tabela, ele ignora a tabela e usa o indice direto para manter os dados. Tem custo de insercao, mas depois a consulta e recuperacao sao absurdamente altos. Por isso filas separadas - a fila de consulta era assincrona e poderia demorar mais para inserir os dados, mas isso nao era um problema.

3.4. Logs de SQL eram registrados e olhavamos o que o pessoal consultava mais - criamos indices e otimizacoes especificas para aquilo que era mais usado.

3.5. Nao dava da escalar verticalmente (muito caro e nao era tao eficiente), enntao era tudo na horizontal: criei instancias separadas para consultas especificas.

3.6. Tabela de consulta nao tinha "redo logs" e tudo era cache

3.7. Lotes: a fila de consulta fazia buferizacao antes de inserir, de forma a mandar lotes de registros, tanto para diminuir overhead de rede quando processamento nos BDs de consulta (e tambem nao ficar com tanto sync wait).

3.8. Tabelas de consulta realizam sync para disco "se e quando" desse - o prioritario era manter em memoria o maximo possivel.

Tudo isso agilizava, mas o trade-off era risco de ter de reprocessar a fila de consulta caso uma instancia travasse (por isso tinha a tabela dos ID e data da tabela de persistencia) - mas na pratica do dia-a-dia isso se mostrou um medo que raramente ocorria.

Saude e Sucesso !