为什么Oracle SQL Optimizer忽略此视图的索引谓词？ [英] Why is Oracle SQL Optimizer ignoring index predicate for this view?

问题描述

我正在尝试优化一组针对包括此视图在内的许多表的存储过程。视图是这样的：

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

我们有TBL_A（id，hist_date，hist_type，other_columns）有两种类型的行：hist_type'O'与hist_type'N'。视图self将表A连接到自身，并将N行转换为相应的O行。如果O行不存在N行，则重复O行值。像这样：

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在：（id，hist_date，hist_type）上有唯一索引。它还有一个唯一索引：（hist_date，id，hist_type），这是主键。

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.

以下查询存在争议（在存储过程中，x声明为TYPE_TABLE_OF_NUMBER）：

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

此访问在访问TBL_A时忽略id列上的索引，而是使用日期进行范围扫描拿起日期的所有行。 然后它使用数组中的值过滤设置。但是，如果我只是将id列表作为数字列表给出，优化器就可以正常使用索引：

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

直接针对TBL_A时，问题也不存在（我有一个解决方法可以做到这一点） ，但它并不理想。）。有没有办法让优化器首先检索数组值并在访问表时将它们用作谓词？或者重组视图以实现这一目标的好方法？

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?

推荐答案

Oracle不使用索引，因为它假定从表（x）中选择column_value 返回8168行。

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.

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

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

以下是修复方法：

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)

这篇关于为什么Oracle SQL Optimizer忽略此视图的索引谓词？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！