为什么Oracle SQL Optimizer忽略了此视图的索引谓词？

我们有两种类型的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,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_type).它还有一个唯一索引：(hist_date,id,hist_type),这是主键.

以下查询存在争议(在存储过程中,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列上的索引,而是使用日期进行范围扫描以获取日期的所有行.然后它使用数组中的值过滤设置.但是,如果我只是将id列表作为数字列表给出,优化器就可以使用索引：

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时,问题也不存在(我有一个解决方法,但它并不理想.).是否有办法让优化器首先检索数组值并在访问时将它们用作谓词桌子？还是重组视图来实现这一目标的好方法？

解决方法

索引检索少量数据的速度更快.在某些时候,扫描整个表比重复遍历索引树更快.

估计常规SQL语句的基数是很困难的.为程序代码创建准确的估计几乎是不可能的.但是我不知道他们在哪里提出了8168.表函数通常与数据仓库中的流水线函数一起使用,有点大数据是有意义的.

Dynamic sampling可以生成更准确的估计,并可能生成将使用该索引的计划.

以下是基数估算值不佳的示例：

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

（编辑：李大同）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!