为什么Oracle SQL优化器忽略此视图的索引谓词？

Question

我正在尝试优化一组存储过程，这些过程与许多表(包括此视图)背道而驰。意见如下：

我们有两种类型的行: TBL_A (id、hist_date、hist_type、other_columns)：hist_type 'O‘和hist_type 'N’。视图self将表A连接到自己，并将N行转到相应的O行。如果O行不存在N行，则重复O行值。就像这样：

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有一个唯一的索引：(id，hist_date，hist_type)。它还有一个关于：(hist_date，id，hist_type)的唯一索引，这是主键。

存在以下查询问题(在存储的proc中，x声明为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';

此查询在访问TBL_A时忽略id列上的索引，而是使用日期进行范围扫描以获取日期的所有行。然后，它使用数组中的值进行筛选。但是，如果我只是将I列表作为数字列表，那么优化器就可以很好地使用索引：

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

当直接针对TBL_A时，这个问题也不存在(我有一个解决办法来解决这个问题，但这并不理想。).Is有一种方法可以让优化器首先检索数组值并在访问表时使用它们作为谓词？还是一种重组观点的好方法来实现这一目标？

Answer 1

Oracle不使用索引，因为它假定select column_value from table(x)返回8168行。

索引用于检索少量数据更快。在某种程度上，扫描整个表比反复遍历索引树要快。

估计常规SQL语句的基数是相当困难的。为过程代码创建精确的估计几乎是不可能的。但我不知道他们是从哪搞来的。在数据仓库中，表函数通常与流水线函数一起使用，大量的数据是有意义的.

动态采样可以生成更准确的估计，并可能生成使用索引的计划。

下面是一个糟糕的基数估计的例子：

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

下面是如何修复它：

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)