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;
declare
dept_alea number(2);
name_alea varchar2(30);
last_alea varchar2(30);
begin
for i in 1..&limit
loop
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);
commit;
end loop;
end;
/

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:

@flush.sql
select * from np where dept_no=5;

@flush.sql
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.