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!