2017 - Oracle Findings

In my personal experience, quite often I came across the need to test and optimize queries used by REF CURSORS. Sometimes it’s a really painless operation, other times it can really be a very long and complex operation that can make you waste a lot of time. Let’s see what we would normally do in a simple case: consider a procedure GET_DATA which returns a SYS_REFCURSOR

create or replace procedure get_data(pi_table_name in VARCHAR2,
                                     po_resultset out SYS_REFCURSOR) is
begin
  open po_resultset for
	   select atc.OWNER, atc.COLUMN_NAME, atc.DATA_TYPE
             from all_tab_columns atc
            where table_name = pi_table_name
            order by column_id;
end get_data;
/

create or replace procedure get_data(pi_table_name in VARCHAR2,

po_resultset out SYS_REFCURSOR) is

begin

open po_resultset for

select atc.OWNER, atc.COLUMN_NAME, atc.DATA_TYPE

from all_tab_columns atc

where table_name = pi_table_name

order by column_id;

end get_data;

The query used for the ref cursor is quite simple, it extracts 3 columns and uses just one parameter as a filter. In such case, if I wanted to test the query, I would probably take it outside and run it manually to perform my test.

INTERVAL partitioning has been introduced by Oracle as an extension of RANGE partitioning. There are few limitations like the fact that’s not supported at subpartition level and the partitioning key has to be a DATE or NUMBER but also some interesting advantages. When a table is partitioned by INTERVAL, in fact, partitions are created automatically as data is loaded into the table so we don’t have to bother creating anything in advance. We might, anyway, prefer to maintain our historical data in tables that are partitioned by RANGE. This because it’s common to keep inside the system a “rolling window” (e.g. last 90 days) of historical data meaning oldest partitions are dropped as soon as they become obsolete and with INTERVAL partitioning the last partition in the range section cannot be dropped. Moreover, in many processes it’s preferrable to have a full control of which partitions are created because data loaded into the system is not guaranteed to be “clean” and rows that don’t map to existing partitions have to be rejected.
After all these considerations, let’s have a look at what we can do to make the best out of both partitioning options (tested on 11g).

Oracle Findings

How to easily test a complex REF CURSOR

Switching between range and interval partitioning: automatically create partitions in a range-partitioned table