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.
Posts
