Skip to Main Content
  • Questions
  • Partition pruning with MEMBER OF operator

Breadcrumb

Question and Answer

Chris Saxon

Thanks for the question, HRVOJE.

Asked: May 22, 2018 - 9:48 am UTC

Last updated: August 02, 2021 - 10:27 am UTC

Version: 12.1

Viewed 1000+ times

You Asked

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



and we said...

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.

Rating

  (2 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

19.11 - no partition pruning ?

Jim Dickson, July 28, 2021 - 3:34 pm UTC

Chris
I recreated your test but used apex_t_number instead - hopefully that does not invalidate the comparison.

In 19.11, I do not see partition pruning. My actual use case involves SQL Macro, so this is simplified version.

SQL> SELECT /*+ gather_plan_statistics */*
2 FROM A_TABLE
3 join table(apex_t_number(1,4)) t
4 on COL_1 = column_value;

COL_1 COLUMN_VALUE
---------- ------------
1 1
4 4

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

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID aaqxhdvh249x6, child number 0
-------------------------------------
SELECT /*+ gather_plan_statistics */* FROM A_TABLE join
table(apex_t_number(1,4)) t on COL_1 = column_value

Plan hash value: 296858652

-----------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | Pstart| Pstop | A-Rows |
-----------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | | | 2 |

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
|* 1 | HASH JOIN | | 1 | 1 | | | 2 |
| 2 | PARTITION LIST ALL | | 1 | 1 | 1 | 3 | 3 |
| 3 | TABLE ACCESS FULL | A_TABLE | 3 | 1 | 1 | 3 | 3 |
| 4 | COLLECTION ITERATOR CONSTRUCTOR FETCH| | 1 | 8168 | | | 2 |
-----------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

1 - access("COL_1"=VALUE(KOKBF$))


PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Note
-----
- dynamic statistics used: dynamic sampling (level=2)
Connor McDonald
July 29, 2021 - 3:10 am UTC

Once you get to realistic sizes you'll most likely see pruning via bloom filter, eg

SQL> create table A_TABLE  (
  2    col_1 integer, padding char(100)
  3  ) partition by list (col_1) (
  4    partition p1 values (1),
  5    partition p4 values (4),
  6    partition p10 values (10)
  7  );

Table created.

SQL>
SQL> insert into A_TABLE select 1, rownum from dual connect by level <= 100000;

100000 rows created.

SQL> insert into A_TABLE select 4, rownum from dual connect by level <= 100000;

100000 rows created.

SQL> insert into A_TABLE select 10, rownum from dual connect by level <= 100000;

100000 rows created.

SQL> commit;

Commit complete.

SQL> exec dbms_stats.gather_table_stats('','A_TABLE');

PL/SQL procedure successfully completed.

SQL>
SQL> SELECT /*+ gather_plan_statistics */ count(*)
  2  FROM A_TABLE
  3  join table(apex_t_number(1,4)) t
  4  on COL_1 = column_value;

  COUNT(*)
----------
    200000

SQL> @exp

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------
--------------------------------------------------------------------------------------------------------------------------------------------------
------------------------------------------------------------------------------------------------------------------------------
SQL_ID  5px31jh5gb7hz, child number 0
-------------------------------------
SELECT /*+ gather_plan_statistics */ count(*) FROM A_TABLE join
table(apex_t_number(1,4)) t on COL_1 = column_value

Plan hash value: 3926452747

--------------------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                                | Name    | Starts | E-Rows | A-Rows |   A-Time   | Buffers | Reads  |  OMem |  1Mem | Used-Mem |
--------------------------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                         |         |      1 |        |      1 |00:00:00.14 |    3078 |   3036 |       |       |          |
|   1 |  SORT AGGREGATE                          |         |      1 |      1 |      1 |00:00:00.14 |    3078 |   3036 |       |       |          |
|*  2 |   HASH JOIN                              |         |      1 |    816M|    200K|00:00:00.12 |    3078 |   3036 |  2546K|  2546K|  734K (0)|
|   3 |    PART JOIN FILTER CREATE               | :BF0000 |      1 |   8168 |      2 |00:00:00.01 |       0 |      0 |       |       |          |
|   4 |     COLLECTION ITERATOR CONSTRUCTOR FETCH|         |      1 |   8168 |      2 |00:00:00.01 |       0 |      0 |       |       |          |
|   5 |    PARTITION LIST JOIN-FILTER            |         |      1 |    300K|    200K|00:00:00.08 |    3078 |   3036 |       |       |          |
|   6 |     TABLE ACCESS FULL                    | A_TABLE |      2 |    300K|    200K|00:00:00.11 |    3078 |   3036 |       |       |          |
--------------------------------------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("COL_1"=VALUE(KOKBF$))


If you're new to Bloom filters, a video here


Member of not using index search

Saša Petković, August 02, 2021 - 7:46 am UTC

Hi,

Can you please comment following example:

CREATE TYPE NUMBER_TAB_TYPE IS TABLE OF NUMBER;

create table test_member (id number primary key, description varchar2(255));

insert into test_member (id, description)
select rownum, dbms_random.string ('a', '4')
from dual
connect by level <= 1000000;

commit;

begin
dbms_stats.gather_table_stats(ownname=>user, tabname=>'TEST_MEMBER', estimate_percent=>null, method_opt=>'FOR ALL COLUMNS SIZE 1');
end;


alter session set events '10046 trace name context forever, level 12';

declare
NUMBER_TAB NUMBER_TAB_TYPE := NUMBER_TAB_TYPE();
begin
NUMBER_TAB.extend;
NUMBER_TAB(1) := 656899;
for c in (SELECT * FROM test_member WHERE id MEMBER OF NUMBER_TAB) loop
dbms_output.put_line (c.id);
end loop;
end;

declare
NUMBER_TAB NUMBER_TAB_TYPE := NUMBER_TAB_TYPE();
begin
NUMBER_TAB.extend;
NUMBER_TAB(1) := 656899;
for c in (SELECT * FROM test_member WHERE id in (select column_value from table(NUMBER_TAB))) loop
dbms_output.put_line (c.id);
end loop;
end;

ALTER SESSION SET EVENTS '10046 trace name context off';

Trace shows next(when passing through tkprof):

SELECT * FROM TEST_MEMBER WHERE ID MEMBER OF :B1

call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 1 0.34 0.34 0 2073 0 1
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 3 0.34 0.34 0 2073 0 1

Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 103 (recursive depth: 1)
Number of plan statistics captured: 1

Rows (1st) Rows (avg) Rows (max) Row Source Operation
---------- ---------- ---------- ---------------------------------------------------
1 1 1 TABLE ACCESS STORAGE FULL TEST_MEMBER (cr=2073 pr=0 pw=0 time=343451 us starts=1 cost=574 size=500000 card=50000)

SELECT * FROM TEST_MEMBER WHERE ID IN (SELECT COLUMN_VALUE FROM TABLE(:B1 ))

call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 1 0.00 0.00 0 4 0 1
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 3 0.00 0.00 0 4 0 1

Misses in library cache during parse: 1
Misses in library cache during execute: 1
Optimizer mode: ALL_ROWS
Parsing user id: 103 (recursive depth: 1)
Number of plan statistics captured: 1

Rows (1st) Rows (avg) Rows (max) Row Source Operation
---------- ---------- ---------- ---------------------------------------------------
1 1 1 NESTED LOOPS (cr=4 pr=0 pw=0 time=48 us starts=1)
1 1 1 NESTED LOOPS (cr=3 pr=0 pw=0 time=40 us starts=1 cost=31 size=12 card=1)
1 1 1 SORT UNIQUE (cr=0 pr=0 pw=0 time=25 us starts=1 cost=29 size=2 card=1)
1 1 1 COLLECTION ITERATOR PICKLER FETCH (cr=0 pr=0 pw=0 time=5 us starts=1 cost=29 size=2 card=1)
1 1 1 INDEX UNIQUE SCAN SYS_C0030542 (cr=3 pr=0 pw=0 time=14 us starts=1 cost=1 size=0 card=1)(object id 7591782)
1 1 1 TABLE ACCESS BY INDEX ROWID TEST_MEMBER (cr=1 pr=0 pw=0 time=6 us starts=1 cost=1 size=10 card=1)

obviously for "member of" optimiser is not using primary key index. Is there any way we can achieve it?
Thanks

Chris Saxon
August 02, 2021 - 10:27 am UTC

If you add an index hint, you'll see that the optimizer uses a full index scan, instead of a range or unique scan:

SELECT /*+ index ( test_member ( id ) ) */ * 
FROM   test_member WHERE id MEMBER OF NUMBER_TAB_TYPE ( 656899 ) ;

select * 
from   dbms_xplan.display_cursor(format => 'BASIC LAST');

------------------------------------------------------------               
| Id  | Operation                           | Name         |               
------------------------------------------------------------               
|   0 | SELECT STATEMENT                    |              |               
|   1 |  TABLE ACCESS BY INDEX ROWID BATCHED| TEST_MEMBER  |               
|   2 |   INDEX FULL SCAN                   | SYS_C0028109 |               
------------------------------------------------------------


With an index unique or range scan, the database is asking:

Is this value in the index?

With MEMBER OF, you're sort-of doing the opposite, asking:

Are any of the ID values in the nested table values?

Thus it has to read the entire table/index.

When you use the nested table in the TABLE subquery, you're converting it to rows - i.e. single values that the database can use to efficiently search the index.

More to Explore

Administration

Need more information on Administration? Check out the Administrators guide for the Oracle Database