Posted by One of the nice things about partitioning in the database is that partition pruning can quickly eliminate the requirement to read large amounts of data when the partitioning key(s) are appropriately provided in your queries (and DML). This also extends to queries where the values provided do not map to any partitions.
Here’s a simple example of a range partitioned table which only cover the values from (less than) 1 to a ceiling of 6.
SQL> create table t
2 partition by range(x)
3 ( partition p0 values less than (1),
4 partition p1 values less than (2),
5 partition p2 values less than (3),
6 partition p3 values less than (4),
7 partition p4 values less than (5),
8 partition p5 values less than (6)
9 )
10 as
11 select rownum x from dual connect by level <= 5;
Table created.
Let’s see what happens when I run a query for a value that by definition cannot possibly exist in the table.
SQL> select * from t where x = 1235;
no rows selected
SQL> select * from table(dbms_xplan.display_cursor);
PLAN_TABLE_OUTPUT
-----------------------------------------------------------------------------------------------
SQL_ID auv890vfz95vu, child number 0
-------------------------------------
select * from t where x = 1235
Plan hash value: 4294331813
----------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |
----------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 2 (100)| | | |
| 1 | PARTITION RANGE EMPTY| | 1 | 3 | 2 (0)| 00:00:01 |INVALID|INVALID|
|* 2 | TABLE ACCESS FULL | T | 1 | 3 | 2 (0)| 00:00:01 |INVALID|INVALID|
----------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter("X"=1235)
That’s pretty cool. You can see that terms “EMPTY” and “INVALID” in the plan. The database knows from the dictionary definitions of the partitions that there is no possible data for X=1235 and hence the table partitions do not need to be accessed at all.
I’ll now repeat the example, but using an INTERVAL partitioned table. I only need to define the first partition, but since the interval size is 1, it will create partitions identical to the range example above.
SQL> create table t
2 partition by range(x) interval(1)
3 ( partition p0 values less than (1) )
4 as
5 select rownum x from dual connect by level <= 5;
Table created.
SQL>
SQL> select * from t where x = 1235;
no rows selected
SQL> select * from table(dbms_xplan.display_cursor);
PLAN_TABLE_OUTPUT
-----------------------------------------------------------------------------------------------
SQL_ID auv890vfz95vu, child number 0
-------------------------------------
select * from t where x = 1235
Plan hash value: 2931986080
-----------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |
-----------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 6 (100)| | | |
| 1 | PARTITION RANGE SINGLE| | 1 | 3 | 6 (0)| 00:00:01 | 1236 | 1236 |
|* 2 | TABLE ACCESS FULL | T | 1 | 3 | 6 (0)| 00:00:01 | 1236 | 1236 |
-----------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter("X"=1235)
Notice that the results here are slightly different. Even though no partition exists for X=1235, for an interval partitioned table the optimizer suggests that it will scan the (fictitious) partition 1236. Is this a bug?
To explain this, I like to think of interval partitioning as an extension of the deferred segment implementation we already have in the database. The database can defer creating a physical segment when a table (or table partition) is created. The segment only needs to come into existence when the first row is created for that table. The way I interpret interval partitioning, is that it takes deferring segments to another level, namely deferring the dictionary metadata as well until it is needed. You can think of interval partition table DDL as creating all of the partitions in the table immediately (ie, they all exist) but the dictionary metadata to support them is deferred until required. Hence partition 1236 in the example does indeed exist – it is just that we have not yet created the dictionary entries for it yet – we won’t do so until the first row is created.
Based on this concept, you can still see the concept of EMPTY/INVALID partitions for a query on an interval partitioned table if you nominate a value that would yield a non-existent partition. You may be thinking “How can we have a non-existent partition for an interval partitioned table?”. Don’t forget that there is an upper limit on the number of partitions you can have for any partitioned table, so all I need do is nominate a value for X that would yield a partition above that upper bound.
SQL> select * from t where x = 1235123123;
no rows selected
SQL> select * from table(dbms_xplan.display_cursor);
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------
SQL_ID dfthyzy792fah, child number 0
-------------------------------------
select * from t where x = 1235123123
Plan hash value: 4294331813
----------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |
----------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 2 (100)| | | |
| 1 | PARTITION RANGE EMPTY| | 1 | 3 | 2 (0)| 00:00:01 |INVALID|INVALID|
|* 2 | TABLE ACCESS FULL | T | 1 | 3 | 2 (0)| 00:00:01 |INVALID|INVALID|
----------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter("X"=1235123123)
Learning is not a spectator sport 