Date format element 'ww'

We all know what the 'ww' date format element does, right? It provides the week number such that January 1 is always in week 1. The 'iw' element is a little different in that January 1 can be week 53 of the previous year if it is a Friday, Saturday or Sunday.

Today, my boss - who has worked with Oracle for 18 years now - was surprised to see that Monday, January 28 2008 is week 4 and Tuesday, January 29 2008 is week 5. He asked me to have a look and I was surprised too. My boss and me both seem to have made the same false assumption for all these years. It is the assumption that a week runs from Monday until Sunday, and that all days in between have the same week number. In our assumption week 1 of 2008 contains 6 days from Tuesday January 1 2008 until Sunday January 6 2008. And week 2 would again be "normal" and runs from Monday January 7 2008 and Sunday January 13 2008.

So I ran a few queries like this to verify this assumption:

rwijk@ORA11G> alter session set nls_date_language = 'american'
  2  /

Sessie is gewijzigd.

rwijk@ORA11G> select to_char
  2          ( date '2003-12-20' + level
  3          , 'day yyyy-mm-dd   yyyy ww iyyy iw'
  4          )
  5     from dual
  6  connect by level <= 42
  7  /

TO_CHAR(DATE'2003-12-20'+LEVEL,'DAYYYY
--------------------------------------
sunday    2003-12-21   2003 51 2003 51
monday    2003-12-22   2003 51 2003 52
tuesday   2003-12-23   2003 51 2003 52
wednesday 2003-12-24   2003 52 2003 52
thursday  2003-12-25   2003 52 2003 52
friday    2003-12-26   2003 52 2003 52
saturday  2003-12-27   2003 52 2003 52
sunday    2003-12-28   2003 52 2003 52
monday    2003-12-29   2003 52 2004 01
tuesday   2003-12-30   2003 52 2004 01
wednesday 2003-12-31   2003 53 2004 01
thursday  2004-01-01   2004 01 2004 01
friday    2004-01-02   2004 01 2004 01
saturday  2004-01-03   2004 01 2004 01
sunday    2004-01-04   2004 01 2004 01
monday    2004-01-05   2004 01 2004 02
tuesday   2004-01-06   2004 01 2004 02
wednesday 2004-01-07   2004 01 2004 02
thursday  2004-01-08   2004 02 2004 02
friday    2004-01-09   2004 02 2004 02
saturday  2004-01-10   2004 02 2004 02
sunday    2004-01-11   2004 02 2004 02
monday    2004-01-12   2004 02 2004 03
tuesday   2004-01-13   2004 02 2004 03
wednesday 2004-01-14   2004 02 2004 03
thursday  2004-01-15   2004 03 2004 03
friday    2004-01-16   2004 03 2004 03
saturday  2004-01-17   2004 03 2004 03
sunday    2004-01-18   2004 03 2004 03
monday    2004-01-19   2004 03 2004 04
tuesday   2004-01-20   2004 03 2004 04
wednesday 2004-01-21   2004 03 2004 04
thursday  2004-01-22   2004 04 2004 04
friday    2004-01-23   2004 04 2004 04
saturday  2004-01-24   2004 04 2004 04
sunday    2004-01-25   2004 04 2004 04
monday    2004-01-26   2004 04 2004 05
tuesday   2004-01-27   2004 04 2004 05
wednesday 2004-01-28   2004 04 2004 05
thursday  2004-01-29   2004 05 2004 05
friday    2004-01-30   2004 05 2004 05
saturday  2004-01-31   2004 05 2004 05

42 rijen zijn geselecteerd.

rwijk@ORA11G>  select to_char
  2          ( date '2007-12-20' + level
  3          , 'day yyyy-mm-dd   yyyy ww iyyy iw'
  4          )
  5     from dual
  6  connect by level <= 42
  7  /

TO_CHAR(DATE'2007-12-20'+LEVEL,'DAYYYY
--------------------------------------
friday    2007-12-21   2007 51 2007 51
saturday  2007-12-22   2007 51 2007 51
sunday    2007-12-23   2007 51 2007 51
monday    2007-12-24   2007 52 2007 52
tuesday   2007-12-25   2007 52 2007 52
wednesday 2007-12-26   2007 52 2007 52
thursday  2007-12-27   2007 52 2007 52
friday    2007-12-28   2007 52 2007 52
saturday  2007-12-29   2007 52 2007 52
sunday    2007-12-30   2007 52 2007 52
monday    2007-12-31   2007 53 2008 01
tuesday   2008-01-01   2008 01 2008 01
wednesday 2008-01-02   2008 01 2008 01
thursday  2008-01-03   2008 01 2008 01
friday    2008-01-04   2008 01 2008 01
saturday  2008-01-05   2008 01 2008 01
sunday    2008-01-06   2008 01 2008 01
monday    2008-01-07   2008 01 2008 02
tuesday   2008-01-08   2008 02 2008 02
wednesday 2008-01-09   2008 02 2008 02
thursday  2008-01-10   2008 02 2008 02
friday    2008-01-11   2008 02 2008 02
saturday  2008-01-12   2008 02 2008 02
sunday    2008-01-13   2008 02 2008 02
monday    2008-01-14   2008 02 2008 03
tuesday   2008-01-15   2008 03 2008 03
wednesday 2008-01-16   2008 03 2008 03
thursday  2008-01-17   2008 03 2008 03
friday    2008-01-18   2008 03 2008 03
saturday  2008-01-19   2008 03 2008 03
sunday    2008-01-20   2008 03 2008 03
monday    2008-01-21   2008 03 2008 04
tuesday   2008-01-22   2008 04 2008 04
wednesday 2008-01-23   2008 04 2008 04
thursday  2008-01-24   2008 04 2008 04
friday    2008-01-25   2008 04 2008 04
saturday  2008-01-26   2008 04 2008 04
sunday    2008-01-27   2008 04 2008 04
monday    2008-01-28   2008 04 2008 05
tuesday   2008-01-29   2008 05 2008 05
wednesday 2008-01-30   2008 05 2008 05
thursday  2008-01-31   2008 05 2008 05

42 rijen zijn geselecteerd.

So week 1, according to the 'ww' date format element, always runs from January 1 until January 7. And in 2004, a week ran from Thursday until Wednesday and in 2008 it runs from Tuesday until Monday.

As said, this was not what I expected. Also, in 2007, where January 1 was a Monday, the behaviour described above was hidden.

Yet, the documentation is very clear about this subject, as can be seen by these quotes:

The week numbers returned by the WW format mask are calculated according to the following algorithm: int(dayOfYear+6)/7. This algorithm does not follow the ISO standard (2015, 1992-06-15).

WW No Week of year (1-53) where week 1 starts on the first day of the year and continues to the seventh day of the year.

Which again shows that in our business one should never assume, but always read the documentation and verify by testing.

Sequence within parent

The sequence within parent column is the column in a detail table that, together with the foreign key column to the master table, constitutes a unique key of the detail table. And sometimes even the primary key if you are not into surrogate keys. A small example of a sequence within parent:

rwijk@ORA11G> create table orders
  2  ( id          number(10) not null primary key
  3  , customer_id number(10) not null
  4  , order_date  date       not null
  5  )
  6  /

Tabel is aangemaakt.

rwijk@ORA11G> create table order_lines
  2  ( order_id               number(10)   not null references orders(id)
  3  , sequence_within_parent number(6)    not null
  4  , product_id             number(10)   not null
  5  , quantity               number(6)    not null
  6  , price_per_piece        number(10,2) not null
  7  , primary key (order_id,sequence_within_parent)
  8  )
  9  /

Tabel is aangemaakt.

The purpose is to let the sequence within parent column start with the value 1 for each order_id and increase it by 1 for each subsequent order_line within the same order. In the days of Designer/2000 you could mark a column with a so-called "Autogen Type" as a sequence within parent, and the Oracle*Forms Generator of Designer would conveniently generate a Pre-Insert trigger at the block level containing a call to a function containing this piece of code:

DECLARE
   CURSOR C IS
     SELECT nvl(max(SEQUENCE_WITHIN_PARENT),0) + 1
     FROM   ORDER_LINES OLE
     WHERE  OLE.ORDER_ID = P_ORDER_ID;
 BEGIN
   OPEN  C;
   FETCH C INTO P_SEQUENCE_WITHIN_PARENT;
   CLOSE C;
 END;

But the problem with this code is that it won't function in a multi user environment, because if this code is executed in two different sessions for the same order_id, both sessions would get the same sequence_within_parent. One would succeed and the other would fail because of the unique index on (order_id,sequence_within_parent). Also, and probably more important, the sequence within parent makes it necessary to execute a select statement to be able to insert a new row. Which harms performance.

So why use the sequence within parent? Are you really going to query the third order_line for order_id 1? I doubt it. In my experience, the sequence within parent is often lightheartedly introduced by the designer "just to be able to have an easy unique identifier". The only case I can see where this column is useful, is when it is important to be able to quickly search for the n-th order_line, and when the total number of detail records within one master record can be a big number. And as said, this case is very rare.

Suppose the tables above are filled like this:

rwijk@ORA11G> insert into orders
  2  select 1, 1234, date '2008-01-01' from dual union all
  3  select 2, 5678, date '2008-01-20' from dual union all
  4  select 3, 1234, date '2008-02-01' from dual
  5  /

3 rijen zijn aangemaakt.

rwijk@ORA11G> insert into order_lines
  2  select 1, 1, 987, 10, 25 from dual union all
  3  select 1, 2, 876, 1, 500 from dual union all
  4  select 1, 3, 765, 50, 2 from dual union all
  5  select 2, 1, 654, 1, 750 from dual union all
  6  select 3, 1, 99, 1, 22.95 from dual union all
  7  select 3, 2, 98, 1, 67.99 from dual union all
  8  select 3, 3, 97, 5, 9.99 from dual union all
  9  select 3, 4, 96, 1, 19.99 from dual union all
 10  select 3, 5, 95, 10, 24.95 from dual
 11  /

9 rijen zijn aangemaakt.

An insert, placed in an api of course, would be something like this:

rwijk@ORA11G> insert into order_lines
  2  ( order_id
  3  , sequence_within_parent
  4  , product_id
  5  , quantity
  6  , price_per_piece
  7  )
  8  values
  9  ( 3
 10  , (select nvl(max(sequence_within_parent),0)+1 from order_lines where order_id=3)
 11  , 90
 12  , 1
 13  , 4.99
 14  )
 15  /

1 rij is aangemaakt.

rwijk@ORA11G> select *
  2    from order_lines
  3   where order_id = 3
  4  /

  ORDER_ID SEQUENCE_WITHIN_PARENT PRODUCT_ID   QUANTITY PRICE_PER_PIECE
---------- ---------------------- ---------- ---------- ---------------
         3                      1         99          1           22,95
         3                      2         98          1           67,99
         3                      3         97          5            9,99
         3                      4         96          1           19,99
         3                      5         95         10           24,95
         3                      6         90          1            4,99

6 rijen zijn geselecteerd.

The alternative is to use a sequence based id column and have a unique key - the unique identifier in ERD terms - on (order_id,product_id). Like this:

rwijk@ORA11G> drop table order_lines
  2  /

Tabel is verwijderd.

rwijk@ORA11G> create table order_lines
  2  ( id                     number(10)   not null primary key
  3  , order_id               number(10)   not null references orders(id)
  4  , product_id             number(10)   not null
  5  , quantity               number(6)    not null
  6  , price_per_piece        number(10,2) not null
  7  , unique (order_id,product_id)
  8  )
  9  /

Tabel is aangemaakt.

rwijk@ORA11G> insert into order_lines
  2  select 1, 1, 987, 10, 25 from dual union all
  3  select 2, 1, 876, 1, 500 from dual union all
  4  select 3, 1, 765, 50, 2 from dual union all
  5  select 4, 2, 654, 1, 750 from dual union all
  6  select 5, 3, 99, 1, 22.95 from dual union all
  7  select 6, 3, 98, 1, 67.99 from dual union all
  8  select 7, 3, 97, 5, 9.99 from dual union all
  9  select 8, 3, 96, 1, 19.99 from dual union all
 10  select 9, 3, 95, 10, 24.95 from dual
 11  /

9 rijen zijn aangemaakt.

rwijk@ORA11G> create sequence order_lines_seq start with 10 increment by 1
  2  /

Reeks is aangemaakt.

The insert now becomes much easier:

rwijk@ORA11G> insert into order_lines
  2  ( id
  3  , order_id
  4  , product_id
  5  , quantity
  6  , price_per_piece
  7  )
  8  values
  9  ( order_lines_seq.nextval
 10  , 3
 11  , 90
 12  , 1
 13  , 4.99
 14  )
 15  /

1 rij is aangemaakt.

It is simple, scalable and always works in a multi-user environment.
And in case the end user, REALLY likes to see a "sequence within parent" column in his gui, then you just define a view like this:

rwijk@ORA11G> create view v_order_lines
  2  as
  3  select order_id
  4       , row_number() over (partition by order_id order by id) sequence_within_parent
  5       , product_id
  6       , quantity
  7       , price_per_piece
  8    from order_lines
  9  /

View is aangemaakt.

rwijk@ORA11G> select *
  2    from v_order_lines
  3   where order_id = 3
  4  /

  ORDER_ID SEQUENCE_WITHIN_PARENT PRODUCT_ID   QUANTITY PRICE_PER_PIECE
---------- ---------------------- ---------- ---------- ---------------
         3                      1         99          1           22,95
         3                      2         98          1           67,99
         3                      3         97          5            9,99
         3                      4         96          1           19,99
         3                      5         95         10           24,95
         3                      6         90          1            4,99

6 rijen zijn geselecteerd.

Flexible attributes and performance

The client I currently work for has quite a few special tables containing flexible attributes. These tables have one foreign key to another table, the base table. The attributes in the table containing the flexibal attributes conceptually belong to the base table, but their attribute values can change over time - the table contains a startdate and an enddate. And it is important that one can see the value of those attributes at any given time.

Today I was working on optimizing a large query that involved (among many others...) a base table of 4M rows, and a flexible attributes table of 40M rows containing approximately 20 flexible attributes. The query needed the current value of 7 of those flexible attributes. I did some testing about what would be the best approach to query all those values. And because I think it is a case that is certainly not rare, it is now here on my blog.

Here is a test case to simulate the situation:

rwijk@ORA11G> create table base (id, description)
  2  as
  3   select level
  4        , 'description' || to_char(level)
  5     from dual
  6  connect by level <= 10000
  7  /

Tabel is aangemaakt.

rwijk@ORA11G> create table flexattributes_of_base
  2  (id,attribute,base_id,value,startdate,enddate)
  3  as
  4   select level
  5        , 'attribute' || to_char(mod(level-1,20))
  6        , ceil(level/40)
  7        , to_char(trunc(dbms_random.value(1,11)))
  8        , case trunc(mod(level-1,40)/20)
  9          when 0 then date '2007-01-01'
 10          else date '2008-01-01'
 11          end
 12        , case trunc(mod(level-1,40)/20)
 13          when 0 then date '2008-01-01'
 14          end
 15     from dual
 16  connect by level <= 400000
 17  /

Tabel is aangemaakt.

rwijk@ORA11G> remark
rwijk@ORA11G> remark  Insert attribute20 with only non-active values
rwijk@ORA11G> remark
rwijk@ORA11G> insert into flexattributes_of_base
  2   select 400000 + level
  3        , 'attribute20'
  4        , level
  5        , 'a'
  6        , date '2007-01-01'
  7        , date '2008-01-01'
  8     from dual
  9  connect by level <= 10000
 10  /

10000 rijen zijn aangemaakt.

rwijk@ORA11G> remark
rwijk@ORA11G> remark Insert attribute21 with more than one active value per base_id,
rwijk@ORA11G> remark which is garbage of course,
rwijk@ORA11G> remark
rwijk@ORA11G> insert into flexattributes_of_base
  2   select 410000 + level
  3        , 'attribute21'
  4        , mod(level-1,10000) + 1
  5        , 'b'
  6        , case
  7          when level <= 10000 then date '2007-01-01'
  8          else date '2008-01-01'
  9          end
 10        , null
 11     from dual
 12  connect by level <= 20000
 13  /

20000 rijen zijn aangemaakt.

rwijk@ORA11G> remark
rwijk@ORA11G> remark Insert attribute22 only for base_id 1
rwijk@ORA11G> remark
rwijk@ORA11G> insert into flexattributes_of_base
  2  values
  3  ( 430001
  4  , 'attribute22'
  5  , 1
  6  , 'c'
  7  , date '2007-01-01'
  8  , null
  9  )
 10  /

1 rij is aangemaakt.

rwijk@ORA11G> alter table base add constraint base_pk primary key (id)
  2  /

Tabel is gewijzigd.

rwijk@ORA11G> alter table flexattributes_of_base
  2    add constraint flex_pk primary key (id)
  3  /

Tabel is gewijzigd.

rwijk@ORA11G> alter table flexattributes_of_base
  2    add constraint flex_uk1 unique (attribute,base_id,startdate)
  3  /

Tabel is gewijzigd.

rwijk@ORA11G> exec dbms_stats.gather_table_stats(user,'base',cascade=>true)

PL/SQL-procedure is geslaagd.

rwijk@ORA11G> begin
  2    dbms_stats.gather_table_stats
  3    ( user
  4    , 'flexattributes_of_base'
  5    , cascade=>true
  6    , method_opt=>'FOR ALL INDEXED COLUMNS'
  7    );
  8  end;
  9  /

PL/SQL-procedure is geslaagd.

So in this testcase, the base table contains 10,000 rows, and the flex_attributes_of_base table contains 430,001 rows. There are 23 flexible attributes ('attribute0' until 'attribute22'). Every record in the base table has two records for each of the first 20 attributes (0-19). One for 2007-01-01 until 2008-01-01 and one for 2008-01-01 and upwards. The next three attributes are slightly different - you may even call it buggy - just to see if the queries output the right data in case:

- an attribute does not have a current value because earlier records all have an enddate (attribute20)

- an attribute has more than one current value, in which case the query should display the one with the most recent startdate (attribute21)

- an attribute that only base_id 1 has a value for (attribute22)

Let's issue a very simple but typical query against the two tables to retrieve the base record with id 1234 and its current value for attribute 11:

rwijk@ORA11G> select b.id
  2       , b.description
  3       , f11.value
  4    from base b
  5       , flexattributes_of_base f11
  6   where b.id = 1234
  7     and b.id = f11.base_id (+)
  8     and f11.attribute (+) = 'attribute11'
  9     and f11.enddate is null
 10     and not exists
 11         ( select 'more recent startdate'
 12             from flexattributes_of_base f11_2
 13            where f11_2.attribute = f11.attribute
 14              and f11_2.base_id = f11.base_id
 15              and f11_2.startdate > f11.startdate
 16         )
 17  /

      ID DESCRIPTION                                         VALUE
-------- --------------------------------------------------- -----
    1234 description1234                                     3

1 rij is geselecteerd.

Note that the exists-subquery is necessary in case of attribute21, when more than one row exists with an empty enddate.

And if you also want the current value of attribute22, you just repeat the same pattern:

rwijk@ORA11G> select b.id
  2       , b.description
  3       , f11.value
  4       , f22.value
  5    from base b
  6       , flexattributes_of_base f11
  7       , flexattributes_of_base f22
  8   where b.id = 1234
  9     and b.id = f11.base_id (+)
 10     and b.id = f22.base_id (+)
 11     and f11.attribute (+) = 'attribute11'
 12     and f22.attribute (+) = 'attribute22'
 13     and f11.enddate is null
 14     and f22.enddate is null
 15     and not exists
 16         ( select 'more recent startdate'
 17             from flexattributes_of_base f11_2
 18            where f11_2.attribute = f11.attribute
 19              and f11_2.base_id = f11.base_id
 20              and f11_2.startdate > f11.startdate
 21         )
 22     and not exists
 23         ( select 'more recent startdate'
 24             from flexattributes_of_base f22_2
 25            where f22_2.attribute = f22.attribute
 26              and f22_2.base_id = f22.base_id
 27              and f22_2.startdate > f22.startdate
 28         )
 29  /

      ID DESCRIPTION                                         VALUE VALUE
-------- --------------------------------------------------- ----- -----
    1234 description1234                                     3

1 rij is geselecteerd.

As said these queries are quite standard, so when they had to query the entire base table with 7 attributes (i'll use 5 below), they did it like this initially:

rwijk@ORA11G> select b.id
  2       , f19.value f19_value
  3       , f20.value f20_value
  4       , f21.value f21_value
  5       , f22.value f22_value
  6       , f23.value f23_value
  7    from base b
  8       , flexattributes_of_base f19
  9       , flexattributes_of_base f20
 10       , flexattributes_of_base f21
 11       , flexattributes_of_base f22
 12       , flexattributes_of_base f23
 13   where b.id = f19.base_id (+)
 14     and b.id = f20.base_id (+)
 15     and b.id = f21.base_id (+)
 16     and b.id = f22.base_id (+)
 17     and b.id = f23.base_id (+)
 18     and f19.attribute (+) = 'attribute19'
 19     and f20.attribute (+) = 'attribute20'
 20     and f21.attribute (+) = 'attribute21'
 21     and f22.attribute (+) = 'attribute22'
 22     and f23.attribute (+) = 'attribute23'
 23     and f19.enddate (+) is null
 24     and f20.enddate (+) is null
 25     and f21.enddate (+) is null
 26     and f22.enddate (+) is null
 27     and f23.enddate (+) is null
 28     and not exists
 29         ( select 'more recent startdate'
 30             from flexattributes_of_base f19_2
 31            where f19_2.attribute = f19.attribute
 32              and f19_2.base_id = f19.base_id
 33              and f19_2.startdate > f19.startdate
 34         )
 35     and not exists
 36         ( select 'more recent startdate'
 37             from flexattributes_of_base f20_2
 38            where f20_2.attribute = f20.attribute
 39              and f20_2.base_id = f20.base_id
 40              and f20_2.startdate > f20.startdate
 41         )
 42     and not exists
 43         ( select 'more recent startdate'
 44             from flexattributes_of_base f21_2
 45            where f21_2.attribute = f21.attribute
 46              and f21_2.base_id = f21.base_id
 47              and f21_2.startdate > f21.startdate
 48         )
 49     and not exists
 50         ( select 'more recent startdate'
 51             from flexattributes_of_base f22_2
 52            where f22_2.attribute = f22.attribute
 53              and f22_2.base_id = f22.base_id
 54              and f22_2.startdate > f22.startdate
 55         )
 56     and not exists
 57         ( select 'more recent startdate'
 58             from flexattributes_of_base f23_2
 59            where f23_2.attribute = f23.attribute
 60              and f23_2.base_id = f23.base_id
 61              and f23_2.startdate > f23.startdate
 62         )
 63   order by b.id
 64  /

But here the same table/index is accessed 10 times. One developer noticed that and wrote another query to address this problem. He was able to get rid of the exists-subquery using the row_number analytic function, like this:

rwijk@ORA11G> select b.id
  2       , f19.value f19_value
  3       , f20.value f20_value
  4       , f21.value f21_value
  5       , f22.value f22_value
  6       , f23.value f23_value
  7    from base b
  8       , ( select base_id
  9                , value
 10                , row_number() over (partition by base_id order by startdate desc) rn
 11             from flexattributes_of_base
 12            where attribute = 'attribute19'
 13              and enddate is null
 14         ) f19
 15       , ( select base_id
 16                , value
 17                , row_number() over (partition by base_id order by startdate desc) rn
 18             from flexattributes_of_base
 19            where attribute = 'attribute20'
 20              and enddate is null
 21         ) f20
 22       , ( select base_id
 23                , value
 24                , row_number() over (partition by base_id order by startdate desc) rn
 25             from flexattributes_of_base
 26            where attribute = 'attribute21'
 27              and enddate is null
 28         ) f21
 29       , ( select base_id
 30                , value
 31                , row_number() over (partition by base_id order by startdate desc) rn
 32             from flexattributes_of_base
 33            where attribute = 'attribute22'
 34              and enddate is null
 35         ) f22
 36       , ( select base_id
 37                , value
 38                , enddate
 39                , row_number() over (partition by base_id order by startdate desc) rn
 40             from flexattributes_of_base
 41            where attribute = 'attribute23'
 42         ) f23
 43   where b.id = f19.base_id (+)
 44     and b.id = f20.base_id (+)
 45     and b.id = f21.base_id (+)
 46     and b.id = f22.base_id (+)
 47     and b.id = f23.base_id (+)
 48     and (f19.rn = 1 or f19.rn is null)
 49     and (f20.rn = 1 or f20.rn is null)
 50     and (f21.rn = 1 or f21.rn is null)
 51     and (f22.rn = 1 or f22.rn is null)
 52     and (f23.rn = 1 or f23.rn is null)
 53   order by b.id
 54  /

This query is a bit better, but still accesses the table 5 times. The number of table scans was reduced to only one, by using a technique that resembles the well known pivot trick:

rwijk@ORA11G> select b.id
  2       , f.f19_value
  3       , f.f20_value
  4       , f.f21_value
  5       , f.f22_value
  6       , f.f23_value
  7    from base b
  8       , ( select base_id
  9                , max(decode(attribute,'attribute19',value))
 10                  keep
 11                  (dense_rank last
 12                   order by decode(attribute,'attribute19',startdate) nulls first
 13                  ) f19_value
 14                , max(decode(attribute,'attribute20',value))
 15                  keep
 16                  (dense_rank last
 17                   order by decode(attribute,'attribute20',startdate) nulls first
 18                  ) f20_value
 19                , max(decode(attribute,'attribute21',value))
 20                  keep
 21                  (dense_rank last
 22                   order by decode(attribute,'attribute21',startdate) nulls first
 23                  ) f21_value
 24                , max(decode(attribute,'attribute22',value))
 25                  keep
 26                  (dense_rank last
 27                   order by decode(attribute,'attribute22',startdate) nulls first
 28                  ) f22_value
 29                , max(decode(attribute,'attribute23',value))
 30                  keep
 31                  (dense_rank last
 32                   order by decode(attribute,'attribute23',startdate) nulls first
 33                  ) f23_value
 34             from flexattributes_of_base
 35            where attribute in ('attribute19','attribute20','attribute21','attribute22','attribute23')
 36              and enddate is null
 37            group by base_id
 38         ) f
 39   where b.id = f.base_id (+)
 40   order by b.id
 41  /

The tkprof output of the three queries:

Query1:

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      1.76       1.77          0          0          0           0
Execute      1      0.00       0.00          0          0          0           0
Fetch      668      1.85       5.95       9622      39430          0       10000
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total      670      3.62       7.72       9622      39430          0       10000

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

Rows     Row Source Operation
-------  ---------------------------------------------------
  10000  SORT ORDER BY (cr=39430 pr=9622 pw=9622 time=218 us cost=9881 size=2274972 card=7738)
  10000   HASH JOIN ANTI (cr=39430 pr=9622 pw=9622 time=217 us cost=9389 size=2274972 card=7738)
  20000    HASH JOIN RIGHT OUTER (cr=37401 pr=9622 pw=9622 time=1172 us cost=7990 size=2199150 card=8145)
  20000     TABLE ACCESS FULL FLEXATTRIBUTES_OF_BASE (cr=2392 pr=72 pw=72 time=361 us cost=669 size=35972 card=1058)
  10000     HASH JOIN ANTI (cr=35009 pr=9550 pw=9550 time=198 us cost=7320 size=1922220 card=8145)
  10000      HASH JOIN RIGHT OUTER (cr=32980 pr=9550 pw=9550 time=870 us cost=5939 size=1817688 card=8574)
  10000       TABLE ACCESS FULL FLEXATTRIBUTES_OF_BASE (cr=2389 pr=2386 pw=2386 time=669 us cost=669 size=34374 card=1011)
  10000       HASH JOIN ANTI (cr=30591 pr=7164 pw=7164 time=189 us cost=5270 size=1526172 card=8574)
  10000        HASH JOIN RIGHT OUTER (cr=28562 pr=7164 pw=7164 time=830 us cost=3909 size=1389850 card=9025)
      0         TABLE ACCESS FULL FLEXATTRIBUTES_OF_BASE (cr=2389 pr=2386 pw=2386 time=0 us cost=669 size=18054 card=531)
  10000         HASH JOIN ANTI (cr=26173 pr=4778 pw=4778 time=180 us cost=3239 size=1083000 card=9025)
  10000          HASH JOIN RIGHT OUTER (cr=24144 pr=4777 pw=4777 time=830 us cost=1900 size=912000 card=9500)
      0           TABLE ACCESS FULL FLEXATTRIBUTES_OF_BASE (cr=2389 pr=2386 pw=2386 time=0 us cost=669 size=9010 card=265)
  10000           HASH JOIN ANTI (cr=21755 pr=2391 pw=2391 time=175 us cost=1231 size=589000 card=9500)
  10000            HASH JOIN RIGHT OUTER (cr=2414 pr=2386 pw=2386 time=979 us cost=676 size=380000 card=10000)
      1             TABLE ACCESS FULL FLEXATTRIBUTES_OF_BASE (cr=2389 pr=2386 pw=2386 time=0 us cost=669 size=9010 card=265)
  10000             INDEX FAST FULL SCAN BASE_PK (cr=25 pr=0 pw=0 time=199 us cost=7 size=40000 card=10000)(object id 72460)
 430001            INDEX FAST FULL SCAN FLEX_UK1 (cr=19341 pr=5 pw=5 time=7911 us cost=551 size=10320024 card=430001)(object id 72462)
 430001          INDEX FAST FULL SCAN FLEX_UK1 (cr=2029 pr=1 pw=1 time=7335 us cost=551 size=10320024 card=430001)(object id 72462)
 430001        INDEX FAST FULL SCAN FLEX_UK1 (cr=2029 pr=0 pw=0 time=7321 us cost=551 size=10320024 card=430001)(object id 72462)
 430001      INDEX FAST FULL SCAN FLEX_UK1 (cr=2029 pr=0 pw=0 time=7338 us cost=551 size=10320024 card=430001)(object id 72462)
 430001    INDEX FAST FULL SCAN FLEX_UK1 (cr=2029 pr=0 pw=0 time=7420 us cost=551 size=10320024 card=430001)(object id 72462)

Query2:

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.32       0.34          0          0          0           0
Execute      1      0.01       0.00          0          0          0           0
Fetch      668      2.15       2.21          2     105894          0       10000
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total      670      2.50       2.55          2     105894          0       10000

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

Rows     Row Source Operation
-------  ---------------------------------------------------
  10000  SORT ORDER BY (cr=105894 pr=2 pw=2 time=200 us cost=1588 size=211 card=1)
  10000   FILTER  (cr=105894 pr=2 pw=2 time=19897 us)
  20000    NESTED LOOPS OUTER (cr=105894 pr=2 pw=2 time=34524 us cost=1587 size=211 card=1)
  10000     FILTER  (cr=65792 pr=2 pw=2 time=12472 us)
  10000      NESTED LOOPS OUTER (cr=65792 pr=2 pw=2 time=11293 us cost=1582 size=172 card=1)
  10000       FILTER  (cr=34710 pr=2 pw=2 time=5387 us)
  10000        NESTED LOOPS OUTER (cr=34710 pr=2 pw=2 time=4959 us cost=1572 size=266 card=2)
  10000         FILTER  (cr=4809 pr=2 pw=2 time=570 us)
  10000          HASH JOIN OUTER (cr=4809 pr=2 pw=2 time=210 us cost=1397 size=3290 card=35)
  10000           FILTER  (cr=2417 pr=2 pw=2 time=1142 us)
  10000            HASH JOIN RIGHT OUTER (cr=2417 pr=2 pw=2 time=821 us cost=677 size=32725 card=595)
      1             VIEW  (cr=2392 pr=2 pw=2 time=0 us cost=670 size=10335 card=265)
      1              WINDOW SORT (cr=2392 pr=2 pw=2 time=0 us cost=670 size=9010 card=265)
      1               TABLE ACCESS FULL FLEXATTRIBUTES_OF_BASE (cr=2392 pr=2 pw=2 time=0 us cost=669 size=9010 card=265)
  10000             INDEX FAST FULL SCAN BASE_PK (cr=25 pr=0 pw=0 time=179 us cost=7 size=160000 card=10000)(object id 72460)
      0           VIEW  (cr=2392 pr=0 pw=0 time=0 us cost=719 size=207012 card=5308)
      0            WINDOW SORT (cr=2392 pr=0 pw=0 time=0 us cost=719 size=180472 card=5308)
      0             TABLE ACCESS FULL FLEXATTRIBUTES_OF_BASE (cr=2392 pr=0 pw=0 time=0 us cost=667 size=180472 card=5308)
      0         VIEW PUSHED PREDICATE  (cr=29901 pr=0 pw=0 time=0 us cost=5 size=39 card=1)
      0          WINDOW SORT (cr=29901 pr=0 pw=0 time=0 us cost=5 size=34 card=1)
      0           TABLE ACCESS BY INDEX ROWID FLEXATTRIBUTES_OF_BASE (cr=29901 pr=0 pw=0 time=0 us cost=4 size=34 card=1)
  10000            INDEX RANGE SCAN FLEX_UK1 (cr=20053 pr=0 pw=0 time=0 us cost=3 size=0 card=1)(object id 72462)
  10000       VIEW PUSHED PREDICATE  (cr=31082 pr=0 pw=0 time=0 us cost=5 size=39 card=1)
  10000        WINDOW SORT (cr=31082 pr=0 pw=0 time=0 us cost=5 size=34 card=1)
  10000         TABLE ACCESS BY INDEX ROWID FLEXATTRIBUTES_OF_BASE (cr=31082 pr=0 pw=0 time=0 us cost=4 size=34 card=1)
  20000          INDEX RANGE SCAN FLEX_UK1 (cr=20107 pr=0 pw=0 time=25499 us cost=3 size=0 card=2)(object id 72462)
  20000     VIEW PUSHED PREDICATE  (cr=40102 pr=0 pw=0 time=75748 us cost=5 size=39 card=1)
  20000      WINDOW SORT (cr=40102 pr=0 pw=0 time=34334 us cost=5 size=34 card=1)
  20000       TABLE ACCESS BY INDEX ROWID FLEXATTRIBUTES_OF_BASE (cr=40102 pr=0 pw=0 time=93885 us cost=4 size=34 card=1)
  20000        INDEX RANGE SCAN FLEX_UK1 (cr=20103 pr=0 pw=0 time=26785 us cost=3 size=0 card=2)(object id 72462)

Query 3:

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.01       0.00          0          0          0           0
Execute      1      0.00       0.00          0          0          0           0
Fetch      668      0.42       0.41         41       3076          0       10000
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total      670      0.43       0.41         41       3076          0       10000

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

Rows     Row Source Operation
-------  ---------------------------------------------------
  10000  MERGE JOIN OUTER (cr=3076 pr=41 pw=41 time=1922 us cost=763 size=1180000 card=10000)
  10000   INDEX FULL SCAN BASE_PK (cr=684 pr=0 pw=0 time=222 us cost=21 size=40000 card=10000)(object id 72460)
  10000   SORT JOIN (cr=2392 pr=41 pw=41 time=0 us cost=742 size=307344 card=2696)
  10000    VIEW  (cr=2392 pr=41 pw=41 time=553 us cost=670 size=307344 card=2696)
  10000     SORT GROUP BY (cr=2392 pr=41 pw=41 time=225 us cost=670 size=91664 card=2696)
  30001      TABLE ACCESS FULL FLEXATTRIBUTES_OF_BASE (cr=2392 pr=41 pw=41 time=1291 us cost=669 size=106420 card=3130)

The query runtime was drastically reduced by this trick. Not exactly rocket science, but very effective.

8 things about me

This tagging thing has reached me as well. I saw it coming to me via Eddie Awad, who tagged Laurent Schneider, who tagged Chen Shapira, who tagged me. So now it's my turn. Although my initial intention with this blog was that it would only be "About Oracle", I'll make an exception now.

So here are 8 random things about me:

1) I was born in december 1972 and have one younger sister.

2) My acting career started and ended with a performance at elementary school where I played a tree.

3) I love listening to music, especially from bands like Sigur Ros, Pink Floyd, Mogwai, Radiohead, Pixies, Godspeed You! Black Emperor, Dead Can Dance, Joy Division and many more.

4) It keeps surprising me that advertising pays off. Personally I tend to stop buying products after seeing some annoying ad. And why spamming apparently works is really beyond me.

5) I love to watch the Tour de France, the biggest road cycling event, despite all the fuzz about dope these days. I organize a little betting pool with family, friends and colleagues. The calculation and administration of this is, of course, done with Oracle and a handful of SQL*Plus scripts. Each year I promise myself to make it easier for the participants by setting up a little ApEx site, but each year I start too late and end up unfinished.

6) I have played chess from the age of 19 up until 3 years ago, and still do occasionally on the internet. I like to play gambits, especially the Budapester and King's Gambit. It led me to one local championship of Gouda, but only because the strongest player failed to play enough games that year.

7) I've been to the USA in 1998, where I drove around in a rented car through Florida for one week. Ten years later I hope to visit the USA again for Oracle Open World in San Francisco.

8) I'm married and have two sons of 2 years old and almost 1 year old.

And now it's my turn to tag (in random order):
Tonguc Yilmaz,
Volder,
Ronald Rood,
Nicolas Gasparotto,
cd and
Adrian Billington.

One oddity explained

A few weeks ago I wrote about four oddities I encountered when doing a little research into several 11g features in this blog post. And this morning I read a very thorough article about the result cache here on oracle-developer.net, which addresses the first oddity by this sentence:

The cache is allocated directly from the shared pool but is maintained separately.

Although Adrian does not state how he came to know this, it sounds as a simple but plausible explanation of why the Concepts Manual shows the result cache as an area separate from the shared pool and why the dbms_result_cache.memory_report tells us it is memory from the shared pool.

At the end of the article, three links to Pythian blog posts are shown. In short, they are a warning that the result cache may not be as scalable as one might hope. Because I enjoyed reading those posts as well, here are the links: post 1, post 2 and post 3.