Oracle SQL | Partition Pruning and Global Indexes

There is a common misconception that partition pruning does not help in the case of global indexes and only works with local indexes or full table scans (FTS).

It is understandable how this misconception arose: indeed, when operations like PARTITION RANGE ITERATOR, PARTITION RANGE SINGLE, etc., appear in execution plans, partition pruning becomes strongly associated with local indexes and FTS.

It is also clear why this is the most noticeable case: the exclusion of partitions in PARTITION RANGE ITERATOR operations is hard to miss, especially since there is a dedicated line for it in the execution plan.

However, this is not all that partition pruning can do. In fact, this way of thinking is not entirely valid, and I will demonstrate this with some simple examples.

Table Setup

Let’s assume we have a table tpart consisting of 3 partitions with the following columns:

pkey – the partitioning key. For simplicity, we will store only 1, 2, and 3, with each value corresponding to a separate partition.
notkey – a column for testing the global index, filled sequentially from 1 to 3000.
padding – a long column used to make table block accesses more noticeable.

create table tpart (pkey int, notkey int,padding varchar2(4000))
  partition by range(pkey)
  (
     partition p1 values less than (2)
    ,partition p2 values less than (3)
    ,partition p3 values less than (4)
  );

We insert 3,000 rows into the table so that:

Partition p1 contains 1,000 rows with pkey=1 and notkey values from 1 to 1000.
Partition p2 contains 1,000 rows with pkey=2 and notkey values from 1001 to 2000.
Partition p3 contains 1,000 rows with pkey=3 and notkey values from 2001 to 3000.

insert into tpart(pkey,notkey,padding)
  select ceil(n/1000) pkey, n, rpad('x',4000,'x') from xmltable('1 to 3000' columns n int path '.');
commit;

SQL> select pkey,min(notkey),max(notkey) from tpart group by pkey order by 1;

      PKEY MIN(NOTKEY) MAX(NOTKEY)
---------- ----------- -----------
         1           1        1000
         2        1001        2000
         3        2001        3000

Simple Partition Pruning Example

We start with a simple query that applies partition pruning. It should return nothing, since notkey values between 2500-2600 are in the third partition (pkey=3), but we explicitly specify pkey=1:

select count(*) from tpart where notkey between 2500 and 2600 and pkey=1;

SQL> select count(*) from tpart where notkey between 2500 and 2600 and pkey=1;

  COUNT(*)
----------
         0

SQL> select * from dbms_xplan.display_cursor('','','allstats last +predicate');

PLAN_TABLE_OUTPUT
-------------------------------------------------------------------------------------------
SQL_ID  0qzh3zxgpc65z, child number 0
-------------------------------------
select count(*) from tpart where notkey between 2500 and 2600 and pkey=1

Plan hash value: 3052279832

-------------------------------------------------------------------------------------------
| Id  | Operation               | Name  | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
-------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT        |       |      1 |        |      1 |00:00:00.01 |    1007 |
|   1 |  SORT AGGREGATE         |       |      1 |      1 |      1 |00:00:00.01 |    1007 |
|   2 |   PARTITION RANGE SINGLE|       |      1 |      1 |      0 |00:00:00.01 |    1007 |
|*  3 |    TABLE ACCESS FULL    | TPART |      1 |      1 |      0 |00:00:00.01 |    1007 |
-------------------------------------------------------------------------------------------

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

   3 - filter(("NOTKEY">=2500 AND "NOTKEY"<=2600 AND "PKEY"=1))

Here, we can clearly see that partition pruning worked due to the PARTITION RANGE SINGLE operation.

Global Index and Partition Pruning

Now, let’s create a global index on notkey (without including the partitioning key) and repeat the query:

SQL> create index ix_tpart on tpart(notkey);

Index created.

SQL> select count(*) from tpart where notkey between 2500 and 2600 and pkey=1;

  COUNT(*)
----------
         0

1 row selected.

SQL> select * from dbms_xplan.display_cursor('','','allstats last +predicate');

PLAN_TABLE_OUTPUT
---------------------------------------------------------------------------------------------------------------------------
SQL_ID  0qzh3zxgpc65z, child number 0
-------------------------------------
select count(*) from tpart where notkey between 2500 and 2600 and pkey=1

Plan hash value: 494535298

---------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                                   | Name     | Starts | E-Rows | A-Rows |   A-Time   | Buffers | Reads  |
---------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                            |          |      1 |        |      1 |00:00:00.01 |       2 |      1 |
|   1 |  SORT AGGREGATE                             |          |      1 |      1 |      1 |00:00:00.01 |       2 |      1 |
|*  2 |   TABLE ACCESS BY GLOBAL INDEX ROWID BATCHED| TPART    |      1 |      1 |      0 |00:00:00.01 |       2 |      1 |
|*  3 |    INDEX RANGE SCAN                         | IX_TPART |      1 |    102 |    101 |00:00:00.01 |       2 |      1 |
---------------------------------------------------------------------------------------------------------------------------

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

   2 - filter("PKEY"=1)
   3 - access("NOTKEY">=2500 AND "NOTKEY"<=2600)

Here’s the key observation: the Buffers column for the second row remains 2, meaning there were no logical reads from the table despite that the execution plan suggests that here we had to access and filter out 101 rows from the partition by the filter predicate “filter(PKEY=1)”.

To verify, let’s run the same query with pkey=3:

select count(*) from tpart where notkey between 2500 and 2600 and pkey=3

Plan hash value: 494535298

------------------------------------------------------------------------------------------------------------------
| Id  | Operation                                   | Name     | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                            |          |      1 |        |      1 |00:00:00.01 |     103 |
|   1 |  SORT AGGREGATE                             |          |      1 |      1 |      1 |00:00:00.01 |     103 |
|*  2 |   TABLE ACCESS BY GLOBAL INDEX ROWID BATCHED| TPART    |      1 |    102 |    101 |00:00:00.01 |     103 |
|*  3 |    INDEX RANGE SCAN                         | IX_TPART |      1 |    102 |    101 |00:00:00.01 |       2 |
------------------------------------------------------------------------------------------------------------------

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

   2 - filter("PKEY"=3)
   3 - access("NOTKEY">=2500 AND "NOTKEY"<=2600)

Here, it is clearly visible that accessing 101 rows from the table required 101 logical reads.

Overall, it immediately becomes apparent that in the previous example with pkey=1, partition pruning worked and helped us avoid approximately 100 LIO to partition blocks. However, to make this even more evident, let’s modify the predicate "pkey=1" to a more complex equivalent that disables partition pruning: pkey=pkey/pkey.

SQL> select count(*) from tpart where notkey between 2500 and 2600 and pkey=pkey/pkey;

  COUNT(*)
----------
         0

1 row selected.

SQL> select * from dbms_xplan.display_cursor('','','allstats last +predicate');

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------
SQL_ID  0q59p4akv4cm0, child number 0
-------------------------------------
select count(*) from tpart where notkey between 2500 and 2600 and pkey=pkey/pkey

Plan hash value: 4115825992

------------------------------------------------------------------------------------------------------------------
| Id  | Operation                                   | Name     | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                            |          |      1 |        |      1 |00:00:00.01 |     103 |
|   1 |  SORT AGGREGATE                             |          |      1 |      1 |      1 |00:00:00.01 |     103 |
|*  2 |   TABLE ACCESS BY GLOBAL INDEX ROWID BATCHED| TPART    |      1 |     34 |      0 |00:00:00.01 |     103 |
|*  3 |    INDEX RANGE SCAN                         | IX_TPART |      1 |    102 |    101 |00:00:00.01 |       2 |
------------------------------------------------------------------------------------------------------------------

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

   2 - filter("PKEY"="PKEY"/"PKEY")
   3 - access("NOTKEY">=2500 AND "NOTKEY"<=2600)

And voilà! Here we see 101 logical reads from the table in exactly the same query but with partition pruning disabled.

For clarity, let’s compare:

select count(*) from tpart where notkey between 2500 and 2600 and pkey=1;         = 2 LIO
select count(*) from tpart where notkey between 2500 and 2600 and pkey=pkey/pkey; = 103 LIO

In fact, this could have been easily noticed if I had used format=ALL or ADVANCED, or at least included +predicate (that’s why I always suggest to use format=>’advanced -qbregistry’):

SQL> select count(*) from tpart where notkey between 2500 and 2600 and pkey=2;

  COUNT(*)
----------
         0

SQL> select * from dbms_xplan.display_cursor('','','allstats last +predicate +partition');

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------------------------------------
SQL_ID  fw7yx554pvv4n, child number 0
-------------------------------------
select count(*) from tpart where notkey between 2500 and 2600 and pkey=2

Plan hash value: 494535298

----------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                                   | Name     | Starts | E-Rows | Pstart| Pstop | A-Rows |   A-Time   | Buffers |
----------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                            |          |      1 |        |       |       |      1 |00:00:00.01 |       2 |
|   1 |  SORT AGGREGATE                             |          |      1 |      1 |       |       |      1 |00:00:00.01 |       2 |
|*  2 |   TABLE ACCESS BY GLOBAL INDEX ROWID BATCHED| TPART    |      1 |      1 |     2 |     2 |      0 |00:00:00.01 |       2 |
|*  3 |    INDEX RANGE SCAN                         | IX_TPART |      1 |    102 |       |       |    101 |00:00:00.01 |       2 |
----------------------------------------------------------------------------------------------------------------------------------

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

   2 - filter("PKEY"=2)
   3 - access("NOTKEY">=2500 AND "NOTKEY"<=2600)

As you can now easily see, the Pstart/Pstop columns appear in the plan. This indicates that partition pruning works in the TABLE ACCESS BY ROWID operation, as Oracle determines which partition a row belongs to based on its ROWID and automatically filters out those that do not satisfy our partition key condition.

At this point, it becomes clear why the title of this blog post is somewhat misleading—the key issue is not the global index but rather the TABLE ACCESS BY ROWID operation. This can be demonstrated in an even simpler way:

Demonstrating Partition Pruning with ROWID

Let’s obtain the ROWID and object number for a row where notkey=2300:

SQL> select rowid,dbms_rowid.rowid_object(rowid) obj# from tpart where notkey=2300;

ROWID                    OBJ#
------------------ ----------
AAAU42AAMAAB9eNAAA      85558

Now, let’s query using this ROWID:

SQL> select pkey,notkey from tpart where rowid='AAAU42AAMAAB9eNAAA';

      PKEY     NOTKEY
---------- ----------

         3       2300

SQL> select * from dbms_xplan.display_cursor('','','allstats last +predicate +partition');


-------------------------------------
select pkey,notkey from tpart where rowid='AAAU42AAMAAB9eNAAA'

Plan hash value: 2140892464

-----------------------------------------------------------------------------------------------------------------------
| Id  | Operation                  | Name  | Starts | E-Rows | Pstart| Pstop | A-Rows |   A-Time   | Buffers | Reads  |
-----------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT           |       |      1 |        |       |       |      1 |00:00:00.01 |       1 |      1 |
|   1 |  TABLE ACCESS BY USER ROWID| TPART |      1 |      1 | ROWID | ROWID |      1 |00:00:00.01 |       1 |      1 |
-----------------------------------------------------------------------------------------------------------------------

We see that Oracle determines from the ROWID which specific partition needs to be accessed, confirming that our row is in the partition where pkey=3.

Now, let’s add the predicate "pkey=1", which will make the row not satisfy the partition key condition:

SQL> select * from tpart where rowid='AAAU42AAMAAB9eNAAA' and pkey=1;

no rows selected

SQL> select * from dbms_xplan.display_cursor('','','allstats last +predicate +partition');

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------
SQL_ID  1br7yh35w3sdv, child number 0
-------------------------------------
select * from tpart where rowid='AAAU42AAMAAB9eNAAA' and pkey=1

Plan hash value: 3283591838

----------------------------------------------------------------------------------------------------
| Id  | Operation                  | Name  | Starts | E-Rows | Pstart| Pstop | A-Rows |   A-Time   |
----------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT           |       |      1 |        |       |       |      0 |00:00:00.01 |
|*  1 |  TABLE ACCESS BY USER ROWID| TPART |      1 |      1 |     1 |     1 |      0 |00:00:00.01 |
----------------------------------------------------------------------------------------------------

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

   1 - filter("PKEY"=1)

Here, it becomes absolutely clear that no logical reads occurred because partition pruning worked, and Oracle did not access the third partition. Instead, it checked the partition key condition and immediately rejected the rowid without accessing any blocks.

Conclusion

Thus, the key takeaway is not about indexes at all, but rather about TABLE ACCESS operations. Partition pruning can still be effective within TABLE ACCESS operations—even without any indexes or explicit PARTITION operations (PARTITION [RANGE|HASH|LIST] [ITERATOR|ALL|SINGLE], etc.).

This works because Oracle only needs to check which partition a given ROWID belongs to in order to filter out unnecessary partitions efficiently.

PS. Read more about non-partioned indexes and partition pruning in details in this great series from Richard Foote: https://richardfoote.wordpress.com/2018/10/09/hidden-efficiencies-of-non-partitioned-indexes-on-partitioned-tables-part-ii-aladdin-sane/