BC Oracle tuning

Oracle training

Oracle support

Remote Oracle

EnterpriseDB: Dynamic SQL and Trigger Example
Oracle Tips by Burleson

To bring together some of the topics outlined above, specifically triggers and dynamic SQL, I will present a way to reduce the maintenance overhead of DML on partitioned tables. Rather than hard code each partition, I will use dynamic SQL to get the data into the correct partition.

If I look back at chapter 3 in the section on partitioned tables, I defined a partitioned table as:

CREATE TABLE base_table (
pk_field   NUMBER NOT NULL PRIMARY KEY,
dt_field   DATE,
  char_field VARCHAR2(100) );

CREATE TABLE base_table_part_200601
( CHECK (TO_NUMBER(TO_CHAR(dt_field,'YYYYMMDD'))
BETWEEN 20060101 AND 20060131 ) )
INHERITS (base_table);

CREATE TABLE base_table_part_200602
( CHECK (TO_NUMBER(TO_CHAR(dt_field,'YYYYMMDD'))
BETWEEN 20060201 AND 20060228 ) )
INHERITS (base_table);

CREATE TABLE base_table_part_200603
( CHECK (TO_NUMBER(TO_CHAR(dt_field,'YYYYMMDD'))
BETWEEN 20060301 AND 20060331 ) )
INHERITS (base_table);

The trigger I used on this table to insert records in the right partition has a hard coded insert wrapped in an IF statement. Every time a new partition is added, the trigger must be modified to account for the new partition.

I will rewrite the trigger to dynamically insert the rows into the correct partition. One word of caution: You must ensure that all of your partitions are pre-created or your trigger will fail. This is not a by product of the dynamic SQL but the dynamic SQL does make it a little bit harder to debug.

The trigger logic is based on the fact that we have a naming standard for our partitions. The standard is <table_name>_part_<date_YYYYMM>. So I know that the partition for the table yada for the month of March in 2007 is named: yada_part_200703. As long as we maintain standards, life becomes much easier for maintenance.

So what was a hard coded trigger now becomes:

CREATE OR REPLACE TRIGGER base_table_handler
BEFORE INSERT OR UPDATE OR DELETE
ON base_table
FOR EACH ROW
BEGIN

IF INSERTING THEN
    EXECUTE IMMEDIATE
       'INSERT INTO base_table_part_' || to_char(:new.dt_field, 'YYYYMM') ||
       '    (pk_field, dt_field, char_field)' ||
       'VALUES (:new_pk_field, :new_dt_field, :new_char_field)'
    USING :new.pk_field, :new.dt_field, :new.char_field;
ELSIF UPDATING THEN
    -- Do the same for update
    NULL;

ELSIF DELETING THEN
-- Do the same for delete
NULL;
END IF;

RETURN NULL;
END;
/

I can run my original insert statements:

-- Insert a row into the first partition

INSERT INTO base_table

(pk_field, dt_field, char_field)
VALUES (1, to_date('20060110', 'YYYYMMDD'), 'Record 1');

-- Insert a row into the second partition

INSERT INTO base_table

(pk_field, dt_field, char_field)
VALUES (1, to_date('20060210', 'YYYYMMDD'), 'Record 2');

-- Insert a row into the third partition

INSERT INTO base_table
(pk_field, dt_field, char_field)
VALUES (1, to_date('20060310', 'YYYYMMDD'), 'Record 3');

These should be inserted into the correct partitions. To ensure that is so, I can rerun my queries:

edb=# -- Select from the base table

edb=# SELECT * FROM base_table;

edb=#
edb=# -- Select from the partition table 200601
edb=# SELECT * FROM base_table_part_200601;

pk_field | dt_field | char_field
----------+--------------------+------------
1 | 10-JAN-06 00:00:00 | Record 1

(1 row)

edb=#

edb=# -- Select from the partition table 200602

edb=# SELECT * FROM base_table_part_200602;

pk_field | dt_field | char_field
----------+--------------------+------------
1 | 10-FEB-06 00:00:00 | Record 2

(1 row)

edb=#

edb=# -- Select from the partition table 200603

edb=# SELECT * FROM base_table_part_200603;

pk_field | dt_field | char_field
----------+--------------------+------------
1 | 10-MAR-06 00:00:00 | Record 3

(1 row)

Voila! The magic of triggers and dynamic SQL.

You can access the entire script to create this example from in the code depot at: edb_chap4_part_tables.sql

* edb_chap4_part_tables.sql

CREATE TABLE base_table (
pk_field NUMBER NOT NULL PRIMARY KEY,
dt_field DATE,
char_field VARCHAR2(100) );

CREATE TABLE base_table_part_200601
( CHECK (TO_NUMBER(TO_CHAR(dt_field,'YYYYMMDD'))
BETWEEN 20060101 AND 20060131 ) )
INHERITS (base_table);

CREATE TABLE base_table_part_200602
( CHECK (TO_NUMBER(TO_CHAR(dt_field,'YYYYMMDD'))
BETWEEN 20060201 AND 20060228 ) )
INHERITS (base_table);

CREATE TABLE base_table_part_200603
( CHECK (TO_NUMBER(TO_CHAR(dt_field,'YYYYMMDD'))
BETWEEN 20060301 AND 20060331 ) )
INHERITS (base_table);

CREATE OR REPLACE TRIGGER base_table_handler
BEFORE INSERT OR UPDATE OR DELETE
ON base_table
FOR EACH ROW
BEGIN

IF INSERTING THEN
    EXECUTE IMMEDIATE
       'INSERT INTO base_table_part_' || to_char(:new.dt_field, 'YYYYMM') ||
       '    (pk_field, dt_field, char_field)' ||
       'VALUES (:new_pk_field, :new_dt_field, :new_char_field)'
USING :new.pk_field, :new.dt_field, :new.char_field;
ELSIF UPDATING THEN

    -- Do the same for update

NULL;
ELSIF DELETING THEN

-- Do the same for delete

NULL;
END IF;

RETURN NULL;
END;
/

-- Insert a row into the first partition

INSERT INTO base_table
(pk_field, dt_field, char_field)
VALUES (1, to_date('20060110', 'YYYYMMDD'), 'Record 1');

-- Insert a row into the second partition

INSERT INTO base_table
(pk_field, dt_field, char_field)
VALUES (1, to_date('20060210', 'YYYYMMDD'), 'Record 2');

-- Insert a row into the third partition

INSERT INTO base_table
(pk_field, dt_field, char_field)
VALUES (1, to_date('20060310', 'YYYYMMDD'), 'Record 3');

-- Select from the base table

SELECT * FROM base_table;

-- Select from the partition table 200601

SELECT * FROM base_table_part_200601;

-- Select from the partition table 200602

SELECT * FROM base_table_part_200602;

-- Select from the partition table 200603

SELECT * FROM base_table_part_200603;

This ends the major topics in SPL. I know from personal experience that many Oracle applications use XML. The next section describes the current method of XML manipulation in EnterpriseDB Advanced Server.

This is an excerpt from the book "EnterpriseDB: The Definitive Reference" by Rampant TechPress.

Oracle performance Tuning 10g reference poster

Oracle training & performance tuning books

EnterpriseDB: Dynamic SQL and Trigger Example Oracle Tips by Burleson

EnterpriseDB: Dynamic SQL and Trigger Example
Oracle Tips by Burleson