List partitions

Despite the title and content, this article is more about thinking and testing than it is about list partitions as such. It’s just based on a little thought experiment with list partitioning.

I have a list-partitioned table, with no indexes, defined as follows:

rem
rem     Script:         pt_l_elimination.sql
rem     Author:         Jonathan Lewis
rem     Dated:          Oct 2010
rem

create table area_sales (
        area            varchar2(10)    not null,
        dated           date            not null,
        quantity        number(6),
        value           number(8,2),
        padding         char(100),
        constraint as_area_ck check (area in ('England','Ireland','Scotland','Wales'))
)
partition by list (area) (
        partition england values ('England'),
        partition scotland values ('Scotland'),
        partition ireland values ('Ireland'),
        partition wales values ('Wales')
)
;

You’ll notice that there is no default partition, the partitioning column is declared as not null and is limited by a check constraint to a very specific set of values, and that every partition is defined to hold exactly one of the legal partition key values. Given those restrictions you might like to think about which of the following queries will be able to do perfect partition elimination:

select  count(*)
from    area_sales 
where   area = 'England'
;

select  count(*)
from    area_sales 
where   area in ( 'England', 'Ireland', 'Wales')
;

select  count(*)
from    area_sales 
where   area != 'England'
;

select  count(*)
from    area_sales 
where   area not in ( 'England', 'Ireland', 'Wales')
;

select  count(*)
from    area_sales 
where   area between 'England' and 'Ireland'
;

Once you’ve thought about what’s likely to happen, you might want to create and populate the table with a few thousand rows and run some tests to see if your thoughts were correct. Here’s a simple statement to spread some data evenly through the tables:

insert into area_sales
select
        decode(mod(rownum,4),
                0,'England',
                2,'Scotland',
                1,'Ireland',
                3,'Wales'
        ),
        sysdate + 0.01 * rownum,
        rownum,
        rownum,
        rownum
from
        all_objects
where
        rownum <= 30000
;

Once you’ve done the tests, and satisfied yourself that you understand what’s going on, you might start thinking about a few variations that might make the behaviour change, such as:

you add a default partition

you add a default partition and remove the check constraint

you add a default partition and remove the not null constraint

you define partitions that hold multiple values

you change the order of partitions so the key values don’t appear in alphabetical order

you use a different version of Oracle

I’m not going to tell you the answers to these questions. The point I want to make is that this is the type of model, and these are the types of question you should ask yourself when you start to investigate a feature to see whether it will behave as well as you hope, and what special benefits you might get from it.

In fact, these are the questions I’m asking myself right now as I look at a system which is using list-partitioned tables to hold (lots of) sales data. I think it might be a good idea to restructure the table to disallow nulls, include a check constraint, and hold just one key value per partition – and then modify some of the code to get rid of “not in” and “!=” predicates.

The trouble is it’s a massive table with lots of indexes, the number of partitions would jump from eight to 40, and the number of times that rows would migrate due to partition key updates would increase; so the cost of restructuring would be large and there would be some fairly undesirable impact on DML that would offset the benefits of more efficient queries.

Update (June 2020)

I was prompted by a question on the Oracle developer forum to re-discover this note and decided to recreate and re-run the simple tests above. Originally I had run the tests under 11.1.0.6, and two of the queries ( “area != ‘England'”, and “area not in (‘England’,’Ireland’,’Wales’)”) had failed to do partition elimination, producing the following execution plans:

----------------------------------------------------------------------------------
| Id  | Operation           | Name       | Rows  | Bytes | Cost  | Pstart| Pstop |
----------------------------------------------------------------------------------
|   0 | SELECT STATEMENT    |            |     1 |     8 |   107 |       |       |
|   1 |  SORT AGGREGATE     |            |     1 |     8 |       |       |       |
|   2 |   PARTITION LIST ALL|            | 22500 |   175K|   107 |     1 |     4 |
|*  3 |    TABLE ACCESS FULL| AREA_SALES | 22500 |   175K|   107 |     1 |     4 |
----------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   3 - filter("AREA"<>'England')

----------------------------------------------------------------------------------
| Id  | Operation           | Name       | Rows  | Bytes | Cost  | Pstart| Pstop |
----------------------------------------------------------------------------------
|   0 | SELECT STATEMENT    |            |     1 |     8 |   107 |       |       |
|   1 |  SORT AGGREGATE     |            |     1 |     8 |       |       |       |
|   2 |   PARTITION LIST ALL|            | 12656 |    98K|   107 |     1 |     4 |
|*  3 |    TABLE ACCESS FULL| AREA_SALES | 12656 |    98K|   107 |     1 |     4 |
----------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   3 - filter("AREA"<>'England' AND "AREA"<>'Ireland' AND "AREA"<>'Wales')

Note, particularly, the operator “Partition list all”.

Upgrade to 12c, and the optimizer has been enhanced – quite cleverly – and in this test I’ve used rowsource execution statistics to show that partition elimination really is happening at runtime:

----------------------------------------------------------------------------------------------------------------------------
| Id  | Operation              | Name       | Starts | E-Rows | Cost (%CPU)| Pstart| Pstop | A-Rows |   A-Time   | Buffers |
----------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT       |            |      1 |        |   389 (100)|       |       |      1 |00:00:00.01 |     591 |
|   1 |  SORT AGGREGATE        |            |      1 |      1 |            |       |       |      1 |00:00:00.01 |     591 |
|   2 |   PARTITION LIST INLIST|            |      1 |  22500 |   389   (3)|   KEY |   KEY |  22500 |00:00:00.01 |     591 |
|   3 |    TABLE ACCESS FULL   | AREA_SALES |      3 |  22500 |   389   (3)|   KEY |   KEY |  22500 |00:00:00.01 |     591 |
----------------------------------------------------------------------------------------------------------------------------

----------------------------------------------------------------------------------------------------------------------------
| Id  | Operation              | Name       | Starts | E-Rows | Cost (%CPU)| Pstart| Pstop | A-Rows |   A-Time   | Buffers |
----------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT       |            |      1 |        |   131 (100)|       |       |      1 |00:00:00.01 |     197 |
|   1 |  SORT AGGREGATE        |            |      1 |      1 |            |       |       |      1 |00:00:00.01 |     197 |
|   2 |   PARTITION LIST INLIST|            |      1 |  12656 |   131   (4)|   KEY |   KEY |   7500 |00:00:00.01 |     197 |
|   3 |    TABLE ACCESS FULL   | AREA_SALES |      1 |  12656 |   131   (4)|   KEY |   KEY |   7500 |00:00:00.01 |     197 |
----------------------------------------------------------------------------------------------------------------------------

There are two points to note here – first the operation “Partition list inlist”, and in both cases that has resulted in the number of Starts for the “Table access full” being less than four; secondly the absence of any predicate information (and that isn’t a bug, or accident in copying).

While deriving the INLIST, the optimiser has managed to take advantage of the fact that all of my partitions are defined by exactly one key value, so (for example) it knows that “area != ‘England'” will eliminate all the data from the partition that holds ‘England’, so it doesn’t need to visit the ‘England’ partition therefore it doesn’t need to use the predicate on any partition.

A very similar, but slightly more complex, argument holds with my “not in ()” predicate, viz:  for each of England, Ireland, Wales, the value will eliminate all the data from the partition that holds that value, so the predicate never needs to be used – Oracle will not be visiting a partition where the predicate would discard a subset of that partition’s data.