Oracle Precedence

This is a hypothetical example and, contrary to what I say below, I have nothing against French or German cars. I wanted to buy a new car so I made up a shortlist of manufacturers and put them in a table:

SQL> create table short_list
  2  (manufacturer varchar2(12))
  3  /

Table created.

SQL> insert into short_list values ('BMW')
  2  /

1 row created.

SQL> insert into short_list values ('Renault')
  2  /

1 row created.

SQL> insert into short_list values ('Subaru')
  2  /

1 row created.

SQL>

Then I decided I did not want to buy a French car so I made a list of French manufacturers and put them in a second table: 
 
SQL> create table french
  2  (manufacturer varchar2(12))
  3  /

Table created.

SQL> insert into french values ('Citroen')
  2  /

1 row created.

SQL> insert into french values ('Renault')
  2  /

1 row created.

SQL>

Then I decided I did not want to buy a German car either so I made a list of German manufacturers and put them in a third table:

SQL> create table german
  2  (manufacturer varchar2(12))
  3  /

Table created.

SQL> insert into german values ('BMW')
  2  /

1 row created.

SQL> insert into german values ('Mercedes')
  2  /

1 row created.

SQL>

Then I decided to see which manufacturers were still in my short list. My first attempt did not produce the result I expected. I think Oracle evaluated the not in before the union so the German manufacturers were still in the list:

SQL> select manufacturer from short_list
  2  where manufacturer not in
  3  (select manufacturer from french)
  4  union
  5  (select manufacturer from german)
  6  /

MANUFACTURER
------------
BMW
Mercedes
Subaru
 
SQL>

Swapping the select statements before and after the union confirmed that the problem was caused by Oracle's precedence rules. This time, the French manufacturers were still in the list:

SQL> select manufacturer from short_list
  2  where manufacturer not in
  3  (select manufacturer from german)
  4  union
  5  (select manufacturer from french)
  6  /

MANUFACTURER
------------
Citroen
Renault
Subaru

SQL>

To get the correct result I had to surround the select statements before and after the union with an extra set of brackets. This told Oracle to evaluate the union first:

SQL> select manufacturer from short_list
  2  where manufacturer not in
  3  ((select manufacturer from french)
  4    union
  5  (select manufacturer from german))
  6  /

MANUFACTURER
------------
Subaru

SQL>

logons current

This example was tested on an Oracle 9 database. The logons current statistic in V$SYSSTAT shows how many sessions are currently logged on:
  

SQL> SELECT VALUE FROM V$SYSSTAT WHERE NAME = 'logons current'
  2  /

     VALUE
---------- 
       205 

SQL> SELECT COUNT(*) FROM V$SESSION
  2  /

  COUNT(*)
---------- 
       205

SQL>

Maximum Permitted Size of a VARCHAR2 Column

The example below, which I ran on an Oracle 9.2.0.7.0 database, is too trivial to need explanation:

SQL> create table andrew (wide_column varchar2(4000))
  2  /

Table created.

SQL> drop table andrew
  2  /

Table dropped.

SQL> create table andrew (wider_column varchar2(4001))
  2  /
create table andrew (wider_column varchar2(4001))
                                           *
ERROR at line 1:
ORA-00910: specified length too long for its datatype

SQL>

DECODE

I was tidying up some old E-mails recently and found this example from 2008. I think it may have been from a Spanish student who needed help with his homework. He had a table called COCHES. It contained details of car manufacturers (in the column called TIPO). Each one made cars with 3 or 5 doors. He needed to update the table with one SQL statement and change the values in the doors (PUERTAS) column from 3 to 5 and vice versa. I suggested he could do it with decode as shown below:

SQL> select * from coches;
 
TIPO          PUERTAS
---------- ----------
Austin              5
Morris              3
 
SQL> update coches set puertas = decode (puertas,3,5,5,3);
 
2 rows updated.
 
SQL> select * from coches;
 
TIPO          PUERTAS
---------- ----------
Austin              3
Morris              5
 
SQL>

ORA-00205, ORA-00202 and ORA-27086

I heard of a problem which occurred after a UNIX server crashed. The server hosted several databases whose datafiles, control files and redo logs were on attached storage. The attached storage unit, a Celerra, did not fail. When the server was rebooted, the databases would not start because Oracle could not lock their control files. An ORA-00205 message was displayed and in the alert log there were ORA-00202 and ORA-27086 messages. The problem was caused because the attached storage was still holding locks which had been created before the server crashed. It was not possible to reboot the attached storage because it also had datafiles, control files and redo logs belonging to databases on other UNIX servers. To cure the problem the server's file systems had to be unmounted and mounted again. I decided to try to simulate this problem on a PC at home which runs Red Hat Linux and has a database called ANDREW. First I located one of its control files:

[oracle@localhost ANDREW]$ pwd
/home/oracle/andrew/ANDREW
[oracle@localhost ANDREW]$ ls control01.ctl
control01.ctl
[oracle@localhost ANDREW]$

Then I wrote the following simple C program to lock the control file for an hour:

#include <fcntl.h>
#include <unistd.h>
int main ()
{
int fildes;
int status;
fildes = open("control01.ctl", O_RDWR);
status = lockf(fildes, F_TLOCK, (off_t)10);
system ("sleep 3600");
}

And compiled and ran it in the directory containing the control file. When I tried to open the database, I saw the same ORA-00205 message (the date and time on the PC are wrong):

[oracle@localhost ~]$ sqlplus / as sysdba

SQL*Plus: Release 10.2.0.1.0 - Production on Tue Jan 1 02:13:10 2002

Copyright (c) 1982, 2005, Oracle.  All rights reserved.

Connected to an idle instance.

SQL> startup
ORACLE instance started.

Total System Global Area  167772160 bytes
Fixed Size                  1218316 bytes
Variable Size              62916852 bytes
Database Buffers          100663296 bytes
Redo Buffers                2973696 bytes
ORA-00205: error in identifying control file, check alert log for more info

SQL>

And the alert log showed an ORA-00202 and ORA-27086:

Tue Jan  1 02:13:16 2002
ORA-00202: control file: '/home/oracle/andrew/ANDREW/control01.ctl'
ORA-27086: unable to lock file - already in use
Linux Error: 11: Resource temporarily unavailable
Additional information: 8
Additional information: 4643
Tue Jan  1 02:13:16 2002
ORA-205 signalled during: ALTER DATABASE   MOUNT...
MMNL started with pid=12, OS id=4698

I used Ctrl + C to stop the C program which was locking the control file then I was able to open the database:

[oracle@localhost bdump]$ sqlplus / as sysdba

SQL*Plus: Release 10.2.0.1.0 - Production on Tue Jan 1 02:28:48 2002

Copyright (c) 1982, 2005, Oracle.  All rights reserved.

Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options

SQL> startup
ORA-01081: cannot start already-running ORACLE - shut it down first
SQL> shutdown
ORA-01507: database not mounted

ORACLE instance shut down.
SQL> startup
ORACLE instance started.

Total System Global Area  167772160 bytes
Fixed Size                  1218316 bytes
Variable Size              62916852 bytes
Database Buffers          100663296 bytes
Redo Buffers                2973696 bytes
Database mounted.
Database opened.
SQL>

NOT EXISTS v NOT IN

This example, tested on Oracle 10, shows how NOT EXISTS may be more efficient than NOT IN.
 

First, create an emp table for employees E1 through E9999:

SQL> create table emp
  2  (empno  varchar2(5),
  3   deptno varchar2(5))
  4  /

Table created.

SQL>

Next, create a dept table for departments D1 through D9999:

SQL> create table dept
  2  (deptno varchar2(5))
  3  /

Table created.

SQL>

Put employee E1 in department D1, employee E2 in department D2 etc:

SQL> declare
  2  begin
  3  for ctr in 1..9999 loop
  4  insert into emp values ('E'||ctr,'D'||ctr);
  5  insert into dept values ('D'||ctr);
  6  end loop;
  7  end;
  8  /

PL/SQL procedure successfully completed.

SQL> 

Create an index and gather statistics on the emp table:

SQL> create index deptno_index on emp(deptno)
  2  /

Index created.

SQL> exec dbms_stats.gather_table_stats -
> (ownname => 'SYSTEM', tabname => 'EMP');

PL/SQL procedure successfully completed.

SQL>

Then delete employee E5000. This will leave department D5000 empty:

SQL> delete from emp
  2  where empno = 'E5000';

1 row deleted.

SQL>

Finally, find the empty department using 2 different SQL statements:
 
SQL> set timing on
SQL> alter session
  2  set timed_statistics = true
  3  /

Session altered.

Elapsed: 00:00:00.05
SQL> alter session
  2  set sql_trace = true
  3  /

Session altered.

Elapsed: 00:00:00.05
SQL>

First, use NOT EXISTS. This runs in a fraction of a second:

SQL> select deptno from dept
  2  where not exists
  3  (select deptno from emp
  4  where emp.deptno = dept.deptno)
  5  /

DEPTN
-----
D5000

Elapsed: 00:00:00.09
SQL>

For the second version, use NOT IN. This takes around 7 seconds:

 
SQL> select deptno from dept
  2  where deptno not in
  3  (select deptno from emp)
  4  /

DEPTN
-----
D5000

Elapsed: 00:00:07.10
SQL> alter session
  2  set sql_trace = false
  3  /

Session altered.

Elapsed: 00:00:00.04
SQL>

By running the trace file through tkprof, you can see why NOT EXISTS is faster. It uses the index whereas NOT IN does not. As usual, click on the screen prints to display them at their original size and bring them into focus:

V$SESSION_LONGOPS

You can query long running SQL in V$SESSION_LONGOPS.

 

In the example shown, the SQL is a simple delete statement so I have managed to shorten the output by using a col sql_text format a20. Normally it is much longer.

 

Each time you run the SQL, the figures are recalculated. The elapsed_seconds column, which I have renamed as time_taken, should increase every time the SQL is rerun.

 

The time_remaining column, which I have renamed as time_left, is only an estimate. Normally it goes down each time the SQL is rerun but sometimes it goes up.

 

Once the SQL is finished, the executions column changes to 1 and the time_remaining column goes to 0:

 

  1  SELECT SQL_TEXT, EXECUTIONS,

  2  ELAPSED_SECONDS TIME_TAKEN,

  3  TIME_REMAINING TIME_LEFT

  4  FROM V$SESSION SES, V$SQL SQL,

  5  V$SESSION_LONGOPS LONGOPS

  6  WHERE SES.USERNAME       = 'BRAID'

  7  AND   SES.SQL_ADDRESS    = SQL.ADDRESS

  8  AND   SES.SQL_HASH_VALUE = SQL.HASH_VALUE

  9  AND   SQL.ADDRESS        = LONGOPS.SQL_ADDRESS

 10* AND   SQL.HASH_VALUE     = LONGOPS.SQL_HASH_VALUE

SQL> /

 

SQL_TEXT             EXECUTIONS TIME_TAKEN  TIME_LEFT

-------------------- ---------- ---------- ----------

delete b_alp                  0        204       1010

 

SQL> /

 

SQL_TEXT             EXECUTIONS TIME_TAKEN  TIME_LEFT

-------------------- ---------- ---------- ----------

delete b_alp                  0        710        507

 

SQL> /

 

SQL_TEXT             EXECUTIONS TIME_TAKEN  TIME_LEFT

-------------------- ---------- ---------- ----------

delete b_alp                  1       1220          0

 

SQL>