ROLLUP Function

“The database seems a bit slow today…” was the comment I received upon my arrival at work recently. It wasn’t too hard to see why either with a rather large, convoluted reporting style query chugging along, much to the annoyance of the other activity in the database. Peering into the depths of the query I noticed at its core it comprised of a UNION of 3 queries. What struck me as strange was that each of the three components were nearly identical to each other:

SELECT col_1, col_2, col3, col_4
FROM <tables>
WHERE <selection criteria>
GROUP BY
      col_1, col_2, col3, col_4
UNION ALL
SELECT col_1, col_2, col3, 'TOTAL'
FROM <tables>
WHERE <selection criteria>
GROUP BY
      col_1, col_2, col3, 'TOTAL'
UNION ALL
SELECT col_1, col_2, 'TOTAL', 'TOTAL'
FROM <tables>
WHERE <selection criteria>
GROUP BY
      col_1, col_2, 'TOTAL', 'TOTAL'

The execution details of the query showed that Oracle spent 10 minutes executing each component. The tables and the WHERE clause were identical in each of the query components. This meant that the second component is nothing more than an aggregation of the first and the third component an aggregation of the second. Surely there’s a better way of obtaining the aggregation entries without rerunning the entire query again? And there is…

Lurking in the depths of the Database Data Warehousing Guide, and not the SQL Language Reference, is a chapter titled SQL for Aggregation in Data Warehouses. The particular function we want for our query is ROLLUP. The documentation has this to say about ROLLUP:

ROLLUP enables a SELECT statement to calculate multiple levels of subtotals across a specified group of dimensions. It also calculates a grand total. ROLLUP is a simple extension to the GROUP BY clause, so its syntax is extremely easy to use. The ROLLUP extension is highly efficient, adding minimal overhead to a query.

By way of simple example we create a table with 4 columns: 3 of them dimension type attributes and one numeric metric, along with some test data:

CREATE TABLE rollup_test
	(col_a 		VARCHAR2(1)	NOT NULL
	,col_b		VARCHAR2(1)	NOT NULL
	,col_c		VARCHAR2(1)	NOT NULL
	,val		NUMBER(3)	NOT NULL);

INSERT INTO rollup_test
VALUES ('A','A','A',1);
INSERT INTO rollup_test
VALUES ('A','A','B',1);
INSERT INTO rollup_test
VALUES ('A','A','C',1);

INSERT INTO rollup_test
VALUES ('A','B','A',1);
INSERT INTO rollup_test
VALUES ('A','B','B',1);
INSERT INTO rollup_test
VALUES ('A','B','C',1);

INSERT INTO rollup_test
VALUES ('A','C','A',1);
INSERT INTO rollup_test
VALUES ('A','C','B',1);
INSERT INTO rollup_test
VALUES ('A','C','C',1);

INSERT INTO rollup_test
VALUES ('B','A','A',1);
INSERT INTO rollup_test
VALUES ('B','A','B',1);
INSERT INTO rollup_test
VALUES ('B','A','C',1);

INSERT INTO rollup_test
VALUES ('B','B','A',1);
INSERT INTO rollup_test
VALUES ('B','B','B',1);
INSERT INTO rollup_test
VALUES ('B','B','C',1);

INSERT INTO rollup_test
VALUES ('B','C','A',1);
INSERT INTO rollup_test
VALUES ('B','C','B',1);
INSERT INTO rollup_test
VALUES ('B','C','C',1);

COMMIT;

and we can see the impact that ROLLUP has using the following query:

SQL> SELECT col_a
  2  ,      NVL(col_b,'TOTAL') AS b
  3  ,      NVL(col_c,'TOTAL') AS c
  4  ,      SUM(val)           AS sum_val
  5  FROM   rollup_test
  6  GROUP  BY
  7         col_a
  8  ,      ROLLUP(col_b, col_c)
  9  ORDER  BY
 10         col_a
 11  ,      NVL(col_b,'TOTAL')
 12  ,      NVL(col_c,'TOTAL');

COL_A  B      C       SUM_VAL
------ ------ ------ --------
A      A      A             1
A      A      B             1
A      A      C             1
A      A      TOTAL         3
A      B      A             1
A      B      B             1
A      B      C             1
A      B      TOTAL         3
A      C      A             1
A      C      B             1
A      C      C             1
A      C      TOTAL         3
A      TOTAL  TOTAL         9
B      A      A             1
B      A      B             1
B      A      C             1
B      A      TOTAL         3
B      B      A             1
B      B      B             1
B      B      C             1
B      B      TOTAL         3
B      C      A             1
B      C      B             1
B      C      C             1
B      C      TOTAL         3
B      TOTAL  TOTAL         9

26 rows selected.

Applying it to our reporting query was as simple as replacing the UNION ALL with:

SELECT col_1, col_2, NVL(col3,'TOTAL'), NVL(col_4,'TOTAL')
FROM <tables>
WHERE <selection criteria>
GROUP BY
      col_1, col_2, ROLLUP(col3, col_4)

No more UNION ALL, no more execution of (nearly) the same query three times and an immediate saving of 20 minutes of wasted execution time… and all before my morning coffee.

Advertisements

Purging AQ can be slow

So, you’ve constructed an awesome multi consumer, event-based processing system built around Oracle AQ. The system’s humming along with messages are flying all over the place when something goes wrong, maybe a network outage or an application fault, and it all grinds to a halt. Within the database things keep going but the messages are piling up.

“No problem” you say, “I’ve set an expiration time on the messages so they’ll get moved onto the exception queue if they hang around for too long.”. Sure enough, the next morning when you get into work and everything is running smoothly again you see that the exception queue has a couple of million of entries in it. After a bit of checking you decide the expired messages are no longer necessary and that they can be removed. “Easy, I’ll just run a purge of the exception queue, using the dbms_aqadm.purge_queue_table routine” you think. Something like:

DECLARE
  l_purge_options dbms_aqadm.aq$_purge_options_t;
BEGIN
   l_purge_options.block := false;
   l_purge_options.delivery_mode := dbms_aqadm.persistent;

  dbms_aqadm.purge_queue_table (queue_table     => 'TEST_Q_TAB'
                               ,purge_condition => 'qtview.queue = ''AQ$_TEST_Q_TAB_E'' '
                               ,purge_options   => l_purge_options);
END;
/

Lunchtime comes around and the purge is still running. “It’s taking a while…”, you muse. A few hours later and you’re thinking about leaving for the day but the purge is still running. By now you’re beginning to wonder what’s going on and whether you should kill the purge and try it again tomorrow.

Well, what’s going on is that using dbms_aqadm.purge_queue_table against a multi conumer queue and specifying a purge condition is slow. Real slow. Here are the results of removing 100,000 messages on a 12.2 database running on my laptop:

  • Multi consumer queue: 20 mins, 57.11 secs
  • Single consumer queue: 4.87 secs
  • Dequeue operation: 34.49 secs

Yep, the purge operation against a multi consumer queue is around 250 time slower than the same operation against a single consumer queue. Interestingly, it is much faster to dequeue the messages rather than purging them. I raised this with Oracle Support but it appears that this is due to the work that the purge needs to do, i.e. it’s working as designed.

In summary, when it comes to removing messages from a multi-consumer queue you may like to check how many messages there are and dequeue those messages rather than using the dbms_aqadm.purge_queue_table routine.

The test scripts used to create the results shown above are available.

Database Views: the good, the bad and the ugly

Views are good as they can be used to hide complexity.

Views are bad as they can hide complexity.

It’s when views are used for the first point above without consideration of the second point that things can turn ugly. Here’s recently encountered example…

A developer decided to modify a process so that it would determine when it was being run from within the internal database job scheduler. The following simple query was added to the code:

SELECT job_name
INTO   l_result
FROM   user_scheduler_running_jobs
where  session_id = l_sid;

What the developer overlooked was that user_scheduler_running_jobs is a view and even though it’s an Oracle data dictionary view the underlying table structure might not be as simple as the query would suggest. The following is the execution plan that Oracle selected:

-------------------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                            | Name                        | Rows  | Bytes | Cost (%CPU)| Time     |    TQ  |IN-OUT| PQ Distrib |
-------------------------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                     |                             |       |       |     6 (100)|          |        |      |            |
|   1 |  VIEW                                | USER_SCHEDULER_RUNNING_JOBS |     3 |   198 |     6  (34)| 00:00:01 |        |      |            |
|   2 |   UNION-ALL                          |                             |       |       |            |          |        |      |            |
|   3 |    MERGE JOIN CARTESIAN              |                             |     1 |   190 |     5  (20)| 00:00:01 |        |      |            |
|*  4 |     HASH JOIN OUTER                  |                             |     1 |   190 |     5  (20)| 00:00:01 |        |      |            |
|   5 |      NESTED LOOPS                    |                             |     1 |   171 |     4   (0)| 00:00:01 |        |      |            |
|   6 |       NESTED LOOPS                   |                             |     1 |   171 |     4   (0)| 00:00:01 |        |      |            |
|*  7 |        HASH JOIN OUTER               |                             |     1 |   138 |     1   (0)| 00:00:01 |        |      |            |
|   8 |         NESTED LOOPS                 |                             |     1 |    99 |     1   (0)| 00:00:01 |        |      |            |
|   9 |          NESTED LOOPS                |                             |     1 |    99 |     1   (0)| 00:00:01 |        |      |            |
|  10 |           MERGE JOIN CARTESIAN       |                             |     1 |    73 |     0   (0)|          |        |      |            |
|* 11 |            FIXED TABLE FULL          | X$KCCDI                     |     1 |    15 |     0   (0)|          |        |      |            |
|  12 |            BUFFER SORT               |                             |     1 |    58 |     0   (0)|          |        |      |            |
|* 13 |             PX COORDINATOR           |                             |       |       |            |          |        |      |            |
|  14 |              PX SEND QC (RANDOM)     | :TQ10000                    |     1 |    26 |     0   (0)|          |  Q1,00 | P->S | QC (RAND)  |
|* 15 |               VIEW                   | GV$SCHEDULER_RUNNING_JOBS   |       |       |            |          |  Q1,00 | PCWP |            |
|* 16 |                FIXED TABLE FULL      | X$JSKSLV                    |     1 |    26 |     0   (0)|          |  Q1,00 | PCWP |            |
|* 17 |           INDEX UNIQUE SCAN          | SCHEDULER$_JOB_PK           |     1 |       |     0   (0)|          |        |      |            |
|* 18 |          TABLE ACCESS BY INDEX ROWID | SCHEDULER$_JOB              |     1 |    26 |     1   (0)| 00:00:01 |        |      |            |
|* 19 |         PX COORDINATOR               |                             |       |       |            |          |        |      |            |
|  20 |          PX SEND QC (RANDOM)         | :TQ20000                    |     1 |    28 |     0   (0)|          |  Q2,00 | P->S | QC (RAND)  |
|* 21 |           VIEW                       | GV$SESSION                  |       |       |            |          |  Q2,00 | PCWP |            |
|  22 |            MERGE JOIN CARTESIAN      |                             |     1 |    28 |     0   (0)|          |  Q2,00 | PCWP |            |
|  23 |             NESTED LOOPS             |                             |     1 |    12 |     0   (0)|          |  Q2,00 | PCWP |            |
|* 24 |              FIXED TABLE FIXED INDEX | X$KSLWT (ind:1)             |     1 |     8 |     0   (0)|          |  Q2,00 | PCWP |            |
|* 25 |              FIXED TABLE FIXED INDEX | X$KSLED (ind:2)             |     1 |     4 |     0   (0)|          |  Q2,00 | PCWP |            |
|  26 |             BUFFER SORT              |                             |     1 |    16 |     0   (0)|          |  Q2,00 | PCWP |            |
|* 27 |              FIXED TABLE FIXED INDEX | X$KSUSE (ind:1)             |     1 |    16 |     0   (0)|          |  Q2,00 | PCWP |            |
|* 28 |        INDEX RANGE SCAN              | I_OBJ1                      |     1 |       |     2   (0)| 00:00:01 |        |      |            |
|  29 |       TABLE ACCESS BY INDEX ROWID    | OBJ$                        |     1 |    33 |     3   (0)| 00:00:01 |        |      |            |
|  30 |      PX COORDINATOR                  |                             |       |       |            |          |        |      |            |
|  31 |       PX SEND QC (RANDOM)            | :TQ30000                    |   173 |  2595 |     1 (100)| 00:00:01 |  Q3,00 | P->S | QC (RAND)  |
|  32 |        VIEW                          | GV$PROCESS                  |       |       |            |          |  Q3,00 | PCWP |            |
|* 33 |         FIXED TABLE FULL             | X$KSUPR                     |   173 |  2595 |     1 (100)| 00:00:01 |  Q3,00 | PCWP |            |
|  34 |     BUFFER SORT                      |                             |     1 |       |     5  (20)| 00:00:01 |        |      |            |
|  35 |      FIXED TABLE FULL                | X$KCCDI2                    |     1 |       |     0   (0)|          |        |      |            |
|* 36 |    HASH JOIN OUTER                   |                             |     1 |   286 |     1 (100)| 00:00:01 |        |      |            |
|* 37 |     HASH JOIN OUTER                  |                             |     1 |   267 |     0   (0)|          |        |      |            |
|* 38 |      FILTER                          |                             |       |       |            |          |        |      |            |
|* 39 |       HASH JOIN OUTER                |                             |     1 |   228 |     0   (0)|          |        |      |            |
|  40 |        NESTED LOOPS                  |                             |     1 |   189 |     0   (0)|          |        |      |            |
|  41 |         NESTED LOOPS                 |                             |     1 |   189 |     0   (0)|          |        |      |            |
|  42 |          NESTED LOOPS                |                             |     1 |    97 |     0   (0)|          |        |      |            |
|* 43 |           PX COORDINATOR             |                             |       |       |            |          |        |      |            |
|  44 |            PX SEND QC (RANDOM)       | :TQ40000                    |     1 |    26 |     0   (0)|          |  Q4,00 | P->S | QC (RAND)  |
|* 45 |             VIEW                     | GV$SCHEDULER_RUNNING_JOBS   |       |       |            |          |  Q4,00 | PCWP |            |
|* 46 |              FIXED TABLE FULL        | X$JSKSLV                    |     1 |    26 |     0   (0)|          |  Q4,00 | PCWP |            |
|  47 |           TABLE ACCESS BY INDEX ROWID| SCHEDULER$_LIGHTWEIGHT_JOB  |     1 |    39 |     0   (0)|          |        |      |            |
|* 48 |            INDEX UNIQUE SCAN         | SCHEDULER$_LWJOB_PK         |     1 |       |     0   (0)|          |        |      |            |
|* 49 |          INDEX RANGE SCAN            | SCHEDULER$_LOBJ_UK          |     1 |       |     0   (0)|          |        |      |            |
|* 50 |         TABLE ACCESS BY INDEX ROWID  | SCHEDULER$_LWJOB_OBJ        |     1 |    92 |     0   (0)|          |        |      |            |
|* 51 |        PX COORDINATOR                |                             |       |       |            |          |        |      |            |
|  52 |         PX SEND QC (RANDOM)          | :TQ50000                    |     1 |    39 |            |          |  Q5,00 | P->S | QC (RAND)  |
|* 53 |          VIEW                        | GV$SCHEDULER_INMEM_RTINFO   |       |       |            |          |  Q5,00 | PCWP |            |
|* 54 |           FIXED TABLE FULL           | X$JSKMIMRT                  |     1 |    39 |            |          |  Q5,00 | PCWP |            |
|* 55 |      PX COORDINATOR                  |                             |       |       |            |          |        |      |            |
|  56 |       PX SEND QC (RANDOM)            | :TQ60000                    |     1 |    28 |     0   (0)|          |  Q6,00 | P->S | QC (RAND)  |
|* 57 |        VIEW                          | GV$SESSION                  |       |       |            |          |  Q6,00 | PCWP |            |
|  58 |         MERGE JOIN CARTESIAN         |                             |     1 |    28 |     0   (0)|          |  Q6,00 | PCWP |            |
|  59 |          NESTED LOOPS                |                             |     1 |    12 |     0   (0)|          |  Q6,00 | PCWP |            |
|* 60 |           FIXED TABLE FIXED INDEX    | X$KSLWT (ind:1)             |     1 |     8 |     0   (0)|          |  Q6,00 | PCWP |            |
|* 61 |           FIXED TABLE FIXED INDEX    | X$KSLED (ind:2)             |     1 |     4 |     0   (0)|          |  Q6,00 | PCWP |            |
|  62 |          BUFFER SORT                 |                             |     1 |    16 |     0   (0)|          |  Q6,00 | PCWP |            |
|* 63 |           FIXED TABLE FIXED INDEX    | X$KSUSE (ind:1)             |     1 |    16 |     0   (0)|          |  Q6,00 | PCWP |            |
|  64 |     PX COORDINATOR                   |                             |       |       |            |          |        |      |            |
|  65 |      PX SEND QC (RANDOM)             | :TQ70000                    |   173 |  2595 |     1 (100)| 00:00:01 |  Q7,00 | P->S | QC (RAND)  |
|  66 |       VIEW                           | GV$PROCESS                  |       |       |            |          |  Q7,00 | PCWP |            |
|* 67 |        FIXED TABLE FULL              | X$KSUPR                     |   173 |  2595 |     1 (100)| 00:00:01 |  Q7,00 | PCWP |            |
|* 68 |    HASH JOIN OUTER                   |                             |     1 |   202 |     1 (100)| 00:00:01 |        |      |            |
|  69 |     MERGE JOIN CARTESIAN             |                             |     1 |   183 |     0   (0)|          |        |      |            |
|* 70 |      HASH JOIN OUTER                 |                             |     1 |   179 |     0   (0)|          |        |      |            |
|* 71 |       HASH JOIN                      |                             |     1 |   140 |     0   (0)|          |        |      |            |
|* 72 |        HASH JOIN                     |                             |     1 |    82 |     0   (0)|          |        |      |            |
|* 73 |         PX COORDINATOR               |                             |       |       |            |          |        |      |            |
|  74 |          PX SEND QC (RANDOM)         | :TQ80000                    |     1 |    69 |            |          |  Q8,00 | P->S | QC (RAND)  |
|* 75 |           VIEW                       | GV$SCHEDULER_INMEM_RTINFO   |       |       |            |          |  Q8,00 | PCWP |            |
|* 76 |            FIXED TABLE FULL          | X$JSKMIMRT                  |     1 |    69 |            |          |  Q8,00 | PCWP |            |
|  77 |         PX COORDINATOR               |                             |       |       |            |          |        |      |            |
|  78 |          PX SEND QC (RANDOM)         | :TQ90000                    |     1 |    13 |            |          |  Q9,00 | P->S | QC (RAND)  |
|  79 |           VIEW                       | GV$SCHEDULER_INMEM_MDINFO   |       |       |            |          |  Q9,00 | PCWP |            |
|  80 |            FIXED TABLE FULL          | X$JSKMIMMD                  |     1 |    13 |            |          |  Q9,00 | PCWP |            |
|* 81 |        PX COORDINATOR                |                             |       |       |            |          |        |      |            |
|  82 |         PX SEND QC (RANDOM)          | :TQ100000                   |     1 |    26 |     0   (0)|          |  10,00 | P->S | QC (RAND)  |
|* 83 |          VIEW                        | GV$SCHEDULER_RUNNING_JOBS   |       |       |            |          |  10,00 | PCWP |            |
|* 84 |           FIXED TABLE FULL           | X$JSKSLV                    |     1 |    26 |     0   (0)|          |  10,00 | PCWP |            |
|* 85 |       PX COORDINATOR                 |                             |       |       |            |          |        |      |            |
|  86 |        PX SEND QC (RANDOM)           | :TQ110000                   |     1 |    28 |     0   (0)|          |  11,00 | P->S | QC (RAND)  |
|* 87 |         VIEW                         | GV$SESSION                  |       |       |            |          |  11,00 | PCWP |            |
|  88 |          MERGE JOIN CARTESIAN        |                             |     1 |    28 |     0   (0)|          |  11,00 | PCWP |            |
|  89 |           NESTED LOOPS               |                             |     1 |    12 |     0   (0)|          |  11,00 | PCWP |            |
|* 90 |            FIXED TABLE FIXED INDEX   | X$KSLWT (ind:1)             |     1 |     8 |     0   (0)|          |  11,00 | PCWP |            |
|* 91 |            FIXED TABLE FIXED INDEX   | X$KSLED (ind:2)             |     1 |     4 |     0   (0)|          |  11,00 | PCWP |            |
|  92 |           BUFFER SORT                |                             |     1 |    16 |     0   (0)|          |  11,00 | PCWP |            |
|* 93 |            FIXED TABLE FIXED INDEX   | X$KSUSE (ind:1)             |     1 |    16 |     0   (0)|          |  11,00 | PCWP |            |
|  94 |      BUFFER SORT                     |                             |     1 |     4 |     0   (0)|          |        |      |            |
|  95 |       TABLE ACCESS CLUSTER           | USER$                       |     1 |     4 |     0   (0)|          |        |      |            |
|* 96 |        INDEX UNIQUE SCAN             | I_USER#                     |     1 |       |     0   (0)|          |        |      |            |
|  97 |     PX COORDINATOR                   |                             |       |       |            |          |        |      |            |
|  98 |      PX SEND QC (RANDOM)             | :TQ120000                   |   173 |  2595 |     1 (100)| 00:00:01 |  12,00 | P->S | QC (RAND)  |
|  99 |       VIEW                           | GV$PROCESS                  |       |       |            |          |  12,00 | PCWP |            |
|*100 |        FIXED TABLE FULL              | X$KSUPR                     |   173 |  2595 |     1 (100)| 00:00:01 |  12,00 | PCWP |            |
-------------------------------------------------------------------------------------------------------------------------------------------------

Yep, to top it all off, that’s a parallel query execution too.

The ugly part occurred when this query got deployed and the queue-based processing that contained the modified code went from processing 500+ messages per second down to just 2. Ouch!

Thankfully the problem was trivial to diagnose and easily fixed so no damage was done.

CHECK constraint evaluation

A developer approached me with the question “Are check constraints only evaluated when the columns that they apply to are modified and not some other column on the row?”. An unusual question perhaps but as it turned out they were creating a number of check constraints and wanted to assess the overhead this might introduce.

My response was “yes, a constraint it only evaluated when the columns(s) associated with the constraint are modified”… but then I had to think about how to prove this. After a minute or two I came up with the following.

Let’s take a table with two columns and insert a single row:

SQL> CREATE TABLE chk_test
  2     (col_1     NUMBER(2) NOT NULL
  3     ,col_2     NUMBER(2) NOT NULL)
  4  /

Table created.

SQL> INSERT INTO chk_test
  2  VALUES (1, -1)
  3  /

1 row created.

SQL> COMMIT
  2  /

Commit complete.

Onto this table we’ll add a CHECK constraint such that COL_2 must be greater than 0. However, because our table already has a value in COL_2 that violates this constraint, we’ll create the constraint in an ENABLE NOVALIDATE state, meaning that existing values do not need to abide by it but any new data changes must:

SQL> ALTER TABLE chk_test
  2     ADD CONSTRAINT chk_test_ch1
  3     CHECK (col_2 > 0)
  4     ENABLE NOVALIDATE
  5  /

Table altered.

We can verify the constraint is working by trying to update COL_2:

SQL> UPDATE chk_test
  2  SET col_2 = 0
  3  /
UPDATE chk_test
*
ERROR at line 1:
ORA-02290: check constraint (DEVELOPER.CHK_TEST_CH1) violated

However, we are allowed to modify COL_1 even though the existing value of COL_2 violates the constraint:

SQL> UPDATE chk_test
  2  SET col_1 = 0
  3  /

1 row updated.

So, a change to the row that did not modify COL_2 did not trigger the evaluation of the CHECK constraint… proof that CHECK constraints are only evaluated when the column(s) they relate to are manipulated.

Edition-based Redefinition to avoid ORA-04068

I have previously written about how to avoid package state to facilitate on-line deployments, so that PL/SQL code can be modified in a live environment without impacting existing sessions. The main drawback to the methods outlined are that they assume the call interface, or contract, for routines won’t change. When dealing with complex deployments, Oracle 11g Release 2 and later provides for a versioning capabilitiy in the form of edition-based redefinition.

Let’s take a quick look at how edition-based redefinition could be used to avoid ORA-04068. We’ll use the inital package code from the previous article that makes no effort to avoid package state. However, first we need to do a little bit of preparation. From an appropriately authorised account we will set up a new EDITION_USER account and create a couple of editions within the database:

CREATE USER edition_user
   IDENTIFIED BY edition_user;

ALTER USER edition_user ENABLE EDITIONS;
GRANT CONNECT TO edition_user;
GRANT CREATE PROCEDURE TO edition_user;

CREATE EDITION edition_1 AS CHILD OF ora$base;
CREATE EDITION edition_2 AS CHILD OF edition_1;

GRANT USE ON EDITION edition_1 TO edition_user;
GRANT USE ON EDITION edition_2 TO edition_user;

We’ve now got a new account and two new editions that the account has access to. The above assumes no previous editions have been defined, i.e. the default ORA$BASE edition was still in effect.

From a new session logged onto the new EDITION_USER account we’ll create our package under EDITION_1:

ALTER SESSION SET EDITION = edition_1;

CREATE OR REPLACE EDITIONABLE PACKAGE pkg_state
AS
   PROCEDURE set_variable (p_number IN NUMBER);
   FUNCTION get_variable
   RETURN NUMBER;
END pkg_state;
/

CREATE OR REPLACE EDITIONABLE PACKAGE BODY pkg_state
AS
   g_pkg_variable   NUMBER(10);

   PROCEDURE set_variable (p_number IN NUMBER)
   AS
   BEGIN   
      g_pkg_variable := p_number;
   END set_variable;
   
   FUNCTION get_variable
   RETURN NUMBER
   AS
   BEGIN
      RETURN g_pkg_variable;
   END get_variable;

END pkg_state;
/

Running the packaged routines within the session we get:

SQL> EXEC pkg_state.set_variable (5)

PL/SQL procedure successfully completed.

SQL> EXEC dbms_output.put_line ('get_value: ' || TO_CHAR(pkg_state.get_variable));
get_value: 5

PL/SQL procedure successfully completed.

Everything working as expected. Our session has now got package state for PKG_STATE. In a new session we’re going to modify the package, doubling the value we output in the GET_VARIABLE routine, but we’ll do it under EDITION_2:

ALTER SESSION SET EDITION = edition_2;

CREATE OR REPLACE EDITIONABLE PACKAGE pkg_state
AS
   PROCEDURE set_variable (p_number IN NUMBER);
   FUNCTION get_variable
   RETURN NUMBER;
END pkg_state;
/

CREATE OR REPLACE EDITIONABLE PACKAGE BODY pkg_state
AS
   g_pkg_variable   NUMBER(10);

   PROCEDURE set_variable (p_number IN NUMBER)
   AS
   BEGIN   
      g_pkg_variable := p_number;
   END set_variable;
   
   FUNCTION get_variable
   RETURN NUMBER
   AS
   BEGIN
      RETURN g_pkg_variable * 2;
   END get_variable;

END pkg_state;
/

Executing the code from within the EDITION_2 session we see that all is working as it should:

SQL> EXEC pkg_state.set_variable (5)

PL/SQL procedure successfully completed.

SQL> EXEC dbms_output.put_line ('get_value: ' || TO_CHAR(pkg_state.get_variable));
get_value: 10

PL/SQL procedure successfully completed.

Let’s go back to our initial session and see what happens if we execute the GET_VARIABLE packaged routine again:

SQL> EXEC dbms_output.put_line ('get_value: ' || TO_CHAR(pkg_state.get_variable));
get_value: 5

PL/SQL procedure successfully completed.

We can see that it’s still executing the initial version of the code and that it’s still got its package state. The compilation of the code under EDITION_2 did not impact our first session, which is running under EDITION_1. If we change out first session to EDITION_2 and run the routines everything works fine too:

SQL> ALTER SESSION SET EDITION = edition_2;

Session altered.

SQL> EXEC pkg_state.set_variable (2)

PL/SQL procedure successfully completed.

SQL> EXEC dbms_output.put_line ('get_value: ' || TO_CHAR(pkg_state.get_variable));
get_value: 4

PL/SQL procedure successfully completed.

We can see that now we’re executing the modified code under EDITION_2. Switching back to EDITION_1 we find that our package state from the EDITION_1 execution has been preserved:

SQL> ALTER SESSION SET EDITION = edition_1;

Session altered.

SQL> EXEC dbms_output.put_line ('get_value: ' || TO_CHAR(pkg_state.get_variable));
get_value: 5

PL/SQL procedure successfully completed.

So, edition-based redefinition allows us to have multiple versions of our package code in the database at the same time. Each version has its own package state so compiling a new version of the code into a different edition does not invalidate the package state associated with sessions using a different edition.

A word of caution however, Edition-based redefinition is a very powerful feature and one that needs careful planning before using it. I would strongly recommend reading the on-line documentation before implementating anything. Editions are database-wide and currently, as of Oracle 12.1.0.2, limited to a simple single parent-child sequence. For manageability reasons, care should be taken not to create too many editions with various changes in each edition. If at all possible, I would recommend there be just 2 active editions: a “current” one and a “future” one. Once the functionality of the future edition has been confirmed and all applications have been moved over to be using it then that becomes the current edition and the previous current edition is removed. Trying to track a dozen or more active editions, all with their own specific changes, is likely to lead to trouble.

It’s obvious…

Whilst attempting to apply a CHECK constraint to a table, Oracle threw back the following:

SQL Error: ORA-02293: cannot validate (xxxx.xxxxxxxxx) - check constraint violated
02293. 00000 - "cannot validate (%s.%s) - check constraint violated"
*Cause:    an alter table operation tried to validate a check constraint to
           populated table that had nocomplying values.
*Action:   Obvious

As a database person, to me the obvious action would be to correct the data and re-apply the constraint. To a non-database developer the obvious action may be to discard the constraint entirely.

It’s a matter of perspective… obviously.

Image Attributes

I’ve worked on a number of systems where images were stored in the tables of the database, rather than some sort of reference to a file system location. This approach has quite a few merits; adding and modifying images will be transactional with whatever business process is updating the database and the images will be backed up with the rest of the data.

However, quite often I see the images stored in simple BLOB data type columns. This approach works just fine until you want to know details image. An example that springs to mind was the need to know the format and dimensions of the images so they could make decisions about how the images should be displayed on a screen.

Oracle has an object type for images where this type of metadata is exposed: ORDImage. You’ll find the documentation of this object type in the Multimedia User’s Guide and Multimedia Reference. One solution to the problem of determining attributes of an image stored in a BLOB is to query the BLOB, casting it to a ORDIMage type and reading the attributes that way. Let’s take a quick look at how this is done…

For the image I’m going to use for this exercise I’m going to use the banner image from this blog; picture I took just outside of Queenstown in New Zealand. This image is a 1000 x 288 jpg image.

First we need to load the image into the database in a BLOB column of a table:

CREATE TABLE img_tab
   (id      NUMBER (6) NOT NULL
   ,img     BLOB)
/

CREATE DIRECTORY img_dir AS 'C:\oracle\images'
/

DECLARE
   l_blob        BLOB;
   l_bfile       BFILE := BFILENAME('IMG_DIR', 'cropped-queenstown_sml.jpg');
   l_dest_offset INTEGER := 1;
   l_src_offset  INTEGER := 1;
BEGIN

   dbms_lob.createtemporary
      (lob_loc => l_blob
      ,cache   => TRUE);
      
   dbms_lob.open (file_loc => l_bfile);
                           
   dbms_lob.loadblobfromfile
      (dest_lob    => l_blob
      ,src_bfile   => l_bfile
      ,amount      => dbms_lob.lobmaxsize
      ,dest_offset => l_dest_offset 
      ,src_offset  => l_src_offset);   
   
   INSERT INTO img_tab
      (id
      ,img)
   VALUES
      (1
      ,l_blob);

   COMMIT;
   
   dbms_lob.close (file_loc => l_bfile);
END;
/

Running the above gets us our image into a column named IMG in the table IMG_TAB. Now to see what Oracle ORDImage object type can tell us about our image. We can do this directly in SQL, without having to resort to PL/SQL (note, the second parameter of the ORDImage constructor, the “1”, instructs Oracle to process the image and obtain the metadata):

WITH imgs AS
   (SELECT ORDImage(img, 1) AS ord_img
    FROM   img_tab)
SELECT i.ord_img.getFileFormat() AS file_format
,      i.ord_img.getContentLength() AS content_length
,      i.ord_img.getHeight() AS height
,      i.ord_img.getWidth() AS width
FROM   imgs i

which gives us an output of:

FILE_FORMAT     CONTENT_LENGTH     HEIGHT      WIDTH
--------------- -------------- ---------- ----------
JFIF                     51960        288       1000

Exactly what we wanted to know.

A word of caution though. Running a query like this against a large number of stored BLOB images is likely to be processor and IO intensive. Pick a time of day when the system has capacity.