My Oracle Support Banner

No Partition Pruning With Nested Table (Doc ID 2811764.1)

Last updated on NOVEMBER 12, 2021

Applies to:

Oracle Database - Enterprise Edition - Version 19.11.0.0.0 and later
Information in this document applies to any platform.

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

To view full details, sign in with your My Oracle Support account.

Don't have a My Oracle Support account? Click to get started!


In this Document
Symptoms
Cause
Solution
References


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