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 t1 (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 select case 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; commit; execute dbms_stats.gather_table_stats(user,'T1');
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.
select * from t1;
This is our base test: select all data from our partitioned table:
select * from t1 where c1='ABC';
select * from t1 where c1='ACD';
Filtering on one of the values of the partition key, ‘ABC’:
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.