Pages

Friday, January 15, 2010

To implement entity rules and inter-entity rules, one of the options is to use an on commit-time fast refreshable materialized view with a check constraint on top. You can read a few examples in theseposts. It's an elegant way to check for conditions at commit-time, and quite remarkable when you see it for the first time. But there is a serious caveat to this way of implementing business rules, which may or may not be relevant for your situation. In any case, it doesn't hurt to be aware of this caveat. To show what I mean I create a table called percentages_per_year:

The data is such that all percentages in a year add up to exactly 100. To ensure this is always the case in the future, I implement it as a business rule using an on commit-time fast refreshable materialized view:

The jobs will run a procedure called update_percentages. The procedure sets its module and action column, to be able to identify the trace files that are created. It logs itself before the update and after the commit:

You can see the overlap of the processes. The process for year 2007 has finished first, and doesn't experience any noticeable wait. The other three processes do. A look at the trace file shows one line like this:

This line is followed after the COMMIT (XCTEND rlbk=0) in the trace file. The wait event is called "enq: JI - contention", where JI stands for materialized view refresh :-). In the documention it says that JI stands for "Enqueue used during AJV snapshot refresh". So it's still unclear where those letters JI really stand for, although the J may be from the J in AJV. And google suggests that AJV stands for "Aggregate join view". Maybe there are so many two-letter enqueue names already that they just picked one?

In the WAIT line from the trace file, we see three parameters after the "ela= 85497". We can see these parameters in the v$event_name view too:

The second parameter is the object_id as you can find it in the all_objects view. The third parameter isn't useful here.

So a materialized view cannot be fast refreshed more than once in a given period. It's serialized during the commit. Normally when I update record 1 and commit and you update record 2 and commit, we don't have to wait for each other. But when having an on commit-time fast refreshable materialized view on top of the table, we do have to wait when two sessions do some totally unrelated transactions concurrently against the same table. Is this a problem? Not when the table is modified infrequently or only by a single session. But it can be a big problem when applied to a table that modifies a lot concurrently.

So be sure to use on commit-time fast refreshable materialized views for implementing business rules only on tables that are not concurrently accessed or infrequently changed. Or be prepared to be called by the users of your system ...

Sunday, January 10, 2010

At work I am building an interface using SQL object types and SQL collection types. I noticed a difference between using the COLLECT aggregate function in combination with a CAST function, versus the CAST-MULTISET method. I started out using CAST-COLLECT, but switched to CAST-MULTISET. Here is a simulation:

Two simple tables with a master-detail relationship. To be able to communicate (not to store, except for simple auditing) a customer, SQL types come in handy. Three types, two object types and a collection type:

Sunday, January 3, 2010

This was the title of a thread on OTN's SQL and PL/SQL Forum. Centinul posted an interesting puzzle to be solved by either SQL or PL/SQL here. If you like to puzzle yourself, then stop reading here, and go visit the link (and don't look at the answers of course :-) ).

The puzzle was not only a great way to spend time, it's also a nice show case for hierarchical queries and the answer of Frank Kulash taught me something new. The setup:

Our goal is from a start position identified as (A,E), First Row, Fifth column, traverse DOWN the matrix to reach a valid point on row "O."

Restrictions

1. You can only move UP, DOWN, LEFT, or RIGHT (not diagonally) by one unit.2. The path must be a repeating pattern of 0 1 0 1 0 1 ... etc For example a move from (A,E) to (B,E) is valid while a move from (A,E) to (A,F) is not.

The correct solution has the following requirements:

1. Identifies the path from start to finish using an identifiable way to determine the ROW,COLUMN for each entry point in the path while abiding by the restrictions above.

My solution is to first transform the table with a matrix layout to a transactional layout using three columns:

rw, an integer between 1 and 15, representing the row

col, an integer between 1 and 10, representing the column

value, an integer between 0 and 1, representing the matrix cell's value

Lines 1-18 transform the table to 150 rows in transactional layout. A hierarchical query then solves the puzzle by starting with the row [A,E] (lines 25-26 of the query). Line 27 specifies NOCYCLE to ignore loops. When joining the table with itself - like you do when using hierarchical queries - you want the next level of rows to be only the rows that are immediately UP, DOWN, LEFT or RIGHT of the current cell. This is what lines 30-34 implement. You want to discard the row when the cell value of the matrix is the same as in the previous level to ensure that 0's are followed by 1's and vice versa (line 28). And when you've reached row O - when rw equals 15 - the query needs to stop (line 29). Of this entire hierarchy, you only want one output row: the last one, which equals the one with rw = 15. And this was new to me: you can specify a where clause in your hierarchical query, which is evaluated AFTER the connect by. Of course this behaviour is documented:

Oracle processes hierarchical queries as follows:

* A join, if present, is evaluated first, whether the join is specified in the FROM clause or with WHERE clause predicates. * The CONNECT BY condition is evaluated. * Any remaining WHERE clause predicates are evaluated.

If you look closely at my answer in the thread and here, you'll notice the difference. Finally a SYS_CONNECT_BY_PATH is used to print the entire route from [A,E] to [O,I].