DEFAULT SEQ.NEXTVAL in 12c

One of the cool things in 12c is that (finally after all these years) a sequence can be assigned as the default value for a column.

Of course, some may say “big deal” – after all, it is trivial to create a simple trigger to do the job.  And perhaps, we “crafty” folks at Oracle are just doing a hidden trigger in the background anyway Smile

Well… we’re not.  We’ve put some work into this to make it sing.  Let’s look at a demo.  First, here’ s the trigger-based approached.


SQL> create sequence SEQ cache 1000;

Sequence created.

SQL>
SQL> create table T ( x int , y int);

Table created.

SQL>
SQL> create or replace
  2  trigger TRG before insert on T
  3  for each row
  4  begin
  5      :new.x := seq.nextval;
  6  end;
  7  /

Trigger created.

SQL>
SQL> drop table logger purge;

Table dropped.

SQL>
SQL> create table logger ( sid int, started timestamp, stopped timestamp);

Table created.

SQL>
SQL> create or replace procedure hammer is
  2  begin
  3   insert into logger values ( sys_context('USERENV','SID'), systimestamp , null );
  4   for i in 1 .. 100000 loop
  5     insert into T (y) values (i);
  6     commit;
  7   end loop;
  8   update logger set stopped = systimestamp where sid = sys_context('USERENV','SID');
  9   commit;
 10  end;
 11  /

Procedure created.

SQL>
SQL> declare
  2   j int;
  3  begin
  4  for i in 1 .. 4 loop
  5    dbms_job.submit(j,'hammer;');
  6  end loop;
  7  end;
  8  /

PL/SQL procedure successfully completed.

SQL>
SQL> commit;

Commit complete.

SQL>


We have a table T with a standard trigger-based sequence number assignment. We are going to hammer that table (hence the procedure name) with 4 concurrent sessions, each doing 100,000 inserts into the table to mimic a high utilisation transaction table. We can see what throughput we achieved once the jobs are done.


SQL> select 4*100000 / avg(extract(second from (stopped-started))) rows_per_sec
  2  from logger;

ROWS_PER_SEC
------------
  19965.3103

Now let’s be clear. Just under 20,000 inserts per second (on my laptop) is nothing to sneeze at. The trigger certainly is not “mauling” the performance, but the question is – would it be better using the new native default feature in 12c. Let’s take a look


SQL> drop table T purge;

Table dropped.

SQL>
SQL> drop sequence SEQ;

Sequence dropped.

SQL> create sequence SEQ cache 1000;

Sequence created.

SQL>
SQL> create table T ( x int default seq.nextval, y int);

Table created.

SQL>
SQL> drop table logger purge;

Table dropped.

SQL>
SQL> create table logger ( sid int, started timestamp, stopped timestamp);

Table created.

SQL>
SQL> create or replace procedure hammer is
  2  begin
  3   insert into logger values ( sys_context('USERENV','SID'), systimestamp , null );
  4   for i in 1 .. 100000 loop
  5     insert into T (y) values (i);
  6     commit;
  7   end loop;
  8   update logger set stopped = systimestamp where sid = sys_context('USERENV','SID');
  9   commit;
 10  end;
 11  /

Procedure created.

SQL>
SQL> declare
  2   j int;
  3  begin
  4  for i in 1 .. 4 loop
  5    dbms_job.submit(j,'hammer;');
  6  end loop;
  7  end;
  8  /

PL/SQL procedure successfully completed.

SQL>
SQL> commit;

Commit complete.

SQL> select 4*100000 / avg(extract(second from (stopped-started))) rows_per_sec from logger;

ROWS_PER_SEC
------------
  27709.1439

1 row selected.

Man… that is smoking hot performance Smile

And so there you have it.  The new DEFAULT additions in 12c are a very nice touch indeed.

Never rely on an assumed order

We’ve (hopefully) all had it drummed into us enough times – you cannot assume the order of results from queries unless you explicitly include an ORDER BY statement.

Here’s another trivial example of this- I was doing a little demo script for an AskTom question.

Here’s the script running in 11.2.0.4


SQL> create table T
  2  as
  3  select
  4    rownum c1,
  5    mod(rownum,10) c2,
  6    trunc(rownum/1000) c3,
  7    mod(rownum,100) c4,
  8    trunc(rownum/100) c5,
  9    mod(rownum,1000) c6,
 10    trunc(rownum/10) c7
 11  from dual
 12  connect by level <= 100;

Table created.

SQL>
SQL> begin
  2  for i in 1 .. 7 loop
  3    execute immediate 'create bitmap index IX'||i||' on T ( c'||i||')';
  4  end loop;
  5  end;
  6  /

PL/SQL procedure successfully completed.

SQL>
SQL> select
  2    index_name,
  3    leaf_blocks,
  4    avg_leaf_blocks_per_key,
  5    avg_data_blocks_per_key
  6  from user_indexes
  7  where table_name = 'T';

INDEX_NAME                     LEAF_BLOCKS AVG_LEAF_BLOCKS_PER_KEY AVG_DATA_BLOCKS_PER_KEY
------------------------------ ----------- ----------------------- -----------------------
IX1                                      1                       1                       1
IX2                                      1                       1                       1
IX3                                      1                       1                       1
IX4                                      1                       1                       1
IX5                                      1                       1                       1
IX6                                      1                       1                       1
IX7                                      1                       1                       1

And here’s the same script running in 12.1.0.2


SQL> create table T
  2  as
  3  select
  4    rownum c1,
  5    mod(rownum,10) c2,
  6    trunc(rownum/1000) c3,
  7    mod(rownum,100) c4,
  8    trunc(rownum/100) c5,
  9    mod(rownum,1000) c6,
 10    trunc(rownum/10) c7
 11  from dual
 12  connect by level <= 100;

Table created.

SQL>
SQL> begin
  2  for i in 1 .. 7 loop
  3    execute immediate 'create bitmap index IX'||i||' on T ( c'||i||')';
  4  end loop;
  5  end;
  6  /

PL/SQL procedure successfully completed.

SQL>
SQL> select
  2    index_name,
  3    leaf_blocks,
  4    avg_leaf_blocks_per_key,
  5    avg_data_blocks_per_key
  6  from user_indexes
  7  where table_name = 'T';

INDEX_NAME                     LEAF_BLOCKS AVG_LEAF_BLOCKS_PER_KEY AVG_DATA_BLOCKS_PER_KEY
------------------------------ ----------- ----------------------- -----------------------
IX7                                      1                       1                       1
IX6                                      1                       1                       1
IX5                                      1                       1                       1
IX4                                      1                       1                       1
IX3                                      1                       1                       1
IX2                                      1                       1                       1
IX1                                      1                       1                       1

EXCHANGE PARTITION revisited

A while back I did a blog post showing that when you have set a column to UNUSED, it still “counts” as a validation step when doing exchange partition.  So if you had a partitioned table that previously had a column set to unused, then the candidate table to be exchanged in also had to have undergone the alteration.  Just having the visible columns aligned is not sufficient.

In the comments, Sergey presented an interesting variation of this, which we will look at below


SQL> drop table t purge;

Table dropped.

SQL> drop table tp purge;

Table dropped.

SQL>
SQL> create table t(id integer, c1 integer default 999 not null);

Table created.

SQL>
SQL> create table tp(id integer)
  2    partition by range (id)
  3   (partition p1 values less than (1),
  4    partition p2 values less than (2),
  5    partition p3 values less than (3),
  6    partition p9 values less than (maxvalue)
  7   );

Table created.

SQL>
SQL> alter table tp add c1 integer default 999 not null;

Table altered.


So in our partitioned table, at some stage we added a new column C1 and its a non-null column. This in itself is pretty cool, in that in recent releases, this is an instant operation – the historical rows do not need to be revisited and updated.

Our two tables look identical, but let’s see what happens when we do an exchange:



SQL>
SQL> set lines 60
SQL> desc T
 Name                          Null?    Type
 ----------------------------- -------- --------------------
 ID                                     NUMBER(38)
 C1                            NOT NULL NUMBER(38)

SQL> desc TP
 Name                          Null?    Type
 ----------------------------- -------- --------------------
 ID                                     NUMBER(38)
 C1                            NOT NULL NUMBER(38)


SQL>
SQL> alter table tp exchange partition p2 with table t;
alter table tp exchange partition p2 with table t
*
ERROR at line 1:
ORA-14097: column type or size mismatch in ALTER TABLE EXCHANGE PARTITION


We get a similar issue. Because one table had the default column definition from creation, the other via an alteration, the tables are still seen as “different”. If we dive into the data dictionary internals, even without really knowing the particulars, we can see a difference in the column definitions


SQL> set lines 120
SQL>
SQL> select obj#, name, col#, segcol#, property
  2  from sys.col$
  3  where obj# in (
  4    select object_id
  5    from user_objects
  6    where object_name in ('T','TP')
  7    and object_type = 'TABLE'
  8  )
  9  order by obj#, segcol#;

      OBJ# NAME                                           COL#    SEGCOL#   PROPERTY
---------- ---------------------------------------- ---------- ---------- ----------
    115959 ID                                                1          1          0
    115959 C1                                                2          2          0
    115960 ID                                                1          1          0
    115960 C1                                                2          2 1073741824

4 rows selected.


So how do we solve it ? There is an event you can set so that your template table for exchange will be mapped nicely to the partitioned table. So we just set that event and use the partition table as a source


SQL>
SQL>
SQL> drop table t purge;

Table dropped.

SQL>
SQL> alter session set events='14529 trace name context forever';

Session altered.

SQL> create table T as select * from TP where 1=0;

Table created.

SQL> alter session set events='14529 trace name context off';

Session altered.

SQL>
SQL> alter table tp exchange partition p2 with table t;

Table altered.

SQL>

As you can see, you only need the event for the creation of your table. After that, its business as usual.

Public / private cursors

As we all know (hopefully), we should always deploy named program units (procedures/functions) within packages. Its a great method of encapsulation of logic.

However, not many are aware that its not just procedures and functions that can have a public (package spec) and private (package body) component.

Cursors can also be defined in the same way as the following example shows:




CREATE PACKAGE my_pkg AS
   CURSOR c_recent_hires RETURN emp%ROWTYPE;
   ...
END emp_stuff;
/

CREATE PACKAGE BODY my_pkg AS
   CURSOR c_recent_hires RETURN emp%ROWTYPE IS
      SELECT * FROM emp 
      WHERE hiredate > sysdate-30;
   ...
END emp_stuff;
/


AskTom–some thoughts on the future

The structure of an AskTom question contains four elements:

  • The original question
  • Our answer
  • A review, which can be posted by anyone
  • And then we can opt to add a single Followup to any of those reviews

image

We’re thinking of changing AskTom to allow greater flexibility, namely:

  • The original question (unchanged)
  • Our answer (unchanged)
  • A review, which can be posted by anyone (unchanged)
  • We will then allow anyone to add a Followup to a review, and also, there could be any number of Followups attached to a single review.

Let us know your thoughts via Comment on this blog.  If you prefer a private response, you can also send your thoughts to: asktom_us@oracle.com

What do you think ?

Bitwise operations

The long existing BITAND function is now within the documentation, to let you do logical AND on two numbers, and is also available from PL/SQL

image

 

If you need other bit operations, a little boolean math should suffice Smile Just make sure you stay within the limits of BINARY_INTEGER


CREATE OR replace FUNCTION bitor( x IN binary_integer, y IN binary_integer ) RETURN binary_integer  AS
BEGIN
    RETURN x - bitand(x,y) + y;
END;
/

CREATE OR replace FUNCTION bitxor( x IN binary_integer, y IN binary_integer ) RETURN binary_integer  AS
BEGIN
    RETURN bitor(x,y) - bitand(x,y);
END;
/



Auto-backups of PLSQL source

I saw this on an ideas forum today

image

and whilst most people would take care of this with a source code control system, its also true that people might do several compilations / tests with their PLSQL source before checking it in officially to their source repository.

So the idea has some merit… and maybe we can roll our own without too much fuss. I make no claims that this is a ‘complete’ solution, but it should get you going Smile



SQL> set timing off
SQL> drop trigger plsql_trigger;

Trigger dropped.

SQL>
SQL> drop table plsql_params purge;

Table dropped.

SQL> drop table plsql_history cascade constraints purge;

Table dropped.

SQL> drop table plsql_history_source cascade constraints  purge;

Table dropped.

SQL> drop table plsql_log cascade constraints  purge;

Table dropped.

SQL>
SQL>
SQL> create table plsql_params
  2  as select 3 versions_kept from dual;

Table created.

SQL>
SQL> create table plsql_history (
  2     TSTAMP     TIMESTAMP
  3    ,OWNER      VARCHAR2(128)
  4    ,NAME       VARCHAR2(128)
  5    ,TYPE       VARCHAR2(12)
  6    ,constraint plsql_history_pk primary key ( tstamp,owner,name,type)
  7  )
  8  organization index;

Table created.

SQL>
SQL> create table plsql_history_source (
  2     TSTAMP     TIMESTAMP
  3    ,OWNER      VARCHAR2(128)
  4    ,NAME       VARCHAR2(128)
  5    ,TYPE       VARCHAR2(12)
  6    ,LINE       NUMBER
  7    ,TEXT       VARCHAR2(4000)
  8    ,constraint plsql_history_source_pk primary key ( tstamp,owner,name,type,line)
  9    ,constraint plsql_history_source_fk foreign key ( tstamp,owner,name,type) references plsql_history ( tstamp,owner,name,type )
 10  );

Table created.

SQL>
SQL> create table plsql_log
  2  (
  3     TSTAMP     TIMESTAMP
  4    ,MSG        varchar2(1000)
  5  );

Table created.

SQL>
SQL>
SQL> create or replace trigger plsql_trigger
  2  before create on SCHEMA
  3  declare
  4    l_owner   varchar2(128) := ora_dict_obj_owner;
  5    l_name    varchar2(128) := ora_dict_obj_name;
  6    l_type    varchar2(128) := ora_dict_obj_type;
  7    l_archived timestamp := systimestamp;
  8    l_tstamp_to_clear timestamp;
  9
 10    procedure logger(m varchar2) is
 11      pragma autonomous_transaction;
 12    begin
 13      insert into plsql_log values (systimestamp,m);
 14      commit;
 15    end;
 16  begin
 17    if l_type in ('FUNCTION','PROCEDURE','PACKAGE','PACKAGE BODY') then
 18       insert into plsql_history values (l_archived, l_owner, l_name, l_type);
 19
 20       insert into plsql_history_source
 21       select l_archived, owner, name, type, line, text
 22       from   dba_source
 23       where  owner = l_owner
 24       and    name = l_name
 25       and    type = l_type;
 26
 27       logger('Archived '||l_type||'-'||l_owner||'.'||l_name);
 28
 29       select max(case when tot > versions_kept and seq = tot - versions_kept then tstamp end)
 30       into   l_tstamp_to_clear
 31       from   plsql_params,
 32              ( select ph.*,
 33                       row_number() over ( order by tstamp ) as seq,
 34                       count(*) over () as tot
 35                from plsql_history ph
 36              )
 37       where  owner = l_owner
 38       and    name = l_name
 39       and    type = l_type;
 40
 41       if l_tstamp_to_clear is not null then
 42         logger('Clearance timestamp for '||l_type||'-'||l_owner||'.'||l_name||' is '||l_tstamp_to_clear);
 43
 44         delete from plsql_history_source where tstamp <= l_tstamp_to_clear;
 45         delete from plsql_history        where tstamp <= l_tstamp_to_clear;  46         logger('Cleared '||sql%rowcount||' versions for '||l_type||'-'||l_owner||'.'||l_name);  47       end if;  48    end if;  49  end;  50  / Trigger created. SQL>
SQL> create or replace procedure P_TEST is
  2  begin
  3    null; -- version 1
  4  end;
  5  /

Procedure created.

SQL>
SQL> create or replace procedure P_TEST is
  2  begin
  3    null; -- version 2
  4  end;
  5  /

Procedure created.

SQL>
SQL>
SQL> create or replace procedure P_TEST is
  2  begin
  3    null; -- version 3
  4  end;
  5  /

Procedure created.

SQL>
SQL>
SQL> create or replace procedure P_TEST is
  2  begin
  3    null; -- version 4
  4  end;
  5  /

Procedure created.

SQL>
SQL>
SQL> create or replace procedure P_TEST is
  2  begin
  3    null; -- version 5
  4  end;
  5  /

Procedure created.

SQL> select * from plsql_history;

TSTAMP                             OWNER        NAME         TYPE
---------------------------------- ------------ ------------ ---------------
02-FEB-16 12.12.42.280000 PM       MCDONAC      P_TEST       PROCEDURE
02-FEB-16 12.12.42.306000 PM       MCDONAC      P_TEST       PROCEDURE
02-FEB-16 12.12.42.339000 PM       MCDONAC      P_TEST       PROCEDURE

SQL>
SQL> select tstamp, text from plsql_history_source;

TSTAMP                             TEXT
---------------------------------- ------------------------------------------------------------
02-FEB-16 12.12.42.280000 PM       procedure P_TEST is
02-FEB-16 12.12.42.280000 PM       begin
02-FEB-16 12.12.42.280000 PM         null; -- version 2
02-FEB-16 12.12.42.280000 PM       end;
02-FEB-16 12.12.42.306000 PM       procedure P_TEST is
02-FEB-16 12.12.42.306000 PM       begin
02-FEB-16 12.12.42.306000 PM         null; -- version 3
02-FEB-16 12.12.42.306000 PM       end;
02-FEB-16 12.12.42.339000 PM       procedure P_TEST is
02-FEB-16 12.12.42.339000 PM       begin
02-FEB-16 12.12.42.339000 PM         null; -- version 4
02-FEB-16 12.12.42.339000 PM       end;

12 rows selected.

SQL>
SQL> select * from plsql_log;

TSTAMP                             MSG
---------------------------------- --------------------------------------------------------------------------------
02-FEB-16 12.12.42.221000 PM       Archived PROCEDURE-MCDONAC.P_TEST
02-FEB-16 12.12.42.255000 PM       Archived PROCEDURE-MCDONAC.P_TEST
02-FEB-16 12.12.42.281000 PM       Archived PROCEDURE-MCDONAC.P_TEST
02-FEB-16 12.12.42.307000 PM       Archived PROCEDURE-MCDONAC.P_TEST
02-FEB-16 12.12.42.307000 PM       Clearance timestamp for PROCEDURE-MCDONAC.P_TEST is 02-FEB-16 12.12.42.181000 PM
02-FEB-16 12.12.42.313000 PM       Cleared 1 versions for PROCEDURE-MCDONAC.P_TEST
02-FEB-16 12.12.42.340000 PM       Archived PROCEDURE-MCDONAC.P_TEST
02-FEB-16 12.12.42.340000 PM       Clearance timestamp for PROCEDURE-MCDONAC.P_TEST is 02-FEB-16 12.12.42.254000 PM
02-FEB-16 12.12.42.340000 PM       Cleared 1 versions for PROCEDURE-MCDONAC.P_TEST

9 rows selected.