Category: Oracle 19c

USER_TRIGGERS_AE

One of the fundamental building blocks of EBR (Edition Based Redefinition) is Crossedition Triggers.

This special type of triggers is used when we change the structure of tables in an online application upgrade, and the data of these tables may be changed while we do the upgrade and afterwards, until no session uses the pre-upgrade edition. These triggers keep the pre-upgrade representation of the data and post-upgrade representation of the data in sync.

Crossedition triggers are temporary in nature – they should be dropped when all the clients use the edition in which the triggers were created (or one of its descendent editions).

In theory, crossedition triggers live for a very short period.

In real life, when we have multiple active editions and different types of clients that may keep using older editions for a while before upgrading to the latest edition, it may take days and even weeks before we can drop the crossedition triggers. In my opinion, this is perfectly fine, and actually one of the benefits that I see in EBR.

Another significant difference between crossedition triggers and regular triggers is that crossedition triggers are not inherited by descendent editions. However, they may be triggered by DML operations that are executed in a descendent edition.

This means that we need a way to check which crossedition triggers exist in our schema/database.
Continue reading “USER_TRIGGERS_AE”

ANY_VALUE and FIRST/LAST (KEEP)

The FIRST and LAST (a.k.a. “KEEP”) aggregate functions are very useful when you want to order a row set by one column and return the value of another column from the first or last row in the ordered set.
I wrote about these functions in the past; for example here and here.

To make sure the result of the FIRST (LAST) function is deterministic, we have to define a tie-breaker for the case that multiple rows have the same first (last) value. The tie-breaker is an aggregate function that is applied on the column we want to return.

For example, the following query returns for each department the FIRST_NAME of the employee with the lowest SALARY. If there are multiple employees with the lowest salary in the same department, the query returns the “minimum” first_name – so here the MIN function is the tie-breaker.
In this example the lowest salary in department 90 is 17000, and both Neena and Lex have this salary. We use MIN(FIRST_NAME), so the result is “Lex”.

Many times we don’t really need a tie-breaker, because we know that there is a single first/last row. For example, if we use a unique expression in the ORDER BY clause of the function. And sometimes we simply don’t care which record is returned in case of a tie. But since the syntax requires a tie-breaker, we have to use some “random” function, like MIN or MAX.

The ANY_VALUE function (that was added in Oracle 19c) is perfect, in my opinion, for this case. It may improve performance, but – more importantly – it makes the code clearer, by better reflecting our intention.
Assuming that in the previous example we don’t care which one of the employees with the lowest salary is returned, we can rewrite it like this:

select
  department_id,
  any_value(first_name) keep(dense_rank FIRST order by salary)
from employees
group by department_id;

DIY ANY_VALUE

I really like the ANY_VALUE aggregate function. But since it was added in Oracle 19c, I can’t use it in my Oracle XE database, as currently the latest version of XE is 18c.
So I decided to implement it as a user-defined function using the Oracle Data Cartridges Interface.

Note that this implementation is for VARCHAR2. If the function is used on other data types, the regular rules for implicit conversion apply.

    
SQL> create type any_value_string_t as object
  2  (
  3      v_value varchar2(4000),
  4      static function odciaggregateinitialize(sctx in out any_value_string_t) return number,
  5      member function odciaggregateiterate
  6      (
  7          self  in out any_value_string_t,
  8          value in varchar2
  9      ) return number,
 10      member function odciaggregatemerge
 11      (
 12          self in out any_value_string_t,
 13          ctx2 in any_value_string_t
 14      ) return number,
 15      member function odciaggregateterminate
 16      (
 17          self        in any_value_string_t,
 18          returnvalue out varchar2,
 19          flags       in number
 20      ) return number
 21  );
 22  /

Type created.

SQL> create type body any_value_string_t as
  2
  3      static function odciaggregateinitialize(sctx in out any_value_string_t) return number is
  4      begin
  5          sctx := any_value_string_t(null);
  6          return odciconst.success;
  7      end;
  8
  9      member function odciaggregateiterate
 10      (
 11          self  in out any_value_string_t,
 12          value in varchar2
 13      ) return number is
 14      begin
 15          if self.v_value is null then
 16              self.v_value := value;
 17          end if;
 18          return odciconst.success;
 19      end;
 20
 21      member function odciaggregateterminate
 22      (
 23          self        in any_value_string_t,
 24          returnvalue out varchar2,
 25          flags       in number
 26      ) return number is
 27      begin
 28          returnvalue := self.v_value;
 29          return odciconst.success;
 30      end;
 31
 32      member function odciaggregatemerge
 33      (
 34          self in out any_value_string_t,
 35          ctx2 in any_value_string_t
 36      ) return number is
 37      begin
 38          if self.v_value is null then
 39              self.v_value := ctx2.v_value;
 40          end if;
 41          return odciconst.success;
 42      end;
 43
 44  end;
 45  /

Type body created.

SQL> begin
  2    $IF DBMS_DB_VERSION.ver_le_18 $THEN
  3      execute immediate q''create function any_value (p_value varchar2) return varchar2
  4                           parallel_enable
  5                           aggregate using any_value_string_t;'';
  6      execute immediate q''grant execute on any_value to public'';
  7      execute immediate q''create public synonym any_value for any_value'';
  8    $ELSE
  9      raise_application_error(-20000,'ANY_VALUE is now supported by Oracle');
 10    $END
 11  end;
 12  /

PL/SQL procedure successfully completed.

Now any_value can be used just like any built-in aggregate function:

SQL> conn hr/hr
Connected.
SQL> select banner from v$version;

BANNER
------------------------------------------------------------------------------------------------------------------------
Oracle Database 18c Express Edition Release 18.0.0.0.0 - Production

1 row selected.

SQL> select d.department_id,
  2         any_value(d.department_name) department_name,
  3         count(*) number_of_employees
  4  from   employees   e,
  5         departments d
  6  where  d.department_id = e.department_id
  7  group  by d.department_id;

DEPARTMENT_ID DEPARTMENT_NAME      NUMBER_OF_EMPLOYEES
------------- -------------------- -------------------
           10 Administration                         1
           20 Marketing                              2
           30 Purchasing                             6
           40 Human Resources                        1
           50 Shipping                              45
           60 IT                                     5
           70 Public Relations                       1
           80 Sales                                 34
           90 Executive                              3
          100 Finance                                6
          110 Accounting                             2

11 rows selected.

Non Persistable Types

The ability to create user-defined types in Oracle is very powerful and is supported for many years (since Oracle 8).
It allows us to extend the built-in data types and adjust them to our specific needs, and to use them in our data model and in our code.
In the data model they can be used to define the type of specific columns or the type of tables (“object tables”).
In the code they can be used in SQL statements and in PL/SQL.

I’m not a big fan of using user-defined types in the data model.
I am, however, a huge fan of using them in the code.

Here are a few examples for posts in which user-defined types are used in the code:

So whenever I create a user-defined type my intention is that it will be used only in the code.
If the type is created in a PL/SQL scope, then my intention is enforced by definition.
But what if the type is created in the schema level? Can I enforce my intention? As of version 18c I can.

Persistability

A new feature in Oracle 18c allows to define a user-defined type as either persistable (which is the default) or not persistable.
A persistable type can be used in the code and in the data model, just like any type before 18c.
Let’s create color_t and color_tt as persistable types:

SQL> create type color_t as object (
  2    r number(3),
  3    g number(3),
  4    b number(3),
  5    member function hex_code return varchar2
  6  )
  7  /

Type created.

SQL>
SQL> create type body color_t as
  2    member function hex_code return varchar2
  3    is
  4    begin
  5      return '#' || to_char(self.r, 'fm0x') ||
  6                    to_char(self.g, 'fm0x') ||
  7                    to_char(self.b, 'fm0x');
  8    end hex_code;
  9  end color_t;
 10  /

Type body created.

SQL> create type color_tt as
  2    table of color_t
  3  /

Type created.

We can use persistable types in the data model.
As the type of an object table:

SQL> create table colors of color_t;

Table created.

As the type of a column:

SQL> create table people (
  2    first_name varchar2(20),
  3    last_name  varchar2(30),
  4    eye_color  color_t
  5  );

Table created.

And we can use persistable types in the code.
In PL/SQL:

SQL> declare
  2    v_yellow color_t := color_t(255,255,0);
  3    v_navy   color_t := color_t(0,0,128);
  4  begin
  5    dbms_output.put_line('Yellow: ' || v_yellow.hex_code);
  6    dbms_output.put_line('Navy:   ' || v_navy.hex_code);
  7  end;
  8  /
Yellow: #ffff00
Navy:   #000080

PL/SQL procedure successfully completed.

In SQL:

SQL> select *
  2  from color_tt(
  3         color_t(0,0,0),
  4         color_t(255,255,255));

         R          G          B
---------- ---------- ----------
         0          0          0
       255        255        255

SQL> drop table people;

Table dropped.

SQL> drop table colors;

Table dropped.

SQL> drop type color_tt;

Type dropped.

SQL> drop type color_t;

Type dropped.

A non persistable type can be used only in the code.
If we try to use it as the type of an object table or a column, we get the ORA-22384 exception.
Let’s create color_t and color_tt as non-persistable types:

SQL> create type color_t as object (
  2    r number(3),
  3    g number(3),
  4    b number(3),
  5    member function hex_code return varchar2
  6  )
  7  not persistable
  8  /

Type created.

SQL>
SQL> create type body color_t as
  2    member function hex_code return varchar2
  3    is
  4    begin
  5      return '#' || to_char(self.r, 'fm0x') ||
  6                    to_char(self.g, 'fm0x') ||
  7                    to_char(self.b, 'fm0x');
  8    end hex_code;
  9  end color_t;
 10  /

Type body created.

SQL> create type color_tt as
  2    table of (color_t)
  3  not persistable
  4  /

Type created.

We cannot use non-persistable types in the data model:

SQL> create table colors of color_t;
create table colors of color_t
*
ERROR at line 1:
ORA-22384: cannot create a column or table of a non-persistable type

SQL> create table people (
  2    first_name varchar2(20),
  3    last_name  varchar2(30),
  4    eye_color  color_t
  5  );
create table people (
*
ERROR at line 1:
ORA-22384: cannot create a column or table of a non-persistable type

We can use non-persistable types in the code, in PL/SQL and in SQL:

SQL> declare
  2    v_yellow color_t := color_t(255,255,0);
  3    v_navy   color_t := color_t(0,0,128);
  4  begin
  5    dbms_output.put_line('Yellow: ' || v_yellow.hex_code);
  6    dbms_output.put_line('Navy:   ' || v_navy.hex_code);
  7  end;
  8  /
Yellow: #ffff00
Navy:   #000080

PL/SQL procedure successfully completed.

SQL>
SQL> select *
  2  from color_tt(
  3         color_t(0,0,0),
  4         color_t(255,255,255));

         R          G          B
---------- ---------- ----------
         0          0          0
       255        255        255

SQL> drop type color_tt;

Type dropped.

SQL> drop type color_t;

Type dropped.

Type Dependency

As you may expect, a persistable type cannot depend on non-persistable types.

SQL> create type color_t as object (
  2    r number(3),
  3    g number(3),
  4    b number(3)
  5  )
  6  not persistable
  7  /

Type created.

SQL> create type color_tt as table of color_t
  2  /

Warning: Type created with compilation errors.

SQL> show err
Errors for TYPE COLOR_TT:

LINE/COL ERROR
-------- -----------------------------------------------------------------
0/0      ORA-22383: a persistable type cannot have non-persistable
         attributes

A non-persistable type can depend on both persistable and non-persistable types.

SQL> create or replace type color_tt as
  2    table of (color_t)
  3  not persistable
  4  /

Type created.

Note: the parenthesis are required when creating a collection type with the persistability clause

SQL> drop type color_tt;

Type dropped.

SQL> drop type color_t;

Type dropped.

Type Inheritance

A subtype inherits the persistability attribute of its supertype, and cannot override it. 

SQL> create type color_t as object (
  2    r number(3),
  3    g number(3),
  4    b number(3)
  5  )
  6  not final
  7  not persistable
  8  /

Type created.

SQL> create type rgba_color_t
  2    under color_t
  3    (alpha number(3))
  4  persistable
  5  /

Warning: Type created with compilation errors.

SQL> show err
Errors for TYPE RGBA_COLOR_T:

LINE/COL ERROR
-------- -----------------------------------------------------------------
0/0      PLS-00772: PERSISTABLE or NOT PERSISTABLE not permitted with
         UNDER clause

SQL> create type rgba_color_t
  2    under color_t
  3    (alpha number(3))
  4  /

Type created.

SQL> select type_name,persistable
  2  from user_types;

TYPE_NAME            PERSISTABLE
-------------------- --------------------
COLOR_T              NO
RGBA_COLOR_T         NO

In the next post I’ll write about an enhancement that was added to non-persistent types in Oracle 21c.

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.