Who's online

There are currently 0 users and 28 guests online.

Recent comments


The first matching row

I was recently asked when presented with a query along the lines:

SQL> select * from
  2  ( select x
  3    from   t1
  4    where  x = :b1
  5    union all
  6    select x
  7    from   t2
  8    where  x = :b1
  9  )
 10  where rownum = 1
 11  /

and asked – will the “ROWNUM” lead Oracle to be smart enough to stop after the “top” half of the UNION ALL if it gets row back ?

Let’s find out. First we create tablespaces for tables to be stored, and plonk a table in each.

Tightening up your data model

Having NOT NULL constraints declared on columns that are genuinely not nullable not only is good practice, but can yield performance gains. Here’s a little routine that lists those columns that probably need a NOT NULL constraint. It looks at all columns and

  • check to see if a constraint with definition “COLUMN_NAME” IS NOT NULL is present
  • counts the null values

If the latter is zero, and no constraint was found, then we print out ‘alter table … ‘ DDL for those columns that may need such a constraint defined.

Overcoming Your Oracle Data Insecurity Webinar


Overcoming Your Oracle Data Insecurity

Webinar – Wednesday, March 30 – 10am PT

In 2016, the risks and reasons why companies should secure their sensitive data are well known (Sony, Target, and Anthem just to name a few examples). Yet, most companies have pitiful or limited data security in their Oracle databases.

TOP-N histograms on 12c

I had an interesting question on AskTom today, with a nicely provided test case !!!,  so I thought I’d share it here with some additional commentary.

Fibre Channel vs NFS

NFS is the future, has larger bandwidth than FC, market is growing faster than FC, cheaper, easier, more flexible, cloud ready and improving faster than FC.

Screen Shot 2016-03-03 at 8.56.29 PM


photo by Paul Oka

Fibre Channel

FOLLOWS clause

In early Oracle releases, you could have 1 trigger per type of trigger per table (eg if you had a before-row-insert trigger, you could have only 1). Now there is no limit. But that does raise the question – if I have “n” BEFORE ROW triggers on a table, in which order do they fire ? Let’s look at the following example:

SQL> drop table t1 purge;

Table dropped.

SQL> create table T1 ( x int, y int, z int );

Table created.

SQL> create or replace
  2  trigger trg1
  3  before insert on t1
  4  for each row
  5  begin
  6    :new.y := :new.x;
  7  end;
  8  /

Trigger created.

SQL> create or replace
  2  trigger trg2
  3  before insert on t1
  4  for each row
  5  begin
  6    :new.z := :new.y;
  7  end;
  8  /

Trigger created.

Seems simple enough…Copy ‘x’ into ‘y’, and then copy ‘y’ into ‘z’. So lets see what happens

Is a year a leap year ?

This post seems timely given that yesterday was Feb 29. 

In almost every case I can think of, you should be relying on native Oracle date functions to perform any kind of date arithmetic.

This is perhaps one of the very very few exceptions Smile

SQL> set timing off
SQL> create or replace
  2  function is_leap_year1(y number) return boolean is
  3    x date;
  4  begin
  5    x := to_date('2902'||y,'ddmmyyyy');
  6    return true;
  7  exception
  8    when others then return false;
  9  end;
 10  /

Function created.

SQL> create or replace
  2  function is_leap_year2(y number) return boolean is
  3  begin
  4    return mod(y,4)=0 and ( mod(y,100) != 0 or mod(y,400) = 0 );
  5  end;
  6  /

Function created.


Loading LOB from a file

I observed this idiosyncracy recently when loading some lob from external files using PL/SQL:

First we’ll create a file to be loaded, and ‘wc’ tells us it is 75 bytes in size 

SQL> !echo "This is line 1" > /tmp/lobfile
SQL> !echo "This is line 2" >> /tmp/lobfile
SQL> !echo "This is line 3" >> /tmp/lobfile
SQL> !echo "This is line 4" >> /tmp/lobfile
SQL> !echo "This is line 5" >> /tmp/lobfile
SQL> !wc /tmp/lobfile
       5      20      75 /tmp/lobfile

Then create a standard routine to load it into the database

Amped on Amper

This “problem” rates in the top 10 all time viewed questions on AskTom, and it demonstrates the importance of reading the Concepts guide with Oracle

SQL> create table CARTOONS ( name varchar2(30));

Table created.

SQL> insert into CARTOONS values ('Tom & Jerry');
Enter value for jerry:

And the question comes in: “How can I insert an ampersand into my table?”

How can I see my invisible columns

A cool new feature in 12c is the ability to make a column invisible.  The concept has existed since 8i to handle things like “set unused” and function based indexes, but now it is available to developers directly.

SQL> create table T ( c1 int, c2 int );

Table created.

SQL> desc T
 Name                          Null?    Type
 ----------------------------- -------- -----------------
 C1                                     NUMBER(38)
 C2                                     NUMBER(38)

SQL> alter table T modify c2 invisible;

Table altered.

SQL> desc T
 Name                          Null?    Type
 ----------------------------- -------- -----------------
 C1                                     NUMBER(38)

So at this point… how I can tell in SQL Plus that I even have an invisible column, without querying the data dictionary.