Poor man’s partitioning with instead of triggers – PART 1

The Enterprise Edition of Oracle Corp’s. flagship database has many features. One of these is partitioning. Partitioning allows a user to segment table rows into separate physical storage structures. This can improve things such as performance and manageability.

Performance can be improved by utilizing what Oracle terms “partition pruning”. For example, let’s assume you have a table with one billion rows, which you then partition into 20 partitions based on date. If you ask for all rows with a date of August 18, 2010, Oracle is “smart” enough to prune and search only the partition in which the row you requested could possibly exist. This means it has to search 1/20 of the data it would have had to search in an un-partitioned table.

For manageability, you may be able to drop the oldest partition from some point in the past for which you no longer need the data. This is far faster and requires fewer resources than a delete of the same data.

However, as nice as it is, it is an additional cost option. Some users would like to have the ability to use it, but can’t afford to pay for it. In this case, there is a “poor man’s” option, which is the subject of this article. Since at least Oracle Release 8.0.6, Oracle has provided the ability for a user to insert records using a view. A view is normally thought of as a query whose definition is stored in the database. However, using what is called an “instead of” trigger, a user can create the view, and then create a trigger on the view which decides in which physical table to place the data. The view is usually (although it doesn’t have to be) a union of at least two tables, and is created with the instead of option. Below is a simple example:

create or replace view myview is
  select * from table1
  union all
  select * from table2
/

create or replace trigger mytrg
  instead of insert on myview
  for each row
begin
  if some_condition_met then
    insert into table1;
  else
    insert into table2;
end;
/

What this allows us to do is to code the trigger to put our rows in a physical table much as the partitioning option does.

There are some drawbacks to using this method:

1.The instead of trigger is dropped when the view is changed
2.dropping a table used in the view invalidates the view
3.all sql executed in the trigger is soft parsed. session_cursor_cache alleviates most of this as an issue, but it should be thoroughly tested for performance. The appendix of this document provides a python based stress tester that compares both partitioning and instead of triggers.

The best application for this is probably those tables where it is reasonably easy to forecast the demand for partitions. Monthly partitions are an excellent example. Another would be partitioning by order_id, or some other method. Add whatever you think you need for a *long* time going forward. The nice thing is this functions in a similar fashion to local partitions, so you can truncate an “old” table without impacting current activity. This would take care of the space concern. Also, ensure you put them in tablespaces where you can easily manage the space. Ideally, put each table in its own tablespace so you can truncate the table and resize the datafile when the data in that table is no longer needed.

Obviously, hash partitioning wouldn’t work here. If you are interested, you could use the ora_hash function.

TEST CASE

Below is a test case setup that uses instead of triggers. We create three tables, one for each month of sales as indicated in the sales_YYYY_MM naming convention.

create table sales_2010_07 (period_start date,period_end date,product_name varchar2(50),product_total number)
/

create table sales_2010_08 (period_start date,period_end date,product_name varchar2(50),product_total number)
/

create table sales_2010_09 (period_start date,period_end date,product_name varchar2(50),product_total number)
/

We then create a view which unions the three tables together.

create or replace view sales as
  select * from sales_2010_07
  union all
  select * from sales_2010_08
  union all
  select * from sales_2010_09
/

…and also create a trigger on the view. The trigger determines the current date, and inserts the row in the table associated with the month of the date being inserted.

UPDATE: 2012_05_12 – I took out the delete and update statements, as they did not work as pointed out by Ajay. I may add these back in later, but we never used that part, so I didn’t want to mislead anyone.

create or replace trigger sales_trg
  instead of insert on sales
  for each row
declare
  l_date varchar2(30);
begin
  l_date := to_char(trunc(last_day(:new.period_start) + 1),'YYYY_MM');
  if inserting then
    execute immediate 'insert into sales_' || l_date || ' values(:1,:2,:3,:4)' 
      using :new.period_start,:new.period_end,:new.product_name,:new.product_total;
  end if;
end;
/

We can then use what is below to load our table to verify that it works functionally.

declare
  type t_product_list is table of varchar2(50);
  l_product_list t_product_list := t_product_list();
  l_date date;
  l_product_name varchar2(50);
begin
  l_product_list.extend(5);
  l_product_list(1) := 'Hamburger';
  l_product_list(2) := 'Frosty';
  l_product_list(3) := 'Fries';
  l_product_list(4) := 'Chili';
  l_product_list(5) := 'Salad';
  for i in 1..10000 loop
    l_date := trunc(sysdate - trunc(dbms_random.value(1,45)),'MON'); --first day of the period
    l_product_name := l_product_list(trunc(dbms_random.value(1,6))); --random product from our list
    insert into sales values(l_date,trunc(last_day(l_date)),l_product_name,dbms_random.value(1,100000)); -- this also randomly assigns a sales figure
  end loop;
end;
/

In our next entry, we will provide the test case results.