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.

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

Changes to collections and delimited lists

Made some changes to the page on creating delimited lists to collections ( and vice versa ), including putting a section on how to create a delimited list using just SQL (via the SYS_CONNECT_BY_PATH function), as well as revisiting the wording on quite a few sections on that page.

I also noticed a slight problem on the main page in that the text for one of the menu items was out of sync with the rest of the pattern, i.e. it was red when it should have been green.

cardinality

I've significantly tidied up and updated the page on cardinality, correcting a few things I noticed, but generally expanding on a few things, making it more consistent with the other pages.

I've also corrected an erroneous link to the old "gathering stats" page that I removed a while ago, but it was part of the whole tidy up exercise anyway.

I've also added a "useful links" section (which i've tended to do on a lot of the newer / updated pages). I find further information links very useful on other sites, and I assume that other people do to.

On SQL_TRACE et al.

Back again for another day of site maintenance and blogging.

This time, I'm still on the SQL Tracing page, extending it and making it much better and easier to follow. I've extended the section on AUTOTRACE, expanding on a few things, and (thanks to Yasin Baskan for this) including something I'd forgotten, which is that setting AUTOTRACE ON (or any setting which generates statistics, this doesn't include SET AUTOTRACE TRACEONLY EXPLAIN, of course) generates an "extra" session which is used to maintain the statistics for the "main" session, i.e. the one you executed the query in, which, as Yasin rightly points out, can itself offer SQL performance challenges.

I've also expanded on the DBMS_XPLAN bit, putting in how to utilise a seperate plan table from
plan_table.

Hopefully, I'll get around to reviewing the Cardinality page later, that's another page that needs a little bit of attention.

Some significant changes......

Quite a few changes tonight. Added a minor section to the page about NULL on the obvious omission that you can't search for NULL using LIKE. The most major change is that i've amalgamated the page on EXPLAIN PLAN and on SQL tuning into one page now called "SQL tracing". I've also tidied the information up and elaborated further on certain points, such as DBMS_XPLAN and 10046 event analysis (with and without DBMS_MONITOR).

Observant people may also have noticed that i've removed the section on PL/SQL tuning. It was fairly obvious information (and in some ways outdated since it was written a while ago), so it went. I will repost the information on using DBMS_PROFILER and DBMS_TRACE some time, I'm going to substantially rewrite it, but for the moment, it's gone. If you still want the information that was on there, give me an email.

The migration

At the same time as putting a link to a great white paper (Alberto Dell'erra's investigations into equi-join cardinality when utilising histograms), I thought it'd be a good idea to actually create a "proper" blog, rather than a static blog like it was before. So, here it is. It also means that people can actually post comments, rather than emailing like was done before.