Partitioning without Enterprise Edition

Cyrille Modiano

OCP Oracle DBA in Montreal at Desjardins

Latest posts by Cyrille Modiano (see all)

Oracle Partitioning

Partitioning is part of the Oracle Enterprise Edition, this is an expensive option.
Sometimes you just have one big table storing historical data in your database and this is just too much to pay. In that case you can use the procedure below to “emulate” the partitioning. I say “emulate” because you won’t have all the performance benefits and functionnalities of the partitionning. This method is called manual partitioning.

Manual partitioning concept

The concept is really simple, instead of creating partitions, you will create tables corresponding each to a range of values.
For exemple you have to keep 30 days of data with several millions rows of log every day.
If you have only one table, your table should have almost 100 millions rows at the end of the month which will result to bad performances in queries against this table.
What we will do is to create a table for each day of the month, each table will contain approximately 10 millions rows resulting to better performance.

Example

Let’s create the tables:

Now we will create an index on the date column because we will have to query our table based on that column:

Now, to be able to query our tables we will have to create a view wich will make a union all of all our tables:

Now you tell me, ok this is cool but how do I insert values in my tables? You’re right, as we are not using a simple view (the view use an UNION ALL), it is not possible to insert directly, what you need to do is create a procedure which will insert the data in the good table for you, here is an example :

Once this procedure created, we will perform an insert by calling the procedure insted of an insert statement:

This statement should have inserted my data in LOG_TABLE_3, let’see :

Now what happens if we query the view, how Oracle will deal with all our tables:

Ok, it works, but as we do not use the partitioning option we have no global index allowing Oracle to know in which partition our data is.

However this can be a good solution if you cannot afford the cost of the Partitioning option and it could be used with all Oracle versions from Oracle 7 to Oracle 12c.

See you soon !

2 thoughts on “Partitioning without Enterprise Edition

    1. Thank you kishore, this idea is not from me, it was the way to do partitioning before there was an option for that 🙂
      It is still a good option if you can’t afford the partitioning option.

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.