What does 1048575 in the Pstop column in the explain plan indicate?

The answer lies in the dictionary view ALL_PART_TABLES (or DBA_PART_TABLES).
The column partition_count is the total number of partitions, but for interval partitioned tables, this number is always 1048575.


When you explain a SQL statement that is accessing a partitioned table using interval partitioning, you may notice how the number 1048575 always shows up in the Pstop column of your EXPLAIN PLAN output.

My table and the local index is partitoned as follows:

CREATE TABLE MYTABLE
(
  SEKNUM           NUMBER(19)            DEFAULT "MYTAB_MYSEQ"."NEXTVAL" NOT NULL,
  COL1             VARCHAR2(200 CHAR)    NOT NULL,
  COL2             VARCHAR2(100 CHAR)    NOT NULL,
PARTITION BY RANGE (SEKNUM)
INTERVAL( 100000)
);

CREATE UNIQUE INDEX UK_SEK ON HENDELSER_PART_ST1_S.HENDELSER
(SEKVENSNUMMER)
  LOCAL;

This gives me a table with about 200 partitions.

When executing a query using the > operator, like below:

set lines 200
set autot on explain

SELECT count(*)
FROM   mytable
WHERE  seknum > 430000;

It would generate the following plan

--------------------------------------------------------------------------------------------------------
| Id  | Operation                 | Name       | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
--------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT          |            |     1 |     6 | 41342   (4)| 00:00:02 |       |       |
|   1 |  SORT AGGREGATE           |            |     1 |     6 |            |          |       |       |
|   2 |   PARTITION RANGE ITERATOR|            |    20M|   115M| 41342   (4)| 00:00:02 |     5 |1048575|
|*  3 |    INDEX RANGE SCAN       | UK_SEK     |    20M|   115M| 41342   (4)| 00:00:02 |     5 |1048575|
--------------------------------------------------------------------------------------------------------

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

   3 - access("SEKNUM">430000)

The Pstop value can be confusing. Obviously, you don't have that many partitions.
To verify that partition pruning is indeed working, change the operator from > ("greater than") to < ("less than"), and rerun the query:

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

| Id  | Operation                 | Name       | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |

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

|   0 | SELECT STATEMENT          |            |     1 |     6 |   887   (4)| 00:00:01 |       |       |

|   1 |  SORT AGGREGATE           |            |     1 |     6 |            |          |       |       |

|   2 |   PARTITION RANGE ITERATOR|            |   431K|  2525K|   887   (4)| 00:00:01 |     1 |     5 |

|*  3 |    INDEX RANGE SCAN       | UK_SEK     |   431K|  2525K|   887   (4)| 00:00:01 |     1 |     5 |

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



Predicate Information (identified by operation id):

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



3 - access("SEKNUM"<430000)

You now see that the Pstop column is 5, so Oracle search partitions 1 through 4 for the rows.

These are:

set lines 200
col partition_name format a30
col high_value format a20

select partition_name,partition_position,high_value
from dba_tab_partitions 
where table_NAME='MYTABLE'
and table_owner='SCOTT'
and partition_position between 1 and 4;

PARTITION_NAME                 PARTITION_POSITION HIGH_VALUE          
------------------------------ ------------------ --------------------
P1_INIT                                         1 100001              
SYS_P9545                                       2 200001              
SYS_P9548                                       3 300001              
SYS_P9551                                       4 400001              

4 rows selected.