Category: SQL

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.

PL/SQL Attributes in User-Defined SUBTYPES

I filed a ticket in MOS for this issue; I’ll update this post with the status

In the previous posts I wrote about non-persistable types (a new feature of Oracle 18c), and about the ability to use PL/SQL types as attributes of non-persistable types (a new feature of Oracle 21c).

It seems that the latter is not fully supported yet for type inheritance.

We can define a type with PL/SQL attributes, and then create a sub-type that adds pure SQL attributes. No problem with this, and the PL/SQL attributes are inherited.

21c> create type supertype as object (
  2    x pls_integer,
  3    y boolean
  4  )
  5  not final
  6  not persistable
  7  /

Type created.

21c> create type subtype
  2    under supertype (
  3      z integer
  4    )
  5  /

Type created.

21c> declare
  2      obj1 subtype;
  3      obj2 supertype;
  4  begin
  5      obj1 := subtype(7, true, 42);
  6      obj2 := obj1;
  7      dbms_output.put_line(obj2.x);
  8  end;
  9  /
7

PL/SQL procedure successfully completed.

But if we try to add new PL/SQL attributes when creating a subtype it fails.

21c> create type subtype2
  2    under supertype (
  3      z pls_integer
  4    )
  5  /

Warning: Type created with compilation errors.

21c> show err
Errors for TYPE SUBTYPE2:

LINE/COL ERROR
-------- -----------------------------------------------------------------
3/7      PLS-00530: Illegal type used for object type attribute:
         'PLS_INTEGER'.

My guess is that it happens due to a too strict check in the CREATE TYPE statement – looking for an explicit NOT PERSISTABLE declaration. But it’s impossible to make such a declaration, because, by definition, a subtype inherits the persistability attribute of its supertype, and cannot override it.

21c> create type subtype2
  2    under supertype (
  3      z pls_integer
  4    )
  5  not persistable
  6  /

Warning: Type created with compilation errors.

21c> show err
Errors for TYPE SUBTYPE2:

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

PL/SQL Attributes in User-Defined Types

In the previous post I wrote about non-persistable types – a feature that was added in Oracle 18c and provides a declarative way to prohibit the use of a schema-level user-defined type in the data model and to limit it to the code only.
Oracle 21c adds another enhancement, and allows to use unique PL/SQL predefined types (i.e., BOOLEAN, PLS_INTEGER/BINARY_INTEGER and their predefined subtypes) as attributes of non-persistable schema-level types.

To demonstrate it I’ll change COLOR_T – the type I used in the examples in the previous post. Let’s declare the r, g and b attributes as BINARY_INTEGER instead of NUMBER(3), and add a BOOLEAN attribute – is_favorite.
Trying to do it for a persistable type (in version 21 or before) fails with the PLS-00530 error:

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

Warning: Type created with compilation errors.

SQL> sho err
Errors for TYPE COLOR_T:

LINE/COL ERROR
-------- -----------------------------------------------------------------
2/5      PLS-00530: Illegal type used for object type attribute:
         'BINARY_INTEGER'.

3/5      PLS-00530: Illegal type used for object type attribute:
         'BINARY_INTEGER'.

4/5      PLS-00530: Illegal type used for object type attribute:
         'BINARY_INTEGER'.

5/15     PLS-00530: Illegal type used for object type attribute:
         'BOOLEAN'.

And we’ll get the same error if we try to do it for a non-persistable type in versions before 21c:

19c> create type color_t as object (
  2    r binary_integer,
  3    g binary_integer,
  4    b binary_integer,
  5    is_favorite boolean,
  6    member function hex_code return varchar2
  7  )
  8  not persistable
  9  /

Warning: Type created with compilation errors.

19c> show err
Errors for TYPE COLOR_T:

LINE/COL ERROR
-------- -----------------------------------------------------------------
2/5      PLS-00530: Illegal type used for object type attribute:
         'BINARY_INTEGER'.

3/5      PLS-00530: Illegal type used for object type attribute:
         'BINARY_INTEGER'.

4/5      PLS-00530: Illegal type used for object type attribute:
         'BINARY_INTEGER'.

5/15     PLS-00530: Illegal type used for object type attribute:
         'BOOLEAN'.

In Oracle 21c it succeeds:

21c> create type color_t as object (
  2    r binary_integer,
  3    g binary_integer,
  4    b binary_integer,
  5    is_favorite boolean,
  6    member function hex_code return varchar2
  7  )
  8  not persistable
  9  /

Type created.

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

Using Non-Persistable Types

Non-persistable types, in general, can be used in both PL/SQL and SQL.
But if the type contains unique PL/SQL attributes its usage is restricted to PL/SQL only.

21c> create function f (i_color in color_t) return varchar2 as
  2  begin
  3      if i_color.is_favorite then
  4          return i_color.hex_code;
  5      else
  6          return 'I do not like this color';
  7      end if;
  8  end;
  9  /

Function created.

21c> declare
  2      l_yellow color_t := color_t(255, 255, 0, false);
  3      l_navy   color_t := color_t(0, 0, 128, true);
  4  begin
  5      dbms_output.put_line('Yellow: ' || f(l_yellow));
  6      dbms_output.put_line('Navy:   ' || f(l_navy));
  7  end;
  8  /
Yellow: I do not like this color
Navy:   #000080

PL/SQL procedure successfully completed.

If we try to use it in a SQL statement, we get the ORA-39972 error:

21c> select count(*) from color_tt();
select count(*) from color_tt()
                     *
ERROR at line 1:
ORA-39972: cannot use an object type with PL/SQL unique attributes in a SQL statement

Using such types in SQL is not supported even where unique PL/SQL types are allowed: 

21c> declare
  2      l_yellow color_t := color_t(255, 255, 0, false);
  3      l_text varchar2(100);
  4  begin
  5      select f(l_yellow) into l_text from dual;
  6      dbms_output.put_line('Yellow: ' || l_text);
  7  end f;
  8  /
    select f(l_yellow) into l_text from dual;
           *
ERROR at line 5:
ORA-06550: line 5, column 12:
PL/SQL: ORA-39972: cannot use an object type with PL/SQL unique attributes in a SQL statement
ORA-06550: line 5, column 5:
PL/SQL: SQL Statement ignored

Compare the previous example with the following one – showing that binding unique PL/SQL types to SQL is allowed (as of Oracle 12.1).

21c> create or replace function f (i_bool in boolean) return varchar2 as
  2  begin
  3      return case i_bool when true then 'Yes' else 'No' end;
  4  end f;
  5  /

Function created.

21c> declare
  2      l_true boolean := true;
  3      l_text varchar2(100);
  4  begin
  5      select f(l_true)
  6      into   l_text
  7      from   dual;
  8      dbms_output.put_line('Do I like this color? ' || l_text);
  9  end;
 10  /
Do I like this color? Yes

PL/SQL procedure successfully completed.

Can any predefined PL/SQL type be used?

All the predefined PL/SQL types and subtypes can be used when creating non-persistable collection types (i.e., varrays and nested tables).
However, when creating non-persistable object types, we cannot use subtypes that are restricted with the NOT NULL constraint (like NATURALN or SIMPLE_INTEGER, for example).
The reason is that it is impossible to define attributes of object types as NOT NULL.

21c> create type simple_int_tt as
  2    table of (simple_integer)
  3    not persistable
  4  /

Type created.

21c> create type object_t as object (
  2    a binary_integer,
  3    b  simple_integer,
  4    c boolean
  5  )
  6  not persistable
  7  /

Warning: Type created with compilation errors.

21c> show err
Errors for TYPE OBJECT_T:

LINE/COL ERROR
-------- -----------------------------------------------------------------
3/3      PLS-00218: a variable declared NOT NULL must have an
         initialization assignment

I think the error message here is a bit misleading, because there is no way to define an initialization assignment

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.

Is Catching ORA-02292 A Good Solution? It Depends…

I’ve recently realized that some technique that I use sometimes is subject to an important restriction which I used to ignore.

The Requirement

We have two tables (let’s call them PARENTS and CHILDREN) with a foreign key between them (CHILDREN references PARENTS).
We need to write a procedure that deletes a given child, and if its parent has no other children the parent should be deleted as well.

Demo Data

SQL> select * from parents;

        ID
----------
         1
         2

SQL> select * from children;

        ID  PARENT_ID
---------- ----------
       101          1
       201          2
       202          2

The Implementation

A simple solution for the conditional deletion of the parent is to try to delete it.
If this parent has no other children, the DELETE statement will succeed.
If it has other children, the DELETE statement will fail with ORA-2292 (integrity constraint violated – child record found), and we can catch this exception and ignore it.

I like this kind of solutions mainly because Oracle automatically takes care of the necessary serialization of concurrent sessions (trying, in this case, to delete or insert children of the same parent).

SQL> create or replace package demo as
  2      procedure delete_child(i_id in children.id%type);
  3  end demo;
  4  /

Package created.

SQL> create or replace package body demo as
  2      procedure delete_parent_if_it_has_no_children(i_id in parents.id%type) is
  3          e_children_exist exception;
  4          pragma exception_init(e_children_exist, -2292);
  5      begin
  6          delete parents p
  7          where  p.id = i_id;
  8          dbms_output.put_line('parent was deleted successfully');
  9      exception
 10          when e_children_exist then
 11              dbms_output.put_line('parent was not deleted');
 12      end delete_parent_if_it_has_no_children;
 13
 14      procedure delete_child(i_id in children.id%type) is
 15          v_parent_id children.parent_id%type;
 16      begin
 17          delete children c
 18          where  c.id = i_id
 19          returning c.parent_id into v_parent_id;
 20
 21          delete_parent_if_it_has_no_children(v_parent_id);
 22
 23      end delete_child;
 24  end demo;
 25  /

Package body created.

Parent 1 has only one child – 101, so when we delete child 101 its parent is deleted as well:

SQL> exec demo.delete_child(101)
parent was deleted successfully

PL/SQL procedure successfully completed.

Parent 2 has two children – 201 and 202. When we delete one of the children, the parent is not deleted. When we delete the second child, the parent is deleted.

SQL> exec demo.delete_child(201)
parent was not deleted

PL/SQL procedure successfully completed.

SQL> exec demo.delete_child(202)
parent was deleted successfully

PL/SQL procedure successfully completed.

SQL> rollback;

Rollback complete.

The Catch

This solution is based on the fact that the foreign key constraint is enforced in the statement level. It means that we can use this solution as long as the foreign key is not deferred. Deferred constraints are enforced at the end of the transaction, and therefore the DELETE PARENTS statement will succeed without raising an exception, even if the deleted parent has children.

I executed the previous example after creating the tables as follows:

SQL> create table parents (
  2    id number not null primary key
  3  );

Table created.

SQL> create table children (
  2    id number not null primary key,
  3    parent_id not null
  4      constraint fk_children_parents
  5        references parents
  6        deferrable initially immediate
  7  );

Table created.

SQL> begin
  2    insert into parents values (1);
  3    insert into parents values (2);
  4
  5    insert into children values (101,1);
  6    insert into children values (201,2);
  7    insert into children values (202,2);
  8
  9    commit;
 10  end;
 11  /

PL/SQL procedure successfully completed.

Now let’s set the foreign key as deferred, and try to delete only one child of parent 2.

SQL> set constraint fk_children_parents deferred;

Constraint set.

SQL> exec demo.delete_child(201)
parent was deleted successfully

PL/SQL procedure successfully completed.

The parent was deleted successfully, although it still has an existing child.
But when we try to commit the transaction, the foreign key is checked, and the whole transaction is rolled back (including the deletion of the child).

SQL> commit;
commit
*
ERROR at line 1:
ORA-02091: transaction rolled back
ORA-02292: integrity constraint (TRANZMATE_DEV.FK_CHILDREN_PARENTS) violated - child record found

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.

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”