Oracle 用算子成员进行分区修剪

你好，汤姆!

在某些嵌套表上使用运算符的成员时，是否可以强制Oracle使用 (子) 分区修剪？

例如:

SELECT * FROM A_TABLE WHERE COL_1 MEMBER OF NUMBER_TAB_TYPE(1,10,4);
复制

其中NUMBER_TAB_TYPE定义为

CREATE TYPE NUMBER_TAB_TYPE IS TABLE OF NUMBER;
复制

COL_1是列表分区表A_TABLE的类型编号的分区键。

更好的例子是

SELECT * FROM A_TABLE WHERE COL_1 MEMBER OF :A;
复制

其中: A是绑定变量，将用NUMBER_TAB_TYPE集合填充。


对于Oracle来说，这应该是相同的任务

SELECT /* USE_NL(A_TABLE T) */ * FROM A_TABLE JOIN (SELECT 1 KEY_VAL FROM DUAL UNION SELECT 10 FROM DUAL UNION SELECT 4 FROM DUAL) T
ON A_TABLE.COL_1 = T.KEY_VAL;
复制

在上面的示例中必须使用USE_NL提示，因为无法为这种子查询 (甚至没有dynamic_sampling提示) 收集适当的统计信息以进行分区修剪。

此示例也不起作用:

SELECT * FROM A_TABLE WHERE COL_1 IN (SELECT 1 KEY_VAL FROM DUAL UNION SELECT 10 FROM DUAL UNION SELECT 4 FROM DUAL);
复制

内部使用UNION operator时!？
即Oracle正在计划中使用分区全部，而不是分区INLIST或分区子查询。


有什么方法可以在不使用提示和类似技巧的情况下启用适当的分区修剪
当我们在常量嵌套表中有一些值时，在PL/SQL中填充，
为了避免动态查询，我们需要像运算符逗号分隔的值一样对这些值进行硬编码？

谢谢!

BR，
Hrvoje

为什么说联合等价物不能使用分区修剪？

这当然是可能的，因为这个演示显示:

create table A_TABLE  (
  col_1 integer
) partition by list (col_1) (
  partition p1 values (1),
  partition p4 values (4),
  partition p10 values (10)
);

insert into A_TABLE values (1);
insert into A_TABLE values (4);
insert into A_TABLE values (10);
commit;

set serveroutput off
select /*+ gather_plan_statistics */*
from a_table
where col_1 in (
  select 1 key_val from dual
  union
  select 4 from dual
);

select * 
from   table(dbms_xplan.display_cursor(null, null, 'ROWSTATS LAST +PARTITION'));

PLAN_TABLE_OUTPUT                                                                          
SQL_ID  6w9f5yv3rg4qq, child number 0                                                      
-------------------------------------                                                      
select /*+ gather_plan_statistics */* from a_table where col_1 in (                        
select 1 key_val from dual   union   select 4 from dual )                                  
                                                                                           
Plan hash value: 3466091503                                                                
                                                                                           
----------------------------------------------------------------------------------------   
| Id  | Operation                | Name     | Starts | E-Rows | Pstart| Pstop | A-Rows |   
----------------------------------------------------------------------------------------   
|   0 | SELECT STATEMENT         |          |      1 |        |       |       |      2 |   
|   1 |  NESTED LOOPS            |          |      1 |      2 |       |       |      2 |   
|   2 |   VIEW                   | VW_NSO_1 |      1 |      2 |       |       |      2 |   
|   3 |    SORT UNIQUE           |          |      1 |      2 |       |       |      2 |   
|   4 |     UNION-ALL            |          |      1 |        |       |       |      2 |   
|   5 |      FAST DUAL           |          |      1 |      1 |       |       |      1 |   
|   6 |      FAST DUAL           |          |      1 |      1 |       |       |      1 |   
|   7 |   PARTITION LIST ITERATOR|          |      2 |      1 |   KEY |   KEY |      2 |   
|*  8 |    TABLE ACCESS FULL     | A_TABLE  |      2 |      1 |   KEY |   KEY |      2 |   
----------------------------------------------------------------------------------------   
                                                                                           
Predicate Information (identified by operation id):                                        
---------------------------------------------------                                        
                                                                                           
   8 - filter("COL_1"="KEY_VAL") 
复制

请注意，步骤7使用分区列表迭代器。你知道它被修剪掉了一个分区，因为有三个分区。但是表访问只启动了两次。所以你知道其中一个分区被跳过了。

并且您可以使用嵌套表获得类似的您可以将数组放置在表运算符中，而不是使用成员。这将其作为行返回。

加入这个和分区修剪的输出肯定是可能的:

CREATE TYPE NUMBER_TAB_TYPE IS TABLE OF NUMBER;
/

SELECT /*+ gather_plan_statistics */* 
FROM   A_TABLE
join   table(NUMBER_TAB_TYPE(1,4)) t
on     COL_1 = column_value;

select * 
from   table(dbms_xplan.display_cursor(null, null, 'ROWSTATS LAST +PARTITION'));

PLAN_TABLE_OUTPUT                                                                                       
SQL_ID  362up9bgaqh6r, child number 6                                                                   
-------------------------------------                                                                   
SELECT /*+ gather_plan_statistics */*  FROM   A_TABLE join                                              
table(NUMBER_TAB_TYPE(1,4)) t on     COL_1 = column_value                                               
                                                                                                        
Plan hash value: 2191391213                                                                             
                                                                                                        
-----------------------------------------------------------------------------------------------------   
| Id  | Operation                              | Name    | Starts | E-Rows | Pstart| Pstop | A-Rows |   
-----------------------------------------------------------------------------------------------------   
|   0 | SELECT STATEMENT                       |         |      1 |        |       |       |      2 |   
|   1 |  NESTED LOOPS                          |         |      1 |      2 |       |       |      2 |   
|   2 |   COLLECTION ITERATOR CONSTRUCTOR FETCH|         |      1 |      2 |       |       |      2 |   
|   3 |   PARTITION LIST ITERATOR              |         |      2 |      1 |   KEY |   KEY |      2 |   
|*  4 |    TABLE ACCESS FULL                   | A_TABLE |      2 |      1 |   KEY |   KEY |      2 |   
-----------------------------------------------------------------------------------------------------   
                                                                                                        
Predicate Information (identified by operation id):                                                     
---------------------------------------------------                                                     
                                                                                                        
   4 - filter("COL_1"=VALUE(KOKBF$))
复制

如果您没有进行分区修剪，请发布一个完整的测试用例，其中显示:

-创建表格
-插入
-上面的dbms_xplan.display_cursor显示的执行

我们可以帮助弄清楚发生了什么。