enq: JI - contention

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 these posts. 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:

rwijk@ORA11GR1> create table percentages_per_year (ym,percentage)
  2  as
  3   select date '2005-12-01' + numtoyminterval(level,'month')
  4        , case mod(level,3) when 0 then 5 else 10 end
  5     from dual
  6  connect by level <= 48
  7  /

Table created.

rwijk@ORA11GR1> select * from percentages_per_year
  2  /

YM                  PERCENTAGE
------------------- ----------
01-01-2006 00:00:00         10
01-02-2006 00:00:00         10
01-03-2006 00:00:00          5
01-04-2006 00:00:00         10
01-05-2006 00:00:00         10
01-06-2006 00:00:00          5
01-07-2006 00:00:00         10
01-08-2006 00:00:00         10
01-09-2006 00:00:00          5
01-10-2006 00:00:00         10
01-11-2006 00:00:00         10
01-12-2006 00:00:00          5
01-01-2007 00:00:00         10
01-02-2007 00:00:00         10
01-03-2007 00:00:00          5
01-04-2007 00:00:00         10
01-05-2007 00:00:00         10
01-06-2007 00:00:00          5
01-07-2007 00:00:00         10
01-08-2007 00:00:00         10
01-09-2007 00:00:00          5
01-10-2007 00:00:00         10
01-11-2007 00:00:00         10
01-12-2007 00:00:00          5
01-01-2008 00:00:00         10
01-02-2008 00:00:00         10
01-03-2008 00:00:00          5
01-04-2008 00:00:00         10
01-05-2008 00:00:00         10
01-06-2008 00:00:00          5
01-07-2008 00:00:00         10
01-08-2008 00:00:00         10
01-09-2008 00:00:00          5
01-10-2008 00:00:00         10
01-11-2008 00:00:00         10
01-12-2008 00:00:00          5
01-01-2009 00:00:00         10
01-02-2009 00:00:00         10
01-03-2009 00:00:00          5
01-04-2009 00:00:00         10
01-05-2009 00:00:00         10
01-06-2009 00:00:00          5
01-07-2009 00:00:00         10
01-08-2009 00:00:00         10
01-09-2009 00:00:00          5
01-10-2009 00:00:00         10
01-11-2009 00:00:00         10
01-12-2009 00:00:00          5

48 rows selected.

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:

rwijk@ORA11GR1> create materialized view log on percentages_per_year
  2    with sequence, rowid (ym,percentage) including new values
  3  /

Materialized view log created.

rwijk@ORA11GR1> create materialized view mv
  2    refresh fast on commit
  3  as
  4  select to_char(ym,'yyyy') year
  5       , sum(percentage)    year_percentage
  6       , count(percentage)  count_percentage
  7       , count(*)           count_all
  8    from percentages_per_year
  9   group by to_char(ym,'yyyy')
 10  /

Materialized view created.

rwijk@ORA11GR1> alter materialized view mv
  2    add constraint year_percentage_100_ck
  3    check (year_percentage=100)
  4  /

Materialized view altered.

rwijk@ORA11GR1> select * from mv
  2  /

YEAR YEAR_PERCENTAGE COUNT_PERCENTAGE  COUNT_ALL
---- --------------- ---------------- ----------
2009             100               12         12
2008             100               12         12
2007             100               12         12
2006             100               12         12

4 rows selected.

rwijk@ORA11GR1> exec dbms_stats.gather_table_stats(user,'percentages_per_year')

PL/SQL procedure successfully completed.

Now the idea is to run 4 jobs, each updating 12 records of one year. To see what's going on inside each job, I create a logging table with logging procedures that run autonomous transactions:

rwijk@ORA11GR1> create table t_log
  2  ( year      number(4)
  3  , starttime timestamp(6)
  4  , endtime   timestamp(6)
  5  )
  6  /

Table created.

rwijk@ORA11GR1> create procedure log_start (p_year in t_log.year%type)
  2  is
  3    pragma autonomous_transaction;
  4  begin
  5    insert into t_log (year,starttime)
  6    values (p_year,systimestamp)
  7    ;
  8    commit;
  9  end log_start;
 10  /

Procedure created.

rwijk@ORA11GR1> create procedure log_end (p_year in t_log.year%type)
  2  is
  3    pragma autonomous_transaction;
  4  begin
  5    update t_log
  6       set endtime = systimestamp
  7     where year = p_year
  8    ;
  9    commit;
 10  end log_end;
 11  /

Procedure created.

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:

rwijk@ORA11GR1> create procedure update_percentages (p_year in number)
  2  is
  3  begin
  4    dbms_application_info.set_module('p',to_char(p_year))
  5    ;
  6    dbms_session.session_trace_enable
  7    ;
  8    log_start(p_year)
  9    ;
 10    update percentages_per_year
 11       set percentage = case when extract(month from ym) <= 8 then 9 else 7 end
 12     where extract(year from ym) = p_year
 13    ;
 14    commit
 15    ;
 16    log_end(p_year)
 17    ;
 18  end update_percentages;
 19  /

Procedure created.

And now let 4 jobs run:

rwijk@ORA11GR1> declare
  2    l_job_id binary_integer;
  3  begin
  4    for i in 2006 .. 2009
  5    loop
  6      dbms_job.submit(l_job_id,'rwijk.update_percentages('||to_char(i)||');');
  7    end loop;
  8  end;
  9  /

PL/SQL procedure successfully completed.

rwijk@ORA11GR1> commit
  2  /

Commit complete.

rwijk@ORA11GR1> pause

After all jobs have run concurrently and have finished, the logging table looks like this:

rwijk@ORA11GR1> select year
  2       , starttime
  3       , endtime
  4       , endtime - starttime duration
  5    from t_log
  6   order by endtime
  7  /

 YEAR STARTTIME                       ENDTIME
----- ------------------------------- -------------------------------
DURATION
-------------------------------
 2007 14-JAN-10 09.59.45.625000 PM    14-JAN-10 09.59.45.843000 PM
+000000000 00:00:00.218000

 2006 14-JAN-10 09.59.45.609000 PM    14-JAN-10 09.59.45.875000 PM
+000000000 00:00:00.266000

 2008 14-JAN-10 09.59.45.656000 PM    14-JAN-10 09.59.45.906000 PM
+000000000 00:00:00.250000

 2009 14-JAN-10 09.59.45.625000 PM    14-JAN-10 09.59.45.937000 PM
+000000000 00:00:00.312000


4 rows selected.

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:

WAIT #8: nam='enq: JI - contention' ela= 85497 name|mode=1246298118 view object #=106258 0=0 obj#=-1 tim=7377031494

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:

rwijk@ORA11GR1> select event#
  2       , name
  3       , parameter1
  4       , parameter2
  5       , parameter3
  6       , wait_class
  7    from v$event_name
  8   where name = 'enq: JI - contention'
  9  /

    EVENT# NAME
---------- ----------------------------------------------------------------
PARAMETER1
----------------------------------------------------------------
PARAMETER2
----------------------------------------------------------------
PARAMETER3
----------------------------------------------------------------
WAIT_CLASS
----------------------------------------------------------------
       818 enq: JI - contention
name|mode
view object #
0
Other


1 row selected.

The first parameter is "name|mode", which equals a rather strange number 1246298118. This pretty-old-but-still-very-useful article of Anjo Kolk helped me interpreting the number:

rwijk@ORA11GR1> select chr(bitand(1246298118,-16777216)/16777215) ||
  2         chr(bitand(1246298118,16711680)/65535) “Lock”
  3       , bitand(1246298118, 65535) “Mode”
  4    from dual
  5  /

Lo       Mode
-- ----------
JI          6

1 row selected.

Where lock mode 6 means an exclusive lock.

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 ...

CAST-COLLECT versus CAST-MULTISET

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:

rwijk@ORA11GR1> create table customers
  2  ( id        number(6)    not null primary key
  3  , name      varchar2(30) not null
  4  , birthdate date         not null
  5  )
  6  /

Table created.

rwijk@ORA11GR1> create table bankaccounts
  2  ( nr              number(10)   not null primary key
  3  , customer_id     number(6)    not null
  4  , current_balance number(14,2) not null
  5  )
  6  /

Table created.

rwijk@ORA11GR1> insert into customers
  2  select 1, 'Jeffrey Lebowski', date '1964-01-01' from dual union all
  3  select 2, 'Walter Sobchak', date '1961-01-01' from dual
  4  /

2 rows created.

rwijk@ORA11GR1> create sequence customers_seq start with 3
  2  /

Sequence created.

rwijk@ORA11GR1> insert into bankaccounts
  2  select 123456789, 1, 10 from dual union all
  3  select 987654321, 1, 100 from dual union all
  4  select 234567890, 2, 2000 from dual
  5  /

3 rows created.

rwijk@ORA11GR1> select * from customers
  2  /

        ID NAME                           BIRTHDATE
---------- ------------------------------ -------------------
         1 Jeffrey Lebowski               01-01-1964 00:00:00
         2 Walter Sobchak                 01-01-1961 00:00:00

2 rows selected.

rwijk@ORA11GR1> select * from bankaccounts
  2  /

        NR CUSTOMER_ID CURRENT_BALANCE
---------- ----------- ---------------
 123456789           1              10
 987654321           1             100
 234567890           2            2000

3 rows selected.

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:

rwijk@ORA11GR1> create type to_bankaccount is object
  2  ( nr              number(10)
  3  , current_balance number(14,2)
  4  );
  5  /

Type created.

rwijk@ORA11GR1> create type ta_bankaccounts is table of to_bankaccount;
  2  /

Type created.

rwijk@ORA11GR1> create type to_customer is object
  2  ( name         varchar2(30)
  3  , birthdate    date
  4  , bankaccounts ta_bankaccounts
  5  );
  6  /

Type created.

rwijk@ORA11GR1> select type_name
  2       , typecode
  3       , attributes
  4    from user_types
  5  /

TYPE_NAME                      TYPECODE                       ATTRIBUTES
------------------------------ ------------------------------ ----------
TO_BANKACCOUNT                 OBJECT                                  2
TA_BANKACCOUNTS                COLLECTION                              0
TO_CUSTOMER                    OBJECT                                  3

3 rows selected.

The external system can call a function to get the customer information. The function below simulates that function. I used the CAST-COLLECT method:

rwijk@ORA11GR1> create function customer_with_collect
  2  ( p_customer_id in customers.id%type
  3  ) return to_customer
  4  is
  5    o_customer to_customer;
  6  begin
  7    select to_customer
  8           ( c.name
  9           , c.birthdate
 10           , ( select cast
 11                      ( collect(to_bankaccount(ba.nr,ba.current_balance))
 12                        as ta_bankaccounts
 13                      )
 14                 from bankaccounts ba
 15                where ba.customer_id = c.id
 16             )
 17           )
 18      into o_customer
 19      from customers c
 20     where c.id = p_customer_id
 21    ;
 22    return o_customer;
 23  end customer_with_collect;
 24  /

Function created.

rwijk@ORA11GR1> select type_name
  2       , typecode
  3       , attributes
  4    from user_types
  5  /

TYPE_NAME                      TYPECODE                       ATTRIBUTES
------------------------------ ------------------------------ ----------
TO_BANKACCOUNT                 OBJECT                                  2
TA_BANKACCOUNTS                COLLECTION                              0
TO_CUSTOMER                    OBJECT                                  3

3 rows selected.

rwijk@ORA11GR1> select customer_with_collect(1)
  2    from dual
  3  /

CUSTOMER_WITH_COLLECT(1)(NAME, BIRTHDATE, BANKACCOUNTS(NR, CURRENT_BALANCE))
------------------------------------------------------------------------------
TO_CUSTOMER('Jeffrey Lebowski', '01-01-1964 00:00:00', TA_BANKACCOUNTS(TO_BANK
ACCOUNT(123456789, 10), TO_BANKACCOUNT(987654321, 100)))


1 row selected.

rwijk@ORA11GR1> select type_name
  2       , typecode
  3       , attributes
  4    from user_types
  5  /

TYPE_NAME                      TYPECODE                       ATTRIBUTES
------------------------------ ------------------------------ ----------
TO_BANKACCOUNT                 OBJECT                                  2
TA_BANKACCOUNTS                COLLECTION                              0
TO_CUSTOMER                    OBJECT                                  3
SYSTPuS41SrC7Q9G6/OfQT4VHPA==  COLLECTION                              0

4 rows selected.

But as you can see, when you use the function, an internal type has been persistently created. This is the internal type that is used to store the result of the COLLECT-function in:

rwijk@ORA11GR1> select collect(to_bankaccount(nr,current_balance))
  2    from bankaccounts
  3  /

COLLECT(TO_BANKACCOUNT(NR,CURRENT_BALANCE))(NR, CURRENT_BALANCE)
------------------------------------------------------------------------------
SYSTPuS41SrC7Q9G6/OfQT4VHPA==(TO_BANKACCOUNT(123456789, 10), TO_BANKACCOUNT(98
7654321, 100), TO_BANKACCOUNT(234567890, 2000))


1 row selected.

Note that the name of the type is the same as listed earlier.

If you don't query the user_types view as I did, then you might be surprised that the following sequence doesn't work:

rwijk@ORA11GR1> drop type to_customer
  2  /

Type dropped.

rwijk@ORA11GR1> drop type ta_bankaccounts
  2  /

Type dropped.

rwijk@ORA11GR1> drop type to_bankaccount
  2  /
drop type to_bankaccount
*
ERROR at line 1:
ORA-02303: cannot drop or replace a type with type or table dependents

So I have to drop the internal type first, before I can drop type TO_BANKACCOUNT:

rwijk@ORA11GR1> column type_name new_value systype
rwijk@ORA11GR1> select type_name
  2    from user_types
  3   where type_name like 'SYS%'
  4  /

TYPE_NAME
------------------------------
SYSTPuS41SrC7Q9G6/OfQT4VHPA==

1 row selected.

rwijk@ORA11GR1> drop type "&systype"
  2  /
old   1: drop type "&systype"
new   1: drop type "SYSTPuS41SrC7Q9G6/OfQT4VHPA=="

Type dropped.

rwijk@ORA11GR1> drop type to_bankaccount
  2  /

Type dropped.

This behaviour annoyed me slightly, so I checked out another way using CAST-MULTISET:

rwijk@ORA11GR1> create type to_bankaccount is object
  2  ( nr              number(10)
  3  , current_balance number(14,2)
  4  );
  5  /

Type created.

rwijk@ORA11GR1> create type ta_bankaccounts is table of to_bankaccount;
  2  /

Type created.

rwijk@ORA11GR1> create type to_customer is object
  2  ( name         varchar2(30)
  3  , birthdate    date
  4  , bankaccounts ta_bankaccounts
  5  );
  6  /

Type created.

rwijk@ORA11GR1> create function customer_with_multiset
  2  ( p_customer_id in customers.id%type
  3  ) return to_customer
  4  is
  5    o_customer to_customer;
  6  begin
  7    select to_customer
  8           ( c.name
  9           , c.birthdate
 10           , cast
 11             ( multiset
 12               ( select to_bankaccount(ba.nr,ba.current_balance)
 13                   from bankaccounts ba
 14                  where ba.customer_id = c.id
 15               )
 16               as ta_bankaccounts
 17             )
 18           )
 19      into o_customer
 20      from customers c
 21     where c.id = p_customer_id
 22    ;
 23    return o_customer;
 24  end customer_with_multiset;
 25  /

Function created.

And with CAST-MULTISET, there are no additional internal types created:

rwijk@ORA11GR1> select customer_with_multiset(1)
  2    from dual
  3  /

CUSTOMER_WITH_MULTISET(1)(NAME, BIRTHDATE, BANKACCOUNTS(NR, CURRENT_BALANCE))
------------------------------------------------------------------------------
TO_CUSTOMER('Jeffrey Lebowski', '01-01-1964 00:00:00', TA_BANKACCOUNTS(TO_BANK
ACCOUNT(123456789, 10), TO_BANKACCOUNT(987654321, 100)))


1 row selected.

rwijk@ORA11GR1> select type_name
  2       , typecode
  3       , attributes
  4    from user_types
  5  /

TYPE_NAME                      TYPECODE                       ATTRIBUTES
------------------------------ ------------------------------ ----------
TA_BANKACCOUNTS                COLLECTION                              0
TO_BANKACCOUNT                 OBJECT                                  2
TO_CUSTOMER                    OBJECT                                  3

3 rows selected.

So that's 0-1 in favour of CAST-MULTISET. Let's check performance:

rwijk@ORA11GR1> declare
  2    o_customer to_customer;
  3  begin
  4    runstats_pkg.rs_start;
  5    for i in 1..10000
  6    loop
  7      o_customer := customer_with_collect(1);
  8    end loop;
  9    runstats_pkg.rs_middle;
 10    for i in 1..10000
 11    loop
 12      o_customer := customer_with_multiset(1);
 13    end loop;
 14    runstats_pkg.rs_stop(1000);
 15  end;
 16  /
Run1 draaide in 171 hsecs
Run2 draaide in 130 hsecs
Run1 draaide in 131.54% van de tijd

Naam                                              Run1        Run2    Verschil
STAT.undo change vector size                    10,264       2,844      -7,420
LATCH.shared pool                               10,236           8     -10,228
STAT.buffer is pinned count                          0      20,000      20,000
STAT.redo size                                  30,784       3,780     -27,004

Run1 latches totaal versus run2 -- verschil en percentage
Run1      Run2  Verschil     Pct
183,334   170,551   -12,783 107.50%

PL/SQL procedure successfully completed.

That's 0-2.

Holiday Challenge Time

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:

rwijk@ORA11GR1> create table matrix (id,a,b,c,d,e,f,g,h,i,j)
2  as
3  select 'A',1,1,1,0,0,0,1,0,1,0 from dual union all
4  select 'B',0,0,0,0,1,1,1,0,1,0 from dual union all
5  select 'C',0,1,1,1,1,0,0,0,0,1 from dual union all
6  select 'D',1,0,0,0,1,0,1,0,0,0 from dual union all
7  select 'E',1,1,0,0,0,0,0,0,0,1 from dual union all
8  select 'F',0,1,1,0,1,1,0,0,0,0 from dual union all
9  select 'G',0,1,0,1,1,1,0,1,0,1 from dual union all
10  select 'H',1,1,0,1,0,0,0,1,0,0 from dual union all
11  select 'I',0,1,1,1,0,1,1,1,0,1 from dual union all
12  select 'J',1,0,0,0,0,0,0,1,0,1 from dual union all
13  select 'K',1,0,0,1,1,1,0,1,1,0 from dual union all
14  select 'L',0,0,1,0,1,1,0,0,1,0 from dual union all
15  select 'M',0,0,1,0,0,0,0,1,1,1 from dual union all
16  select 'N',1,0,1,0,0,0,1,1,1,0 from dual union all
17  select 'O',1,0,0,1,1,0,1,0,0,0 from dual
18  /

Table created.

The table looks like this:




Quoting the challenge:

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

In this layout a hierarchical query can do the job relatively easy:

rwijk@ORA11GR1> with matrix_transformed as
  2  ( select ascii(id) - 64 rw
  3         , col
  4         , case col
  5           when 1 then a
  6           when 2 then b
  7           when 3 then c
  8           when 4 then d
  9           when 5 then e
 10           when 6 then f
 11           when 7 then g
 12           when 8 then h
 13           when 9 then i
 14           when 10 then j
 15           end as value
 16      from matrix
 17         , ( select level col from dual connect by level <= 10 )
 18  )
 19   select ltrim
 20          ( sys_connect_by_path('['||chr(64+rw)||','||chr(64+col)||']',';')
 21          , ';'
 22          ) path
 23     from matrix_transformed
 24    where rw = 15
 25    start with rw = 1
 26          and  col = 5
 27  connect by nocycle
 28          prior value != value
 29      and prior rw != 15
 30      and (  ( rw = prior rw and col = prior col - 1 )
 31          or ( rw = prior rw and col = prior col + 1 )
 32          or ( rw = prior rw - 1 and col = prior col )
 33          or ( rw = prior rw + 1 and col = prior col )
 34          )
 35  /

PATH
------------------------------------------------------------------------------
[A,E];[B,E];[B,D];[C,D];[D,D];[D,E];[D,F];[D,G];[C,G];[B,G];[B,H];[B,I];[C,I];
[C,J];[D,J];[E,J];[F,J];[G,J];[G,I];[G,H];[G,G];[G,F];[H,F];[I,F];[J,F];[K,F];
[K,G];[K,H];[L,H];[L,I];[L,J];[M,J];[N,J];[N,I];[O,I]

[A,E];[B,E];[B,D];[C,D];[D,D];[D,E];[D,F];[D,G];[C,G];[B,G];[B,H];[B,I];[B,J];
[C,J];[D,J];[E,J];[F,J];[G,J];[G,I];[G,H];[G,G];[G,F];[H,F];[I,F];[J,F];[K,F];
[K,G];[K,H];[L,H];[L,I];[L,J];[M,J];[N,J];[N,I];[O,I]


2 rows selected.

These two rows represent these paths:



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].