Re: triggers, sequence - need advice

daud11_at_hotmail.com (Daud) wrote:

> One each table, they have 3 triggers (after insert/update/delete) to
> basically to the same thing....capture data and save in intermediate
> table. Would I see any improvement by combining the three triggers
> into just 1?

Will that change the "what must be done" per update/insert/delete? Obviously not I think. Thus the only difference is that instead of 3 code sources, you now have a single one (which just could be a tad slower if you need to use IF statements to do this in a single source).

> When inserting data into the intermediate table they are
> using an oracle sequence for sort of ordering (same sequence for
> insert/update/delete from all tables). Should I change and use time
> instead?

Hmm.. is it faster to select NEXTVAL or SYSDATE? If you have to look at things like that for performance tuning, then there's something wrong... Solution: drink more coffee. :-)

> Anything else I can look into...perhaps what is the better way of
> doing something like this?

Better? Maybe not. Different? Yes.

How big are those tables? If under a million rows, then they are small tables and can easy be duplicated using a CREATE TABLE NOLOGGING AS SELECT. However, this ignores the deleted rows (they are of course gone and the fact that they existed, is no more). Still, a CREATE TABLE can be done every 6 hours to grab the latest snapshot from the table.

Another option is proper snapshots, aka materialised views. I have never done this on the same db though and there's a few disclaimers there I recall. But materialised views are easy to setup and will not impact production transaction performance.

What is the requirement behind these reporting tables and how are they pushed into a non-Oracle reporting database? Can the reporting tables not be eliminated all together and the requirement addressed by pushing the data across directly from the production tables (e.g. adding a LAST_DATE_CHANGED timestamp to the Prod tables and have the extract process using that to determine what data has changed and needs to be pushed across to the report database).

If none of these alternatives can do it, you will need to look at the trigger source and destination tables.

Is the source as small and tight as possible - no superflous statements. No silly SQL's that can be done better?

Are the destination tables heavily indexed? Bad idea if they are as a single insert/update will cause a multitude of reads on these indexes (and can add more than a 1000% overhead to the insert/update).

Consider doing the absolute minimum in the trigger. Why do the actual update/insert of the destination report table in the trigger? Why not queue it? Or use background jobs to do most of the work? (poor man replication ;-)

Or why not simply get a better reporting solution? One that does not require 3x the storage of data to produce reports from a non-Oracle platform? (just how much did you have to pay for this "solution"?)

--
Billy