Monday 30 July 2007

The order of triggers...

It was always considered bad design to design a system which relied on the order of the same type of triggers, i.e.
having two BEFORE UPDATE triggers where trigger 2 relied on trigger 1 having fired. The reason for this is that Oracle doesn't guarantee the order in which these two triggers should fire. Sure, BEFORE UPDATE trigger(s) will fire before AFTER UPDATE trigger(s), but two BEFORE UPDATE triggers can fire in any order.

Well, along come Oracle again and rewrite the rulebook, well.. sort of. As part of 11g, the trigger definition can now contain the FOLLOWS keyword, which takes an argument of a trigger name. This keyword allows you to specify a trigger which this trigger is guaranteed fire after (i.e. this trigger follows another...).

I've updated the trigger firing sequence section of the main site to include a mention of this and a reference to the above page.

Friday 27 July 2007

Invisible indexes

Just read this blog about invisible indexes in 11g.

Basically, you create an index like
CREATE INDEX index_name ON table_name ( column(s) ) INVISIBLE;

What this has the effect of doing is to only allow use of this index if it's specified in an INDEX hint for the query.

This means that the developer / DBA can issue indexes on systems which will not be utilised by any other queries, and, therefore, can be used to test the performance impact of an index without affecting the main system.

Blogged with Flock