Category: SQL

Optimization that Violates Data Integrity

The Constraint Optimization series:

In the previous post I showed that Oracle does a nice optimization when we add a new (nullable with no default value) column with an inline check constraint in a single ALTER TABLE statement.
However, there is one case where this optimization allows for data integrity violation instead of forbidding it (which makes it a bug, in this specific case, rather than an optimization). It happens when the check constraint is “column IS NOT NULL”.

I’ll setup a table with a few records for demonstrating this:

SQL> create table t (x number);

Table created.

SQL> insert into t select rownum from dual connect by level<=10;

10 rows created.

SQL> commit;

Commit complete.

First, let's try to add a column with an out-of-line constraint, so the optimization will not apply:

SQL>
SQL> ALTER TABLE T ADD (
  2    C NUMBER,
  3    CONSTRAINT C_CHK CHECK (C IS NOT NULL)
  4  );
  CONSTRAINT C_CHK CHECK (C IS NOT NULL)
             *
ERROR at line 3:
ORA-02293: cannot validate (TRANZMATE_PRODUCTION.C_CHK) - check constraint violated

It failed, and that's good.

Now, let's try to add the same column with the same check constraint, but this time we'll use an inline constraint:

SQL> ALTER TABLE T ADD (
  2    C NUMBER CONSTRAINT C_CHK CHECK (C IS NOT NULL)
  3  );

Table altered.

And it succeeded, although it should have failed.
So now we have a validated constraint, stating that C IS NOT NULL, and at the same time the table contains records, and in all of them C IS NULL:

SQL> select table_name,search_condition,status,validated,rely
  2  from user_constraints where constraint_name='C_CHK';

TABLE_NAME SEARCH_CONDITION     STATUS     VALIDATED  RELY
---------- -------------------- ---------- ---------- ------------
T          C IS NOT NULL        ENABLED    VALIDATED

SQL> select * from t;

         X          C
---------- ----------
         1
         2
         3
         4
         5
         6
         7
         8
         9
        10

10 rows selected.

Since we're talking about adding a nullable column with no default value to an existing table that already contains rows, specifying a check constraint that states that this column is not nullable doesn't make much sense, and if someone tries to do it it's probably their bug. Still, it would be nice to get an error in this case, rather than violating the data integrity.

Why does it happen?

When Oracle validates some check constraint, it checks if there are records for which the constraint condition is FALSE. When the column is NULL, then usually the condition evaluates to NULL, not to FALSE, so the constraint is valid. For example, when C is NULL, then "C>0" is NULL (neither FALSE nor TRUE).
I guess this is why Oracle skips the validation step completely as part of this optimization.

What should happen?

It's good that Oracle doesn't validate each and every record, as it's really unnecessary, but to make this optimization foolproof it should validate a single value – NULL.

Optimization of Check Constraint Creation

The Constraint Optimization series:

I have a table T with many records and I want to add a new column C to this table with some check constraint on C.

Does it matter if I use the following statement

ALTER TABLE T ADD (C NUMBER, CONSTRAINT C_CHK CHECK (C>0));

or this one

ALTER TABLE T ADD (C NUMBER CONSTRAINT C_CHK CHECK (C>0));

?
Note that the only difference is the comma that appears in the first option and not in the second one.

It certainly matters.
In the first option the constraint is an out-of-line (“table-level”) check constraint, while in the second option it is an inline (“column-level”) one.
With out-of-line check constraint Oracle scans the table, and validates that each and every record satisfies the constraint. This is similar to adding the column in one statement, and then adding the constraint in a second statement.
But when we add the column with an inline check constraint in a single statement, Oracle optimizes this process. Since the added column is NULL for all the existing records, it is actually unnecessary to access the records in order to perform the validation.

We can see a small example (tested in 11.2.0.4, 12.1.0.2, 12.2.0.1). First, adding the column with the out-of-line check constraint:

> create table t (x varchar2(1000));

Table created.

> insert /*+ append */ into t
> select lpad(rownum,1000,'x') from dual connect by level<=1e6;

1000000 rows created.

> commit;

Commit complete.

> col value new_value value_0 noprint
> select b.value from v$statname a, v$mystat b 
> where a.statistic# = b.statistic# and a.name = 'session logical reads';

> set timing on
> ALTER TABLE T ADD (C NUMBER, CONSTRAINT C_CHK CHECK (C>0));

Table altered.

Elapsed: 00:00:04.27

> set timing off
> select b.value-&VALUE_0 logical_reads from v$statname a, v$mystat b
> where a.statistic# = b.statistic# and a.name = 'session logical reads';

LOGICAL_READS
-------------
       143243

Note the elapsed time and the number of logical reads – most of them are due to accessing all the rows of T in order to validate the check constraint.
Now, let’s see the same example but with the inline check constraint instead of the out-of-line one:

> drop table t purge;

Table dropped.

> create table t (x varchar2(1000));

Table created.

> insert /*+ append */ into t
> select lpad(rownum,1000,'x') from dual connect by level<=1e6;

1000000 rows created.

> commit;

Commit complete.

> col value new_value value_0 noprint
> select b.value from v$statname a, v$mystat b
> where a.statistic# = b.statistic# and a.name = 'session logical reads';

> set timing on
> ALTER TABLE T ADD (C NUMBER CONSTRAINT C_CHK CHECK (C>0));

Table altered.

Elapsed: 00:00:00.01

> set timing off
> select b.value-&VALUE_0 logical_reads from v$statname a, v$mystat b
> where a.statistic# = b.statistic# and a.name = 'session logical reads';

LOGICAL_READS
-------------
          359

Much faster, with significantly less logical reads, since accessing the actual records is omitted.

A Recipe for Summoning the RBO Monster (even in Oracle 12c): On Delete Cascade, Function-Based Index and Missing Table Statistics

The last version of Oracle in which CHOOSE was officially supported as an OPTIMIZER_MODE parameter value was 9.2.
This is what the documentation of Oracle 9.2 says about it:

choose
The optimizer chooses between a cost-based approach and a rule-based approach based on whether statistics are available.
If the data dictionary contains statistics for at least one of the accessed tables, then the optimizer uses a cost-based approach and optimizes with a goal of best throughput. If the data dictionary contains only some statistics, then the cost-based approach is used, and the optimizer must guess the statistics for the subjects without any statistics. This can result in sub-optimal execution plans. If the data dictionary contains no statistics for any of the accessed tables, then the optimizer uses a rule-based approach.

In subsequent versions CHOOSE does not appear in the documentation as a valid value, and the “What’s New in Oracle Performance?” chapter in the documentation of Oracle 10.1 includes this sentence:

Some consequences of the desupport of RBO are:
• CHOOSE and RULE are no longer supported as OPTIMIZER_MODE initialization parameter values …

But the Oracle database keeps using CHOOSE, and therefore keeps potentially using the RBO, internally, even in version 12c. And last week I hit a performance issue that was caused because of this fact and some poor circumstances.

Here is a simplified example, running on Oracle 12.1.0.2.

We have two tables – PARENTS and CHILDREN – with an “on delete cascade” foreign key constraint.

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

Table created.

> insert into parents select rownum from dual connect by level<=10000;

10000 rows created.

> create table children (
    id number not null primary key,
    parent_id number not null references parents (id) on delete cascade,
    filler varchar2(4000)
  );

Table created.

> insert into children
  select rownum,mod(rownum,10000)+1,lpad('x',4000,'x')
  from dual
  connect by level<=100000;

100000 rows created.

> commit;

Commit complete.

There is an index that supports the foreign key, and it is a function-based index. Note that the leading column of the index is simply the referencing column.

> create index children_idx on children (parent_id, substr(filler,1,10));

Index created.

Now let’s delete a record from the PARENTS table.

> alter system flush buffer_cache;

System altered.

> set timing on
> delete parents where id=1;

1 row deleted.

Elapsed: 00:00:27.80
> set timing off
> roll
Rollback complete.

Why did it take so much time?

When we delete a record from PARENTS, Oracle implicitly deletes the child records of this parent (because of the “on delete cascade” foreign key). This is the recursive SQL statement:

 delete from "DEMO"."CHILDREN" where "PARENT_ID" = :1

Oracle uses the CHOOSE optimizer mode for the implicit deletion from CHILDREN.

> select value,isdefault
  from v$sql_optimizer_env
  where sql_id='f7j1aq9z8k6r1'
  and child_number=0
  and name='optimizer_mode';

VALUE                ISDEFAULT
-------------------- ---------
choose               NO

1 row selected.

Unfortunately I forgot to gather statistics on the CHILDREN table. The lack of statistics means that Oracle chose to use the rule-based optimizer for the implicit DELETE statement.
And this means that it cannot use the CHILDREN_IDX index, because it is a function-based index.

> SELECT * FROM table(DBMS_XPLAN.DISPLAY_CURSOR('f7j1aq9z8k6r1',0));

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID  f7j1aq9z8k6r1, child number 0
-------------------------------------
 delete from "DEMO"."CHILDREN" where "PARENT_ID" = :1

Plan hash value: 3379301338

---------------------------------------
| Id  | Operation          | Name     |
---------------------------------------
|   0 | DELETE STATEMENT   |          |
|   1 |  DELETE            | CHILDREN |
|*  2 |   TABLE ACCESS FULL| CHILDREN |
---------------------------------------

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

   2 - filter("PARENT_ID"=:1)

Note
-----
   - rule based optimizer used (consider using cbo)


23 rows selected.

If I execute the DELETE FROM CHILDREN statement explicitly, the CBO chooses the good execution plan that uses the index, because the statistics on the index have been automatically gathered during the index creation. It’s the missing statistics on the table that caused the implicit (recursive) DELETE statement to use the RBO.

> alter system flush buffer_cache;

System altered.

> set timing on
> delete from "DEMO"."CHILDREN" where "PARENT_ID" = 1;

10 rows deleted.

Elapsed: 00:00:00.21
> set timing off
> select * from table(dbms_xplan.display_cursor());

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID  0cm664fx8b944, child number 0
-------------------------------------
delete from "DEMO"."CHILDREN" where "PARENT_ID" = 1

Plan hash value: 1030488021

----------------------------------------------------------------------------------
| Id  | Operation         | Name         | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------
|   0 | DELETE STATEMENT  |              |       |       |     2 (100)|          |
|   1 |  DELETE           | CHILDREN     |       |       |            |          |
|*  2 |   INDEX RANGE SCAN| CHILDREN_IDX |    10 | 40100 |     2   (0)| 00:00:01 |
----------------------------------------------------------------------------------

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

   2 - access("PARENT_ID"=1)


19 rows selected.

Even if I didn’t have statistics on the index, the CBO would still choose the index for performing the explicit DELETE, because it would use dynamic sampling:

> exec dbms_stats.delete_index_stats (user,'CHILDREN_IDX')

PL/SQL procedure successfully completed.

> delete from "DEMO"."CHILDREN" where "PARENT_ID" = 3;

10 rows deleted.

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

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID  a8x8wszgx1g6r, child number 0
-------------------------------------
delete from "DEMO"."CHILDREN" where "PARENT_ID" = 3

Plan hash value: 1030488021

----------------------------------------------------------------------------------
| Id  | Operation         | Name         | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------
|   0 | DELETE STATEMENT  |              |       |       |     3 (100)|          |
|   1 |  DELETE           | CHILDREN     |       |       |            |          |
|*  2 |   INDEX RANGE SCAN| CHILDREN_IDX |  1066 |  2111K|     3   (0)| 00:00:01 |
----------------------------------------------------------------------------------

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

   2 - access("PARENT_ID"=3)

Note
-----
   - dynamic statistics used: dynamic sampling (level=2)


23 rows selected.

Finally, if the index were a regular one rather than function-based, then a good plan would have been chosen for the recursive SQL, because the RBO prefers an index range scan over a full table scan:

> drop index children_idx;

Index dropped.

> create index children_idx on children (parent_id, id);

Index created.

> alter system flush buffer_cache;

System altered.

> set timing on
> delete parents where id=4;

1 row deleted.

Elapsed: 00:00:00.14
> set timing off

> select * from table(DBMS_XPLAN.DISPLAY_CURSOR('f7j1aq9z8k6r1',0));

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID  f7j1aq9z8k6r1, child number 0
-------------------------------------
 delete from "DEMO"."CHILDREN" where "PARENT_ID" = :1

Plan hash value: 1030488021

------------------------------------------
| Id  | Operation         | Name         |
------------------------------------------
|   0 | DELETE STATEMENT  |              |
|   1 |  DELETE           | CHILDREN     |
|*  2 |   INDEX RANGE SCAN| CHILDREN_IDX |
------------------------------------------

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

   2 - access("PARENT_ID"=:1)

Note
-----
   - rule based optimizer used (consider using cbo)


23 rows selected.

The “Guardian Trigger” Design Pattern

Suppose that every time we add records into the T1 table we have to do some additional stuff.
One option to implement this is by using an AFTER INSERT trigger that will perform this additional stuff, but I really dislike this option (mainly because the code becomes hidden in a way, and there may be a negative impact on performance).
I prefer writing a procedure that inserts the records into T1 and performs this additional stuff. 

CREATE OR REPLACE PACKAGE my_package AS 
    PROCEDURE insert_t1 (...);
END my_package;
/
CREATE OR REPLACE PACKAGE BODY my_package AS 
    PROCEDURE insert_t1 (...) IS 
    BEGIN 
        INSERT INTO t1 ...;
        -- do some additional stuff, that must be done
        -- whenever inserting new records into T1 
    END insert_t1;
END my_package;
/

But what will happen if someone (perhaps even myself in one year from now) will insert records into T1 from another place (in another procedure or directly via an INSERT statement) and will not know (or maybe forget) about the mandatory additional stuff?
The best thing would be to make this procedure the one and only way to insert records into T1. But how to do it?
I like doing it in a way that I call “the guardian trigger”, using a combination of a global variable and a trigger (but this trigger doesn’t do actual stuff, it is only a gatekeeper).

I’m adding a Boolean global variable and initialize it to FALSE. I change it to TRUE at the beginning of the insert_t1 procedure, and make sure to set it back to FALSE when leaving the procedure.

CREATE OR REPLACE PACKAGE BODY my_package AS 
    g_is_insert_into_t1_allowed BOOLEAN := FALSE; 
    PROCEDURE insert_t1 (...) IS 
    BEGIN 
        g_is_insert_into_t1_allowed := TRUE; 
        INSERT INTO t1 ...;
        -- do some additional stuff, that must be done
        -- whenever inserting new records into T1 
        g_is_insert_into_t1_allowed := FALSE; 
    EXCEPTION 
        WHEN OTHERS THEN 
            g_is_insert_into_t1_allowed := FALSE; 
            RAISE; 
    END insert_t1;
END my_package;
/

Now I’m adding a function – a public function – to get the current value of the global variable:

CREATE OR REPLACE PACKAGE my_package AS 
    FUNCTION is_insert_into_t1_allowed RETURN BOOLEAN;  
    PROCEDURE insert_t1 (...);
END my_package;
/

CREATE OR REPLACE PACKAGE BODY my_package AS 
    g_is_insert_into_t1_allowed BOOLEAN := FALSE;
    FUNCTION is_insert_into_t1_allowed RETURN BOOLEAN IS 
    BEGIN 
        RETURN g_is_insert_into_t1_allowed; 
    END is_insert_into_t1_allowed;  
    PROCEDURE insert_t1 (...) IS 
    BEGIN 
        g_is_insert_into_t1_allowed := TRUE;
        INSERT INTO t1 ...;
        -- do some additional stuff, that must be done
        -- whenever inserting new records into T1 
        g_is_insert_into_t1_allowed := FALSE;
    EXCEPTION 
        WHEN OTHERS THEN 
            g_is_insert_into_t1_allowed := FALSE;
            RAISE;
    END insert_t1;
END my_package;
/

And as the final step I’m creating a statement level trigger – BEFORE INSERT ON T1; a trigger that doesn’t change anything, it just checks that the global variable is currently set to TRUE.

CREATE OR REPLACE TRIGGER t1_trig
    BEFORE INSERT ON t1
BEGIN 
    IF NOT my_package.is_insert_into_t1_allowed THEN 
        raise_application_error(-20000,
          'INSERTs into T1 are allowed only via my_package.insert_t1');
    END IF;
END t1_trig;
/

So if I’ll try to insert records into T1 not via the procedure the trigger will not allow it, it will raise an exception saying “INSERTs into T1 are allowed only via my_package.insert_t1”.
Obviously this pattern can be implemented for other operations, not only for INSERT.

INSERT into a View with a GROUP BY Clause

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

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.

 

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

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)

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)

Note: I wrote this post in May 2016, when the latest released version of Oracle was 12.1.
As of Oracle 12.2 COLLECT DISTINCT is supported in PL/SQL.

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.