Consulta Oracle SQL lenta mesmo com índices

Tenho um banco de dados Oracle 19c (19.0.0.0.0) e duas tabelas com muitas colunas, mencionarei apenas as relevantes:

tabela 1:

• data de criação
• caractere contact_key (96 bytes)

mesa 2:

• caractere contact_key (96 bytes)
• csi_tfid varchar2(300 bytes)

table1 tem 210 milhões de linhas e é particionada anualmente. table2 possui 6,8 milhões de registros e não é particionada.

A consulta a seguir é executada muito rapidamente, em torno de 0,05 a 0,1 segundo:

select * from table1 m, table2 c
where
c.contact_key = m.contact_key
and c.csi_tfid = '1234567';

Mas assim que adiciono uma condição where para obter apenas os últimos registros (o que é relevante para a aplicação), a velocidade de execução cai para 1 minuto, ou até mais lenta:

select * from table1 m, table2 c
where
c.contact_key = m.contact_key
and c.csi_tfid = '1234567'
and m.createtime >= (sysdate-30);

Tentei colocar uma data codificada lá como to_date('2024-04-09', 'YYYY-MM-DD'), mesmo resultado.

Tenho índices individuais em todas as colunas desta consulta:

tabela 1:

• ik_table1_contact_k não exclusivo
• ik_table1_createtime não exclusivo

mesa 2:

• ik_table2_contact_key único
• ik_table2_csi_tfid não exclusivo

Tentei adicionar índice composto a ambas as tabelas, para incluir contact_key e createtime (para tabela1) e contact_key e csi_tfid (para tabela2), aparentemente sem efeito.

Para a consulta rápida, a Oracle gera este plano:

Plan hash value: 323565418
 
-----------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                                   | Name                | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
-----------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                            |                     |     3 |  1857 |   118   (0)| 00:00:01 |       |       |
|   1 |  NESTED LOOPS                               |                     |     3 |  1857 |   118   (0)| 00:00:01 |       |       |
|   2 |   TABLE ACCESS BY INDEX ROWID BATCHED       | TABLE2              |     3 |   966 |     7   (0)| 00:00:01 |       |       |
|*  3 |    INDEX RANGE SCAN                         | IK_TABLE2_CSI_TFID  |     3 |       |     3   (0)| 00:00:01 |       |       |
|   4 |   PARTITION RANGE ALL                       |                     |     1 |   297 |    37   (0)| 00:00:01 |     1 |    12 |
|   5 |    TABLE ACCESS BY LOCAL INDEX ROWID BATCHED| TABLE1              |     1 |   297 |    37   (0)| 00:00:01 |     1 |    12 |
|*  6 |     INDEX RANGE SCAN                        | IK_TABLE1_CONTACT_K |     1 |       |    36   (0)| 00:00:01 |     1 |    12 |
-----------------------------------------------------------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   3 - access("C"."CSI_TFID"='1234567')
   6 - access("C"."CONTACT_KEY"="M"."CONTACT_KEY")

Para a consulta lenta, com a condição createtime, é bem diferente:

Plan hash value: 1504517877
 
-------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                                   | Name                  | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
-------------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                            |                       |     2 |  1238 |    10   (0)| 00:00:01 |       |       |
|   1 |  NESTED LOOPS                               |                       |     2 |  1238 |    10   (0)| 00:00:01 |       |       |
|   2 |   PARTITION RANGE ITERATOR                  |                       |     3 |   891 |     6   (0)| 00:00:01 |   KEY |    12 |
|   3 |    TABLE ACCESS BY LOCAL INDEX ROWID BATCHED| TABLE1                |     3 |   891 |     6   (0)| 00:00:01 |   KEY |    12 |
|*  4 |     INDEX RANGE SCAN                        | IK_TABLE1_CREATETIME  |     3 |       |     3   (0)| 00:00:01 |   KEY |    12 |
|*  5 |   TABLE ACCESS BY INDEX ROWID               | TABLE2                |     1 |   322 |     2   (0)| 00:00:01 |       |       |
|*  6 |    INDEX UNIQUE SCAN                        | IK_TABLE2_CONTACT_KEY |     1 |       |     1   (0)| 00:00:01 |       |       |
-------------------------------------------------------------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   4 - access("M"."CREATETIME">=SYSDATE@!-30)
   5 - filter("C"."CSI_TFID"='1234567')
   6 - access("C"."CONTACT_KEY"="M"."CONTACT_KEY")
 
Note
-----
   - this is an adaptive plan

Mostra o custo muito baixo para ambas as consultas, por isso é estranho para mim como essa única condição pode adicionar tanta complexidade.

Na tabela1, contact_key será basicamente único, não há restrição exclusiva, mas a lógica do aplicativo garante que seja único, então acho que a consulta rápida usa esta coluna para pesquisa, que será rápida. Mas a consulta lenta usa a coluna createtime para pesquisar nesta tabela, que será mais lenta, pois haverá muitos registros onde creatime é > sysdate - 30.

Tentei contornar desta forma, esperando poder fazer a consulta rápida como subconsulta e depois filtrar apenas os resultados disso:

select sq.* from (
  select * from table1 m, table2 c
  where
  c.contact_key = m.contact_key
  and c.csi_tfid = '1234567'
) sq
where sq.createtime >= (sysdate-30);

Mas recebi a mesma consulta lenta, com o mesmo plano de execução. O que mais posso tentar? O aplicativo atualmente não possui lógica para filtrar os resultados, ele depende da instrução SELECT.

-- ATUALIZAÇÃO do DBA

O DBA verificou algumas estatísticas e histórico, e parece que esta consulta começou a "comportar-se mal" por volta de 1º de maio. Antes usava outro índice, semelhante ao sugerido pelo @Andy DB Analyst (outro índice, não mencionei acima). Verifiquei todos os índices, as estatísticas, a data da última análise, etc., são quase idênticas.