Changing primary key index

While investigating a recent performance issue I arrived at the conclusion that an index comprising of the primary key plus one other column would be required. I have previously noted that a unique constraint may be enforced using a non-unique index.

Having another index on the table that mirrored the primary key plus another column seemed wasteful so I set about removing the existing primary key index and replacing it with the new index. Thankfully Oracle provides the ability to alter an existing primary key constraint to be enforced through a different index without the need of dropping and recreating the constraint, as I’ll illustrate using the following set-up:

SQL>CREATE TABLE tab
  2     (id     NUMBER (6) NOT NULL
  3     ,cat    NUMBER (6) NOT NULL
  4     ,val    NUMBER (6) NOT NULL)
  5  /

Table created.

SQL>ALTER TABLE tab
  2     ADD CONSTRAINT tab_pk
  3     PRIMARY KEY (id)
  4  /

Table altered.

SQL>CREATE INDEX tab_pk2
  2  ON tab (id, cat)
  3  /

Index created.

SQL>SELECT index_name
  2  ,      uniqueness
  3  FROM   user_indexes
  4  WHERE  table_name = 'TAB'
  5  /

INDEX_NAME      UNIQUENESS
--------------- ---------------
TAB_PK          UNIQUE
TAB_PK2         NONUNIQUE

In the above we have a simple table with a primary key defined on the column ID, a unique index TAB_PK on the ID column and a non-unique index on the ID and CAT colums. If we were to simply try removing the unique index that’s currently enforcing the primary key constraint then we’ll be met with:

SQL>DROP INDEX tab_pk
  2  /
DROP INDEX tab_pk
           *
ERROR at line 1:
ORA-02429: cannot drop index used for enforcement of unique/primary key

What we need to do first is to tell Oracle to enforce the primary key constraint using the non-unique index:

SQL>ALTER TABLE tab
  2     MODIFY CONSTRAINT tab_pk
  3     USING INDEX tab_pk2
  4  /

Table altered.

This is now where things get a bit more interesting. If we look at the indexes on our table we find:

SQL>SELECT index_name
  2  ,      uniqueness
  3  FROM   user_indexes
  4  WHERE  table_name = 'TAB'
  5  /

INDEX_NAME      UNIQUENESS
--------------- ---------------
TAB_PK2         NONUNIQUE

Hey, what hapened to our unique index? It seems that Oracle has dropped it. So why did that happen? As it turns out, if the index is created automatically as part of the creation of the primary key constraint then it will be automatically removed when a new index is used to enforce the constraint. If the index were created before the constraint then this doesn’t happen, as illustrated below:

SQL>SELECT index_name
  2  ,      uniqueness
  3  FROM   user_indexes
  4  WHERE  table_name = 'TAB'
  5  /

no rows selected

SQL>CREATE UNIQUE INDEX tab_pk
  2     ON tab (id)
  3  /

Index created.

SQL>ALTER TABLE tab
  2     ADD CONSTRAINT tab_pk
  3     PRIMARY KEY (id)
  4     USING INDEX tab_pk
  5  /

Table altered.

SQL>CREATE INDEX tab_pk2
  2  ON tab (id, cat)
  3  /

Index created.

SQL>ALTER TABLE tab
  2     MODIFY CONSTRAINT tab_pk
  3     USING INDEX tab_pk2
  4  /

Table altered.

SQL>SELECT index_name
  2  ,      uniqueness
  3  FROM   user_indexes
  4  WHERE  table_name = 'TAB'
  5  /

INDEX_NAME      UNIQUENESS
--------------- ---------------
TAB_PK          UNIQUE
TAB_PK2         NONUNIQUE

In the above we created the index for enforcing the primary key before declaring the constraint. When we switched to using a new index for the constraint the original index remained.

Some simple renaming is all that remains:

SQL>ALTER INDEX tab_pk
  2     RENAME TO tab_pk_old
  3  /

Index altered.

SQL>ALTER INDEX tab_pk2
  2     RENAME TO tab_pk
  3  /

Index altered.

I must say that I prefer the old index remaining behind after the change to the primary key constraint. Leaving the original index in place allows me the opportunity to revert the change in the unlikely event that the new index causes query execution plan problems. The original index can be marked as invisible:

ALTER INDEX tab_pk_old INVISIBLE

and should problems arise it can quickly be reinstated without having to do rebuild it from scratch, which is important for any reasonably large table:

ALTER INDEX tab_pk_old VISIBLE

If no problems arise after the change of primary key index then the old (now invisible) index can be dropped at some later point in time:

DROP INDEX tab_pk_old

DML within DML

As noted in my previous post, a recent code review revealed a couple of things that I wasn’t aware of… which is one of the pleasures of working in IT; learning new things. Aggregation within the RETURNING INTO clause of a DML statement was a good thing to find out about. I’m having a hard time thinking of a scenario that would legitimately justify the other feature; performing DML within DML…

To demonstrate, let’s create two simple tables, TAB_A and TAB_B, each with two columns; ID and VAL. Both table start off with a small set of data:

CREATE TABLE tab_a
   (id    NUMBER (6) NOT NULL
   ,val   NUMBER (6) NOT NULL
   ,CONSTRAINT tab_a_pk PRIMARY KEY (id))
/

CREATE TABLE tab_b
   (id    NUMBER (6) NOT NULL
   ,val   NUMBER (6) NOT NULL
   ,CONSTRAINT tab_b_pk PRIMARY KEY (id))
/

INSERT INTO tab_a
SELECT ROWNUM
,      ROWNUM
FROM   dual
CONNECT BY ROWNUM <= 10
/

INSERT INTO tab_b
SELECT ROWNUM
,      ROWNUM
FROM   dual
CONNECT BY ROWNUM <= 10
/

COMMIT
/

Next we’ll create a package with two routines. The first routine is a function that sets the VAL value in TAB_A to a random value and returns that value. The second routine is a procedure that sets the VAL value in TAB_B using the return value of the first function:

CREATE OR REPLACE PACKAGE proc_pkg
AS
   FUNCTION val_a (p_id IN tab_a.id%TYPE)
      RETURN tab_a.val%TYPE;
   PROCEDURE update_b (p_id IN tab_b.id%TYPE);
END proc_pkg;
/

CREATE OR REPLACE PACKAGE BODY proc_pkg
AS
   FUNCTION val_a (p_id IN tab_a.id%TYPE)
      RETURN tab_a.val%TYPE
   AS
      l_return_value tab_a.val%TYPE;
   BEGIN
      UPDATE tab_a
      SET    val = TRUNC(dbms_random.value (100,200))
      WHERE  id = p_id
      RETURNING val INTO l_return_value;
      RETURN l_return_value;
   END val_a;
   
   PROCEDURE update_b  (p_id IN tab_b.id%TYPE)
   AS
   BEGIN
      UPDATE tab_b
      SET    val = val_a (id)
      WHERE  id = p_id;
   END update_b;
END proc_pkg;
/

If you look at the UPDATE_B routine, we’re updating TABL_B but since we invoke function VAL_A we’re also updating TAB_A. My initial thought when seeing code like this was “hey, that won’t work”… and it seems I was wrong:

SQL> EXEC proc_pkg.update_b (5)

PL/SQL procedure successfully completed.

SQL> SELECT *
  2  FROM   tab_a
  3  WHERE  id = 5
  4  /

        ID        VAL
---------- ----------
         5        163

SQL> SELECT *
  2  FROM   tab_b
  3  WHERE  id = 5
  4  /

        ID        VAL
---------- ----------
         5        163

Okay, so it works; performing DML on one table can invoke a function that performs DML on another table. We can insert, update and delete data on a table from a PL/SQL function invoked via DML on a different table. If we were to try running a simple SELECT query using a function that does DML then we get ORA-14551:

SQL> SELECT id
  2  ,      proc_pkg.val_a (id)
  3  FROM   tab_a
  4  /
,      proc_pkg.val_a (id)
       *
ERROR at line 2:
ORA-14551: cannot perform a DML operation inside a query
ORA-06512: at "DEVELOPER.PROC_PKG", line 8

Yes, you can get around this particular error by making the function an autonomous transaction… but don't do that, it's not what autonomous transaction are designed for.

My problem is that I have difficulty thinking of a scenario whereby doing DML from within DML might be a good idea instead of splitting it into separate operations. In some ways it’s similar to a row level trigger and I dislike that approach to data processing as it leads to spaghetti code. Sure, some sort of demo where I want to log details about the execution of the function as it executes I can understand, but in normal business type processing…? Furthermore, keep in mind that we're essentially doing row by row type processing here; our TAB_A DML function will be invoked for each row modified in TAB_B so we're not doing bulk processing on TAB_A. This is likely to be a performance inhibitor for the TAB_A changes.

I guess this is something that I would classify as a nice feature but use only with caution when no other option exists… although I'm happy to take advice on this.