Hello Tom !
Is it possible to force Oracle to use (sub-)partition pruning when MEMBER OF operator is used on some nested table?
For example:
SELECT * FROM A_TABLE WHERE COL_1 MEMBER OF NUMBER_TAB_TYPE(1,10,4);
where NUMBER_TAB_TYPE is defined as
CREATE TYPE NUMBER_TAB_TYPE IS TABLE OF NUMBER;
and COL_1 is PARTITION KEY of type NUMBER for LIST PARTITIONED table A_TABLE.
Better example is
SELECT * FROM A_TABLE WHERE COL_1 MEMBER OF :A;
where :A is bind variable which would be filled with a NUMBER_TAB_TYPE collection.
This should be the same task for Oracle as
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;
The USE_NL hint must be used in the above example as there is no way to gather proper statistics for this kind of subquery (not even with dynamic_sampling hint) in order to enact partition pruning.
This example is also not working:
SELECT * FROM A_TABLE WHERE COL_1 IN (SELECT 1 KEY_VAL FROM DUAL UNION SELECT 10 FROM DUAL UNION SELECT 4 FROM DUAL);
when UNION operator is used inside !?
I.e. Oracle is using PARTITION ALL in plan instead of PARTITION INLIST or PARTITION SUBQUERY.
Is there any way to enable proper partition pruning, without using hints and similar tricks
when we have some set of values in constant nested table, filled within PL/SQL,
to avoid dynamic queries for which we would need to hard-code these values as IN operator comma-separated values?
Thank you !
BR,
Hrvoje
Why do you say the union equivalent can't use partition pruning?
It's certainly possible, as this demo shows:
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") Notice that step 7 uses PARTITION LIST ITERATOR. You know it's pruned out a partition because there are three partitions. But the table access was only started two times. So you know one of the partitions was skipped.
And you can get similar using the nested table. Instead of using member of, you can place the array in a table operator. This returns it as rows.
Join on the output of this and partition pruning certainly is possible:
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$))If you're not getting partition pruning, please post a complete test case that shows:
- create table
- inserts
- the execution displayed by dbms_xplan.display_cursor above
and we can help figure out what's going on.