Author: Oren Nakdimon

ANY_VALUE

A new aggregate function – ANY_VALUE – was added to Oracle. It is documented as of Oracle 21c, but apparently it exists also in 19c (at least in 19.8 – the version in which I tested it).

Many times when writing an aggregate query we add expressions to the GROUP BY clause just because we want to add them to the select list, although they don’t change the aggregation result.

For example, let’s count the number of cities per country_id:

select c2.country_id,
       count(*) number_of_cities
from   cities    c1,
       countries c2
where  c2.country_id = c1.country_id
group  by c2.country_id;

Now, what if we want to return also the country name?

country_id is the primary key of the countries table, so there is no reason to change the aggregation key. But in an aggregate query the select list can include only aggregate functions, GROUP BY expressions, constants, or expressions involving one of these.

Option 1

So a common practice is to add country_name to the GROUP BY clause:

select c2.country_id,
       c2.country_name,
       count(*) number_of_cities
from   cities    c1,
       countries c2
where  c2.country_id = c1.country_id
group  by c2.country_id,
          c2.country_name;

Adding country_name to the GROUP BY clause is artificial, makes the query less clean, and the SQL engine may need to work harder as the aggregation key is wider.

Option 2

An alternative approach is to choose some simple aggregate function that won’t change the desired result, like MIN or MAX (but not SUM or COUNT…), and apply it on country_name in the select list:

select c2.country_id,
       min(c2.country_name) country_name,
       count(*) number_of_cities
from   cities    c1,
       countries c2
where  c2.country_id = c1.country_id
group  by c2.country_id;

Since all the records in the same group belong to the same country_id, then country_name is the same for all of them, so MIN(country_name)=MAX(country_name)=the right value.
In this option we don’t litter the GROUP BY clause, but we add an arbitrary aggregate function to the select list, which still feels artificial, and adds some (small) extra work for the SQL engine.

Option 3 – ANY_VALUE

Now we can use ANY_VALUE instead. ANY_VALUE(country_name) returns, for each group, the value of country_name from one of the records in that group.

select c2.country_id,
       any_value(c2.country_name) country_name,
       count(*) number_of_cities
from   cities    c1,
       countries c2
where  c2.country_id = c1.country_id
group  by c2.country_id;

Since ANY_VALUE is considered an aggregate function, we don’t need to add it to the GROUP BY clause. But, by definition, it’s a very simple function, so the performance overhead should be negligible with respect to other aggregate functions.

Using ANY_VALUE also makes the query more readable, in my opinion. It makes our intention clear (unlike the use of the MIN function in the previous example).

Option 4

There is another alternative to solve our example:

select c2.country_id,
       c2.country_name,
       c1.number_of_cities
from   (select country_id,
               count(*) number_of_cities
        from   cities
        group  by country_id) c1,
       countries c2
where  c2.country_id = c1.country_id;

Here we “aggregate and then join”, rather than “join and then aggregate”.

Use ANY_VALUE Wisely

I think that ANY_VALUE is a great addition to Oracle SQL. But use it wisely. Remember that by definition it is not deterministic. If you know that some_expression is unique for all the records in the same group in your query, then any_value(some_expression) is deterministic in your query and can be used safely. Otherwise, think very carefully if it’s appropriate for your case.

PL/SQL Associative Array Constants

My previous post was about declaring a PL/SQL constant that its type is a PL/SQL record. Today’s post is about declaring a constant that its type is an associative array.

Setup

I’ll extend the example I used in the previous post.
color_t is a record type that represents an RGB color value using 3 bytes (R, G and B).
The get_hex_code function gets a color_t parameter and returns the color’s hexadecimal format.
Here is the package spec:

SQL> create or replace package colors as
  2      subtype byte_t is binary_integer range 0 .. 255 not null;
  3      type color_t is record(
  4          r byte_t default 0,
  5          g byte_t default 0,
  6          b byte_t default 0);
  7      function get_hex_code(i_color in color_t) return varchar2;
  8      procedure print;
  9  end colors;
 10  /

Package created.

I’d like to declare an associative array constant for holding several colors (and then print their hex codes).
The key of the array is the color name and the value is a color_t representing the color RGB triplet.

Pre-18c

In Oracle 12.2 and before we need to write a function and use it for initializing the constant, like init_c_colors in the following example:

SQL> create or replace package body colors as
  2      subtype color_name_t is varchar2(20);
  3      type color_tt is table of color_t index by color_name_t;
  4
  5      function init_c_colors return color_tt;
  6      c_colors constant color_tt := init_c_colors();
  7
  8      function construct_color
  9      (
 10          r in binary_integer default null,
 11          g in binary_integer default null,
 12          b in binary_integer default null
 13      ) return color_t is
 14          v_color color_t;
 15      begin
 16          v_color.r := nvl(construct_color.r, v_color.r);
 17          v_color.g := nvl(construct_color.g, v_color.g);
 18          v_color.b := nvl(construct_color.b, v_color.b);
 19          return v_color;
 20      end construct_color;
 21
 22      function init_c_colors return color_tt is
 23          v_colors color_tt;
 24      begin
 25          v_colors('black') := construct_color(0, 0, 0);
 26          v_colors('white') := construct_color(255, 255, 255);
 27          v_colors('pink') := construct_color(255, 192, 203);
 28          v_colors('yellow') := construct_color(r => 255, g => 255);
 29          v_colors('navy') := construct_color(b => 128);
 30          return v_colors;
 31      end init_c_colors;
 32
 33      function get_hex_code(i_color in color_t) return varchar2 is
 34      begin
 35          return '#' || to_char(i_color.r, 'fm0x') ||
 36                        to_char(i_color.g, 'fm0x') ||
 37                        to_char(i_color.b, 'fm0x');
 38      end get_hex_code;
 39
 40      procedure print is
 41          v_color color_name_t;
 42      begin
 43          v_color := c_colors.first;
 44          while v_color is not null
 45          loop
 46              dbms_output.put_line(rpad(v_color || ':', 8) ||
 47                                   get_hex_code(c_colors(v_color)));
 48              v_color := c_colors.next(v_color);
 49          end loop;
 50      end print;
 51  end colors;
 52  /

Package body created.

SQL> exec colors.print
black:  #000000
navy:   #000080
pink:   #ffc0cb
white:  #ffffff
yellow: #ffff00

PL/SQL procedure successfully completed.

Oracle 18c and Later

According to the Database PL/SQL Language Reference documentation (including the documentation for 18c, 19c and 20c):

When declaring an associative array constant, you must create a function that populates the associative array with its initial value and then invoke the function in the constant declaration.

And this is indeed what we did in the previous example.
But the documentation is outdated. As of Oracle 18c each associative array has a default constructor (the formal name is Qualified Expressions). So we don’t have to write our own constructor function anymore:

Connected to:
Oracle Database 18c Enterprise Edition Release 18.0.0.0.0 - Production
Version 18.3.0.0.0

SQL> create or replace package colors as
  2      subtype byte_t is binary_integer range 0 .. 255 not null;
  3      type color_t is record(
  4          r byte_t default 0,
  5          g byte_t default 0,
  6          b byte_t default 0);
  7
  8      function get_hex_code(i_color in color_t) return varchar2;
  9      procedure print;
 10  end colors;
 11  /

Package created.

SQL>
SQL> create or replace package body colors as
  2      subtype color_name_t is varchar2(20);
  3      type color_tt is table of color_t index by color_name_t;
  4
  5      c_colors constant color_tt :=
  6                  color_tt('black'  => color_t(0, 0, 0),
  7                           'white'  => color_t(255, 255, 255),
  8                           'pink'   => color_t(255, 192, 203),
  9                           'yellow' => color_t(r => 255, g => 255),
 10                           'navy'   => color_t(b => 128));
 11
 12      function get_hex_code(i_color in color_t) return varchar2 is
 13      begin
 14          return '#' || to_char(i_color.r, 'fm0x') ||
 15                        to_char(i_color.g, 'fm0x') ||
 16                        to_char(i_color.b, 'fm0x');
 17      end get_hex_code;
 18
 19      procedure print is
 20          v_color color_name_t;
 21      begin
 22          v_color := c_colors.first;
 23          while v_color is not null
 24          loop
 25              dbms_output.put_line(rpad(v_color || ':', 8) ||
 26                                   get_hex_code(c_colors(v_color)));
 27              v_color := c_colors.next(v_color);
 28          end loop;
 29      end print;
 30  end colors;
 31  /

Package body created.

SQL> exec colors.print
black:  #000000
navy:   #000080
pink:   #ffc0cb
white:  #ffffff
yellow: #ffff00

PL/SQL procedure successfully completed.

PL/SQL Record Constants

Can we declare a PL/SQL constant that its type is a PL/SQL record?
Since a constant must get its value upon declaration, we have to construct a value of the appropriate type.

Example Setup

Consider the following example.
color_t is a record type that represents an RGB color value using 3 bytes (R, G and B).
The get_hex_code function gets a color_t parameter and returns the color’s hexadecimal format.
I’d like to declare constants for several colors (and then print their hex codes). What should come instead of the question marks?

create or replace package colors as
    subtype byte_t is binary_integer range 0 .. 255 not null;
    type color_t is record(
        r byte_t default 0,
        g byte_t default 0,
        b byte_t default 0);
    function get_hex_code(i_color in color_t) return varchar2;
    procedure print;
end colors;
/

create or replace package body colors as
    c_black  constant color_t := ?;
    c_white  constant color_t := ?;
    c_pink   constant color_t := ?;
    c_yellow constant color_t := ?;
    c_navy   constant color_t := ?;

    function get_hex_code(i_color in color_t) return varchar2 is
    begin
        return '#' || to_char(i_color.r, 'fm0x') || 
                      to_char(i_color.g, 'fm0x') || 
                      to_char(i_color.b, 'fm0x');
    end get_hex_code;

    procedure print is
    begin
        dbms_output.put_line('Black:  ' || get_hex_code(c_black));
        dbms_output.put_line('White:  ' || get_hex_code(c_white));
        dbms_output.put_line('Pink:   ' || get_hex_code(c_pink));
        dbms_output.put_line('Yellow: ' || get_hex_code(c_yellow));
        dbms_output.put_line('Navy:   ' || get_hex_code(c_navy));
    end print;
end colors;
/

Pre-18c

In Oracle 12.2 and before we need to write a function and use it for initializing the constants. Something like construct_color in the following example:

SQL> create or replace package colors as
  2      subtype byte_t is binary_integer range 0 .. 255 not null;
  3      type color_t is record(
  4          r byte_t default 0,
  5          g byte_t default 0,
  6          b byte_t default 0);
  7
  8      -- null parameters mean defaults from color_t
  9      function construct_color
 10      (
 11          r in binary_integer default null,
 12          g in binary_integer default null,
 13          b in binary_integer default null
 14      ) return color_t;
 15
 16      function get_hex_code(i_color in color_t) return varchar2;
 17      procedure print;
 18  end colors;
 19  /

Package created.

SQL>
SQL> create or replace package body colors as
  2      c_black  constant color_t := construct_color(0, 0, 0);
  3      c_white  constant color_t := construct_color(255, 255, 255);
  4      c_pink   constant color_t := construct_color(255, 192, 203);
  5      c_yellow constant color_t := construct_color(r => 255, g => 255);
  6      c_navy   constant color_t := construct_color(b => 128);
  7
  8      function construct_color
  9      (
 10          r in binary_integer default null,
 11          g in binary_integer default null,
 12          b in binary_integer default null
 13      ) return color_t is
 14          v_color color_t;
 15      begin
 16          v_color.r := nvl(construct_color.r, v_color.r);
 17          v_color.g := nvl(construct_color.g, v_color.g);
 18          v_color.b := nvl(construct_color.b, v_color.b);
 19          return v_color;
 20      end construct_color;
 21
 22      function get_hex_code(i_color in color_t) return varchar2 is
 23      begin
 24          return '#' || to_char(i_color.r, 'fm0x') || to_char(i_color.g, 'fm0x') || to_char(i_color.b, 'fm0x');
 25      end get_hex_code;
 26
 27      procedure print is
 28      begin
 29          dbms_output.put_line('Black:  ' || get_hex_code(c_black));
 30          dbms_output.put_line('White:  ' || get_hex_code(c_white));
 31          dbms_output.put_line('Pink:   ' || get_hex_code(c_pink));
 32          dbms_output.put_line('Yellow: ' || get_hex_code(c_yellow));
 33          dbms_output.put_line('Navy:   ' || get_hex_code(c_navy));
 34      end print;
 35  end colors;
 36  /

Package body created.

SQL>
SQL> exec colors.print
Black:  #000000
White:  #ffffff
Pink:   #ffc0cb
Yellow: #ffff00
Navy:   #000080

PL/SQL procedure successfully completed.

Oracle 18c and Later

According to the Database PL/SQL Language Reference documentation (including the documentation for 18c, 19c and 20c):

When declaring a record constant, you must create a function that populates the record with its initial value and then invoke the function in the constant declaration.

And this is indeed what we did in the previous example.
But the documentation is outdated. As of Oracle 18c each PL/SQL record type has a default constructor (the formal name is Qualified Expressions). So we don’t have to write our own constructor function anymore:

Connected to:
Oracle Database 18c Enterprise Edition Release 18.0.0.0.0 - Production
Version 18.3.0.0.0

SQL> create or replace package colors as
  2      subtype byte_t is binary_integer range 0 .. 255 not null;
  3      type color_t is record(
  4          r byte_t default 0,
  5          g byte_t default 0,
  6          b byte_t default 0);
  7      function get_hex_code(i_color in color_t) return varchar2;
  8      procedure print;
  9  end colors;
 10  /

Package created.

SQL>
SQL> create or replace package body colors as
  2      c_black  constant color_t := color_t(0, 0, 0);
  3      c_white  constant color_t := color_t(255, 255, 255);
  4      c_pink   constant color_t := color_t(r => 255, g => 192, b => 203);
  5      c_yellow constant color_t := color_t(r => 255, g => 255);
  6      c_navy   constant color_t := color_t(b => 128);
  7
  8      function get_hex_code(i_color in color_t) return varchar2 is
  9      begin
 10          return '#' || to_char(i_color.r, 'fm0x') || to_char(i_color.g, 'fm0x') || to_char(i_color.b, 'fm0x');
 11      end get_hex_code;
 12
 13      procedure print is
 14      begin
 15          dbms_output.put_line('Black:  ' || get_hex_code(c_black));
 16          dbms_output.put_line('White:  ' || get_hex_code(c_white));
 17          dbms_output.put_line('Pink:   ' || get_hex_code(c_pink));
 18          dbms_output.put_line('Yellow: ' || get_hex_code(c_yellow));
 19          dbms_output.put_line('Navy:   ' || get_hex_code(c_navy));
 20      end print;
 21  end colors;
 22  /

Package body created.

SQL>
SQL> exec colors.print
Black:  #000000
White:  #ffffff
Pink:   #ffc0cb
Yellow: #ffff00
Navy:   #000080

PL/SQL procedure successfully completed.

Note that we can use either positional association (the first value is associated with r, the second with g, the third with b)

  2      c_black  constant color_t := color_t(0, 0, 0);
  3      c_white  constant color_t := color_t(255, 255, 255);

or named association

  4      c_pink   constant color_t := color_t(r => 255, g => 192, b => 203);

And since I declared the fields of color_t with 0 as the default value, I can omit some of the associations and the default value will be used for the construction:

  5      c_yellow constant color_t := color_t(r => 255, g => 255);
  6      c_navy   constant color_t := color_t(b => 128);

Yellow: #ffff00
Navy:   #000080

Footnote

As part of the introduction of Qualified Expressions in 18c, a similar enhancement was added to PL/SQL associative arrays. I’ll show this in the next post.

SORT GROUP BY NOSORT ROLLUP

In the previous post we saw that the Oracle Optimizer has a special operation – SORT GROUP BY ROLLUP – for performing several aggregations of the same population in a single scan, as long as the grouping keys are in a “rollup form”; for example:

(a)
(a,b,c)
(a,b,c,d)

If there is an index that its leading part is the same as the rollup grouping key, and that at least one of its columns is defined as NOT NULL, and if the optimizer thinks that cost-wise it is worth it, then the sorting part of the operation is omitted and the operation becomes SORT GROUP BY NOSORT ROLLUP.

Here is an example (executed in Oracle 18.3).

SQL> create table t (
  2    x number not null,
  3    y number,
  4    z number,
  5    w char(1000)
  6  );

Table created.

SQL> insert into t (x,y,z,w)
  2  select mod(rownum, 3),
  3         mod(rownum, 3)+1,
  4         mod(rownum, 3)+2,
  5         rownum
  6  from dual
  7  connect by level <= 1000
  8  order by 1,2,3;

1000 rows created.

SQL> commit;

Commit complete.

SQL> exec dbms_stats.gather_table_stats(user,'T')

PL/SQL procedure successfully completed.

SQL> create index i on t (x,y,z);

Index created.

SQL> set timing on
SQL> set autotrace on
SQL> select grouping_id(x,y) grp_id,
  2         x,
  3         y,
  4         count(z)
  5  from   t
  6  group  by grouping sets ((x),(x,y));

    GRP_ID          X          Y   COUNT(Z)
---------- ---------- ---------- ----------
         0          0          1        333
         1          0                   333
         0          1          2        334
         1          1                   334
         0          2          3        333
         1          2                   333

6 rows selected.

Elapsed: 00:00:00.05

Execution Plan
----------------------------------------------------------
Plan hash value: 3362344319

------------------------------------------------------------------------------------
| Id  | Operation                   | Name | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |      |     3 |    27 |     4   (0)| 00:00:01 |
|   1 |  SORT GROUP BY NOSORT ROLLUP|      |     3 |    27 |     4   (0)| 00:00:01 |
|   2 |   INDEX FULL SCAN           | I    |  1000 |  9000 |     4   (0)| 00:00:01 |
------------------------------------------------------------------------------------


Statistics
----------------------------------------------------------
          1  recursive calls
          0  db block gets
          5  consistent gets
          4  physical reads
          0  redo size
        889  bytes sent via SQL*Net to client
        624  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          6  rows processed

Not all GROUPING SETS are created equal

Introduction

I’ve just realized, once again, that the Oracle Optimizer is even smarter than I thought.
The comments (by Iudith Mentzel and Thomas Mautsch) to my previous post, GROUPING SETS and COLLECT don’t get along, made me understand that not all GROUPING SETS were created equal.

The examples in this post are from Oracle 18.3

Extended Aggregation Options

GROUPING SETS, ROLLUP and CUBE are great features that enable us to perform several aggregations on the same population in a single statement, that is shorter, more elegant, and usually more efficient, than the alternative of combining (e.g., using UNION ALL) the results of the individual aggregations.

In my opinion, GROUPING SETS, ROLLUP and CUBE should be in the toolbox of every Oracle developer. If you are not familiar with them, I highly recommend reading Tim Hall‘s article https://oracle-base.com/articles/misc/rollup-cube-grouping-functions-and-grouping-sets.

Under the Hood

We can use GROUPING SETS with many different combinations, but the implementation under the hood may be different for different combinations.
Let’s take, for example, the following two queries. Note that the first query has to perform 4 aggregations (some of them with a composite grouping key), while the second query performs only 2 aggregations (with a single column grouping key).
Try to guess which of the two will run faster… Continue reading “Not all GROUPING SETS are created equal”

GROUPING SETS and COLLECT Don’t Get Along

I’ve recently got reminded that assuming something will work, just because it makes sense, doesn’t mean it will really work.

While reviewing some code a few days ago, I saw a query of the following form:

select 'X='||x, collect(z)
from t
group by x
union all
select 'Y='||y, collect(z)
from t
group by y;

I immediately recommended to convert it to use GROUPING SETS; like this:

select decode(grouping(x), 0, 'X='||x, 'Y='||y),
       collect(z)
from t
group by grouping sets (x,y);

The code will be shorter, more elegant, and probably more efficient. Great, isn’t it?
The only problem is that it doesn’t work 🙁

Let’s create a demo table:

SQL> create table t (
  2    x number,
  3    y number,
  4    z number
  5  );

Table created.

SQL> insert into t (x,y,z)
  2  select mod(rownum, 2),
  3         mod(rownum, 3),
  4         rownum
  5  from dual
  6  connect by level <= 10;

10 rows created.

SQL> commit;

Commit complete.

SQL> exec dbms_stats.gather_table_stats(user,'T')

PL/SQL procedure successfully completed.

GROUPING SETS works nicely with most of the aggregate functions…

SQL> select decode(grouping(x), 0, 'X='||x, 'Y='||y) grp_by,
  2         count(*) "CNT",
  3         count(distinct z) "CNT D",
  4         sum(z) "SUM",
  5         avg(z) "AVG",
  6         stddev(z) "STDDEV",
  7         min(z) "MIN",
  8         max(z) "MAX"
  9  from t
 10  group by grouping sets (x,y)
 11  order by 1;

GRP_BY      CNT CNT D  SUM  AVG STDDEV  MIN  MAX
---------- ---- ----- ---- ---- ------ ---- ----
X=0           5     5   30    6   3.16    2   10
X=1           5     5   25    5   3.16    1    9
Y=0           3     3   18    6      3    3    9
Y=1           4     4   22  5.5   3.87    1   10
Y=2           3     3   15    5      3    2    8

5 rows selected.

… but not with the COLLECT aggregate function.
In Oracle 11.2 and 12.1 we get ORA-604 and ORA-907:

SQL> select decode(grouping(x), 0, 'X='||x, 'Y='||y),
  2         collect(z)
  3  from t
  4  group by grouping sets (x,y);
group by grouping sets (x,y)
             *
ERROR at line 4:
ORA-00604: error occurred at recursive SQL level 1
ORA-00907: missing right parenthesis

Looks like a bug.
But in Oracle 12.2 something has changed. No, the bug was not fixed. Instead, it is now officially not supported:

SQL> select decode(grouping(x), 0, 'X='||x, 'Y='||y),
  2         collect(z)
  3  from t
  4  group by grouping sets (x,y);
       collect(z)
       *
ERROR at line 2:
ORA-03001: unimplemented feature

-- tested in 12.2, 18.3 and 19.3

So, at least for now, the original query should remain as is:

SQL> select 'X='||x grp_by, collect(z) coll
  2  from t
  3  group by x
  4  union all
  5  select 'Y='||y, collect(z)
  6  from t
  7  group by y;

GRP_BY     COLL
---------- --------------------------------------------------
X=0        ST00001HGTfH6lTUWkKMCXAmZAQg=(2, 10, 8, 6, 4)
X=1        ST00001HGTfH6lTUWkKMCXAmZAQg=(1, 9, 7, 5, 3)
Y=0        ST00001HGTfH6lTUWkKMCXAmZAQg=(3, 9, 6)
Y=1        ST00001HGTfH6lTUWkKMCXAmZAQg=(1, 10, 7, 4)
Y=2        ST00001HGTfH6lTUWkKMCXAmZAQg=(2, 8, 5)

EXPAND_SQL_TEXT – Much More Than Just Expanding Views

Overview

There are features in Oracle SQL that are implemented by other, older, features. This is a clever way for supporting a new syntax with low efforts and low risk – the Oracle Corp engineers only need to convert the SQL statement with the new syntax to an equivalent statement that uses the old syntax they already support. And Oracle has a perfect place for doing this conversion – the expansion stage in the parsing process.

SQL Expansion

When a SQL statement is processed, it goes through several stages, in this order: parsing, optimization, row source generation, and execution.

Note: Parsing is a confusing term, as many times when we say “parsing” (especially “hard parsing”) we actually mean “parsing + optimization + row source generation”.

The first stage, the parsing, is not too complex (comparing to the optimization stage). Continue reading “EXPAND_SQL_TEXT – Much More Than Just Expanding Views”

EBR – Part 12: Editions and Services

This is part 12 of a post series about Oracle Edition-Based Redefinition.

Visit the index page for all the parts of the series

Introduction

In the previous part of this series I wrote about using the Database Default Edition as a way for exposing new editions when using EBR for online application upgrades. As I wrote there, this is a simple method, but in my opinion it is also an impractical method in most cases. From my experience, the best way to expose new editions is by using services. This gives us high levels of both flexibility and control.

When we create or modify an Oracle service, we can specify the session edition for subsequent database connections using this service.
If our new edition includes changes that require code changes in the client-side (for example, when we make API changes), then we’ll expose the new edition by using a new service.
If the new edition includes only changes that are transparent to the client-side (for example, changes in package bodies only), then we don’t have to create a new service. Instead, we can modify the latest service to use the new edition from now on.

Examples

Let’s see some examples. Continue reading “EBR – Part 12: Editions and Services”

EBR – Part 11: Database-Level Default Edition

This is part 11 of a post series about Oracle Edition-Based Redefinition.

Visit the index page for all the parts of the series

Introduction

As we’ve seen in the previous posts, the process of an online application upgrade, in high level, includes the following steps:

  • Creating a new edition
  • Applying all the necessary changes in the privacy of the new, unexposed, edition
  • Making sure that all the objects in the new edition are valid, and that all the objects that should be actual in the new edition are indeed actual
  • Exposing the new edition to the clients

How do we expose the new edition?

In the previous examples I did it by explicitly changing the client’s session edition, from the client session itself. But that was just for demo purposes, and it is certainly not the recommended way.
The best way to expose new editions, in my opinion, is by using services. This gives us high levels of both flexibility and control.
I’ll dedicate the next post for exposing new editions via services.
In this post I’ll show another option – Database Default Edition. This method is very simple, but also very limiting, and therefore it’s suitable only for very specific cases.

Database Default Edition

There is always one database-level default edition. We can see it using this query: Continue reading “EBR – Part 11: Database-Level Default Edition”

ENABLE NOVALIDATE – Too Polite?

Onine DDL operations are much more polite than offline DDL operations. They usually wait patiently for transactions that hold resources they need until these transactions end, and they do not block new DML statements.

As I wrote in the past, adding a constraint as Enabled and Validated (which is the default for new constrtaints) is an offline operation, but if we split it into two DDL statements – one for adding the constraint as Enabled and Not Validated and the second for making the constraint Validated – then each of these two separate statements is an online operation.

In this post I’d like to show that the first step – creating the constraint as Enabled and Not Validated – is even “more online” than it seems.

Let’s create some table t and insert one record into it:

One> create table t (
  2    a number,
  3    b number
  4  );

Table created.

One> insert into t(a,b) values (111,-1);

1 row created.

I did not commit or rollback this transaction, so it is still open and it’s locking the table in RX mode.

Now, from another session (note the SQL Prompts “One” and “Two”), I’ll add an Enabled and Not Validated check constraint to the table:

Two> alter table t
  2    add constraint t_b_chk
  3    check (b>0) enable novalidate;

Session Two is blocked now by session One (the wait event is “enq: TX – row lock contention”). Since it is an online operation it just waits, without throwing an ORA-54 error as an offline operation would have done.

But actually, it seems that this wait is unnecessary. The operation has already happened.

Using session One, we can see that the constraint already appears in the data dictionary:

One> select constraint_name,
  2         search_condition,
  3         status,
  4         validated
  5  from user_constraints
  6  where table_name = 'T';
  
CONSTRAINT_NAME SEARCH_CONDITION STATUS     VALIDATED
--------------- ---------------- ---------- -------------
T_B_CHK         B>0              ENABLED    NOT VALIDATED

And if we try now to perform a DML that violates the constraint, we’ll get an error message, because the constraint is already enabled:

One> insert into t(a,b) values (222,-2);
insert into t(a,b) values (222,-2)
*
ERROR at line 1:
ORA-02290: check constraint (DEMO.T_B_CHK) violated

Session Two will be released as soon as session One either commits or rolls back, but it seems that it could have been released before that.
Even if we kill session Two before the transaction in session One ends, it doesn’t really matter, because the constraint has already been created and enabled.