Por que o Oracle SQL Optimizer está ignorando o predicado de índice para esta exibição?
Estou tentando otimizar um conjunto de procs armazenados que estão indo contra muitas tabelas, incluindo esta exibição. A visualização é a seguinte:
Temos TBL_A (id, hist_date, hist_type, other_columns) com dois tipos de linhas: hist_type 'O' vs. hist_type 'N'. A exibição se associa automaticamente à tabela A e transpõe as N linhas contra as O correspondentes. Se nenhuma linha N existir para a linha O, os valores da linha O serão repetidos. Igual a:
CREATE OR REPLACE FORCE VIEW V_A (id, hist_date, hist_type, other_columns_o, other_columns_n)
select
o.id, o.hist_date, o.hist_type,
o.other_columns as other_columns_o,
case when n.id is not null then n.other_columns else o.other_columns end as other_columns_n
from
TBL_A o left outer join TBL_A n
on o.id=n.id and o.hist_date=n.hist_date and n.hist_type = 'N'
where o.hist_type = 'O';
TBL_A possui um índice exclusivo em: (id, hist_date, hist_type). Ele também possui um índice exclusivo em: (hist_date, id, hist_type) e essa é a chave primária.
A seguinte consulta está em questão (em um processo armazenado, com x declarado como TYPE_TABLE_OF_NUMBER):
select b.id BULK COLLECT into x from TBL_B b where b.parent_id = input_id;
select v.id from v_a v
where v.id in (select column_value from table(x))
and v.hist_date = input_date
and v.status_new = 'CLOSED';
Esta consulta ignora o índice na coluna id ao acessar TBL_A e, em vez disso, faz uma varredura de intervalo usando a data para selecionar todas as linhas da data.Então filtra esse conjunto usando os valores da matriz. No entanto, se eu simplesmente der a lista de IDs como uma lista de números, o otimizador usa o índice muito bem:
select v.id from v_a v
where v.id in (123, 234, 345, 456, 567, 678, 789)
and v.hist_date = input_date
and v.status_new = 'CLOSED';
O problema também não existe ao ir diretamente contra o TBL_A (e eu tenho uma solução alternativa que faz isso, mas não é o ideal.) Existe uma maneira de fazer com que o otimizador recupere primeiro os valores da matriz e os use como predicados ao acessar a mesa? Ou uma boa maneira de reestruturar a visão para conseguir isso?