Why is Oracle SQL Optimizer ignoring index predica

Question

I'm trying to optimize a set of stored procs which are going against many tables including this view. The view is as such:

We have TBL_A (id, hist_date, hist_type, other_columns) with two types of rows: hist_type 'O' vs. hist_type 'N'. The view self joins table A to itself and transposes the N rows against the corresponding O rows. If no N row exists for the O row, the O row values are repeated. Like so:

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 has a unique index on: (id, hist_date, hist_type). It also has a unique index on: (hist_date, id, hist_type) and this is the primary key.

The following query is at issue (in a stored proc, with x declared as 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';

This query ignores the index on id column when accessing TBL_A and instead does a range scan using the date to pick up all the rows for the date. Then it filters that set using the values from the array. However if I simply give the list of ids as a list of numbers the optimizer uses the index just fine:

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';

The problem also doesn't exist when going against TBL_A directly (and I have a workaround that does that, but it's not ideal.).Is there a way to get the optimizer to first retrieve the array values and use them as predicates when accessing the table? Or a good way to restructure the view to achieve this?

Answer 1

Oracle does not use the index because it assumes select column_value from table(x) returns 8168 rows.

Indexes are faster for retrieving small amounts of data. At some point it's faster to scan the whole table than repeatedly walk the index tree.

Estimating the cardinality of a regular SQL statement is difficult enough. Creating an accurate estimate for procedural code is almost impossible. But I don't know where they came up with 8168. Table functions are normally used with pipelined functions in data warehouses, a sorta-large number makes sense.

Dynamic sampling can generate a more accurate estimate and likely generate a plan that will use the index.

Here's an example of a bad cardinality estimate:

create or replace type type_table_of_number as table of number;

explain plan for
select * from table(type_table_of_number(1,2,3,4,5,6,7));
select * from table(dbms_xplan.display(format => '-cost -bytes'));

Plan hash value: 1748000095

-------------------------------------------------------------------------
| Id  | Operation                             | Name | Rows  | Time     |
-------------------------------------------------------------------------
|   0 | SELECT STATEMENT                      |      |  8168 | 00:00:01 |
|   1 |  COLLECTION ITERATOR CONSTRUCTOR FETCH|      |  8168 | 00:00:01 |
-------------------------------------------------------------------------

Here's how to fix it:

explain plan for select /*+ dynamic_sampling(2) */ *
                 from table(type_table_of_number(1,2,3,4,5,6,7));
select * from table(dbms_xplan.display(format => '-cost -bytes'));

Plan hash value: 1748000095

-------------------------------------------------------------------------
| Id  | Operation                             | Name | Rows  | Time     |
-------------------------------------------------------------------------
|   0 | SELECT STATEMENT                      |      |     7 | 00:00:01 |
|   1 |  COLLECTION ITERATOR CONSTRUCTOR FETCH|      |     7 | 00:00:01 |
-------------------------------------------------------------------------

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