Looking at Edition-Based Redefinition, Part 3

Our technologist concludes his exploration of Edition-Based Redefinition.

By Tom Kyte

May/June 2010

In the last issue of Oracle Magazine (March/April 2010), I continued to describe what I consider to be the killer feature of Oracle Database 11g Release 2 : Edition-Based Redefinition. If you recall from the last issue, Edition-Based Redefinition gives you the ability to perform an online application upgrade of your application.

This is the third and final installment of a series of columns examining this new feature. If you have not read the prior two installments, I encourage you to do so before reading this column, because this column builds on the example we’ve been working with.

Last time, our application upgrade was not just a simple patch of our application. It not only replaced some PL/SQL units (patching) but also updated the physical structure of a table involved in our application. It was a true application upgrade that included changes to objects whose type is editionable (our PL/SQL units in the example) and also to some objects whose type is not editionable (our table structures). The following object types are editionable:

• Synonyms

• Views (including editioning views, which we’ll define below)

• All PL/SQL object types (functions, procedures, packages, and so on)

All other types of database objects are noneditionable. By deliberate design, a table can never be an editioned object. That’s because it holds data and the developer needs to decide explicitly which data (that is, which table columns) will be unchanged by the upgrade and which table columns will be replaced. Recall from the previous column that to create the illusion (to our application) that a table is editioned, we hide physical schema changes to it with an editioning view. The different occurrence of the same editioning view in each edition retrieves just the columns from the table that the application using the particular edition needs.

Let’s begin by recapping where we are in our example. We have an application table we are going to use in the standard sample schema HR: the EMPLOYEES table, specifically. This master list of all employees for some company includes attributes such as their first names, last names, e-mail addresses, and phone numbers. We’ll pretend that this table has historically been part of an application for a U.S.-based company so the phone numbers are stored in a format a U.S.-based company would recognize easily. U.S. phone numbers are stored with just an area code and then the seven-digit number—for example, 650.507.9876. International numbers are stored in a format that has the U.S. escape code (used to dial an international number) followed by the country code and then the phone number— for example, 011.44.1644.429262.

But this fictitious company has been purchased and is now part of a global company. To adhere to the standards of the new parent company, the purchased company must change how it stores phone numbers. It will now store and display all numbers in two fields: a country code field and a phone number field. So for example, using two sample phone numbers, the before and after storage would be

Now this small change (but there is no such thing as a small change, really) involves a couple of steps. Specifically, we have to

• Modify the schema so the EMPLOYEE table has two new columns: COUNTRY_CODE and PHONE#

• Modify the schema so the EMPLOYEE table doesn’t have a PHONE_NUMBER column anymore

• Create a new index on PHONE# for a search screen (and maybe other indexes as well, but we’ll stick with just one index)

• Mass-move the existing data from PHONE_NUMBER to COUNTRY_CODE and PHONE#

• Replace the application code in the database that relies on the PHONE_NUMBER column

In my previous column, we walked through the concept and implementation of an editioning view. That view provided a buffer between our application code and the underlying physical schema. The editioning view we introduced in the example, coupled with the nonblocking fast add column feature of Oracle Database 11g Release 1 and above, enabled us to add the two new columns without having any impact on the existing application. The editioning view also enabled us to hide the existing PHONE_NUMBER column from the new application release—in effect enabling us to virtually drop the PHONE_NUMBER column in version 2 of our application—without affecting version 1.

We also created a new index on the newly added columns, using the ONLINE and INVISIBLE capabilities. Starting with Oracle Database 11g Release 1, the CREATE INDEX ONLINE operation is 100 percent online—it never locks the table at any point, as it did in prior releases. The new INVISIBLE option enables us to create the index without affecting query plans in the existing application version. We used this option because adding an index could make some queries go faster, others run at the same speed, and yet a third category of queries go slower. Because we didn’t test the existing application query plans with this new index, we wanted to keep the new index hidden.

We left the example in the last issue at the “mass-move the existing data” stage. We described how a single mass update to move the data would tend to lock the entire table. During the mass update, all other transactions against the EMPLOYEES table would be blocked; version 1 of the application would, in fact, be offline. Additionally, once the single mass update was complete, if the existing (version 1) application inserted a new row or modified the PHONE_NUMBER column in an existing record, those changes would not be reflected in the new columns. We would lose those changes.

In order to overcome these obstacles, we looked at two new features. First we discussed the DBMS_PARALLEL_EXECUTE package. (We first looked at that feature, which I nicknamed “do-it-yourself parallelism,” in Oracle Magazine , November/December 2009). Using DBMS_PARALLEL_EXECUTE, we’ll be able to update every row in the table in tiny “chunks,” using whatever degree of parallelism we desire—anywhere from parallel 1 (serial) to 1,000—and hence we will never have to lock the entire table but, rather, will lock only a chunk at a time. Second, we introduced a new trigger type: the crossedition trigger. Crossedition triggers will be used only during the application upgrade process (and we’ll drop these triggers as soon as possible after the upgrade process is complete). Crossedition triggers can be used to forward a change from the old version of the application to the new version. They can also be used to reverse a change made by the new application back to the old application schema.

So, to catch up, I’ll assume you already have the following in place:

• The DEMO account we’ve been using.

• A new VERSION2 edition created as a child of the ORA$BASE edition.

• A copy of the HR.EMPLOYEES table, renamed EMPLOYEES_RT (“RT” stands for “Real Table”—my naming convention) in the DEMO schema. This table was altered to include our two new columns. Additionally, the EMPLOYEES_PHONE#_IDX index was altered.

• The original EMP_PKG in the ORA$BASE edition that represents our application code; it knows how to create a new employee and how to show the existing data.

• The EMPLOYEES editioning view in the ORA$BASE edition, which selects just the columns from EMPLOYEES_RT that the existing version of our application expects.

• A forward crossedition trigger, EMPLOYEES_FWDXEDITION, that converts an old-style phone number into the new two-column-style phone number.

This was all provided in the second column installment (see techntework/issue-archive/2010/10-mar/o20asktom.html for the code). We left the end of that example with a simple

SQL> update employees
set phone_number = phone_number;
109 rows updated.

That did the mass move of our data, and the forward crossedition trigger would ensure that any subsequent changes made by the existing application would be reflected in the new columns. But that UPDATE would lock the entire table as it progressed through the rows—it would have been an offline operation, and our goal was to make this an online upgrade. So, the steps we will cover to complete the application upgrade example will be

• Use DBMS_PARALLEL_EXECUTE to update the table in small chunks, permitting concurrent access by the mass move and the existing application

• Install the new application code while the existing application is still running

• Install a reverse crossedition trigger that will enable us to execute both the old and the new application code concurrently for a “hot rollover”—one that requires no downtime at all

• Clean up after the new application version is the only version being used

So let’s pretend we did not perform that UPDATE but instead added the columns to the EMPLOYEES_RT table and put the forward crossedition trigger in place. We now want to update every row in EMPLOYEES_RT to get the forward crossedition trigger to fire, hence transforming and copying the data from the old column into the two new columns. Enter the DBMS_PARALLEL_EXECUTE package.

In my book Expert Oracle Database Architecture , I spent quite a few pages describing how to perform batch operations “in parallel,” using a do-it-yourself parallelism approach. The approach was to break up a table into ranges, using rowids (see asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:10498431232211 for an overview of that approach) or primary key ranges (see “Splitting Up a Large Table,” for an overview of that approach). Although the approaches I described were straightforward, they were always rather manual. You had to take my “technique” and tweak it for your specific circumstances.

Well, tweak no more. In Oracle Database 11g Release 2, we have a simple way to accomplish exactly what I was trying to demonstrate. The new DBMS_PARALLEL_EXECUTE package can split up a large table by rowid ranges, by key value, or by a user-defined method. The table is broken up logically, and the database processes each of the ranges in the background, using the scheduler, with error logging, retries, and more. Before we use DBMS_PARALLEL_EXECUTE, however, we’ll have to (for purposes of demonstration) scale up our EMPLOYEES_RT table, because it is very small right now. First we’ll make it 100 times larger, as shown in Listing 1.

Code Listing 1: Increasing the size of the EMPLOYEES_RT table

SQL> insert into employees
  2  select * from
  3  (
  4  with data(r)
  5  as
  6  (select 1 r from dual
  7   union all
  8   select r+1 from data where r <= 100
  9  )
 10  select rownum+(select max(employee_id)
 11                        from employees_rt),
 12         FIRST_NAME, LAST_NAME, EMAIL,
 13         PHONE_NUMBER, HIRE_DATE, JOB_ID,
 14         SALARY, COMMISSION_PCT, MANAGER_ID,
 15         DEPARTMENT_ID
 16    from employees_rt, data
 17  );
11009 rows created.

Listing 1 uses a new Oracle Database 11g Release 2 feature—a recursive WITH subquery—in lines 4 through 9 to generate 100 rows. The listing also performs a Cartesian join with the EMPLOYEES_RT table, which creates a result set that has each row in EMPLOYEES_RT repeated 100 times. It also uses rownum plus the current maximum EMPLOYEE_ID to generate a new primary key on our table and inserts these new rows, instantly making our table 100 times larger than it was (well, 101 times larger, technically). We can review the data as it now exists in Listing 2.

Code Listing 2: Reviewing the data in EMPLOYEES_RT

SQL> select phone_number, country_code, phone#
  2    from employees_rt
  3   where country_code is null
  4     and rownum <= 5
  5   union all
  6  select phone_number, country_code, phone#
  7    from employees_rt
  8   where country_code is NOT null
  9     and rownum <= 5;
PHONE_NUMBER        COUNTRY_CODE   PHONE#
------------        ------------   -----------
650.507.9833
650.507.9844
515.123.4444
515.123.5555
603.123.6666
011.44.1344.429268  +44            1344.429268
011.44.1344.467268  +44            1344.467268
011.44.1344.429278  +44            1344.429278
011.44.1344.619268  +44            1344.619268
011.44.1344.429018  +44            1344.429018
10 rows selected.
SQL> select count(*),
  2  count(distinct dbms_rowid.rowid_block_number(rowid)) cnt_blk
  3    from employees
  4  /
  COUNT(*)     CNT_BLK
------------  ---------
  11118        138

Note how some of the rows have the new columns filled in already. We didn’t insert them that way—that was the work of the forward crossedition trigger we put in place. We’ve set it up already so that if we INSERT or UPDATE the PHONE_NUMBER column in the existing application, the forward crossedition trigger will automatically forward the change from the columns used by the old version to the columns used by the new version. So this demonstrates that any newly created data in the old application version will maintain the values in the new application version format transparently for us.

Next, here are the steps to use to perform the mass update of the EMPLOYEES_RT table without locking the entire table. We need to update all the existing rows, touching the PHONE_NUMBER column to get the data copied from PHONE_NUMBER into the COUNTRY_CODE and PHONE# columns. Our existing table is about 138 blocks (as shown in Listing 2), and we’d like to update about 10 percent of it at a time. (EMPLOYEES_RT is a small table; on a larger table, you’d likely use a much smaller percentage to avoid locking too much of the table at a time.) So we’ll break it up into chunks that are 10 blocks or thereabouts in size. The method for doing that is shown in Listing 3.

Code Listing 3: Creating chunks for the mass update

SQL> begin
  2      dbms_parallel_execute.create_task('update employees_rt');
  3      dbms_parallel_execute.create_chunks_by_rowid
  4      ( task_name   => 'update employees_rt',
  5        table_owner => user,
  6        table_name  => 'EMPLOYEES_RT',
  7        by_row      => false,
  8        chunk_size  => 10);
  9  end;
 10  /
PL/SQL procedure successfully completed.

The PL/SQL block of code in Listing 3 did two things: first it created a named task we’ll manipulate further. The first thing it did to that task was to add table chunks by using the CREATE_CHUNKS_BY_ROWID procedure. When the code called that routine, the routine read the EMPLOYEES_RT table and broke it up into chunks of about 10 blocks apiece (chunk_size=>10). Note that this CREATE_CHUNKS_BY_ROWID API allows for two ways of chunking up a table: based on numbers of blocks or numbers of rows. Because the code says by_row=>false, the API used the block chunking method. The result of the PL/SQL procedure in Listing 3 is visible in the new USER_PARALLEL_EXECUTE_CHUNKS view in Listing 4.

Code Listing 4: Looking at chunks in USER_PARALLEL_EXECUTE_CHUNKS

SQL> select chunk_id, status, start_rowid, end_rowid
  2    from user_parallel_execute_chunks
  3   where task_name = 'update employees_rt'
  4     and rownum <= 5
  5  /
  CHUNK_ID   STATUS      START_ROWID          END_ROWID
----------   ---------   ------------------   ------------------
  134        UNASSIGNED  AAAVkkAAEAAAA/YAAA   AAAVkkAAEAAAA/fCcP
  135        UNASSIGNED  AAAVkkAAEAAAA/oAAA   AAAVkkAAEAAAA/vCcP
  136        UNASSIGNED  AAAVkkAAEAAAA/wAAA   AAAVkkAAEAAAA/3CcP
  137        UNASSIGNED  AAAVkkAAEAAAA/4AAA   AAAVkkAAEAAAA//CcP
  138        UNASSIGNED  AAAVkkAAEAAABwIAAA   AAAVkkAAEAAABwPCcP

We can see a series of chunks, rowid ranges in fact, that logically divide this table up into pieces. Each of those rowid ranges in Listing 4 is nonoverlapping, but the ranges completely cover the table (the union of all of them encompasses the entire table). This is how we can break our single update into n smaller updates that do not conflict with each other.

Now we are ready to perform our update. This is accomplished via the code in Listing 5, which ran our task, using two threads of execution (parallel_level=>2). I used 2 just to demonstrate that you can chunk something up into many more chunks than you ultimately run concurrently. We know we have at least five chunks (displayed in Listing 4), but we’re running only two at a time. The update we execute is parameterized to accept a rowid range and works only on the records in that range. So, using “parallel 2,” we updated every row in the table—bit by bit—locking only a small subset of the table at a time. This enabled the existing application to function normally while we did our mass move of data at the same time.

Code Listing 5: Running the UPDATE EMPLOYEES_RT task

SQL> begin
  2      dbms_parallel_execute.run_task
  3      ( task_name      => 'update employees_rt',
  4        sql_stmt       => 'update employees_rt
  5                              set phone_number = phone_number
  6                            where rowid between :start_id
  7                                            and :end_id',
  8        language_flag  => DBMS_SQL.NATIVE,
  9        parallel_level => 2 );
 10  end;
 11  /
PL/SQL procedure successfully completed.

NOTE: Executing the task in parallel requires using a schema that has been granted the CREATE JOB privilege, because RUN_TASK will use the scheduler to run the updates in parallel. If you execute the task sequentially, this is not necessary.

After that operation is done and we are satisfied with the results, we can drop the task we created:

SQL> begin
  2     dbms_parallel_execute.drop_
task('update employees_rt');
  3  end;
  4  /
PL/SQL procedure successfully
completed.

We can be safe in the knowledge that all legacy data has been converted and updated in the new application and that the forward crossedition trigger will automatically convert and update in the new application any “new legacy” data that is created, as well (by the forward crossedition trigger).

Now we’d like to install our new application code. We do that the same way we did in the first installment of this three-part series: we simply switch to the new edition and CREATE OR REPLACE our editionable objects (the EMPLOYEES editioning view and the EMP_PKG package). Note that for brevity, not all of the code is shown in Listing 6, which shows just the modifications to the existing code.

Code Listing 6: Installing the new application

SQL> alter session set edition = version2;
Session altered.
SQL>
SQL> select object_name, object_type, status, edition_name
  2    from user_objects_ae
  3   where object_name in ( 'EMPLOYEES', 'EMP_PKG' );
OBJECT_NAME  OBJECT_TYPE   STATUS   EDITION_NAME
-----------  -----------   ------   ------------
EMPLOYEES    VIEW          VALID    ORA$BASE
EMP_PKG      PACKAGE       VALID    ORA$BASE
EMP_PKG      PACKAGE BODY  VALID    ORA$BASE

This listing shows that we are using the VERSION2 edition, and right now all the code is inherited from the existing version (version 1) of our application in the ORA$BASE edition.

Now we replace the view and package, as shown in Listing 7.

Code Listing 7: Replacing the view and package

SQL> create OR REPLACE editioning view employees
  2  as
  3  select EMPLOYEE_ID, FIRST_NAME,
  4         LAST_NAME, EMAIL, COUNTRY_CODE, PHONE#,
...
  9  /
View created.
SQL> create or replace package emp_pkg
  2  as
...
  9      EMAIL in employees.EMAIL%type,
 10      COUNTRY_CODE in employees.COUNTRY_CODE%type := null,
 11      PHONE# in employees.PHONE#%type := null,
 12      HIRE_DATE in employees.HIRE_DATE%type,
...
 18    return employees.employee_id%type;
 19  end;
 20  /
Package created.
SQL> create or replace package body emp_pkg
  2  as
  3
  4  procedure show
...
  9      ( select first_name, last_name,
 10               country_code, phone#, email
 11          from employees
 12         where last_name like
 13               show.last_name_like
 14         order by last_name )
 15      loop
 16          dbms_output.put_line
 17          ( rpad( x.first_name || ' ' ||
 18                  x.last_name, 40 ) ||
 19            rpad( nvl(x.country_code, ' '), 5 ) ||
 20            rpad( nvl(x.phone#, ' '), 20 ) ||
 21            x.email );
 22      end loop;
 23  end show;
 24
 25  function add
 26  ( FIRST_NAME in employees.FIRST_NAME%type := null,
 27    LAST_NAME in employees.LAST_NAME%type,
 28    EMAIL in employees.EMAIL%type,
 29    COUNTRY_CODE in employees.COUNTRY_CODE%type := null,
 30    PHONE# in employees.PHONE#%type := null,
...
 37  )
 38  return employees.employee_id%type
 39  is
 40      employee_id  employees.employee_id%type;
 41  begin
 42      insert into employees
 43      ( EMPLOYEE_ID, FIRST_NAME, LAST_NAME,
 44        EMAIL, COUNTRY_CODE, PHONE#, HIRE_DATE,
 45        JOB_ID, SALARY, COMMISSION_PCT,
...
 55  end add;
 56
 57  end;
 58  /
Package body created.

And now we can see that we have both versions installed, as shown in Listing 8.

Code Listing 8: Looking at both application versions installed

SQL> select object_name, object_type, status, edition_name
  2    from user_objects_ae
  3   where object_name in ( 'EMPLOYEES', 'EMP_PKG' );
OBJECT_NAME  OBJECT_TYPE   STATUS   EDITION_NAME
-----------  -----------   ------   ------------
EMP_PKG      PACKAGE BODY  VALID    ORA$BASE
EMP_PKG      PACKAGE       VALID    ORA$BASE
EMPLOYEES    VIEW          VALID    ORA$BASE
EMP_PKG      PACKAGE BODY  VALID    VERSION2
EMP_PKG      PACKAGE       VALID    VERSION2
EMPLOYEES    VIEW          VALID    VERSION2
6 rows selected.

The editions are installed but not quite ready to go yet. The code in ORA$BASE is all set, but the code in VERSION2 is not quite ready. What if we call the EMP_PKG.ADD routine in VERSION2? It will populate the COUNTRY_CODE and PHONE# column but not the PHONE_NUMBER legacy column! Enter the reverse crossedition trigger, shown in Listing 9.

Code Listing 9: Creating the reverse crossedition trigger

SQL> create or replace trigger employees_revxedition
  2  before insert or update of country_code,phone# on employees_rt
  3  for each row
  4  reverse crossedition
  5  declare
  6      first_dot  number;
  7      second_dot number;
  8  begin
  9          if :new.country_code = '+1'
 10          then
 11             :new.phone_number :=
 12                :new.phone#;
 13          else
 14             :new.phone_number :=
 15                '011.' ||
 16                substr( :new.country_code, 2 ) ||
 17                '.' || :new.phone#;
 18          end if;
 19  end;
 20  /
Trigger created.

Here we have a trigger that fires only in the context of the VERSION2 application and maintains data in the old application version’s format. It will either store just the phone number without a country code for U.S. phone numbers or store the U.S. representation of a foreign phone number, using the 011.country_code.phone_number format. Once that is in place, we can use the new application code in VERSION2:

SQL> begin
  2   dbms_output.put_line
  3   ( emp_pkg.add
  4     ( first_name   => 'Tom'
  5       last_name    => 'Kight',
  6       email        => 'TKYTE',
  7       country_code => '+44',
  8       phone#       => '703.123.4567',
  9       hire_date    => sysdate,
 10       job_id       => 'IT_PROG' ) );
 11  end;
 12  /
502
PL/SQL procedure successfully
completed.
SQL> exec emp_pkg.show( 'Kight' );
Tom Kight +44  703.123.4567  TKYTE
PL/SQL procedure successfully
completed.

We can see that the data is input correctly. Further, in ORA$BASE we can verify that the legacy data format is still valid:

SQL> connect demo/demo
Connected.
demo> exec emp_pkg.show( 'Kight' );
Tom Kight  011.44.703.123.4567 TKYTE

Remember that because the database’s default edition is ORA$BASE, any session will use—will see—that edition by default. So simply by logging in again, we are running the old version of the application and seeing the old output format with the PHONE_NUMBER column.

To make using the new edition the default, we will use an AFTER LOGON trigger to set the edition for each newly created session. This will enable new sessions to see the new version of our code while enabling existing sessions to continue to execute the old version. By enabling existing versions to continue executing the old code, we will entirely eliminate the dreaded “ORA-4068 existing state of packages has been discarded” error you would likely have gotten in the past when replacing existing code. This ability further enhances the online application upgrade process. Not only are we eliminating downtime during the application upgrade, but we are also eliminating it during the switchover to the new version.

The AFTER LOGON trigger will use a new API call in the DBMS_SESSION package named SET_EDITION_DEFERRED. This API switches to the edition specified as the sole parameter in a deferred fashion, meaning that the switch to the edition takes place after the call to the database (after the logon) completes. The deferred setting of the session edition is necessary to prevent obvious “what should the code do?” questions that would arise if the edition were switched immediately—during that initial call. Consider a hypothetical piece of code that executed the following logic:

Begin
        Proc;
        Switch editions to a different
      edition right now, immediately;
        Proc;
End;

The code to make the new version of our application the default for new sessions is simply

SQL> grant use on
edition version2 to public;
Grant succeeded.
SQL> create or
replace trigger set_edition_on_logon
  2  after logon on database
  3  begin
  4          dbms_session.set_
edition_deferred( 'VERSION2' );
  5  end;
  6  /
Trigger created.

The GRANT is necessary to make the VERSION2 edition available to all users (of course, you could grant it to a smaller set of users instead, depending on your needs), and the trigger puts our new edition in place. Now, when we log in to the database, we’ll observe

SQL> connect demo/demo
Connected.
demo> SELECT SYS_CONTEXT ('userenv',
'current_edition_name') sc
  2    FROM DUAL;
SC
---------
VERSION2
demo> exec emp_pkg.show( 'Kight' );
Tom Kight  +44  703.123.4567 TKYTE
PL/SQL procedure successfully
completed.

Now all that remains is cleaning up. The cleanup takes place after everyone is finished using the ORA$BASE edition—after no existing sessions are using the old code. We can review which edition each session currently in the database is using, by querying V$SESSION—the SESSION_EDITION_ID column (which can be joined to the *_OBJECTS views by SESSION_EDITION_ID to OBJECT_ID to see the name of the session edition)—and when none are using ORA$BASE, we know we can perform our cleanup. In this case, the cleanup consists of

• Dropping the forward and reverse crossedition triggers

• Optionally, dropping or setting as unused the PHONE_NUMBER column

And that is it. We are done with our online application upgrade!