EBR – Part 10: Data Dictionary Views for Editioning Views

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

Visit the index page for all the parts of the series

Introduction

In a previous post I wrote about Editioning Views and their significant role in online application upgrades using EBR.
In this post we’ll see how editioning views are represented in the data dictionary views.

I’ll discuss only the USER_* views, but everything is true for the corresponding ALL_*, DBA_* and CDB_* views as well

Setup

At this point, after completing three upgrades already (in part 3, part 5 and part 9), our latest edition is V3, and we have the PEOPLE$0 table and two actual instances of the PEOPLE editioning view (one in V2 and one in V3).

Let’s create a new edition – V4:

-- connected as system
ORA$BASE> create edition v4;

Edition created.

ORA$BASE> grant use on edition v4 to demo_ebr;

Grant succeeded.

In the new edition, let’s change the PEOPLE editioning view and give different aliases to some of the columns:

ORA$BASE> conn demo_ebr
Enter password:
Connected.
ORA$BASE> @set_edition v4

Session altered.

V4> create or replace editioning view people as
  2  select id,
  3         first_name   as given_name,
  4         last_name    as family_name,
  5         phone_number,
  6         eye_color
  7  from   people$0;

View created.

And let’s create also a regular view on top of PEOPLE:

V4> create view brown_eyed_people as
  2  select *
  3  from   people
  4  where  eye_color = 'Brown';

View created.

Note that the BROWN_EYED_PEOPLE regular view references the PEOPLE editioning view. As described and emphasized, only editioning views reference tables; all the other editioned objects (including regular views) should reference editioning views (or other regular views) only.

USER_OBJECTS

In USER_OBJECTS and in USER_OBJECTS_AE both regular views and editioning views appear simply as views (the OBJECT_TYPE column contains the value “VIEW”). Editioning views have no distinctive indication here.

V4>  select object_type,
  2         object_name,
  3         edition_name
  4  from   user_objects
  5  where  object_type in ('TABLE', 'VIEW')
  6  order by 1,2;

OBJECT_TYPE  OBJECT_NAME                    EDITION_NAME
------------ ------------------------------ ------------
TABLE        PEOPLE$0
VIEW         BROWN_EYED_PEOPLE              V4
VIEW         PEOPLE                         V4

V4>  select object_type,
  2         object_name,
  3         edition_name
  4  from   user_objects_ae
  5  where  object_type in ('TABLE', 'VIEW')
  6  order by 1,2,3;

OBJECT_TYPE  OBJECT_NAME                    EDITION_NAME
------------ ------------------------------ ------------
TABLE        PEOPLE$0
VIEW         BROWN_EYED_PEOPLE              V4
VIEW         PEOPLE                         V2
VIEW         PEOPLE                         V3
VIEW         PEOPLE                         V4

USER_VIEWS

In USER_VIEWS and in USER_VIEWS_AE there is a column – EDITIONING_VIEW – that tells whether a view is an editioning one or not:

V4>  select view_name,
  2         editioning_view
  3  from   user_views
  4  order by 1;

VIEW_NAME            EDITIONING_VIEW
-------------------- -----------------
BROWN_EYED_PEOPLE    N
PEOPLE               Y

V4>  select view_name,
  2         editioning_view,
  3         edition_name
  4  from   user_views_ae
  5  order by 1,3;

VIEW_NAME            EDITIONING_VIEW   EDITION_NAME
-------------------- ----------------- ------------
BROWN_EYED_PEOPLE    N                 V4
PEOPLE               Y                 V2
PEOPLE               Y                 V3
PEOPLE               Y                 V4

USER_EDITIONING_VIEWS

There is a dedicated data dictionary view – USER_EDITIONING_VIEWS – that contains only the editioning views.
For each editioning view we can see the view name and the name of the table covered by this view. Recall that (in a specific edition) an editioning view covers one and only one table, and a table may be covered by one editioning view at the most.

V4> select * from user_editioning_views;

VIEW_NAME       TABLE_NAME
--------------- ---------------
PEOPLE          PEOPLE$0

And there is a corresponding USER_EDITIONING_VIEWS_AE view:

V4> select * from user_editioning_views_ae;

VIEW_NAME       TABLE_NAME      EDITION_NAME
--------------- --------------- ------------
PEOPLE          PEOPLE$0        V2
PEOPLE          PEOPLE$0        V3
PEOPLE          PEOPLE$0        V4

USER_EDITIONING_VIEW_COLS

There is a dedicated data dictionary view – USER_EDITIONING_VIEW_COLS – that contains for every editioning view all its columns and the corresponding projected columns from the covered table.

V4> select *
  2  from user_editioning_view_cols
  3  order by 1,2;

VIEW_NAME       VIEW_COLUMN_ID VIEW_COLUMN_NAME     TABLE_COLUMN_ID TABLE_COLUMN_NAME
--------------- -------------- -------------------- --------------- --------------------
PEOPLE                       1 ID                                 1 ID
PEOPLE                       2 GIVEN_NAME                         2 FIRST_NAME
PEOPLE                       3 FAMILY_NAME                        3 LAST_NAME
PEOPLE                       4 PHONE_NUMBER                       4 PHONE_NUMBER
PEOPLE                       5 EYE_COLOR                          5 EYE_COLOR

There is also a corresponding USER_EDITIONING_VIEW_COLS_AE view. Let’s use it to see how the PEOPLE editioning view has evolved in the last 3 editions:

V4> select v2,v3,v4
  2  from   (select *
  3          from   user_editioning_view_cols_ae
  4          where  view_name = 'PEOPLE')
  5  pivot(
  6    max(view_column_name || nullif(' [' || nullif(table_column_name, view_column_name) || ']', ' []'))
  7    for edition_name in ('V2' as v2, 'V3' as v3, 'V4' as v4)
  8  )
  9  order  by view_column_id;

V2             V3             V4
-------------- -------------- -------------------------
ID             ID             ID
FIRST_NAME     FIRST_NAME     GIVEN_NAME [FIRST_NAME]
LAST_NAME      LAST_NAME      FAMILY_NAME [LAST_NAME]
PHONE_NUMBER   PHONE_NUMBER   PHONE_NUMBER
               EYE_COLOR      EYE_COLOR

For other parts of the EBR series, please visit the index page.

Wrong Results with IOT, Added Column and Secondary Index

Update: I reported about this bug to Oracle, and they fixed it in version 20.1

I found a “wrong results” bug yesterday, easily reproduced in 11g, 12c and 18c.

In short, we may get wrong results under the following circumstances:

We have an Index-Organized Table (IOT) with multi-column primary key, populated with rows
The table has a secondary index on part of the primary key columns
We add another column to the existing IOT
We select from the IOT while accessing it via the secondary index

Following is a simple example (also uploaded to livesql.oracle.com).

We create an index-organized table named iot, including the columns x and y.
The primary key is comprised of both columns.

SQL> create table iot (
  2    x number,
  3    y number,
  4    constraint iot_pk primary key (x,y)
  5  ) organization index;

Table created.

We create a secondary index on y:

SQL> create index secondary_idx on iot (y);

Index created.

We populate the table with some rows:

SQL> insert into iot select rownum,mod(rownum,3)
  2  from dual
  3  connect by level<=7;

7 rows created.

SQL> commit;

Commit complete.

Now we add another column, z, to the table, and we set some value in z in one row:

SQL> alter table iot add z number;

Table altered.

SQL> update iot set z=42 where x=1;

1 row updated.

SQL> commit;

Commit complete.

So currently this is the content of the table:

SQL> select * from iot;

         X          Y          Z
---------- ---------- ----------
         1          1         42
         2          2
         3          0
         4          1
         5          2
         6          0
         7          1

7 rows selected.

Note that z contains the value 42 in the first row and null in all the other rows.
But if we access the table via the secondary index – for example getting all the records where y=1 – the value of z from the first row “spills over” into the other rows, and we get wrong results:

SQL> set autotrace on explain
SQL> select * from iot where y=1;

         X          Y          Z
---------- ---------- ----------
         1          1         42
         4          1         42
         7          1         42

3 rows selected.


Execution Plan
----------------------------------------------------------
Plan hash value: 177722221

-----------------------------------------------------------------------------------
| Id  | Operation         | Name          | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |               |     3 |   117 |     1   (0)| 00:00:01 |
|*  1 |  INDEX UNIQUE SCAN| IOT_PK        |     3 |   117 |     1   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN| SECONDARY_IDX |     1 |       |     1   (0)| 00:00:01 |
-----------------------------------------------------------------------------------

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

   1 - access("Y"=1)
   2 - access("Y"=1)

Note
-----
   - dynamic sampling used for this statement (level=2)

It seems that the problem is not with the secondary index itself, as we get the correct rows, but something with accessing the table via the secondary index causes the wrong results.
If we hint the query to access the table directly we get the correct results:

SQL> select /*+ index_ffs (iot) */ * from iot where y=1;

         X          Y          Z
---------- ---------- ----------
         1          1         42
         4          1
         7          1

3 rows selected.


Execution Plan
----------------------------------------------------------
Plan hash value: 3252171408

-------------------------------------------------------------------------------
| Id  | Operation            | Name   | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------
|   0 | SELECT STATEMENT     |        |     3 |   117 |     2   (0)| 00:00:01 |
|*  1 |  INDEX FAST FULL SCAN| IOT_PK |     3 |   117 |     2   (0)| 00:00:01 |
-------------------------------------------------------------------------------

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

   1 - filter("Y"=1)

Note
-----
   - dynamic sampling used for this statement (level=2)

EBR – Part 9: Adding a New Column

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

Visit the index page for all the parts of the series

Introduction

In part 7 (“Editioning Views”) I introduced our third development use case – adding a new column that represents a new business logic.

I emphasize the fact it’s a new business logic, because sometimes we add new columns that come instead of existing columns, for replacing an existing business logic. As we’ll see in future posts, such cases require some additional handling in online upgrades.

Now, after part 8 (“The Last Planned Downtime”), in which we converted our schema to use editioning views and made it fully ready for online upgrades, we can implement the requirement in an online way.

The Task

We want to add a new attribute – Eye Color – to the Person entity.
For that we need to add a new column – EYE_COLOR – to the PEOPLE table, add make the corresponding changes in the PEOPLE_DL and APP_MGR packages (adding an input parameter – i_eye_color – to the people_dl.add procedure and changing the implementation of people_dl.add and app_mgr.do_something accordingly).

The Current State

At this point, V2 is our latest edition and it is exposed to the production clients. When connected to V2, we see the following objects: Continue reading “EBR – Part 9: Adding a New Column”

EBR – Part 8: The Last Planned Downtime

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

Visit the index page for all the parts of the series

Introduction

I concluded the previous post by stating that the application code should never reference tables directly; instead, every table should be covered by an editioning view and the application code should reference the views.

Starting from Scratch?

If you start developing a new system from scratch, I strongly recommend to follow this practice, even if initially there is no requirement for online upgrades. That way, you will be ready for online upgrades from day one, so should such a requirement arise at a later stage you will not need to change anything – neither your data structures nor your code. The only overhead is maintaining an editioning view for every table, and I’m sure you’ll realize very quickly that this overhead is negligible. As long as downtime during your upgrades is acceptable, you only need to enable your schema owner for editions (a one-time operation, as shown in part 3), and (by default) you will simply use a single edition – the default one.

Existing Systems

If you have an existing system, and you want to enjoy the benefits of online upgrades and zero downtime, you need to do a one-time conversion of your schema – to cover all the tables by editioning views. Actually, even if you have an existing system and you do not want to enjoy these benefits, I would still recommend getting ready for online upgrades now. The rationale is the same as in the previous section (“Starting from Scratch?”).

Converting the Demo Schema

Let’s handle first the demo schema I’ve been using in this post series. We currently have the following objects: Continue reading “EBR – Part 8: The Last Planned Downtime”

EBR – Part 7: Editioning Views

This is part 7 of a post series about EBR.

Visit the index page for all the parts of the series

Introduction

Our next use case is adding a column that represents a new logic to the PEOPLE table, and making the corresponding changes in the PEOPLE_DL and APP_MGR packages. Of course, as we speak about EBR, the upgrade from the previous version to the new one should be online.
An online upgrade means that the application users should be able to continue working uninterruptedly. The code objects that they use should remain valid and available at any time.
In addition to the challenges raised from the first and second use cases, the current use case introduces another challenge. And unlike the previous challenges, this one cannot be overcome just by using a new edition.

The Current State

At this point, after completing two upgrades already – in part 3 and part 5, we have three editions, with the following actual objects:

V2> select object_name,object_type,status, edition_name
  2  from   user_objects
  3  order  by object_name,object_type;

OBJECT_NAME  OBJECT_TYPE  STATUS  EDITION_NAME
------------ ------------ ------- ------------
APP_MGR      PACKAGE      VALID   ORA$BASE
APP_MGR      PACKAGE BODY VALID   V2
PEOPLE       TABLE        VALID
PEOPLE_DL    PACKAGE      VALID   V2
PEOPLE_DL    PACKAGE BODY VALID   V2
PEOPLE_PK    INDEX        VALID

6 rows selected.

As discussed in part 6, a table is a non-editioned object, and therefore we have a single instance of the PEOPLE table – referenced by all the instances of the PEOPLE_DL package body in all the editions.

The Task

We want to add a new attribute – Eye Color – to the Person entity.
For that we need to add a new column – EYE_COLOR – to the PEOPLE table, add make the corresponding changes in the PEOPLE_DL and APP_MGR packages (adding an input parameter – i_eye_color – to the people_dl.add procedure and changing the implementation of people_dl.add and app_mgr.do_something accordingly).

Our Problem

If we try to add the new column to the table, then Continue reading “EBR – Part 7: Editioning Views”

The Performance of the FIRST and LAST Functions

Overview

One of the first posts I wrote in this blog (almost five years ago) was about the FIRST and LAST aggregate functions.
These functions are, in a way, extended versions of the much more popular aggregate functions MIN and MAX.
MIN and MAX allow you (conceptually) to sort a group of rows by some column and return the value of that column from the first or last row in the sorted group.
The FIRST and LAST functions extend this ability, and allow you to sort a group of rows by one column, but return the value of another column from the first or last row in the sorted group.
You are welcome to read the original post for more details about the functionality and syntax of these functions, and for seeing some examples.

In this post I’d like to focus on the performance of the FIRST and LAST functions.
Continue reading “The Performance of the FIRST and LAST Functions”

EBR – Part 6: Editionable and Non-Editionable, Editioned and Non-Editioned

This is part 6 of a post series about EBR.
In part 1 we created the baseline model and code – a table (PEOPLE) and two packages (PEOPLE_DL and APP_MGR).
In part 2 we saw that even a simple change – a package body compilation – can be dangerous in a busy system.
In part 3 we learned about editions and how they can be used for solving the problems described in part 2, so changes can be applied in both online and safe way. I discussed enabling, creating and using editions; session edition; and editioned objects.
In part 4 we discussed another challenge of online upgrades – invalidation of dependent objects.
In part 5 we saw the importance of explicit actualization of dependent objects.
In this part we’ll dive into the differences between editioned and non-editioned objects, editionable and non-editionable object types, and the relationships between them.

Visit the index page for all the parts of the series

In a previous post, we enabled the user DEMO_EBR for editions. As a result, some of the existing objects of DEMO_EBR (the package specs and package bodies) have become editioned, and the others (a table and an index) have not. Why is that?
Before I answer this question, let’s see what an editioned object is exactly, and what a non-editioned object is.

Editioned and Non-Editioned Objects

An editioned object is Continue reading “EBR – Part 6: Editionable and Non-Editionable, Editioned and Non-Editioned”

COLLECT INTO inside a Cursor Definition?

I have been programming in PL/SQL since 1993, more or less continuously (I’ve just realized that it’s exactly half of my life until now), so I know the PL/SQL syntax quite well.
So when I saw the following piece of code a few days ago, I was willing to bet it would not compile:

declare
    v_number_array sys.odcinumberlist;
begin
    for v_rec in (select rownum as num
                  bulk   collect
                  into   v_number_array
                  from   dual
                  connect by level <= 5)
    loop
        dbms_output.put_line(v_rec.num);
    end loop;
end;

Note it's a Cursor FOR LOOP that includes a BULK COLLECT INTO clause inside the cursor definition.

Luckily, I did not bet. I would have lost... Continue reading "COLLECT INTO inside a Cursor Definition?"

Excessive Locking when Dropping a Table

I tried to drop a table today and failed due to “ORA-00054: resource busy and acquire with NOWAIT specified or timeout expired”.
That was weird because I knew that nobody had been using this table for months, and that the table had no enabled foreign keys.
A quick investigation revealed the cause – the DROP TABLE operation tried to lock another table (in the quite aggressive “Share” mode) that was referenced by a disabled foreign key from the table I was trying to drop. The referenced table was locked by other sessions, and therefore the DROP TABLE operation failed.

Even if the foreign key constraint is enabled, there is no good reason in my opinion to lock the referenced table; all the more so if it’s disabled.
There is a workaround (which I think proves my last sentence): it’s possible to drop the constraint first, and then to drop the table. Dropping the constraint does not lock the referenced table.

Here is a simple test I executed in 11.2.0.4, 12.1.0.2 and 12.2.0.1: Continue reading “Excessive Locking when Dropping a Table”

A Single Query with Many Filter Combinations – Part 3

In a recent post I suggested a way to write a single SQL query that filters a table by one or more of several columns.
Here is the query from that post:

select * from employees where rowid in (
  select rid from (
    select rowid rid from employees where department_id = :department_id
    union all
    select rowid rid from employees where job_id = :job_id
    union all
    select rowid rid from employees where manager_id = :manager_id
    union all
    select rowid rid from employees where last_name = :last_name)
  group by rid
  having count(*) = nvl2(:department_id,1,0) + 
                    nvl2(:job_id,1,0) + 
                    nvl2(:manager_id,1,0) + 
                    nvl2(:last_name,1,0)
);

This is actually a simplification of a real task I handled some time ago.
In today’s post I’d like to look at a variation of the problem, which is closer to the real issue I handled. Continue reading “A Single Query with Many Filter Combinations – Part 3”