Oracle Partitioning the old fashion way

Long long time ago in a in a galaxy far far away, no just kidding about the galaxy part but it was really around Oracle version 8 / 8i when Oracle introduced Oracle partitioning. When there was not partitioning Database Administrators and developers had to come up with different techniques to emulate partitioning (manual partitioning). It recently called my attention that a software vendor didn't have a clue of what I was talking about when I mentioned "manual partition". In their development data model they have 1 single table that keeps historic information and they wanted their customer to pay for Oracle Enterprise Edition + Oracle Partitioning for an application that really requires Oracle Standard Edition (up to 4 CPUs). This translates into several thousand or dollars more in software and maintenance fees. That motivated me to post this blog when I will provide a general idea about the things we use to do, then you take it from here and change it to fit your needs.

In this example we are going to simulate range partitioning by date; instead of a partition table we are going to use 2 tables, table name "primero" (first in Spanish) which will hold the most recent 3 months of data, and table "segundo" (second in Spanish) will hold the rest of the data.

First Step - Table Creation

create table rorta.primero (cust_id number primary key, cust_name varchar2(30),
cust_lname varchar2(30), fecha date)
create table rorta.segundo (cust_id number primary key, cust_name varchar2(30),
cust_lname varchar2(30), fecha date)

Second Step - View Creation (This will allow you to query the 2 tables as a single table)

create view rorta.customer as select * from primero
union all select * from segundo;

Third Step - Create the procedure to insert into the table.

create procedure rorta.insertar as (c_id primero.CUST_ID%TYPE, c_name primero.CUST_NAME%TYPE,
c_lname primero.CUST_LNAME%TYPE, c_fecha primero.FECHA%TYPE)
begin
if c_fecha <= sysdate - 90 then insert into primero values (c_id, c_name, c_lname, c_fecha); else insert into segundo values (c_id, c_name, c_lname, c_fecha); end if; end; Four Step - Insert information in the table.

Fifth Step - Insert into the table

exec rorta.insertar(1,'Rafael','Orta','28-APR-1970')

The way that it works is that instead of executing an insert statement you will pass to the store procedure the data you want to insert as I did above. If you want to select, delete or update you could create similar store procedures to do that operation or you will need to go directly to the table where the data is hosted primero or segundo. If you need to query all the data use the view we created.


I hope this helps, contact me if you need further details at Consulting@Oraprofessionals.com