Friday, August 31, 2007

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

3 comments:

Unknown said...

Hi,

I use this procedure to store historical database based on a sample time.
First table contain current day, second table yesterday and so on with a total of 30 tables.
The problem is when I query the viw based on the sample time, oracle have to scan the index of all 30 tables to see if it vontains data based on the date of the query.
Is there a way to indicate to oracle which table contains which date interval ?

Thank you very much.

Cyrille

Unknown said...

Oracle will pay attention to check constraints on the partition tables when running the view query. Just make a check constraint that limits the dates in each table. You can see it skipping the tables in and explain plan.

Unknown said...

I tried with check constraints but still it's scanning all tables.