What's in my JMS queue?

If you have setup a JMS queue with Oracle's Advanced Queuing, a question that pops up regularly is: "What's currently in the queue?". Now that can't be hard - and it isn't - yet I spent a silly amount of time getting it done. Hopefully this blog post will prevent others spending too much time.

To setup the JMS queue, create a queue table with payload type sys.aq$_jms_text_message, and a queue in that queue table:

rwijk@ORA11GR1> begin
 2    dbms_aqadm.create_queue_table
 3    ( queue_table        => 'my_queue_table'
 4    , queue_payload_type => 'sys.aq$_jms_text_message'
 5    );
 6    dbms_aqadm.create_queue
 7    ( queue_name  => 'my_queue'
 8    , queue_table => 'my_queue_table'
 9    );
10    dbms_aqadm.start_queue
11    ( queue_name => 'my_queue'
12    );
13  end;
14  /

PL/SQL procedure successfully completed.

Next, put a message on the queue:

rwijk@ORA11GR1> declare
 2    l_enqueue_options    dbms_aq.enqueue_options_t;
 3    l_message_properties dbms_aq.message_properties_t;
 4    l_message            sys.aq$_jms_text_message;
 5    l_msgid              raw(16);
 6  begin
 7    l_message := sys.aq$_jms_text_message.construct;
 8    l_message.set_text(xmltype('<emp><ename>ROB</ename></emp>').getClobVal());
 9    dbms_aq.enqueue
10    ( queue_name         => 'my_queue'
11    , enqueue_options    => l_enqueue_options
12    , message_properties => l_message_properties
13    , payload            => l_message
14    , msgid              => l_msgid
15    );
16    commit;
17  end;
18  /

PL/SQL procedure successfully completed.

You can query a queue by querying the queue table and filtering on the q_name column, like this:

rwijk@ORA11GR1> select msgid
 2       , enq_time
 3       , enq_uid
 4       , user_data
 5    from my_queue_table
 6   where q_name = 'MY_QUEUE'
 7  /

MSGID
--------------------------------
ENQ_TIME
---------------------------------------------------------------------------
ENQ_UID
------------------------------
USER_DATA(HEADER(REPLYTO(NAME, ADDRESS, PROTOCOL), TYPE, USERID, APPID, GROUPID,
--------------------------------------------------------------------------------
40279F37C785499CB386CC47ABCAAB1C
10-FEB-09 11.59.16.312000 PM
RWIJK
AQ$_JMS_TEXT_MESSAGE(AQ$_JMS_HEADER(NULL, NULL, NULL, NULL, NULL, NULL, NULL), 2
9, '<emp><ename>ROB</ename></emp>', NULL)


1 row selected.

At work I use an older SQL*Plus version, that is not able to show the contents of the user_data column, because of some LOB content. No problem, as I am really only interested in the text_vc field of the sys.aq$_jms_text_message object. So just query that field:

rwijk@ORA11GR1> desc my_queue_table
Name                                      Null?    Type
----------------------------------------- -------- ------------------------
Q_NAME                                             VARCHAR2(30)
MSGID                                     NOT NULL RAW(16)
CORRID                                             VARCHAR2(128)
PRIORITY                                           NUMBER
STATE                                              NUMBER
DELAY                                              TIMESTAMP(6)
EXPIRATION                                         NUMBER
TIME_MANAGER_INFO                                  TIMESTAMP(6)
LOCAL_ORDER_NO                                     NUMBER
CHAIN_NO                                           NUMBER
CSCN                                               NUMBER
DSCN                                               NUMBER
ENQ_TIME                                           TIMESTAMP(6)
ENQ_UID                                            VARCHAR2(30)
ENQ_TID                                            VARCHAR2(30)
DEQ_TIME                                           TIMESTAMP(6)
DEQ_UID                                            VARCHAR2(30)
DEQ_TID                                            VARCHAR2(30)
RETRY_COUNT                                        NUMBER
EXCEPTION_QSCHEMA                                  VARCHAR2(30)
EXCEPTION_QUEUE                                    VARCHAR2(30)
STEP_NO                                            NUMBER
RECIPIENT_KEY                                      NUMBER
DEQUEUE_MSGID                                      RAW(16)
SENDER_NAME                                        VARCHAR2(30)
SENDER_ADDRESS                                     VARCHAR2(1024)
SENDER_PROTOCOL                                    NUMBER
USER_DATA                                          SYS.AQ$_JMS_TEXT_MESSAGE
USER_PROP                                          SYS.ANYDATA

rwijk@ORA11GR1> select msgid
 2       , enq_time
 3       , enq_uid
 4       , user_data.text_vc
 5    from my_queue_table
 6   where q_name = 'MY_QUEUE'
 7  /
    , user_data.text_vc
      *
ERROR at line 4:
ORA-00904: "USER_DATA"."TEXT_VC": invalid identifier

And this message puzzled me for a quite a while. After an extensive search, an AskTom thread provided the answer. A very simple answer: you need to prefix the expression with a table alias:

rwijk@ORA11GR1> select msgid
 2       , enq_time
 3       , enq_uid
 4       , qt.user_data.text_vc
 5    from my_queue_table qt
 6   where q_name = 'MY_QUEUE'
 7  /

MSGID
--------------------------------
ENQ_TIME
---------------------------------------------------------------------------
ENQ_UID
------------------------------
USER_DATA.TEXT_VC
--------------------------------------------------------------------------------
40279F37C785499CB386CC47ABCAAB1C
10-FEB-09 11.59.16.312000 PM
RWIJK
<emp><ename>ROB</ename></emp>


1 row selected.

FOR UPDATE SKIP LOCKED

The behaviour of the "for update skip locked" clause is not what I expected it to be. I expected it to try to lock all rows when opening a cursor, just like a regular "for update", and then just skipping the locked rows. Below is a test to show how it works in reality. The setup:

A table with three rows:

rwijk@ORA11GR1> create table t (id)
  2  as
  3   select level
  4     from dual
  5  connect by level <= 3
  6  /

Tabel is aangemaakt.

A procedure to check if a record of this table t is locked:

rwijk@ORA11GR1> create procedure check_record_locked
  2  ( p_id in t.id%type
  3  )
  4  is
  5    cursor c
  6    is
  7    select 'dummy'
  8      from t
  9     where id = p_id
 10           for update nowait
 11    ;
 12    e_resource_busy exception;
 13    pragma exception_init(e_resource_busy,-54);
 14  begin
 15    open c;
 16    close c;
 17    dbms_output.put_line('Record ' || to_char(p_id) || ' is not locked.');
 18    rollback;
 19  exception
 20  when e_resource_busy then
 21    dbms_output.put_line('Record ' || to_char(p_id) || ' is locked.');
 22  end check_record_locked;
 23  /

Procedure is aangemaakt.

First, let's examine the default behaviour of the regular "for update" clause. During the 10 seconds sleep, I copy, paste and execute the code in the comments in another SQL*Plus session.

rwijk@ORA11GR1> declare
  2    cursor c is select id from t where id <= 2 for update;
  3    l_id t.id%type;
  4  begin
  5    open c;
  6    fetch c into l_id;
  7    dbms_lock.sleep(10);
  8    --
  9    --  In another session run:
 10    --
 11    --  begin
 12    --    check_record_locked(1);
 13    --    check_record_locked(2);
 14    --    check_record_locked(3);
 15    --  end;
 16    --  /
 17    --
 18    close c;
 19  end;
 20  /

PL/SQL-procedure is geslaagd.

The output from the other session:

rwijk@ORA11GR1> begin
  2    check_record_locked(1);
  3    check_record_locked(2);
  4    check_record_locked(3);
  5  end;
  6  /
Record 1 is locked.
Record 2 is locked.
Record 3 is not locked.

PL/SQL-procedure is geslaagd.

As expected, the opening of the cursor locks rows 1 and 2. Whether the record is fetched or not, has no influence.

Now a similar test with the "for update skip locked" clause:

rwijk@ORA11GR1> rollback
  2  /

Rollback is voltooid.

rwijk@ORA11GR1> declare
  2    cursor c is select id from t where id <= 2 for update skip locked;
  3    l_id t.id%type;
  4  begin
  5    open c;
  6    dbms_lock.sleep(10);
  7    --
  8    --  In another session run:
  9    --
 10    --  begin
 11    --    check_record_locked(1);
 12    --    check_record_locked(2);
 13    --    check_record_locked(3);
 14    --    dbms_lock.sleep(10);
 15    --    check_record_locked(1);
 16    --    check_record_locked(2);
 17    --    check_record_locked(3);
 18    --    dbms_lock.sleep(10);
 19    --    check_record_locked(1);
 20    --    check_record_locked(2);
 21    --    check_record_locked(3);
 22    --  end;
 23    --  /
 24    --
 25    fetch c into l_id;
 26    dbms_lock.sleep(10);
 27    fetch c into l_id;
 28    dbms_lock.sleep(10);
 29    close c;
 30  end;
 31  /

PL/SQL-procedure is geslaagd.

During the first sleep of 10 seconds, the code in the comments is again copied, pasted and executed in a second SQL*Plus session. Note that it prints the situation after just having opened the cursor, after the first fetch and after the second fetch. This is the output:

rwijk@ORA11GR1> begin
  2    check_record_locked(1);
  3    check_record_locked(2);
  4    check_record_locked(3);
  5    dbms_lock.sleep(10);
  6    check_record_locked(1);
  7    check_record_locked(2);
  8    check_record_locked(3);
  9    dbms_lock.sleep(10);
 10    check_record_locked(1);
 11    check_record_locked(2);
 12    check_record_locked(3);
 13  end;
 14  /
Record 1 is not locked.
Record 2 is not locked.
Record 3 is not locked.
Record 1 is locked.
Record 2 is not locked.
Record 3 is not locked.
Record 1 is locked.
Record 2 is locked.
Record 3 is not locked.

PL/SQL-procedure is geslaagd.

So opening a cursor with the "for update skip locked" clause doesn't lock a single row. Fetching a row does lock the row, as can be seen in the output. This was the first surprise: I couldn't find text in the documentation telling about this behaviour.

But won't this behaviour lead to a giant "lost update" problem? When you change a record in a second session between the open and the fetch of the cursor - which is allowed because the record is not locked - the cursor could overwrite this change because it has retrieved the value from before the update. Let's check how this works. The setup is a table with bank accounts containing 5 records with an identifying number, an amount and an indicator telling whether interest has been calculated already:

rwijk@ORA11GR1> create table bankaccounts(nr,amount,interest_calculated_indicator)
  2  as
  3   select level
  4        , 100
  5        , 'N'
  6     from dual
  7  connect by level <= 5
  8  /

Tabel is aangemaakt.

First a test using the "for update" clause. A piece of code that calculates interest by multiplying the amount of all bankaccounts with the indicator set to 'N' by 5%. During the sleep of 10 seconds, the commented code is copied, pasted and executed in a second SQL*Plus session:

rwijk@ORA11GR1> declare
  2    cursor c
  3    is
  4    select nr
  5         , amount
  6      from bankaccounts
  7     where interest_calculated_indicator = 'N'
  8           for update
  9    ;
 10    type ta_nr is table of bankaccounts.nr%type;
 11    type ta_amount is table of bankaccounts.amount%type;
 12    a_nr ta_nr;
 13    a_amount ta_amount;
 14  begin
 15    open c;
 16    dbms_lock.sleep(10);
 17    --
 18    --  In another session, try this:
 19    --
 20    --  update bankaccounts set amount = amount + 100 where nr = 2
 21    --  /
 22    --  commit
 23    --  /
 24    --  select * from bankaccounts
 25    --  /
 26    --
 27    loop
 28      fetch c bulk collect into a_nr,a_amount limit 2;
 29      forall i in 1..a_nr.count
 30        update bankaccounts
 31           set amount = a_amount(i) * 1.05
 32             , interest_calculated_indicator = 'Y'
 33         where nr = a_nr(i)
 34      ;
 35      exit when a_nr.count <> 2;
 36    end loop;
 37  end;
 38  /

PL/SQL-procedure is geslaagd.

The second session has to wait with the update. So all amounts are set to 105.

rwijk@ORA11GR1> select * from bankaccounts
  2  /

        NR     AMOUNT I
---------- ---------- -
         1        105 Y
         2        105 Y
         3        105 Y
         4        105 Y
         5        105 Y

5 rijen zijn geselecteerd.

rwijk@ORA11GR1> commit
  2  /

Commit is voltooid.

After this commit, the second session continues and produces this output:

rwijk@ORA11GR1> update bankaccounts set amount = amount + 100 where nr = 2
  2  /

1 rij is bijgewerkt.

rwijk@ORA11GR1> commit
  2  /

Commit is voltooid.

rwijk@ORA11GR1> select * from bankaccounts
  2  /

        NR     AMOUNT I
---------- ---------- -
         1        105 Y
         2        205 Y
         3        105 Y
         4        105 Y
         5        105 Y

5 rijen zijn geselecteerd.

Everything as expected. But now the same code using a "for update skip locked". First restore the situation to its initial state:

rwijk@ORA11GR1> remark  Restore situation
rwijk@ORA11GR1> update bankaccounts
  2     set amount = 100
  3       , interest_calculated_indicator = 'N'
  4  /

5 rijen zijn bijgewerkt.

rwijk@ORA11GR1> commit
  2  /

Commit is voltooid.

And execute the same code with a "for update skip locked":

rwijk@ORA11GR1> declare
  2    cursor c
  3    is
  4    select nr
  5         , amount
  6      from bankaccounts
  7     where interest_calculated_indicator = 'N'
  8           for update skip locked
  9    ;
 10    type ta_nr is table of bankaccounts.nr%type;
 11    type ta_amount is table of bankaccounts.amount%type;
 12    a_nr ta_nr;
 13    a_amount ta_amount;
 14  begin
 15    open c;
 16    dbms_lock.sleep(10);
 17    --
 18    --  In another session, try this:
 19    --
 20    --  update bankaccounts set amount = amount + 100 where nr = 2
 21    --  /
 22    --  commit
 23    --  /
 24    --  select * from bankaccounts
 25    --  /
 26    --
 27    loop
 28      fetch c bulk collect into a_nr,a_amount limit 2;
 29      forall i in 1..a_nr.count
 30        update bankaccounts
 31           set amount = a_amount(i) * 1.05
 32             , interest_calculated_indicator = 'Y'
 33         where nr = a_nr(i)
 34      ;
 35      exit when a_nr.count <> 2;
 36    end loop;
 37  end;
 38  /

PL/SQL-procedure is geslaagd.

The second session doesn't have to wait, and the immediate response in this second SQL*Plus session is:

rwijk@ORA11GR1> update bankaccounts set amount = amount + 100 where nr = 2
  2  /

1 rij is bijgewerkt.

rwijk@ORA11GR1> commit
  2  /

Commit is voltooid.

rwijk@ORA11GR1> select * from bankaccounts
  2  /

        NR     AMOUNT I
---------- ---------- -
         1        100 N
         2        200 N
         3        100 N
         4        100 N
         5        100 N

5 rijen zijn geselecteerd.

Now I thought the output in the first session would show a lost update: the amount in record 2 being 105. But it didn't:

rwijk@ORA11GR1> select * from bankaccounts
  2  /

        NR     AMOUNT I
---------- ---------- -
         1        105 Y
         2        200 N
         3        105 Y
         4        105 Y
         5        105 Y

5 rijen zijn geselecteerd.

It just didn't process the second row. So a "for update skip locked" doesn't only skip the locked rows, it also skips the rows that were modified between the opening and the fetching, thus avoiding the classic "lost update".

Thanks to James Padfield for hinting at this behaviour.

COUNT DISTINCT

This morning I saw an e-mail with the title "SQL puzzle?" in my inbox. That's a good title to make me read that one first. The poster was stunned by the results of the following two queries:

SQL> SELECT COUNT(DISTINCT bdl.nrl_id)
  2  FROM   a_table bdl
  3  WHERE  bdl.periode >= to_date('20081023092701','yyyymmddhh24miss')
  4  ;

COUNT(DISTINCTBDL.NRL_ID)
-------------------------
                    61111

1 rij is geselecteerd.

SQL> SELECT COUNT(*)
  2  FROM  (SELECT DISTINCT bdl.nrl_id
  3         FROM   a_table bdl
  4         WHERE  bdl.periode >= to_date('20081023092701','yyyymmddhh24miss')
  5        )
  6  ;

  COUNT(*)
----------
     61112

1 rij is geselecteerd.

Of course it's possible that a record was inserted in the meantime, but that was not the case here.

The explanation was quite simple. See this example:

rwijk@ORA11GR1> select comm from emp
  2  /

      COMM
----------

       300
       500

      1400




         0





14 rijen zijn geselecteerd.

rwijk@ORA11GR1> select distinct comm from emp
  2  /

      COMM
----------

      1400
       500
       300
         0

5 rijen zijn geselecteerd.

rwijk@ORA11GR1> select count(*) from (select distinct comm from emp)
  2  /

  COUNT(*)
----------
         5

1 rij is geselecteerd.

rwijk@ORA11GR1> select count(distinct comm) from emp
  2  /

COUNT(DISTINCTCOMM)
-------------------
                  4

1 rij is geselecteerd.

Again those pesky null values. From the documentation:

"All aggregate functions except COUNT(*), GROUPING, and GROUPING_ID ignore nulls."

A distinct doesn't ignore nulls, and the count(*) on top of it doesn't either.

At first glance, a "select count(distinct ...)" appears equal to a "select count(*) from (select distinct ...)", but as shown: it is not.

---

UPDATE

I forgot to mention that a "select count(comm) from (select distinct comm...)" [note: count(comm) instead of count(*)] is equal to a "select count(distinct comm) ...":

rwijk@ORA11GR1> select count(comm) from (select distinct comm from emp);

COUNT(COMM)
-----------
          4

1 rij is geselecteerd.

PL/SQL session

Today I gave a session about PL/SQL to colleagues who mostly work with Tibco in our Enterprise Application Integration (EAI) group. They know how to program and they know SQL. The challenge was to cover most of the main features of PL/SQL in a workshop in only 8 hours. The session contained lots of little exercises. Topics were architecture, datatypes, main statements, exception handling, appearances of PL/SQL, SQL in PL/SQL, cursors, collections and recordtypes, bulk processing and dynamic SQL.

For those who are interested and who can read Dutch: you can download the document, presentation and exercises here:

PL/SQL-sessie

Bijbehorende powerpoint voor hoofdstuk 1

Alle scripts

SQL Model Clause tutorial, part three

SQL Model Clause Tutorial, part one
SQL Model Clause Tutorial, part two

If you prefer to read in Dutch, then you can read the original article here (page 30-34), and if you prefer to read in German, then you can find a translated version here.

Practicalities of the SQL Model Clause

If developing SQL queries is part of your job, then it is definitely worth knowing the SQL model clause. Once you’ve gotten over the initial fear of the new syntax and learned the basics, you can save yourself writing procedural code and creating auxiliary database objects even more often, by doing more in SQL itself. You may end up with shorter, more readable and sometimes faster code. This article will show a couple of uses of the model clause for fairly common type of queries and will discuss in each case whether or not the model clause solution is suitable compared to alternative queries - if any - in terms of readability and performance. By the end you will more easily recognize situations in your daily work where you can apply the model clause.

Background / Overview

The SQL model clause was introduced with the release of Oracle10g Release 1, back in 2003. According to the documentation, the model clause can replace PC-based spreadsheets by creating a multidimensional array from query results and then apply formulas (called rules) to this array to calculate new values. However, judging from the various Oracle forums, it is not used very often yet. And when it is used, people are not doing spreadsheet like calculations with it. The types of queries the model clause does get used, are discussed in this article. These types of queries are:

• Forecasting queries
• Row generation
• Variable number of calculations based on calculated values
• Complex algorithms
• String aggregation

Of course, the possibilities of the model clause are certainly not limited to this list.

If you are not familiar with the SQL model clause yet, then you can catch up by reading Chapter 22 of the Data Warehousing Guide. You can also read the step-by-step tutorials on my weblog here and here.

Forecasting queries

This type of query is used throughout the aforementioned chapter 22 of the Oracle Database Data Warehousing Guide. The basis for this type of query is typically a sales table or view and the question to be answered is a forecast of how future sales will look like. For example, let’s have a look at the SALES table in figure 1.

PRODUCT       YEAR     AMOUNT
------- ---------- ----------
paper         2006          2
pen           2006         80
staples       2006         18
paper         2007          4
pen           2007        100
staples       2007         30

And suppose you want to calculate the 2008 sales to be the 2007 sales multiplied by the same growth factor as from 2006 to 2007. In a formula:

sales[2008] = sales[2007] * ( sales[2007] / sales[2006] )

Using the model clause, such a question can be answered like this:

SQL> select product
 2       , year
 3       , amount
 4    from sales
 5   model
 6         partition by (product)
 7         dimension by (year)
 8         measures (amount)
 9         rules
10         ( amount[2008] = amount[2007] * amount[2007] / amount[2006]
11         )
12   order by year
13       , product
14  /

PRODUCT       YEAR     AMOUNT
------- ---------- ----------
paper         2006          2
pen           2006         80
staples       2006         18
paper         2007          4
pen           2007        100
staples       2007         30
paper         2008          8
pen           2008        125
staples       2008         50

Using the model clause for such a query is not mandatory; you can achieve the same result by doing a UNION ALL and calculate next year’s sales in a separate query with its own extra table access. Or you can use grouping sets. But if you write these statements, you'll see they become messy and complicated quite fast. And what if you want the sales forecast for 2009 as well, for example by multiplying the sales of 2008 by 2? With the model clause, you just add an extra rule:

amount[2009] = amount[2008] * 2

So forecasting queries are the example in chapter 22 of the Data Warehousing Guide for a reason, as the SQL model clause is able to calculate them without much ado.

Row generation

For many years, you generated rows by selecting from a table or view for which you knew it contained at least the amount of rows you wanted to generate. Usually the all_objects view was used for this, like this:

select rownum
  from all_objects
 where rownum <= 100000

Then Mikito Harakiri came up with this simple row generating query against dual:

 select level
   from dual
connect by level <= 100000

And with the model clause there is another alternative:

select i
  from dual
 model
       dimension by (1 i)
       measures (0 x)
       (x[for i from 2 to 100000 increment 1] = 0)

And this is a tkprof snippet of a 10046 level 8 trace of the three variants generating 100,000 rows:

select rownum
  from all_objects
 where rownum <= 100000

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.14       0.24          0          0          0           0
Execute      1      0.00       0.00          0          0          0           0
Fetch     4564      9.90      12.50        557     179849          0       68433
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total     4566     10.04      12.75        557     179849          0       68433

Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 88  

Rows     Row Source Operation
-------  ---------------------------------------------------
  68433  COUNT STOPKEY (cr=186696 pr=636 pw=0 time=61522 us)
  68433   FILTER  (cr=186696 pr=636 pw=0 time=61522 us)
  70174    HASH JOIN  (cr=5383 pr=0 pw=0 time=0 us cost=44 size=1198062 card=11862)
     91     INDEX FULL SCAN I_USER2 (cr=1 pr=0 pw=0 time=0 us cost=1 size=87 card=29)(object id 47)
  70174     HASH JOIN  (cr=5382 pr=0 pw=0 time=0 us cost=42 size=1162476 card=11862)
     91      INDEX FULL SCAN I_USER2 (cr=1 pr=0 pw=0 time=0 us cost=1 size=638 card=29)(object id 47)
  70174      TABLE ACCESS FULL OBJ$ (cr=5381 pr=0 pw=0 time=0 us cost=41 size=901512 card=11862)
   3309    TABLE ACCESS BY INDEX ROWID IND$ (cr=1616 pr=5 pw=0 time=0 us cost=2 size=8 card=1)
   4103     INDEX UNIQUE SCAN I_IND1 (cr=601 pr=4 pw=0 time=0 us cost=1 size=0 card=1)(object id 41)
  25305    HASH JOIN  (cr=43900 pr=98 pw=0 time=0 us cost=3 size=24 card=1)
  25702     INDEX RANGE SCAN I_OBJAUTH1 (cr=43900 pr=98 pw=0 time=0 us cost=2 size=11 card=1)(object id 62)
  28897     FIXED TABLE FULL X$KZSRO (cr=0 pr=0 pw=0 time=0 us cost=0 size=1300 card=100)
      1    FIXED TABLE FULL X$KZSPR (cr=8 pr=1 pw=0 time=0 us cost=0 size=26 card=1)
      1    FIXED TABLE FULL X$KZSPR (cr=0 pr=0 pw=0 time=0 us cost=0 size=26 card=1)
      0    NESTED LOOPS  (cr=88452 pr=386 pw=0 time=0 us)
      0     NESTED LOOPS  (cr=88452 pr=386 pw=0 time=0 us cost=6 size=70 card=1)
      0      NESTED LOOPS  (cr=88452 pr=386 pw=0 time=0 us cost=4 size=60 card=1)
  49740       NESTED LOOPS  (cr=83309 pr=386 pw=0 time=0 us cost=3 size=49 card=1)
  49740        MERGE JOIN CARTESIAN (cr=80820 pr=386 pw=0 time=0 us cost=2 size=46 card=1)
   2487         INDEX RANGE SCAN I_OBJ5 (cr=80820 pr=386 pw=0 time=0 us cost=2 size=33 card=1)(object id 40)
  49740         BUFFER SORT (cr=0 pr=0 pw=0 time=0 us cost=0 size=1300 card=100)
  49740          FIXED TABLE FULL X$KZSRO (cr=0 pr=0 pw=0 time=0 us cost=0 size=1300 card=100)
  49740        INDEX RANGE SCAN I_USER2 (cr=2489 pr=0 pw=0 time=0 us cost=1 size=3 card=1)(object id 47)
      0       INDEX RANGE SCAN I_OBJAUTH1 (cr=5143 pr=0 pw=0 time=0 us cost=1 size=11 card=1)(object id 62)
      0      INDEX RANGE SCAN I_DEPENDENCY1 (cr=0 pr=0 pw=0 time=0 us cost=1 size=0 card=3)(object id 106)
      0     TABLE ACCESS BY INDEX ROWID DEPENDENCY$ (cr=0 pr=0 pw=0 time=0 us cost=2 size=10 card=1)
      1    FIXED TABLE FULL X$KZSPR (cr=0 pr=0 pw=0 time=0 us cost=0 size=26 card=1)
  23864    HASH JOIN  (cr=28167 pr=0 pw=0 time=0 us cost=3 size=24 card=1)
  24116     INDEX RANGE SCAN I_OBJAUTH1 (cr=28167 pr=0 pw=0 time=0 us cost=2 size=11 card=1)(object id 62)
  26105     FIXED TABLE FULL X$KZSRO (cr=0 pr=0 pw=0 time=0 us cost=0 size=1300 card=100)
      1    FIXED TABLE FULL X$KZSPR (cr=0 pr=0 pw=0 time=0 us cost=0 size=26 card=1)
     99    NESTED LOOPS  (cr=1761 pr=0 pw=0 time=0 us cost=2 size=48 card=2)
    120     INDEX RANGE SCAN I_OBJAUTH1 (cr=1761 pr=0 pw=0 time=0 us cost=2 size=11 card=1)(object id 62)
     99     FIXED TABLE FULL X$KZSRO (cr=0 pr=0 pw=0 time=0 us cost=0 size=26 card=2)
      0    NESTED LOOPS  (cr=7012 pr=68 pw=0 time=0 us cost=6 size=70 card=1)
   4820     MERGE JOIN CARTESIAN (cr=6454 pr=68 pw=0 time=0 us cost=5 size=59 card=1)
    241      NESTED LOOPS  (cr=6454 pr=68 pw=0 time=0 us)
   1458       NESTED LOOPS  (cr=6205 pr=28 pw=0 time=0 us cost=5 size=46 card=1)
    249        NESTED LOOPS  (cr=5684 pr=0 pw=0 time=0 us cost=3 size=36 card=1)
    249         INDEX RANGE SCAN I_OBJ5 (cr=5443 pr=0 pw=0 time=0 us cost=2 size=33 card=1)(object id 40)
    249         INDEX RANGE SCAN I_USER2 (cr=241 pr=0 pw=0 time=0 us cost=1 size=3 card=1)(object id 47)
   1458        INDEX RANGE SCAN I_DEPENDENCY1 (cr=521 pr=28 pw=0 time=0 us cost=1 size=0 card=3)(object id 106)
    241       TABLE ACCESS BY INDEX ROWID DEPENDENCY$ (cr=249 pr=40 pw=0 time=0 us cost=2 size=10 card=1)
   4820      BUFFER SORT (cr=0 pr=0 pw=0 time=0 us cost=3 size=1300 card=100)
   4820       FIXED TABLE FULL X$KZSRO (cr=0 pr=0 pw=0 time=0 us cost=0 size=1300 card=100)
      0     INDEX RANGE SCAN I_OBJAUTH1 (cr=558 pr=0 pw=0 time=0 us cost=1 size=11 card=1)(object id 62)
      1    FIXED TABLE FULL X$KZSPR (cr=0 pr=0 pw=0 time=0 us cost=0 size=26 card=1)
      1    FIXED TABLE FULL X$KZSPR (cr=0 pr=0 pw=0 time=0 us cost=0 size=26 card=1)
      1    FIXED TABLE FULL X$KZSPR (cr=0 pr=0 pw=0 time=0 us cost=0 size=26 card=1)
      0    FIXED TABLE FULL X$KZSPR (cr=0 pr=0 pw=0 time=0 us cost=0 size=26 card=1)
      1    FIXED TABLE FULL X$KZSPR (cr=0 pr=0 pw=0 time=0 us cost=0 size=26 card=1)
      1    FIXED TABLE FULL X$KZSPR (cr=0 pr=0 pw=0 time=0 us cost=0 size=26 card=1)
      1    FIXED TABLE FULL X$KZSPR (cr=0 pr=0 pw=0 time=0 us cost=0 size=26 card=1)
      1    FIXED TABLE FULL X$KZSPR (cr=0 pr=0 pw=0 time=0 us cost=0 size=26 card=1)
      4    NESTED LOOPS  (cr=2498 pr=0 pw=0 time=0 us cost=2 size=72 card=2)
     10     NESTED LOOPS  (cr=2498 pr=0 pw=0 time=0 us cost=2 size=23 card=1)
    461      TABLE ACCESS BY INDEX ROWID TRIGGER$ (cr=1967 pr=0 pw=0 time=0 us cost=1 size=12 card=1)
    488       INDEX UNIQUE SCAN I_TRIGGER2 (cr=1479 pr=0 pw=0 time=0 us cost=0 size=0 card=1)(object id 160)
     10      INDEX RANGE SCAN I_OBJAUTH1 (cr=531 pr=0 pw=0 time=0 us cost=1 size=11 card=1)(object id 62)
      4     FIXED TABLE FULL X$KZSRO (cr=0 pr=0 pw=0 time=0 us cost=0 size=26 card=2)
      1    FIXED TABLE FULL X$KZSPR (cr=0 pr=0 pw=0 time=0 us cost=0 size=26 card=1)
      1    FIXED TABLE FULL X$KZSPR (cr=0 pr=0 pw=0 time=0 us cost=0 size=26 card=1)
      0    FIXED TABLE FULL X$KZSPR (cr=0 pr=0 pw=0 time=0 us cost=0 size=26 card=1)
      1    FIXED TABLE FULL X$KZSPR (cr=0 pr=0 pw=0 time=0 us cost=0 size=26 card=1)
      0    FIXED TABLE FULL X$KZSPR (cr=0 pr=0 pw=0 time=0 us cost=0 size=26 card=1)
      1    FIXED TABLE FULL X$KZSPR (cr=0 pr=0 pw=0 time=0 us cost=0 size=26 card=1)
      1    FIXED TABLE FULL X$KZSPR (cr=0 pr=0 pw=0 time=0 us cost=0 size=26 card=1)
      1    FIXED TABLE FULL X$KZSPR (cr=0 pr=0 pw=0 time=0 us cost=0 size=26 card=1)
      0    FIXED TABLE FULL X$KZSPR (cr=0 pr=0 pw=0 time=0 us cost=0 size=26 card=1)
      0    FIXED TABLE FULL X$KZSPR (cr=0 pr=0 pw=0 time=0 us cost=0 size=26 card=1)
      0    FIXED TABLE FULL X$KZSPR (cr=0 pr=0 pw=0 time=0 us cost=0 size=26 card=1)
      0    FIXED TABLE FULL X$KZSPR (cr=0 pr=0 pw=0 time=0 us cost=0 size=26 card=1)
      0    FIXED TABLE FULL X$KZSPR (cr=0 pr=0 pw=0 time=0 us cost=0 size=26 card=1)
      0    FIXED TABLE FULL X$KZSPR (cr=0 pr=0 pw=0 time=0 us cost=0 size=26 card=1)
     92    VIEW  (cr=0 pr=0 pw=0 time=0 us cost=2 size=13 card=1)
     92     FAST DUAL  (cr=0 pr=0 pw=0 time=0 us cost=2 size=0 card=1)
      2    NESTED LOOPS  (cr=1060 pr=0 pw=0 time=0 us cost=2 size=42 card=2)
      4     INDEX RANGE SCAN I_OBJAUTH1 (cr=1060 pr=0 pw=0 time=0 us cost=2 size=8 card=1)(object id 62)
      2     FIXED TABLE FULL X$KZSRO (cr=0 pr=0 pw=0 time=0 us cost=0 size=26 card=2)
      1    FIXED TABLE FULL X$KZSPR (cr=0 pr=0 pw=0 time=0 us cost=0 size=26 card=1)
      0    NESTED LOOPS  (cr=0 pr=0 pw=0 time=0 us cost=2 size=42 card=2)
      0     INDEX RANGE SCAN I_OBJAUTH1 (cr=0 pr=0 pw=0 time=0 us cost=2 size=8 card=1)(object id 62)
      0     FIXED TABLE FULL X$KZSRO (cr=0 pr=0 pw=0 time=0 us cost=0 size=26 card=2)
      0    FIXED TABLE FULL X$KZSPR (cr=0 pr=0 pw=0 time=0 us cost=0 size=26 card=1)
      0    VIEW  (cr=0 pr=0 pw=0 time=0 us cost=1 size=16 card=1)
      0     SORT GROUP BY (cr=0 pr=0 pw=0 time=0 us cost=1 size=86 card=1)
      0      NESTED LOOPS  (cr=0 pr=0 pw=0 time=0 us cost=1 size=86 card=1)
      0       MERGE JOIN CARTESIAN (cr=0 pr=0 pw=0 time=0 us cost=0 size=78 card=1)
      0        NESTED LOOPS  (cr=0 pr=0 pw=0 time=0 us cost=0 size=65 card=1)
      0         INDEX UNIQUE SCAN I_OLAP_CUBES$ (cr=0 pr=0 pw=0 time=0 us cost=0 size=13 card=1)(object id 899)
      0         TABLE ACCESS BY INDEX ROWID OLAP_DIMENSIONALITY$ (cr=0 pr=0 pw=0 time=0 us cost=0 size=52 card=1)
      0          INDEX RANGE SCAN I_OLAP_DIMENSIONALITY$ (cr=0 pr=0 pw=0 time=0 us cost=0 size=0 card=1)(object id 903)
      0        BUFFER SORT (cr=0 pr=0 pw=0 time=0 us cost=0 size=13 card=1)
      0         INDEX FULL SCAN I_OLAP_CUBE_DIMENSIONS$ (cr=0 pr=0 pw=0 time=0 us cost=0 size=13 card=1)(object id 887)
      0       INDEX RANGE SCAN I_OBJ1 (cr=0 pr=0 pw=0 time=0 us cost=1 size=8 card=1)(object id 36)
      0    NESTED LOOPS  (cr=0 pr=0 pw=0 time=0 us cost=2 size=27 card=1)
      0     INDEX FULL SCAN I_USER2 (cr=0 pr=0 pw=0 time=0 us cost=1 size=19 card=1)(object id 47)
      0     INDEX RANGE SCAN I_OBJ4 (cr=0 pr=0 pw=0 time=0 us cost=1 size=8 card=1)(object id 39)


Elapsed times include waiting on following events:
  Event waited on                             Times   Max. Wait  Total Waited
  ----------------------------------------   Waited  ----------  ------------
  SQL*Net message to client                    4564        0.00          0.02
  SQL*Net message from client                  4564        0.00          0.80
  db file sequential read                       557        0.07          2.36
********************************************************************************

 select level
   from dual
connect by level <= 100000

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.00          0          0          0           0
Execute      1      0.00       0.00          0          0          0           0
Fetch     6668      0.31       0.36          0          0          0      100000
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total     6670      0.31       0.36          0          0          0      100000

Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 88  

Rows     Row Source Operation
-------  ---------------------------------------------------
 100000  CONNECT BY WITHOUT FILTERING (cr=0 pr=0 pw=0 time=0 us)
      1   FAST DUAL  (cr=0 pr=0 pw=0 time=0 us cost=2 size=0 card=1)


Elapsed times include waiting on following events:
  Event waited on                             Times   Max. Wait  Total Waited
  ----------------------------------------   Waited  ----------  ------------
  SQL*Net message to client                    6668        0.00          0.02
  SQL*Net message from client                  6668        0.00          0.75
********************************************************************************

select i
  from dual
 model
       dimension by (1 i)
       measures (0 x)
       (x[for i from 2 to 100000 increment 1] = 0)

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.00          0          0          0           0
Execute      1      0.00       0.00          0          0          0           0
Fetch     6668      3.39       3.19          0          0          0      100000
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total     6670      3.39       3.19          0          0          0      100000

Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 88  

Rows     Row Source Operation
-------  ---------------------------------------------------
 100000  SQL MODEL ORDERED (cr=0 pr=0 pw=0 time=0 us)
      1   FAST DUAL  (cr=0 pr=0 pw=0 time=0 us cost=2 size=0 card=1)


Elapsed times include waiting on following events:
  Event waited on                             Times   Max. Wait  Total Waited
  ----------------------------------------   Waited  ----------  ------------
  SQL*Net message to client                    6668        0.00          0.02
  SQL*Net message from client                  6668        0.00          0.75



********************************************************************************

The model clause query for generating rows is faster than the good old query against all_objects, but much slower than the the "connect by level" query. It is therefore not worthwhile to use the SQL model clause for pure row generating. However, for inferred problems of row generation, the model clause can certainly be an option. An example of such an inferred problems is splitting periods into individual days in a calendar, for example when 1 row containing a startdate January 1st and enddate January 5th, have to be split into 5 individual rows. Another example is splitting strings into individual words:

SQL> create table t (id,str)
  2  as
  3  select 1, 'OGh Visie' from dual union all
  4  select 2, 'Oracle Gebruikersclub Holland' from dual union all
  5  select 3, null from dual union all
  6  select 4, 'OGh' from dual
  7  /

Table created.

To split the individual words from these strings you can use one of the next two queries:

SQL> select id
  2       , i seqnr
  3       , str word
  4    from t
  5   model
  6         return updated rows
  7         partition by (id)
  8         dimension by (0 i)
  9         measures (str)
 10         ( str[for i from 1 to regexp_count(str[0],' ')+1 increment 1]
 11           = regexp_substr(str[0],'[^ ]+',1,cv(i))
 12         )
 13   order by id
 14       , seqnr
 15  /

        ID      SEQNR WORD
---------- ---------- -----------------------------
         1          1 OGh
         1          2 Visie
         2          1 Oracle
         2          2 Gebruikersclub
         2          3 Holland
         4          1 OGh

6 rows selected.

SQL> select id
  2       , n seqnr
  3       , regexp_substr(str,'[^ ]+',1,n) word
  4    from t
  5       , ( select level n
  6             from ( select max(regexp_count(str,' '))+1 max_#words
  7                      from t
  8                  )
  9          connect by level <= max_#words
 10         )
 11   where n <= regexp_count(str,' ')+1
 12   order by id
 13       , seqnr
 14  /

        ID      SEQNR WORD
---------- ---------- -----------------------------
         1          1 OGh
         1          2 Visie
         2          1 Oracle
         2          2 Gebruikersclub
         2          3 Holland
         4          1 OGh

6 rows selected.

You can see how they are based on the pure row generating queries described earlier. In these solutions "regexp_count(str,' ')+1" calculates the number of words, by calculating the number of spaces and adding one. "regexp_substr(str,'[^ ]+',1,n)" gets the n-th word out of the string.

Especially for the "connect by level" query, more variants are possible. All of them look pretty cryptic, making them hard to maintain. The SQL model clause query maintains its readability by using the partition clause.

With a 10,000 times enlarged table t, the tkprof file looks like this:

select id
     , i seqnr
     , str word
  from t
 model
       return updated rows
       partition by (id)
       dimension by (0 i)
       measures (str)
       ( str[for i from 1 to regexp_count(str[0],' ')+1 increment 1]
         = regexp_substr(str[0],'[^ ]+',1,cv(i))
       )
 order by id
     , seqnr

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.00          0          0          0           0
Execute      1      0.00       0.00          0          0          0           0
Fetch     4001      1.56       1.58          0        142          0       60000
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total     4003      1.56       1.58          0        142          0       60000

Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 88  

Rows     Row Source Operation
-------  ---------------------------------------------------
  60000  SORT ORDER BY (cr=142 pr=0 pw=0 time=0 us cost=286 size=640000 card=40000)
  60000   SQL MODEL ORDERED (cr=142 pr=0 pw=0 time=0 us cost=286 size=640000 card=40000)
  40000    TABLE ACCESS FULL T (cr=142 pr=0 pw=0 time=0 us cost=69 size=640000 card=40000)

********************************************************************************

select id
     , n seqnr
     , regexp_substr(str,'[^ ]+',1,n) word
  from t
     , ( select level n
           from ( select max(regexp_count(str,' '))+1 max_#words
                    from t
                )
        connect by level <= max_#words
       )
 where n <= regexp_count(str,' ')+1
 order by id
     , seqnr

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.00          0          0          0           0
Execute      1      0.00       0.00          0          0          0           0
Fetch     4001      1.32       1.29          0        568          0       60000
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total     4003      1.32       1.29          0        568          0       60000

Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 88  

Rows     Row Source Operation
-------  ---------------------------------------------------
  60000  SORT ORDER BY (cr=568 pr=0 pw=0 time=0 us cost=140 size=58000 card=2000)
  60000   NESTED LOOPS  (cr=568 pr=0 pw=0 time=20508 us cost=139 size=58000 card=2000)
      3    VIEW  (cr=142 pr=0 pw=0 time=0 us cost=69 size=13 card=1)
      3     CONNECT BY WITHOUT FILTERING (cr=142 pr=0 pw=0 time=0 us)
      1      VIEW  (cr=142 pr=0 pw=0 time=0 us cost=69 size=13 card=1)
      1       SORT AGGREGATE (cr=142 pr=0 pw=0 time=0 us)
  40000        TABLE ACCESS FULL T (cr=142 pr=0 pw=0 time=0 us cost=69 size=480000 card=40000)
  60000    TABLE ACCESS FULL T (cr=426 pr=0 pw=0 time=20508 us cost=69 size=32000 card=2000)

********************************************************************************

Again, the "connect by level" trick wins, but this time it's much closer. So you may let readability prevail.

Variable number of calculations based on calculated values

There are queries out there that just seem impossible to do with just SQL, where a model clause can provide a solution elegantly. This is a class of problems where calculations have to be done on calculated values repeatedly. An example says more than a thousand words, so here is one:

SQL> select * from deposits
  2  /

  CUSTOMER     AMOUNT DEPOSIT_DATE
---------- ---------- -------------------
         1       1000 01-01-2003 00:00:00
         1        200 01-01-2004 00:00:00
         1        500 01-01-2005 00:00:00
         1        100 01-01-2006 00:00:00
         1        800 01-01-2007 00:00:00
         2         20 01-01-2003 00:00:00
         2        150 01-01-2004 00:00:00
         2         60 01-01-2005 00:00:00
         2        100 01-01-2006 00:00:00
         2        100 01-01-2007 00:00:00

10 rows selected.

SQL> select * from interest_rates
  2  /

STARTDATE           PERCENTAGE
------------------- ----------
01-01-2003 00:00:00          5
01-01-2004 00:00:00        3.2
01-01-2005 00:00:00        4.1
01-01-2006 00:00:00        5.8
01-01-2007 00:00:00        4.9

5 rows selected.

The question is: "What's the balance at the end of each year?". For customer 1 at the end of 2003 it is: 1000 * 1,05 = 1050. At the end of 2004 this is (1050 + 200) * 1,032 = 1290, et cetera. This is what I mean by calculations based on calculated values, that you have to do a variable number of times. Using the model clause, this query becomes relatively easy, like this:

SQL> select customer
  2       , amount
  3       , deposit_date
  4       , percentage
  5       , balance balance_at_end_of_year
  6    from deposits s
  7       , interest_rates r
  8   where s.deposit_date = r.startdate
  9   model
 10         partition by (s.customer)
 11         dimension by (s.deposit_date)
 12         measures (s.amount, r.percentage, 0 balance)
 13         rules
 14         ( balance[any] order by deposit_date
 15           = round
 16             (   (nvl(balance[add_months(cv(),-12)],0) + amount[cv()])
 17               * (1 + percentage[cv()]/100)
 18             , 2
 19             )
 20         )
 21   order by customer
 22       , deposit_date
 23  /

  CUSTOMER     AMOUNT DEPOSIT_DATE        PERCENTAGE BALANCE_AT_END_OF_YEAR
---------- ---------- ------------------- ---------- ----------------------
         1       1000 01-01-2003 00:00:00          5                   1050
         1        200 01-01-2004 00:00:00        3.2                   1290
         1        500 01-01-2005 00:00:00        4.1                1863.39
         1        100 01-01-2006 00:00:00        5.8                2077.27
         1        800 01-01-2007 00:00:00        4.9                3018.26
         2         20 01-01-2003 00:00:00          5                     21
         2        150 01-01-2004 00:00:00        3.2                 176.47
         2         60 01-01-2005 00:00:00        4.1                 246.17
         2        100 01-01-2006 00:00:00        5.8                 366.25
         2        100 01-01-2007 00:00:00        4.9                  489.1

10 rows selected.

For doing this type of calculations in SQL, there is hardly an alternative, except a very slow one using advanced XML functions. Before Oracle10 you'd probably resort to a procedural language such as PL/SQL. But nowadays you don't need to anymore.

Complex algorithms

With the SQL model clause, it has become possible to solve algorithms, that previously could not be done in SQL. Even some of the most complex ones. An example is a query to distribute tablespaces evenly among data files of a database, in such a way that the free space in each data file remains as equal as possible. Or even a single query to solve each sudoku puzzle when supplied with a string of 81 digits. Although it can be great fun to write, you'll have to admit that these will be a nightmare to maintain if you are not the original author. Regardless of reduced lines of code or small performance gains, a (pipelined) PL/SQL function seems much more appropriate here.

String aggregation

String aggregation is really just a special case of the category "variable number of calculations based on calculations". But it is such a frequently asked question on Oracle forums, that I will treat it separately.

String aggregation is the act of aggregating several rows into one row and "stringing up" all column values, possibly separated by a special character like a comma. An example with the well known EMP table is to produce the following output:

    DEPTNO ENAMES
---------- ----------------------------------------
        10 CLARK,KING,MILLER
        20 ADAMS,FORD,JONES,SCOTT,SMITH
        30 ALLEN,BLAKE,JAMES,MARTIN,TURNER,WARD

There are lots of ways to do string aggregation:
• using a user defined aggregate function, for example Tom Kyte’s stragg function,
• a hierarchical query with sys_connect_by_path
• with XML-functions
• using the COLLECT function, a SQL type and a user defined function
• using the undocumented (and therefore not recommended) wmsys.wm_concat

Using the model clause it is done like this:

SQL> select deptno
  2       , rtrim(ename,',') enames
  3    from ( select deptno
  4                , ename
  5                , rn
  6             from emp
  7            model
  8                  partition by (deptno)
  9                  dimension by (row_number() over
 10                                (partition by deptno order by ename) rn
 11                               )
 12                  measures     (cast(ename as varchar2(40)) ename)
 13                  rules
 14                  ( ename[any] order by rn desc = ename[cv()]||','||ename[cv()+1]
 15                  )
 16         )
 17   where rn = 1
 18   order by deptno
 19  /

    DEPTNO ENAMES
---------- ----------------------------------------
        10 CLARK,KING,MILLER
        20 ADAMS,FORD,JONES,SCOTT,SMITH
        30 ALLEN,BLAKE,JAMES,MARTIN,TURNER,WARD

Just one query, no auxiliary objects and faster than all other previously mentioned alternatives. Nothing comes for free: the price to pay is a higher PGA memory footprint. Usually this is not a problem, if sufficient memory is available of course. If this is not the case, you'll notice the wait events 'direct path read temp' and 'direct path write temp' and the performance gain quickly turns around and becomes a performance loss.

Conclusion

For a number of problems, the SQL model clause is a very useful extension of the SQL language. This is especially true for forecasting queries and queries with a variable number of calculations based on calculated values, including string aggregation. The model clause can also be used for solving complex algorithms in SQL and for row generation. But this compromises maintainability in the first case and performance in the second case. Nevertheless, if you want to leverage SQL's capabilities, the model clause is absolutely worth studying and using.

Article in OGh Visie

For all Dutch readers interested in the SQL model clause (I wonder how selective those two predicates are ...): the last part of the SQL Model Clause Tutorial is published in the winter edition 2008 of OGh Visie. The subject is "Practicalities of the SQL Model Clause" and it tries to answer the question for which kind of problems a model clause query is best applied.

For all non Dutch readers interested in the SQL model clause: a translation of this article will appear on this blog very soon.

Plan a schedule with the SQL model clause

My DBA colleague Ronald Rood asked an interesting question on OTN's SQL and PL/SQL forum. I'll repeat the question here. First the setup of the table:

rwijk@ORA11GR1> create table sschedule (
  2  item varchar2(10)
  3  , days varchar2(30)
  4  , fixed_start_time varchar2(5)
  5  , minutes number
  6  )
  7  /

Tabel is aangemaakt.

rwijk@ORA11GR1> insert into sschedule (item,days, fixed_start_time, minutes)
  2  select 'lunch', 'mon,tue,wed,thu,fri','12:00',60 from dual union all
  3  select 'a', 'tue,fri','10:00',60 from dual union all
  4  select 'b', 'mon,wed',null, 120 from dual union all
  5  select 'opening','mon,tue,wed,thu,fri','08:55', 5 from dual union all
  6  select 'close','mon,tue,wed,thu','20:00', 5 from dual union all
  7  select 'close','fri','16:00', 5 from dual union all
  8  select 'c', 'mon,wed,fri',null, 20 from dual union all
  9  select 'd', 'mon,wed,fri',null, 20 from dual union all
 10  select 'diner','mon,tue,wed,thu','18:00', 60 from dual union all
 11  select 'e','tue,thu,fri',null, 40 from dual union all
 12  select 'keynote', 'mon','09:00', 120 from dual union all
 13  select 'bye', 'fri','14:00', 120 from dual union all
 14  select 'f','tue,thu,fri',null, 40 from dual union all
 15  select 'g', 'mon,wed',null, 120 from dual
 16  /

14 rijen zijn aangemaakt.

rwijk@ORA11GR1> select * from sschedule
  2  /

ITEM       DAYS                           FIXED    MINUTES
---------- ------------------------------ ----- ----------
lunch      mon,tue,wed,thu,fri            12:00         60
a          tue,fri                        10:00         60
b          mon,wed                                     120
opening    mon,tue,wed,thu,fri            08:55          5
close      mon,tue,wed,thu                20:00          5
close      fri                            16:00          5
c          mon,wed,fri                                  20
d          mon,wed,fri                                  20
diner      mon,tue,wed,thu                18:00         60
e          tue,thu,fri                                  40
keynote    mon                            09:00        120
bye        fri                            14:00        120
f          tue,thu,fri                                  40
g          mon,wed                                     120

14 rijen zijn geselecteerd.

And the accompanying question:

"I have a table that contains a list of items that are to be discussed in a meeting, all taking place in the same room. For some meetings a guest speaker is invited, those meetings have a fixed start time. All meetings have a fixed duration in minutes. The days all start with opening and end with close. How can we generate the start times of the items that have no fixed start time set?

for friday this gives:

ITEM    FIXED MINUTES
------- ----- -------
opening 08:55       5
a       10:00      60
lunch   12:00      60
bye     14:00     120
close   16:00       5
c                  20
d                  20
e                  40
f                  40

e can start at 09:00 to fill the gap between opening and a.
c can start at 09:40 to fill the gap between e and a.
f can start at 11:00 to fill the gap between a and lunch.
d can start at 11:40 to fill the gap between f and lunch.

more combinations are very legal, as long as the fixed_start_times are honoured.
How can this list be generated in 1 sql?
When a day becomes overbooked the items that fall must get something like 'does not fit' to signal it's too busy that day."

In real life I would never implement a SQL only solution for such a problem, because the resulting SQL will be quite hard to maintain, even when properly documented. But it sure is fun to do it using only SQL.

If you clicked the link in the beginning of the post, you might have seen a solution already. In this post I will try to explain how I solved this problem which may look very difficult at first. I think it demonstrates the power of the SQL model clause very well.

The first thing to do here, is to normalize the table, by splitting the comma separated string into separate rows, one row for each day. Without such a normalized set, a solution would be much harder. To split the string into several rows, I use the technique that resembles the one described in this post, particularly the SQL model clause variant. This query does this part of the job:

rwijk@ORA11GR1> select item
  2       , cast(days as varchar2(3)) day
  3       , to_date(fixed_start_time,'hh24:mi') start_time
  4       , minutes
  5       , to_date(fixed_start_time,'hh24:mi')
  6         + numtodsinterval(minutes,'minute') end_time
  7    from sschedule
  8   model
  9         return updated rows
 10         partition by (item,fixed_start_time,minutes)
 11         dimension by (0 i)
 12         measures (days)
 13         ( days [for i from 1 to regexp_count(days[0],',') + 1 increment 1]
 14           = regexp_substr(days[0],'[^,]+',1,cv(i))
 15         )
 16   order by decode(day,'mon',1,'tue',2,'wed',3,'thu',4,'fri',5)
 17       , start_time
 18  /

ITEM       DAY START_TIME             MINUTES END_TIME
---------- --- ------------------- ---------- -------------------
opening    mon 01-12-2008 08:55:00          5 01-12-2008 09:00:00
keynote    mon 01-12-2008 09:00:00        120 01-12-2008 11:00:00
lunch      mon 01-12-2008 12:00:00         60 01-12-2008 13:00:00
diner      mon 01-12-2008 18:00:00         60 01-12-2008 19:00:00
close      mon 01-12-2008 20:00:00          5 01-12-2008 20:05:00
b          mon                            120
d          mon                             20
c          mon                             20
g          mon                            120
opening    tue 01-12-2008 08:55:00          5 01-12-2008 09:00:00
a          tue 01-12-2008 10:00:00         60 01-12-2008 11:00:00
lunch      tue 01-12-2008 12:00:00         60 01-12-2008 13:00:00
diner      tue 01-12-2008 18:00:00         60 01-12-2008 19:00:00
close      tue 01-12-2008 20:00:00          5 01-12-2008 20:05:00
f          tue                             40
e          tue                             40
opening    wed 01-12-2008 08:55:00          5 01-12-2008 09:00:00
lunch      wed 01-12-2008 12:00:00         60 01-12-2008 13:00:00
diner      wed 01-12-2008 18:00:00         60 01-12-2008 19:00:00
close      wed 01-12-2008 20:00:00          5 01-12-2008 20:05:00
b          wed                            120
g          wed                            120
d          wed                             20
c          wed                             20
opening    thu 01-12-2008 08:55:00          5 01-12-2008 09:00:00
lunch      thu 01-12-2008 12:00:00         60 01-12-2008 13:00:00
diner      thu 01-12-2008 18:00:00         60 01-12-2008 19:00:00
close      thu 01-12-2008 20:00:00          5 01-12-2008 20:05:00
e          thu                             40
f          thu                             40
opening    fri 01-12-2008 08:55:00          5 01-12-2008 09:00:00
a          fri 01-12-2008 10:00:00         60 01-12-2008 11:00:00
lunch      fri 01-12-2008 12:00:00         60 01-12-2008 13:00:00
bye        fri 01-12-2008 14:00:00        120 01-12-2008 16:00:00
close      fri 01-12-2008 16:00:00          5 01-12-2008 16:05:00
c          fri                             20
d          fri                             20
e          fri                             40
f          fri                             40

39 rijen zijn geselecteerd.

This query puts every row in its own partition, and indexes this row with dimension i set to 0. These rows are the original ones, which we won't return, because of the use of the keyword RETURN UPDATED ROWS. The model creates new cells with dimension values from 1 and upwards. In each partition the number of commas is calculated with regexp_count(days[0],','). The number of commas plus one is the number of rows that have to be generated. For each new row, regexp_substr(days[0],'[^,]+',1,cv(i)) gives the cv(i)-th element in the string.

The less trickier part of the query is to change some datatypes: days is set to varchar2(3) for a prettier layout only, and the start_time and end_time should be dates for easier calculating. You see that a default date is chosen, being the first day of the current month. This part of the date is irrelevant. Using intervals (numtodsinterval) I can now easily add the number of minutes with the start_time to calculate the end_time.

This was the easy part :-). Next challenge is to process all rows with an empty start_time and allocate a time slot to them. The question does not describe an algorithm how to do this, so I choose an easy one: process all rows with an empty start time and start with the longest one. For each of these rows, assign the row to the largest gap in time, at the beginning of the interval. A few challenges here: determining the gaps, adjusting the gaps after an item has been assigned in a gap and detecting when an item doesn't fit in any gap.

For these challenges I put up another model, partitioned by day. The rows inside each partition are indexed by a number with the row_number analytic function, the rows with an empty start_time first. Like this I have a nice dimension I can iterate over AND I can use the UNTIL clause to only iterate over the ones with a empty start_time. For this I have to introduce the cnt measure, that counts the number of empty start_times in a partition. So in each partition there will be as many iterations as there are cells with empty start_times.

Let's first show the resulting query and then explain what's going on:

rwijk@ORA11GR1> with schedule_normalized as
  2  ( select item
  3         , cast(days as varchar2(3)) day
  4         , to_date(fixed_start_time,'hh24:mi') start_time
  5         , minutes
  6         , to_date(fixed_start_time,'hh24:mi')
  7           + numtodsinterval(minutes,'minute') end_time
  8      from sschedule
  9     model
 10           return updated rows
 11           partition by (item,fixed_start_time,minutes)
 12           dimension by (0 i)
 13           measures (days)
 14           ( days [for i from 1 to regexp_count(days[0],',') + 1 increment 1]
 15             = regexp_substr(days[0],'[^,]+',1,cv(i))
 16           )
 17  )
 18  select item
 19       , day
 20       , to_char(st,'hh24:mi') start_time
 21       , to_char(et,'hh24:mi') end_time
 22       , minutes
 23       , fit
 24    from schedule_normalized
 25   model
 26         partition by (day)
 27         dimension by
 28         ( row_number() over
 29           (partition by day order by start_time nulls first, minutes desc) rn
 30         )
 31         measures
 32         ( item
 33         , start_time st
 34         , minutes
 35         , end_time et
 36         , lead(start_time) over
 37           (partition by day order by start_time) next_start_time
 38         , count(nvl2(start_time,null,1)) over (partition by day) cnt
 39         , 0 rn_with_largest_gap
 40         , row_number() over
 41           (partition by day order by start_time nulls first, minutes desc) rnm
 42         , cast(null as varchar2(11)) fit
 43         )
 44         rules iterate(1000) until (iteration_number + 1 = cnt[1])
 45         ( rn_with_largest_gap[1]
 46           = max(rnm) keep
 47             (dense_rank last order by next_start_time - et nulls first)[any]
 48         , fit[iteration_number+1]
 49           = case
 50               when max(next_start_time - et)[any]
 51                  < minutes[iteration_number+1]/24/60
 52               then 'doesn''t fit'
 53             end
 54         , st[iteration_number+1]
 55           = case
 56               when fit[iteration_number+1] is null
 57               then et[rn_with_largest_gap[1]]
 58             end
 59         , et[iteration_number+1]
 60           = case
 61               when fit[iteration_number+1] is null
 62               then et[rn_with_largest_gap[1]]
 63                    + numtodsinterval(minutes[iteration_number+1],'minute')
 64             end
 65         , next_start_time[iteration_number+1]
 66           = case
 67               when fit[iteration_number+1] is null
 68               then next_start_time[rn_with_largest_gap[1]]
 69             end
 70         , next_start_time[rn_with_largest_gap[1]]
 71           = case
 72               when fit[iteration_number+1] is not null
 73               then next_start_time[rn_with_largest_gap[1]]
 74             end
 75         )
 76   order by decode(day,'mon',1,'tue',2,'wed',3,'thu',4,'fri',5)
 77       , start_time
 78  /

ITEM       DAY START END_T    MINUTES FIT
---------- --- ----- ----- ---------- -----------
opening    mon 08:55 09:00          5
keynote    mon 09:00 11:00        120
d          mon 11:00 11:20         20
lunch      mon 12:00 13:00         60
g          mon 13:00 15:00        120
b          mon 15:00 17:00        120
diner      mon 18:00 19:00         60
c          mon 19:00 19:20         20
close      mon 20:00 20:05          5
opening    tue 08:55 09:00          5
a          tue 10:00 11:00         60
lunch      tue 12:00 13:00         60
f          tue 13:00 13:40         40
e          tue 13:40 14:20         40
diner      tue 18:00 19:00         60
close      tue 20:00 20:05          5
opening    wed 08:55 09:00          5
g          wed 09:00 11:00        120
lunch      wed 12:00 13:00         60
b          wed 13:00 15:00        120
d          wed 15:00 15:20         20
c          wed 15:20 15:40         20
diner      wed 18:00 19:00         60
close      wed 20:00 20:05          5
opening    thu 08:55 09:00          5
lunch      thu 12:00 13:00         60
e          thu 13:00 13:40         40
f          thu 13:40 14:20         40
diner      thu 18:00 19:00         60
close      thu 20:00 20:05          5
opening    fri 08:55 09:00          5
d          fri 09:00 09:20         20
c          fri 09:20 09:40         20
a          fri 10:00 11:00         60
f          fri 11:00 11:40         40
lunch      fri 12:00 13:00         60
e          fri 13:00 13:40         40
bye        fri 14:00 16:00        120
close      fri 16:00 16:05          5

39 rijen zijn geselecteerd.

In the with clause you see the normalized query discussed earlier. The part I'm discussing here, starts at line 18. For each iteration I have to choose which of the rows has the largest open time slot attached at the end. For this the auxiliary measure next_start_time is introduced, calculated with the lead analytic function. The index of the row with the largest open time slot is stored in the auxiliary measure rn_with_largest_gap. Each iteration starts with calculating this value again. All subsequent rules of the models use this rn_with_largest_gap measure. Rules 3 and 4 calculate the start_time and end_time of the rows that started out with an empty start_time. The last two rules adjust the next_start_time measures to the new situation: since the new allocated time slot is adjacent to the existing time slot, the next_start_time of the original one is set to null, and the next_start_time of the new time slot is the original next_start_time.

Last part of the solution is to addition of the fit measure. With this measure and the second rule, I can check whether the largest open time slot is large enough for the item. If it is not large enough, I put in a text "doesn't fit" in this cell. All subsequent rules effectively do nothing when a non null value is encountered in this cell. With the current data, everything fits, but if you click on the link to the original question, you'll see a situation where two rows don't fit.

If you have not given up and have read up through here, you maybe agree with me that this is another nice example of a complex algorithm that can be solved with only SQL. Although in this category, nothing beats this one of course. But remember kids: don't try this at work ;-)