Oracle: Partition by List – Example

The following post will walk you trough an exercise of creating a partitioned table, using the list partitioning, populating and testing the partition pruning.

Please note I will also post the scripts at the end of the post, so you can download to replicate the work.

Creating our Work Table:

I’m creating a sample table T1 with 4 columns, with the following structure:



create table 
(c1 char(3) not null
, c2 date
, c3 number
, c4 varchar2(100))
partition by list (c1)
 partition ACB values ('ABC')
 , partition ACD values ('ACD')

We want to partition this table by the C1 column, which is more, what i call, a Category column: not null, splitting the data into a couple of finite categories. In our case we have 2 categories: ‘ABC’ and ‘ACD’:

I’m generating a sample data set of about 400 000 rows, in this particular case, evenly split between the two partitions.

And afterwards, please note a very important step, I’m gathering my stats 🙂

insert into t1 
 when mod(level,20) <11 then 'ABC'
 when mod(level,20) >10 then 'ACD'
 end as c1
 , sysdate+level/24
 , level
 , 'test record '||level
from dual
connect by level <=400000;


execute dbms_stats.gather_table_stats(user,'T1');

Partition Pruning:

Now, let’s run a couple of test to see  how partition is actually helping our performance.

Please note that i used “Autotrace” to show the actual plan and the partition pruning for our selects.

First scenario:

select * from t1;

This is our base test: select all data from our partitioned table:


Second scenario:

select * from t1
where c1='ABC';


select * from t1
where c1='ACD';

Filtering on one of the values of the partition key, ‘ABC’:


Third scenario:

select * from t1
where c1 in ('ABC', 'ACD');

Filtering on both values of our partition key (‘ABC’ and ‘ACD’):



I’ve been using the SQLDeveloper Autotrace to demonstrate the partition pruning.

As you can see, the selects will do partition pruning when filtering on one or multiple partitions.



Please note scripts are uploaded as PDFs but you can still copy the code in.


2 thoughts on “Oracle: Partition by List – Example”

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

%d bloggers like this: