No Partition Pruning With Nested Table (Doc ID 2811764.1)

Last updated on JULY 20, 2024

Applies to:

Symptoms

Problem Summary
---------------------------------------------
No Partition pruning with Nested Table

Testcase to illustrate the problem:
===================================
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'));

CREATE or replace 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'));

without the nested table:

------------------------------------------------------------------------------
--
--------

| 0 | SELECT STATEMENT | | 1 | | |
|
2 |

| 1 | NESTED LOOPS | | 1 | 2 | |
|
2 |

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------
--
| 2 | VIEW | VW_NSO_1 | 1 | 2 | |
|
2 |

| 3 | HASH UNIQUE | | 1 | 2 | |
|
2 |

| 4 | UNION-ALL | | 1 | | |
|
2 |

| 5 | FAST DUAL | | 1 | 1 | |
|
1 |

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------
--

| 6 | FAST DUAL | | 1 | 1 | |
|
1 |

| 7 | PARTITION LIST ITERATOR| | 2 | 1 | KEY | KEY ▶▶▶▶ ▶▶▶▶ ▶▶▶▶

|* 8 | TABLE ACCESS FULL | A_TABLE | 2 | 1 | KEY | KEY

With nested table:
| 0 | SELECT STATEMENT | | 1 | |
| | 2 |

|* 1 | HASH JOIN | | 1 | 8168 |
| | 2 |

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------
--
| 2 | PARTITION LIST ALL | | 1 | 3 | ▶▶▶▶▶▶▶▶▶▶▶▶▶▶▶▶
1 | 3 | 3 |

| 3 | TABLE ACCESS FULL | A_TABLE | 3 | 3 |
1 | 3 | 3 |

| 4 | COLLECTION ITERATOR CONSTRUCTOR FETCH| | 1 | 8168 |

Cause

My Oracle Support provides customers with access to over a million knowledge articles and a vibrant support community of peers and Oracle experts.