Sunday, December 30, 2007

When the use of Oracle range Partitioning is effective

Have you ever wondered what is the point (number of rows) where the use of Oracle range partitioning start been effective? , we all know that there is some internal overhead in using partitioning but at what moment that overhead pays off.

For this test I created 2 tables as follow:

Non partitioned Table

create table np (id number primary key, name varchar2(30), last_name varchar2(30),
age number, dept_no number);

Partitioned Table

create table pt (id number primary key, name varchar2(30), last_name varchar2(30),
age number, dept_no number)
partition by range (dept_no)
(partition dept1 values less than (2),
partition dept2 values less than (3),
partition dept3 values less than (4),
partition dept4 values less than (5),
partition dept5 values less than (6),
partition dept6 values less than (7),
partition dept7 values less than (8),
partition dept8 values less than (9),
partition dept9 values less than (10),
partition dept10 values less than (maxvalue));

In addition I created 2 indexes one for each table on the column dept_no. For the partition table it was a local index

I used the following sql to populate the tables with different amount of data.

set serveroutput on;
dept_alea number(2);
name_alea varchar2(30);
last_alea varchar2(30);
for i in 1..&limit
select dbms_random.value(1,10) into dept_alea from dual;
select dbms_random.string('U',30) into name_alea from dual;
select dbms_random.string('U',30) into last_alea from dual;
insert into &tabla
values (i, name_alea, last_alea, 37, dept_alea);
dbms_output.put_line (i);
end loop;

In order to be partial and try to obtain valid information I did flushed the buffer cache and shared pool after each query. These are the queries I used:

select * from np where dept_no=5;

select * from pt where dept_no=5;

The following are the results I obtained

Number of Rows Non Partition Time Partition Time

100 Elapsed: 00:00:00.11 Elapsed: 00:00:00.51
1000 Elapsed: 00:00:00.14 Elapsed: 00:00:00.29
10000 Elapsed: 00:00:00.67 Elapsed: 00:00:00.94
11500 Elapsed: 00:00:00.87 Elapsed: 00:00:01.08
12000 Elapsed: 00:00:00.84 Elapsed: 00:00:01.05
12500 Elapsed: 00:00:01.25 Elapsed: 00:00:01.30
15000 Elapsed: 00:00:01.27 Elapsed: 00:00:01.42
17500 Elapsed: 00:00:01.63 Elapsed: 00:00:01.59
18000 Elapsed: 00:00:01.64 Elapsed: 00:00:01.40
20000 Elapsed: 00:00:04.24 Elapsed: 00:00:01.83

Conclusion: Now you know and you don't have to test, if you will have more than 17,500 rows
in a table, and you can range partition and will query or access the table by the partition key, then range partitioning is a good idea. If you are wondering this test was done in 11.0.6 EE on a Intel dual core 1.3Ghz HP computer on Oracle Unbreakable Linux.