INSERT into a View with a GROUP BY Clause

Oren Nakdimon 1 Reply

When I wrote the previous post, about updatable views, I noticed an interesting issue.

The documentation says:

If a view is defined by a query that contains SET or DISTINCT operators, a GROUP BY clause, or a group function, then rows cannot be inserted into, updated in, or deleted from the base tables using the view.

Let’s create a view with a GROUP BY clause and a group function:

ORA$BASE> create table t (
  2      x number,
  3      y date,
  4      z varchar2(100)
  5  );

Table created.

ORA$BASE> create or replace view v as
  2    select x, y, max(z) z from t
  3    group by x, y;

View created.

And now let’s see if Oracle “thinks” that this view is inherently updatable:

ORA$BASE> select column_name,updatable,insertable,deletable
  2    from user_updatable_columns
  3   where table_name='V';

COLUMN_NAME                    UPDATABLE INSERTABL DELETABLE
------------------------------ --------- --------- ---------
X                              NO        NO        NO
Y                              NO        NO        NO
Z                              NO        NO        NO

3 rows selected.

As expected, and in alignment with the documentation, all the columns of this view are not inherently updatable, insertable or deletable.

So the next step (obviously :-) ) is to try inserting a record into the view:

ORA$BASE> insert into v (x,y) values (1,date'2016-06-24');

1 row created.

ORA$BASE> insert into v (x,y) values (1,date'2016-06-24');

1 row created.

ORA$BASE> select * from t;

         X Y          Z
---------- ---------- ----------
         1 24/06/2016
         1 24/06/2016

ORA$BASE> select * from v;

         X Y          Z
---------- ---------- ----------
         1 24/06/2016

And… we succeed.

Tested in 11.2.0.4 and 12.1.0.2.

Misconceptions about (Regular) Views Revealed when Presenting Editioning Views – Part 2

Oren Nakdimon 4 Replies

In a previous post I wrote about one misconception about views that is revealed when I talk about Editioning Views in my EBR (Edition-Based Redefinition) presentations.

This post is about another misconception.

In the part of the presentation in which I “preach” to cover every table with an Editioning View and to replace every reference to tables in the code with reference to the corresponding Editioning Views, I usually get the following question from the audience: “but what about DML?”.

Everybody knows that you can SELECT from a view, but there is a misconception that you cannot perform INSERT, UPDATE, MERGE or DELETE statements directly on a view.

So the truth is that (assuming you have been granted the necessary privileges) you can perform DML statements on every view (although it may require some additional work sometimes), except if the view is defined with the WITH READ ONLY clause.

The high-level general rule is that if Oracle can transform the statement to work on actual tables (the view’s base tables), in a consistent and deterministic way, without too much trouble, then we don’t need to do anything else – the view is inherently updatable and the DML statement will succeed. In the rest of the cases, although the view is not inherently updatable, we can “teach” it how to react to DML statements – by defining INSTEAD OF triggers.

Upon view creation Oracle analyzes for each of the view’s columns if it’s inherently updatable, and stores this information in the USER/ALL/DBA_UPDATABLE_COLUMNS dictionary views.

Let’s see some examples.

> create table t (
      x number,
      y date,
      z varchar2(100)
  );

Table created.

> create view v as
   select x, y, z as an_alias_for_z from t where x>10 order by y;

View created.

> select column_name,updatable,insertable,deletable
   from user_updatable_columns
  where table_name='V';

COLUMN_NAME                    UPDATABLE INSERTABL DELETABLE
------------------------------ --------- --------- ---------
X                              YES       YES       YES
Y                              YES       YES       YES
AN_ALIAS_FOR_Z                 YES       YES       YES

3 rows selected.

> insert into v (x,y,an_alias_for_z) values (1,sysdate,'a');

1 row created. 

> select * from t;

         X Y                   Z
---------- ------------------- ----------
         1 20/06/2016 06:05:12 a

1 row selected.

If the view’s top-level query contains, for example, the DISTINCT operator, then the view is not inherently updatable.

> create or replace view v as
   select distinct x, y, z from t;

View created.

> select column_name,updatable,insertable,deletable
    from user_updatable_columns
   where table_name='V';

COLUMN_NAME                    UPDATABLE INSERTABL DELETABLE
------------------------------ --------- --------- ---------
X                              NO        NO        NO
Y                              NO        NO        NO
Z                              NO        NO        NO

3 rows selected.

> insert into v (x, y, z) values (2,sysdate,'b');
insert into v (x, y, z) values (2,sysdate,'b')
            *
ERROR at line 1:
ORA-01732: data manipulation operation not legal on this view

If a column of the view is defined as an expression, then this column is not inherently updatable, but other “simple” columns are.

> create or replace view v as
    select x, y, upper(z) upper_z from t;

View created.

> select column_name,updatable,insertable,deletable
    from user_updatable_columns
   where table_name='V';

COLUMN_NAME                    UPDATABLE INSERTABL DELETABLE
------------------------------ --------- --------- ---------
X                              YES       YES       YES
Y                              YES       YES       YES
UPPER_Z                        NO        NO        NO

3 rows selected.

> insert into v (x, y, upper_z) values (3,sysdate,'C');
insert into v (x, y, upper_z) values (3,sysdate,'C')
                     *
ERROR at line 1:
ORA-01733: virtual column not allowed here

> insert into v (x, y) values (3,sysdate);

1 row created.

> update v set upper_z='C' where x=3;
update v set upper_z='C' where x=3
             *
ERROR at line 1:
ORA-01733: virtual column not allowed here


> update v set y=sysdate+1;

2 rows updated.

> select * from t;

         X Y                   Z
---------- ------------------- --------------
         3 21/06/2016 06:07:08
         1 21/06/2016 06:07:08 a

2 rows selected.

Even columns of a join view – a view with more than one base table in its FROM clause – may be inherently updatable (under some restrictions).

> create table p (
    id integer primary key,
    name varchar2(100)
  );

Table created.

> create table c (
    id integer primary key,
    p_id integer references p(id),
    details varchar2(100)
  );

Table created.

> insert into p values (1,'Parent 1');

1 row created.

> insert into p values (2,'Parent 2');

1 row created.

> create or replace view v as
    select c.id,c.p_id,c.details,p.name parent_name
      from p,c
     where p.id = c.p_id;

View created.

> select column_name,updatable,insertable,deletable
    from user_updatable_columns
   where table_name='V';

COLUMN_NAME                    UPDATABLE INSERTABL DELETABLE
------------------------------ --------- --------- ---------
ID                             YES       YES       YES
P_ID                           YES       YES       YES
DETAILS                        YES       YES       YES
PARENT_NAME                    NO        NO        NO

4 rows selected.

> insert into v(id,p_id,details,parent_name) values (101,1,'Child 1 of parent 1','Parent 1');
insert into v(id,p_id,details,parent_name) values (101,1,'Child 1 of parent 1','Parent 1')
                              *
ERROR at line 1:
ORA-01776: cannot modify more than one base table through a join view


> insert into v(id,p_id,details) values (101,1,'Child 1 of parent 1');

1 row created.

> update v set parent_name='Parent 8' where id=101;
update v set parent_name='Parent 8' where id=101
             *
ERROR at line 1:
ORA-01779: cannot modify a column which maps to a non key-preserved table


> update v set details=upper(details);

1 row updated.

> select * from v;

        ID       P_ID DETAILS                  PARENT_NAME
---------- ---------- ------------------------ ---------------------
       101          1 CHILD 1 OF PARENT 1      Parent 1

1 row selected.

These are just a few examples. For all the details please see the documentation.

And what about Editioning Views?

Editioning Views are deliberately very limited, because they were designed to allow using them in the code instead of using the base tables.

An Editioning View may contain only the SELECT and FROM clauses, the FROM clause refers to a single table, the SELECT list may contain only columns and aliases (no expressions).

Therefore, by definition, Editioning Views are always inherently updatable.

 

Subtleties – Part 3 (more workarounds for COLLECT DISTINCT in PL/SQL)

In Part 1 we saw that the SQL function COLLECT with the DISTINCT option is not natively supported in PL/SQL, and saw two workarounds – using dynamic SQL and using the SET function.
In Part 2 we saw that the SET function can operate on Nested Table but not on Varray.
In this post we’ll see two more workarounds.

Inline View

We can first remove the duplicates in an inline view, and then use the “simple” COLLECT function on the outcome of the inline view:

create type integer_vt as varray(100) of integer
/

var rc refcursor
begin
  open :rc for
    select person_id,cast(collect(project_id) as integer_vt) project_id_list
    from (
      select distinct person_id,project_id from project_assignments
    )
    group by person_id
    order by person_id;
end;
/

PL/SQL procedure successfully completed. 

print rc

PERSON_ID PROJECT_ID_LIST
---------- -----------------------------------
       101 INTEGER_VT(2, 3, 1)
       102 INTEGER_VT(2)
       103 INTEGER_VT(3)

MULTISET

We can use the MULTISET operator instead of the COLLECT function. Note that this solution is not identical to the previous ones – we use a separate query for each person as the input to the MULTISET operator, and we get results also for people with no project assignments at all:

begin
    open :rc for
        select p.person_id,
               cast(multiset (select distinct project_id
                     from   project_assignments a
                     where  a.person_id = p.person_id) as integer_vt) project_id_list
        from   people p
        order  by person_id;
end;
/

PL/SQL procedure successfully completed. 

print rc

PERSON_ID PROJECT_ID_LIST
---------- ------------------------------
       101 INTEGER_VT(1, 2, 3)
       102 INTEGER_VT(2)
       103 INTEGER_VT(3)
       104 INTEGER_VT()

Conclusion

There is no one “correct” solution. Under different circumstances, different solutions differ in complexity, performance, etc. We should use the one that is best for the specific case.
And if we need to use a collection type, then NESTED TABLE is by far my first choice. I would use the much more limited type VARRAY only if I have a really good reason to do so.

Kakuro Helper using SQL Query with PowerMultiSet and Pivot

When solving Kakuro it is essential to know for a given integer X and a given number of elements N all the combinations of N non-repeating digits [1-9] that their sum equals to X.

For example, there is only one combination for creating the number 7 from 3 elements:

1+2+4

And there are 6 combinations for creating the number 15 from 4 elements:

1+2+3+9
1+2+4+8
1+2+5+7
1+3+4+7
1+3+5+6
2+3+4+6

Let’s generate a list of all the possible Xs and Ns, using SQL of course.
What makes it really easy is the not-so-popular SQL function POWERMULTISET (available since Oracle 10g).
It is a collection function that gets a nested table and returns a collection of collections containing all nonempty subsets of the input collection.

create type integer_ntt as table of integer
/

break on x on num_of_elements skip 1

select sum(b.column_value) x,
       a.num_of_elements,
       listagg(b.column_value,'+') within group(order by b.column_value) expr
from   (select rownum id,
               column_value combination,
               cardinality(column_value) num_of_elements
        from   table(powermultiset(integer_ntt(1,2,3,4,5,6,7,8,9)))) a,
       table(a.combination) b
where  a.num_of_elements > 1
group  by a.id,a.num_of_elements
order  by x,num_of_elements,expr;

         X NUM_OF_ELEMENTS EXPR
---------- --------------- --------------------
         3               2 1+2

         4               2 1+3

         5               2 1+4
                           2+3

         6               2 1+5
                           2+4

                         3 1+2+3

         7               2 1+6
                           2+5
                           3+4

                         3 1+2+4
.
.
.
        15               2 6+9
                           7+8

                         3 1+5+9
                           1+6+8
                           2+4+9
                           2+5+8
                           2+6+7
                           3+4+8
                           3+5+7
                           4+5+6

                         4 1+2+3+9
                           1+2+4+8
                           1+2+5+7
                           1+3+4+7
                           1+3+5+6
                           2+3+4+6

                         5 1+2+3+4+5
.
.
.
        42               7 3+4+5+6+7+8+9

                         8 1+2+4+5+6+7+8+9

        43               8 1+3+4+5+6+7+8+9

        44               8 2+3+4+5+6+7+8+9

        45               9 1+2+3+4+5+6+7+8+9

502 rows selected.

We can get a more user-friendly output by pivoting the results into a matrix, where one axis is X, the second axis is N, and each cell contains all the combinations for this X|N pair.
This can be done easily using the PIVOT operator (available since Oracle 11g):

break on x skip 1
col 2 format a4
col 3 format a6
col 4 format a8
col 5 format a10
col 6 format a12
col 7 format a14
col 8 format a16
col 9 format a18

select *
from   (select sum(b.column_value) x,
               a.num_of_elements,
               listagg(b.column_value, '+') within group(order by b.column_value) expr
        from   (select rownum id,
                       column_value combination,
                       cardinality(column_value) num_of_elements
                from   table(powermultiset(integer_ntt(1,2,3,4,5,6,7,8,9)))) a,
               table(a.combination) b
        where  a.num_of_elements > 1
        group  by a.id,
                  a.num_of_elements)
pivot (listagg(expr, chr(10)) within group (order by expr)
      for num_of_elements in(2,3,4,5,6,7,8,9));

         X 2    3      4        5          6            7              8                9
---------- ---- ------ -------- ---------- ------------ -------------- ---------------- ------------------
         3 1+2

         4 1+3

         5 1+4
           2+3


         6 1+5  1+2+3
           2+4


         7 1+6  1+2+4
           2+5
           3+4


         8 1+7  1+2+5
           2+6  1+3+4
           3+5


         9 1+8  1+2+6
           2+7  1+3+5
           3+6  2+3+4
           4+5


        10 1+9  1+2+7  1+2+3+4
           2+8  1+3+6
           3+7  1+4+5
           4+6  2+3+5


        11 2+9  1+2+8  1+2+3+5
           3+8  1+3+7
           4+7  1+4+6
           5+6  2+3+6
                2+4+5


        12 3+9  1+2+9  1+2+3+6
           4+8  1+3+8  1+2+4+5
           5+7  1+4+7
                1+5+6
                2+3+7
                2+4+6
                3+4+5


        13 4+9  1+3+9  1+2+3+7
           5+8  1+4+8  1+2+4+6
           6+7  1+5+7  1+3+4+5
                2+3+8
                2+4+7
                2+5+6
                3+4+6


        14 5+9  1+4+9  1+2+3+8
           6+8  1+5+8  1+2+4+7
                1+6+7  1+2+5+6
                2+3+9  1+3+4+6
                2+4+8  2+3+4+5
                2+5+7
                3+4+7
                3+5+6


        15 6+9  1+5+9  1+2+3+9  1+2+3+4+5
           7+8  1+6+8  1+2+4+8
                2+4+9  1+2+5+7
                2+5+8  1+3+4+7
                2+6+7  1+3+5+6
                3+4+8  2+3+4+6
                3+5+7
                4+5+6


        16 7+9  1+6+9  1+2+4+9  1+2+3+4+6
                1+7+8  1+2+5+8
                2+5+9  1+2+6+7
                2+6+8  1+3+4+8
                3+4+9  1+3+5+7
                3+5+8  1+4+5+6
                3+6+7  2+3+4+7
                4+5+7  2+3+5+6


        17 8+9  1+7+9  1+2+5+9  1+2+3+4+7
                2+6+9  1+2+6+8  1+2+3+5+6
                2+7+8  1+3+4+9
                3+5+9  1+3+5+8
                3+6+8  1+3+6+7
                4+5+8  1+4+5+7
                4+6+7  2+3+4+8
                       2+3+5+7
                       2+4+5+6


        18      1+8+9  1+2+6+9  1+2+3+4+8
                2+7+9  1+2+7+8  1+2+3+5+7
                3+6+9  1+3+5+9  1+2+4+5+6
                3+7+8  1+3+6+8
                4+5+9  1+4+5+8
                4+6+8  1+4+6+7
                5+6+7  2+3+4+9
                       2+3+5+8
                       2+3+6+7
                       2+4+5+7
                       3+4+5+6


        19      2+8+9  1+2+7+9  1+2+3+4+9
                3+7+9  1+3+6+9  1+2+3+5+8
                4+6+9  1+3+7+8  1+2+3+6+7
                4+7+8  1+4+5+9  1+2+4+5+7
                5+6+8  1+4+6+8  1+3+4+5+6
                       1+5+6+7
                       2+3+5+9
                       2+3+6+8
                       2+4+5+8
                       2+4+6+7
                       3+4+5+7


        20      3+8+9  1+2+8+9  1+2+3+5+9
                4+7+9  1+3+7+9  1+2+3+6+8
                5+6+9  1+4+6+9  1+2+4+5+8
                5+7+8  1+4+7+8  1+2+4+6+7
                       1+5+6+8  1+3+4+5+7
                       2+3+6+9  2+3+4+5+6
                       2+3+7+8
                       2+4+5+9
                       2+4+6+8
                       2+5+6+7
                       3+4+5+8
                       3+4+6+7


        21      4+8+9  1+3+8+9  1+2+3+6+9  1+2+3+4+5+6
                5+7+9  1+4+7+9  1+2+3+7+8
                6+7+8  1+5+6+9  1+2+4+5+9
                       1+5+7+8  1+2+4+6+8
                       2+3+7+9  1+2+5+6+7
                       2+4+6+9  1+3+4+5+8
                       2+4+7+8  1+3+4+6+7
                       2+5+6+8  2+3+4+5+7
                       3+4+5+9
                       3+4+6+8
                       3+5+6+7


        22      5+8+9  1+4+8+9  1+2+3+7+9  1+2+3+4+5+7
                6+7+9  1+5+7+9  1+2+4+6+9
                       1+6+7+8  1+2+4+7+8
                       2+3+8+9  1+2+5+6+8
                       2+4+7+9  1+3+4+5+9
                       2+5+6+9  1+3+4+6+8
                       2+5+7+8  1+3+5+6+7
                       3+4+6+9  2+3+4+5+8
                       3+4+7+8  2+3+4+6+7
                       3+5+6+8
                       4+5+6+7


        23      6+8+9  1+5+8+9  1+2+3+8+9  1+2+3+4+5+8
                       1+6+7+9  1+2+4+7+9  1+2+3+4+6+7
                       2+4+8+9  1+2+5+6+9
                       2+5+7+9  1+2+5+7+8
                       2+6+7+8  1+3+4+6+9
                       3+4+7+9  1+3+4+7+8
                       3+5+6+9  1+3+5+6+8
                       3+5+7+8  1+4+5+6+7
                       4+5+6+8  2+3+4+5+9
                                2+3+4+6+8
                                2+3+5+6+7


        24      7+8+9  1+6+8+9  1+2+4+8+9  1+2+3+4+5+9
                       2+5+8+9  1+2+5+7+9  1+2+3+4+6+8
                       2+6+7+9  1+2+6+7+8  1+2+3+5+6+7
                       3+4+8+9  1+3+4+7+9
                       3+5+7+9  1+3+5+6+9
                       3+6+7+8  1+3+5+7+8
                       4+5+6+9  1+4+5+6+8
                       4+5+7+8  2+3+4+6+9
                                2+3+4+7+8
                                2+3+5+6+8
                                2+4+5+6+7


        25             1+7+8+9  1+2+5+8+9  1+2+3+4+6+9
                       2+6+8+9  1+2+6+7+9  1+2+3+4+7+8
                       3+5+8+9  1+3+4+8+9  1+2+3+5+6+8
                       3+6+7+9  1+3+5+7+9  1+2+4+5+6+7
                       4+5+7+9  1+3+6+7+8
                       4+6+7+8  1+4+5+6+9
                                1+4+5+7+8
                                2+3+4+7+9
                                2+3+5+6+9
                                2+3+5+7+8
                                2+4+5+6+8
                                3+4+5+6+7


        26             2+7+8+9  1+2+6+8+9  1+2+3+4+7+9
                       3+6+8+9  1+3+5+8+9  1+2+3+5+6+9
                       4+5+8+9  1+3+6+7+9  1+2+3+5+7+8
                       4+6+7+9  1+4+5+7+9  1+2+4+5+6+8
                       5+6+7+8  1+4+6+7+8  1+3+4+5+6+7
                                2+3+4+8+9
                                2+3+5+7+9
                                2+3+6+7+8
                                2+4+5+6+9
                                2+4+5+7+8
                                3+4+5+6+8


        27             3+7+8+9  1+2+7+8+9  1+2+3+4+8+9
                       4+6+8+9  1+3+6+8+9  1+2+3+5+7+9
                       5+6+7+9  1+4+5+8+9  1+2+3+6+7+8
                                1+4+6+7+9  1+2+4+5+6+9
                                1+5+6+7+8  1+2+4+5+7+8
                                2+3+5+8+9  1+3+4+5+6+8
                                2+3+6+7+9  2+3+4+5+6+7
                                2+4+5+7+9
                                2+4+6+7+8
                                3+4+5+6+9
                                3+4+5+7+8


        28             4+7+8+9  1+3+7+8+9  1+2+3+5+8+9  1+2+3+4+5+6+7
                       5+6+8+9  1+4+6+8+9  1+2+3+6+7+9
                                1+5+6+7+9  1+2+4+5+7+9
                                2+3+6+8+9  1+2+4+6+7+8
                                2+4+5+8+9  1+3+4+5+6+9
                                2+4+6+7+9  1+3+4+5+7+8
                                2+5+6+7+8  2+3+4+5+6+8
                                3+4+5+7+9
                                3+4+6+7+8


        29             5+7+8+9  1+4+7+8+9  1+2+3+6+8+9  1+2+3+4+5+6+8
                                1+5+6+8+9  1+2+4+5+8+9
                                2+3+7+8+9  1+2+4+6+7+9
                                2+4+6+8+9  1+2+5+6+7+8
                                2+5+6+7+9  1+3+4+5+7+9
                                3+4+5+8+9  1+3+4+6+7+8
                                3+4+6+7+9  2+3+4+5+6+9
                                3+5+6+7+8  2+3+4+5+7+8


        30             6+7+8+9  1+5+7+8+9  1+2+3+7+8+9  1+2+3+4+5+6+9
                                2+4+7+8+9  1+2+4+6+8+9  1+2+3+4+5+7+8
                                2+5+6+8+9  1+2+5+6+7+9
                                3+4+6+8+9  1+3+4+5+8+9
                                3+5+6+7+9  1+3+4+6+7+9
                                4+5+6+7+8  1+3+5+6+7+8
                                           2+3+4+5+7+9
                                           2+3+4+6+7+8


        31                      1+6+7+8+9  1+2+4+7+8+9  1+2+3+4+5+7+9
                                2+5+7+8+9  1+2+5+6+8+9  1+2+3+4+6+7+8
                                3+4+7+8+9  1+3+4+6+8+9
                                3+5+6+8+9  1+3+5+6+7+9
                                4+5+6+7+9  1+4+5+6+7+8
                                           2+3+4+5+8+9
                                           2+3+4+6+7+9
                                           2+3+5+6+7+8


        32                      2+6+7+8+9  1+2+5+7+8+9  1+2+3+4+5+8+9
                                3+5+7+8+9  1+3+4+7+8+9  1+2+3+4+6+7+9
                                4+5+6+8+9  1+3+5+6+8+9  1+2+3+5+6+7+8
                                           1+4+5+6+7+9
                                           2+3+4+6+8+9
                                           2+3+5+6+7+9
                                           2+4+5+6+7+8


        33                      3+6+7+8+9  1+2+6+7+8+9  1+2+3+4+6+8+9
                                4+5+7+8+9  1+3+5+7+8+9  1+2+3+5+6+7+9
                                           1+4+5+6+8+9  1+2+4+5+6+7+8
                                           2+3+4+7+8+9
                                           2+3+5+6+8+9
                                           2+4+5+6+7+9
                                           3+4+5+6+7+8


        34                      4+6+7+8+9  1+3+6+7+8+9  1+2+3+4+7+8+9
                                           1+4+5+7+8+9  1+2+3+5+6+8+9
                                           2+3+5+7+8+9  1+2+4+5+6+7+9
                                           2+4+5+6+8+9  1+3+4+5+6+7+8
                                           3+4+5+6+7+9


        35                      5+6+7+8+9  1+4+6+7+8+9  1+2+3+5+7+8+9
                                           2+3+6+7+8+9  1+2+4+5+6+8+9
                                           2+4+5+7+8+9  1+3+4+5+6+7+9
                                           3+4+5+6+8+9  2+3+4+5+6+7+8


        36                                 1+5+6+7+8+9  1+2+3+6+7+8+9  1+2+3+4+5+6+7+8
                                           2+4+6+7+8+9  1+2+4+5+7+8+9
                                           3+4+5+7+8+9  1+3+4+5+6+8+9
                                                        2+3+4+5+6+7+9


        37                                 2+5+6+7+8+9  1+2+4+6+7+8+9  1+2+3+4+5+6+7+9
                                           3+4+6+7+8+9  1+3+4+5+7+8+9
                                                        2+3+4+5+6+8+9


        38                                 3+5+6+7+8+9  1+2+5+6+7+8+9  1+2+3+4+5+6+8+9
                                                        1+3+4+6+7+8+9
                                                        2+3+4+5+7+8+9


        39                                 4+5+6+7+8+9  1+3+5+6+7+8+9  1+2+3+4+5+7+8+9
                                                        2+3+4+6+7+8+9


        40                                              1+4+5+6+7+8+9  1+2+3+4+6+7+8+9
                                                        2+3+5+6+7+8+9


        41                                              2+4+5+6+7+8+9  1+2+3+5+6+7+8+9

        42                                              3+4+5+6+7+8+9  1+2+4+5+6+7+8+9

        43                                                             1+3+4+5+6+7+8+9

        44                                                             2+3+4+5+6+7+8+9

        45                                                                              1+2+3+4+5+6+7+8+9


43 rows selected.

Misconceptions about (Regular) Views Revealed when Presenting Editioning Views

Oren Nakdimon 1 Reply

Sometimes when you present an advanced feature, questions from the audience reveal misconceptions about basic features.

It happens to me almost every time I talk about Edition-Based Redefinition. I present Editioning Views, and then I get questions that reveal misunderstandings about views in general.

One such misunderstanding is regarding what is kept in the view definition.

When we create a view as “select * from table”, the * is expanded to actual columns during the view creation. The * is not kept as part of the view definition.

For example:

> create table people (
>    person_id integer not null,
>    first_name varchar2(20),
>    last_name varchar2(20)
> );

Table created.

> create or replace view people_v as select * from people;

View created.

> select text from user_views where view_name='PEOPLE_V';

TEXT
--------------------------------------------------------------------------------
select "PERSON_ID","FIRST_NAME","LAST_NAME" from people

> select column_id,column_name,nullable,data_type 
>   from user_tab_columns 
>  where table_name='PEOPLE_V'
>  order by column_id;

 COLUMN_ID COLUMN_NAME          NUL DATA_TYPE
---------- -------------------- --- ----------
         1 PERSON_ID            N   NUMBER
         2 FIRST_NAME           Y   VARCHAR2
         3 LAST_NAME            Y   VARCHAR2

If we add a new column to the PEOPLE table, the PEOPLE_V view will not be affected (as the column list in its definition is not “*”).

> alter table people add (eye_color varchar2(10));

Table altered.

> select text from user_views where view_name='PEOPLE_V';

TEXT
--------------------------------------------------------------------------------
select "PERSON_ID","FIRST_NAME","LAST_NAME" from people

> select column_id,column_name,nullable,data_type 
>   from user_tab_columns 
>  where table_name='PEOPLE_V' 
>  order by column_id;

COLUMN_ID COLUMN_NAME          NUL DATA_TYPE
---------- -------------------- --- ----------
1 PERSON_ID            N   NUMBER
2 FIRST_NAME           Y   VARCHAR2
3 LAST_NAME            Y   VARCHAR2

There is one case though where the * is not expanded during the view creation – if we (forcibly) create a view with invalid SELECT statement:

> create or replace force view people_v as 
>   select * from people
>    where non_exiting_column is not null;

Warning: View created with compilation errors.

> select text from user_views where view_name='PEOPLE_V';

TEXT
------------------------------------------------------------
select * from people where non_exiting_column is not null

> select * from people_v;

select * from people_v
*
ERROR at line 1:
ORA-04063: view "DEMO.PEOPLE_V" has errors

But as soon as the view becomes valid, the * is expanded and the actual column list is stored in the view definition:

> create or replace force view people_v as 
>   select * from people 
>    where last_name is not null;

View created.

> select text from user_views where view_name='PEOPLE_V';

TEXT
------------------------------------------------------------
select "PERSON_ID","FIRST_NAME","LAST_NAME","EYE_COLOR" from
people where last_name is not null

In the next post I’ll discuss another misconception about views.

Subtleties – Part 2 (Nested Tables and Varrays)

Oren Nakdimon 1 Reply

In Part 1 we saw that the SQL function COLLECT with the DISTINCT option is not natively supported in PL/SQL.
One suggested workaround was to apply the SET function on the result of the “simple” COLLECT function (without the DISTINCT option).
This works fine, in both SQL and PL/SQL, as long as the collection type that we use is Nested Table.

create type integer_ntt as table of integer
/

select person_id,set(cast(collect(project_id) as integer_ntt)) project_id_list
from project_assignments
group by person_id
order by person_id;

PERSON_ID PROJECT_ID_LIST
---------- -----------------------------------------
       101 INTEGER_NTT(1, 3, 2)
       102 INTEGER_NTT(2)
       103 INTEGER_NTT(3)

But what if the collection type we use is Varray and not Nested Table?

create type integer_vt as varray(100) of integer
/

select person_id,set(cast(collect(project_id) as integer_vt)) project_id_list 
from project_assignments 
group by person_id
order by person_id;

select person_id,set(cast(collect(project_id) as integer_vt)) project_id_list
                     *
ERROR at line 1:
ORA-00932: inconsistent datatypes: expected UDT got DEMO.INTEGER_VT

So not every operation we can do with one collection type (Nested Table) is supported with other collection type (Varray).
SET is one example for that, and actually it makes sense. The elements within a set are unordered by definition, while a Varray is an ordered list of elements.
Another example for a function that works for nested tables but not for varrays is the CARDINALITY collection function:

select cardinality(integer_ntt(7,8,9)) from dual;

CARDINALITY(INTEGER_NTT(7,8,9))
-------------------------------
                              3

select cardinality(integer_vt(7,8,9)) from dual;

select cardinality(integer_vt(7,8,9)) from dual
                   *
ERROR at line 1:
ORA-00932: inconsistent datatypes: expected UDT got DEMO.INTEGER_VT

Subtleties – Part 1 (SQL and PL/SQL)

Oren Nakdimon 2 Replies

Almost every valid SQL statement (i.e., that is executed successfully by the SQL engine) can be embedded successfully as a static SQL in PL/SQL. Almost, but not every statement.
One example is the COLLECT aggregate function with the DISTINCT option.

To demonstrate it I’ll use the PROJECT_ASSIGNMENTS table, which contains assignments of people to projects. The same person may be assigned to the same project more than once, in different times.

create table people (
       person_id integer not null constraint people_pk primary key,
       first_name varchar2(20) not null,
       last_name varchar2(30) not null
);

create table projects (
       project_id integer not null constraint projects_pk primary key,
       project_name varchar2(100) not null
);

create table project_assignments (
       assignment_id integer not null constraint project_assignments_pk primary key,
       person_id integer not null constraint assignments_fk_people references people,
       project_id integer not null constraint assignments_fk_projects references projects,
       from_date date,
       to_date date
);
break on person_id dup skip 1

select person_id,project_id,from_date,to_date
from   project_assignments
order  by person_id,from_date;


 PERSON_ID PROJECT_ID FROM_DATE  TO_DATE
---------- ---------- ---------- ----------
       101          1 01/01/2016 10/01/2016
       101          2 11/01/2016 20/01/2016
       101          3 21/01/2016 31/01/2016
       101          1 01/02/2016 10/02/2016

       102          2 05/01/2016 15/01/2016
       102          2 05/02/2016 25/02/2016
       102          2 01/03/2016 02/03/2016

       103          3 12/01/2016 13/01/2016


8 rows selected.

All the following examples have been tested in 11.2.0.4 and 12.1.0.2.

The COLLECT function returns a collection of elements. For example, the following query returns all the project assignments per person, using a nested table type:

create type integer_ntt as table of integer
/

select person_id,cast(collect(project_id) as integer_ntt) project_id_list 
from project_assignments 
group by person_id
order by person_id;

PERSON_ID PROJECT_ID_LIST
--------- ----------------------------------------
      101 INTEGER_NTT(1, 1, 3, 2)
      102 INTEGER_NTT(2, 2, 2)
      103 INTEGER_NTT(3)

We can execute the same statement in PL/SQL:

var rc refcursor
begin
  open :rc for
    select person_id,cast(collect(project_id) as integer_ntt) project_id_list
    from project_assignments
    group by person_id
    order by person_id;
end;
/

PL/SQL procedure successfully completed.

print rc

 PERSON_ID PROJECT_ID_LIST
---------- --------------------------------------------------
       101 INTEGER_NTT(1, 1, 3, 2)
       102 INTEGER_NTT(2, 2, 2)
       103 INTEGER_NTT(3)

Now, let’s change the query to get a distinct list of the projects for each person, using the DISTINCT option of the COLLECT function:

select person_id,cast(collect(distinct project_id) as integer_ntt) project_id_list
from project_assignments
group by person_id
order by person_id;

PERSON_ID PROJECT_ID_LIST
---------- ---------------------------------------
       101 INTEGER_NTT(1, 2, 3)
       102 INTEGER_NTT(2)
       103 INTEGER_NTT(3)

No problems here. But if we try to use the exact same query in PL/SQL, it will fail:

begin
  open :rc for
    select person_id,cast(collect(distinct project_id) as integer_ntt) project_id_list
    from project_assignments
    group by person_id
    order by person_id;
end;
/

    select person_id,cast(collect(distinct project_id) as integer_ntt) project_id_list
                          *
ERROR at line 3:
ORA-06550: line 3, column 27:
PL/SQL: ORA-30482: DISTINCT option not allowed for this function
ORA-06550: line 3, column 5:
PL/SQL: SQL Statement ignored

One workaround we can use is using dynamic SQL instead of static SQL. This workaround usually (but not always) works in cases where valid SQL syntax is not supported by the PL/SQL engine. Note that this time the SQL statement is passed as a string literal to the OPEN statement (so the PL/SQL engine does not really “understand” it).

begin
  open :rc for
    'select person_id,cast(collect(distinct project_id) as integer_ntt) project_id_list 
    from project_assignments 
    group by person_id
    order by person_id';
end;
/

print rc

 PERSON_ID PROJECT_ID_LIST
---------- ----------------------------------------
       101 INTEGER_NTT(1, 2, 3)
       102 INTEGER_NTT(2)
       103 INTEGER_NTT(3)

Another workaround is to apply the SET function on the non-unique result of the COLLECT function:

begin
  open :rc for
    select person_id,set(cast(collect(project_id) as integer_ntt)) project_id_list 
    from project_assignments 
    group by person_id
    order by person_id;
end;
/
print rc

PERSON_ID PROJECT_ID_LIST
---------- --------------------------------------
       101 INTEGER_NTT(1, 3, 2)
       102 INTEGER_NTT(2)
       103 INTEGER_NTT(3)

In the next post we’ll see a very similar use case, in which the SET function cannot be used, and some other workarounds.

Who Rewrote My SQL?

Oren Nakdimon Leave a Reply

There are several new features in Oracle 12c that are implemented under the hood by changing the SQL statement that we write to a different statement (e.g., by adding some hidden predicates).
In OUG Ireland 2016 I talked about two such features – In Database Archiving and Temporal Validity – as part of my “Write Less (Code) with More (Oracle12c New Features)” presentation. I usually talk about another such feature in this presentation – the Row Limiting clause. This time I skipped it, but Tim Hall talked about it two hours later in his “Analytic Functions: An Oracle Developer’s Best Friend” presentation. Following these presentations I had two short and interesting chats with Tim and with Jonathan Lewis about when, during the statement execution, Oracle rewrites the statements in these features. These chats are the motivation for this post.

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. The Parser basically checks the syntax and the semantics of the statement. If needed, it also expands the statement. For example, it replaces each view referenced in the statement with its definition, so after parsing the statement refers only to actual tables. Another example: it expands * to the actual column list.
The second stage, the optimization, is much more complex. The Optimizer has several components, and the first one is the Query Transformer. This component may further rewrites the SQL statement that it gets from the Parser, but the purpose here is to find an equivalent statement with a lower cost.

In Oracle 12c we have a simple (and documented) way to see the output of the expansion that is done by the Parser – using the DBMS_UTILITY.EXPAND_SQL_TEXT procedure.

Note: to be more precise, I assume that this procedure reveals everything that the Parser does during the expansion stage, and only that. The documentation of DBMS_UTILITY.EXPAND_SQL_TEXT is very limited. It only says “This procedure recursively replaces any view references in the input SQL query with the corresponding view subquery”, and the Usage Notes imply that it also shows the outcome of applying VPD policies.

Row Limiting

Apparently the new Row Limiting clause, used for Top-N and paging queries, is implemented at the expansion stage. We can see that a query that uses the new Row Limiting syntax is expanded to a pre-12c syntax using analytic functions:

var x clob
begin
  dbms_utility.expand_sql_text(
    input_sql_text => '
      select project_id,
             person_id,
             assignment_id,
             assignment_period_start,
             assignment_period_end
      from project_assignments
      order by project_id,person_id
      OFFSET 100 ROWS 
      FETCH NEXT 4 ROWS ONLY',
    output_sql_text => :x);
end;
/
 
print x

-- I formatted the output to make it more readable
SELECT "A1"."PROJECT_ID"              "PROJECT_ID",
       "A1"."PERSON_ID"               "PERSON_ID",
       "A1"."ASSIGNMENT_ID"           "ASSIGNMENT_ID",
       "A1"."ASSIGNMENT_PERIOD_START" "ASSIGNMENT_PERIOD_START",
       "A1"."ASSIGNMENT_PERIOD_END"   "ASSIGNMENT_PERIOD_END"
FROM   (SELECT "A2"."PROJECT_ID" "PROJECT_ID",
               "A2"."PERSON_ID" "PERSON_ID",
               "A2"."ASSIGNMENT_ID" "ASSIGNMENT_ID",
               "A2"."ASSIGNMENT_PERIOD_START" "ASSIGNMENT_PERIOD_START",
               "A2"."ASSIGNMENT_PERIOD_END" "ASSIGNMENT_PERIOD_END",
               "A2"."PROJECT_ID" "rowlimit_$_0",
               "A2"."PERSON_ID" "rowlimit_$_1",
               row_number() over(ORDER BY "A2"."PROJECT_ID", "A2"."PERSON_ID") "rowlimit_$$_rownumber"
        FROM   "DEMO5"."PROJECT_ASSIGNMENTS""A2") "A1"
WHERE  "A1"."rowlimit_$$_rownumber" <= CASE WHEN (100 >= 0) THEN floor(to_number(100)) ELSE 0 END + 4
AND    "A1"."rowlimit_$$_rownumber" > 100
ORDER  BY "A1"."rowlimit_$_0",
          "A1"."rowlimit_$_1"

For more examples like this, and more details about Row Limiting in general, see Write Less with More – Part 5.

Temporal Validity

Temporal Validity allows to apply filtering based on validity period (or range), either explicitly or implicitly.
Explicit filtering is done at the statement-level. Implicit filtering is done by a session-level control.
We can see that both statement-level control and session-level control are implemented at the expansion stage:

alter table project_assignments 
       add PERIOD FOR assignment_period;

var x clob
begin
  dbms_utility.expand_sql_text(
    input_sql_text => '
      select person_id,
             project_id,
             assignment_period_start,
             assignment_period_end
      from   project_assignments
      AS OF PERIOD FOR assignment_period SYSDATE',
    output_sql_text => :x);
end;
/
 
print x

SELECT "A1"."PERSON_ID"               "PERSON_ID",
       "A1"."PROJECT_ID"              "PROJECT_ID",
       "A1"."ASSIGNMENT_PERIOD_START" "ASSIGNMENT_PERIOD_START",
       "A1"."ASSIGNMENT_PERIOD_END"   "ASSIGNMENT_PERIOD_END"
FROM   (SELECT "A2"."ASSIGNMENT_PERIOD_START" "ASSIGNMENT_PERIOD_START",
               "A2"."ASSIGNMENT_PERIOD_END"   "ASSIGNMENT_PERIOD_END",
               "A2"."ASSIGNMENT_ID"           "ASSIGNMENT_ID",
               "A2"."PERSON_ID"               "PERSON_ID",
               "A2"."PROJECT_ID"              "PROJECT_ID"
        FROM   "DEMO5"."PROJECT_ASSIGNMENTS" "A2"
        WHERE  ("A2"."ASSIGNMENT_PERIOD_START" IS NULL OR "A2"."ASSIGNMENT_PERIOD_START" <= SYSDATE)
        AND    ("A2"."ASSIGNMENT_PERIOD_END" IS NULL OR "A2"."ASSIGNMENT_PERIOD_END" > SYSDATE)) "A1"
> exec dbms_flashback_archive.enable_at_valid_time('CURRENT')

PL/SQL procedure successfully completed.

var x clob
begin
  dbms_utility.expand_sql_text(
    input_sql_text => '
      select person_id,
             project_id,
             assignment_period_start,
             assignment_period_end
      from   project_assignments',
    output_sql_text => :x);
end;
/
 
print x

SELECT "A1"."PERSON_ID"               "PERSON_ID",
       "A1"."PROJECT_ID"              "PROJECT_ID",
       "A1"."ASSIGNMENT_PERIOD_START" "ASSIGNMENT_PERIOD_START",
       "A1"."ASSIGNMENT_PERIOD_END"   "ASSIGNMENT_PERIOD_END"
FROM   (SELECT "A2"."ASSIGNMENT_PERIOD_START" "ASSIGNMENT_PERIOD_START",
               "A2"."ASSIGNMENT_PERIOD_END"   "ASSIGNMENT_PERIOD_END",
               "A2"."ASSIGNMENT_ID"           "ASSIGNMENT_ID",
               "A2"."PERSON_ID"               "PERSON_ID",
               "A2"."PROJECT_ID"              "PROJECT_ID"
        FROM   "DEMO5"."PROJECT_ASSIGNMENTS" "A2"
        WHERE  ("A2"."ASSIGNMENT_PERIOD_START" IS NULL OR "A2"."ASSIGNMENT_PERIOD_START" <= systimestamp(6))
        AND    ("A2"."ASSIGNMENT_PERIOD_END" IS NULL OR "A2"."ASSIGNMENT_PERIOD_END" > systimestamp(6))) "A1"

For more details about Temporal Validity, see Write Less with More – Part 4.

In-Database Archiving

Tables that are defined as ROW ARCHIVAL have the hidden column ORA_ARCHIVE_STATE. By default, when we select from such tables a hidden predicate is added automatically: ORA_ARCHIVE_STATE = ‘0’.
As shown in Write Less with More – Part 3:

drop table projects cascade constraints;
create table projects (
       project_id integer not null constraint projects_pk primary key,
       project_name varchar2(100) not null,
       status number(1) not null,
       last_days_to_show_in_reports integer not null
)
ROW ARCHIVAL;

insert into projects values (1,'Project A',1,2);
insert into projects values (2,'Project B',2,3);
insert into projects values (3,'Project C',1,4);
insert into projects values (4,'Project D',2,3);
commit;

> update projects set ORA_ARCHIVE_STATE='1' where project_id in (1,3);

2 rows updated.

> select * from projects; 

                                      LAST_DAYS_TO
PROJECT_ID PROJECT_NAME     STATUS SHOW_IN_REPORTS
---------- ------------ ---------- ---------------
         2 Project B             2               3
         4 Project D             2               3 

> select * from table(dbms_xplan.display_cursor());

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------
SQL_ID  dcthaywgmzra7, child number 1
-------------------------------------
select * from projects

Plan hash value: 2188942312

------------------------------------------------------------------------------
| Id  | Operation         | Name     | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |          |       |       |     3 (100)|          |
|*  1 |  TABLE ACCESS FULL| PROJECTS |     4 |  8372 |     3   (0)| 00:00:01 |
------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter("PROJECTS"."ORA_ARCHIVE_STATE"='0')

But when does Oracle add this predicate?

In this case, it’s not during expansion:

var x clob
begin
  dbms_utility.expand_sql_text(
    input_sql_text => 'select * from projects',
    output_sql_text => :x);
end;
/

PL/SQL procedure successfully completed.

print x

SELECT "A1"."PROJECT_ID"                   "PROJECT_ID",
       "A1"."PROJECT_NAME"                 "PROJECT_NAME",
       "A1"."STATUS"                       "STATUS",
       "A1"."LAST_DAYS_TO_SHOW_IN_REPORTS" "LAST_DAYS_TO_SHOW_IN_REPORTS"
FROM   "DEMO5"."PROJECTS" "A1"

A 10053 trace shows that the predicate is not added by the Query Transformer either (which I think is a good thing, as the Transformer should not change the meaning of the query):

.
.
.
******************************************
----- Current SQL Statement for this session (sql_id=dcthaywgmzra7) -----
select * from projects
*******************************************
.
.
.
=====================================
SPD: BEGIN context at statement level
=====================================
Stmt: ******* UNPARSED QUERY IS *******
SELECT "PROJECTS"."PROJECT_ID" "PROJECT_ID","PROJECTS"."PROJECT_NAME" "PROJECT_NAME","PROJECTS"."STATUS" "STATUS","PROJECTS"."LAST_DAYS_TO_SHOW_IN_REPORTS" "LAST_DAYS_TO_SHOW_IN_REPORTS" FROM "DEMO5"."PROJECTS" "PROJECTS" WHERE "PROJECTS"."ORA_ARCHIVE_STATE"='0'
Objects referenced in the statement
  PROJECTS[PROJECTS] 113224, type = 1
Objects in the hash table
  Hash table Object 113224, type = 1, ownerid = 8465150763180795273:
    No Dynamic Sampling Directives for the object
Return code in qosdInitDirCtx: ENBLD
===================================
SPD: END context at statement level
===================================
Final query after transformations:******* UNPARSED QUERY IS *******
SELECT "PROJECTS"."PROJECT_ID" "PROJECT_ID","PROJECTS"."PROJECT_NAME" "PROJECT_NAME","PROJECTS"."STATUS" "STATUS","PROJECTS"."LAST_DAYS_TO_SHOW_IN_REPORTS" "LAST_DAYS_TO_SHOW_IN_REPORTS" FROM "DEMO5"."PROJECTS" "PROJECTS" WHERE "PROJECTS"."ORA_ARCHIVE_STATE"='0'
kkoqbc: optimizing query block SEL$1 (#0)
.
.
.

A 10046 trace file contains no indication for ORA_ARCHIVE_STATE at all.

Comparing 10053 trace files of the statement select * from projects between two executions – one with the default behavior where the predicate is added and the second with “alter session set ROW ARCHIVAL VISIBILITY = ALL” which returns all the records with no filtering on ORA_ARCHIVE_STATE – shows only one significant difference: under Compilation Environment Dump we see that ilm_filter = 0 in the former and ilm_filter = 1 in the latter.

So the predicate on ORA_ARCHIVE_STATE is probably added by neither the Parser nor the Query Transformer. I don’t know who does add it and when, but it seems that it is not done in the “standard” way Oracle usually do such things. Perhaps if it would have been done in the standard way, this bug (look at the “The Bad News” section) would not have happened.

Implementing Arc Relationships with Virtual Columns

Oren Nakdimon Leave a Reply

Virtual columns were added in Oracle 11g Release 1. We can implement the same concept using views, but one of the advantages that I see in virtual columns is that we can define foreign key constraints on them. Well, we can define foreign key constraints on views as well, but only in DISABLE NOVALIDATE mode, which makes them a decoration rather than a data integrity protector. So to be more precise, we can define enabled foreign key constraints on virtual columns.

This post is about using virtual columns for implementing arc relationships.

In the following ERD, the arc represents the rule “each Entity Address must be owned by either a Person or a Company”:
arc
There are three common ways to implement this logical data model.

Multiple Tables

We can split the ENTITY_ADDRESS entity into two tables – say, PERSON_ADDRESSES and COMPANY_ADDRESSES. Disadvantages of this solution are that we need to maintain two (or more) tables and the code parts that manipulate them, although they are very similar. In addition, if later on we need to add another entity to the relationship (for example, Store, in addition to Person and Company) we will have to add another join table – STORE_ADDRESSES – and the relevant code.

A Single Table with Dedicated Columns

We can implement the ENTITY_ADDRESS entity as a single table – say, ENTITY_ADDRESSES – with a dedicated column for each referenced entity – PERSON_ID and COMPANY_ID (in addition to ADDRESS_ID) – and a check constraint to make sure one and only one of the columns PERSON_ID and COMPANY_ID is not null. The disadvantage of this solution is that we still need to maintain the dedicated columns and the code parts that manipulate them, although they are very similar. And if we need to add another entity to the relationship we will have to add another column to the table and to add the relevant code to manipulate the new column.

A Generic Table

We can implement the ENTITY_ADDRESS entity as a single table – say, ENTITY_ADDRESSES – with a generic column for the referenced entity and a column that stores the entity type to which the generic column references – e.g., ADDRESS_ID, OWNER_TYPE_ID, OWNER_ID. The disadvantage of this solution is that we cannot define foreign key constraints from ENTITY_ADDRESSES to the PEOPLE and COMPANIES tables.

Using Virtual Columns

I’d like to suggest another solution, that, using virtual columns, overcomes the above disadvantages.
This solution is based on the generic table one, but we’ll add also a virtual column for each referenced entity. The purpose of the virtual columns is just for enforcing the referential integrity, and the application should not be aware of them. Therefore, if another entity should be added afterwards, we will add another virtual column, but we will have no code changes to make.

Here it is:

> create table people (
>   id integer not null primary key,
>   first_name varchar2(30) not null,
>   last_name varchar2(30) not null
> );

Table created.

> create table companies (
>   id integer not null primary key,
>   name varchar2(100) not null,
>   number_of_employees integer,
>   description varchar2(1000)
> );

Table created.

> create table addresses (
>   id integer not null primary key,
>   street varchar2(30),
>   house_number varchar2(10),
>   city varchar2(30),
>   country varchar2(30)
> );

Table created.
> create table entity_addresses (
>   address_id integer not null references addresses,
>   owner_type_id char(1) not null check (owner_type_id in ('P','C')),
>   owner_id integer not null,
>   --
>   primary key (owner_id,owner_type_id,address_id),
>   --
>   person_id generated always as (decode(owner_type_id,'P',owner_id)) virtual
>     constraint address_fk_people references people,
>   company_id generated always as (decode(owner_type_id,'C',owner_id)) virtual
>     constraint address_fk_companies references companies
> );

Table created. 
> insert into people (id,first_name,last_name) values (1,'John','Doe');

1 row created.

> insert into companies (id,name) values (101,'DB Oriented');

1 row created.

> insert into addresses (id,street,city,country) values (1,'Dekel','Zurit','Israel');

1 row created.

> insert into entity_addresses (address_id,owner_type_id,owner_id) 
> values (1,'P',1);

1 row created.

> insert into entity_addresses (address_id,owner_type_id,owner_id) 
> values (1,'P',101);
insert into entity_addresses (address_id,owner_type_id,owner_id) values (1,'P',101)
*
ERROR at line 1:
ORA-02291: integrity constraint (DEMO5.ADDRESS_FK_PEOPLE) violated - parent key not found


> insert into entity_addresses (address_id,owner_type_id,owner_id) 
> values (1,'C',1);
insert into entity_addresses (address_id,owner_type_id,owner_id) values (1,'C',1)
*
ERROR at line 1:
ORA-02291: integrity constraint (DEMO5.ADDRESS_FK_COMPANIES) violated - parent key not found


> insert into entity_addresses (address_id,owner_type_id,owner_id) 
> values (1,'C',101);

1 row created.

Hiding the Virtual Columns

Since the virtual columns in this solution are just for enforcing the referential integrity, I prefer to hide them, so adding new entities to the arc (or removing entities from it) will be transparent to the application.

Invisible Columns

In Oracle 12c we can do it by making these columns invisible.

> alter table entity_addresses modify (
>   person_id invisible,
>   company_id invisible
> );

Table altered.

> select * from entity_addresses;

ADDRESS_ID OWN   OWNER_ID
---------- --- ----------
         1 P            1
         1 C          101

Editioning Views

Being an Edition-Based Redefinition (EBR) evangelist, my application code never refers to tables. Each table is covered by an editioning view, and the application code refers only to views. This practice enables to hide the virtual columns very easily, simply by not including them in the editioning view:

> create editioning view entity_addresses_v as
>   select address_id,
>          owner_type_id,
>          owner_id
>   from entity_addresses;

View created.

Unfortunately, EBR is not widely used :-(
You can, however, use the same idea in non-EBR environment, by creating a regular view rather than an editioning view, and refer to this view from the application code.

PL/SQL Functions in the WITH Clause and Read (In)Consistency

A question was raised in Martin Widlake’s session this morning in OUG Ireland 2016. The (excellent) session was about the performance of calling PL/SQL functions from SQL, and Martin scared the audience (or at least tried to scare…) with the fact that read consistency is not kept (with respect to the base SQL statement) when the PL/SQL functions are called from that SQL statement.
The question that was raised was whether this is also true when the PL/SQL functions are embedded in the WITH clause – a new 12c feature about which you can read in detail in Write Less with More – Part 8.

Here is a small example that shows that read consistency is not kept in this case as well.

create table t (x number);

with function f return number is
    c number;
  begin
    select count(*) into c from t;
    dbms_lock.sleep(10);
    return c;
  end f;
select f from dual
union all
select f from dual
/

Now, right after starting executing the previous query, from another session we do the following:

insert into t (x) values (1);
commit;

and the result that we get from the query is:

0
1

<