Oracle SQL tricks

Here are some useful SQL tricks, tested on an Oracle database.

  1. The Oracle 9i manuals are supposed to be online (free username/password required)
  2. A PL/SQL package to generate random numbers
  3. What if you want a random number in the where clause, like, so you can choose 1/10 of the rows at random? Use a hash instead.

      select * from emp where dbms_utility.get_hash_value(dump(rowid),0,100)<10;
  4. The biggest difference between Oracle and everyone else is the locking mechanism. Everyone else gets share locks on data when they query it. That means other queries can also see the data, but queries block writes and writes block queries. (Update: the default for everyone else, for for example for DB2, is for queries not to block writes. Instead they show any data committed at the time the query internally fetches the data. This means each query result can be internally inconsistent, but it allows greater concurrency.) Oracle doesn't have share locks. Instead it uses snapshots, which are points in time. A query looks at the data as of some point in time. If someone changes the data later, that's OK, the query still sees the old version of the data. Each query result is internally consistent, but consecutive queries may not be consistent with one another. Queries don't block writes and writes don't block queries. Writes still block writes. Both share locks and snapshots are reasonable concurrency models, but they aren't the same. It's wrong to think in terms of one when working with the other.
  5. Derived tables (from-clause subqueries, inline views):
      select * from (select * from emp);
      insert into (select deptno from dept)
        select trunc(a) from (select sum(empno)/5000 a from emp);
    This has the same effect as if you used a view. It is useful for queries that have a repeated complex expression; for example
      select x from (select avg(sin(empno)) x from emp group by deptno)
        where x > 0;
    It's also useful when you want to use one of the values found by a subquery, for example when deleting all outdated entries from a log:
      delete from log where rowid in
        (select rowid from log
         select a.rowid
           from log a, (select id, max(time) mtime from log group by id) b
           where = and a.time = b.mtime);

    As of 9i, inline views can also be expressed with a "WITH clause", for example

      with sub1 as (select deptno, avg(sin(empno)) x from emp group by deptno)
      select a.deptno, a.x, b.x from sub1 a, sub1 b where a.deptno > b.deptno;
  6. When should varchar2 be used instead of char? Always. Varchar2 is always faster, more space efficient, less buggy, and its comparison rules are more likely to be what you expect.
  7. Is SQL case sensitive? No. Can I give tables case-sensitive names with weird characters in them? Yes. Quote them, like so:
      create table "Table" ("My column is KEWL!!!" int);
  8. How do I insert data into an nvarchar2 column? From 9i onwards, just like any other character column, and the character set is guaranteed to hold the Unicode repertoire and measure characters using UCS2 codepoints. There is implicit conversion between char and nchar. Before 9i, avoid nvarchar2. If you really must use it, you insert like so: "insert into nemp (ename) values (n'SCOTT')".
  9. Building a big bogus table:
      create table a (a1 number, a2 varchar2(50));
        for i in 1..10000 loop
          insert into a values (i, 'I am a unique and extraordinary individual');
        end loop;
      alter table a add constraint apk primary key (a1);
      select count(*) from a;         -- 10000 rows
  10. Like the making of sausage and politics, it is best not to understand the making of Oracle data types.
      select dump(1), dump(-1), dump(sysdate) from dual;
    Ouch! Ouch! You're twisting my arm. I'll explain, for Oracle numbers at least.
    1. All data on disk looks like <length><stuff>
    2. All data on disk is byte-sortable (other than the lengths).
    3. Digits are base 100, and the first byte is the (base-100) exponent. For example, the number 1 has exponent 193, 10 also has 193, 100 has 194, 0.01 has 192.
    4. Negative numbers complement the exponent. 62 for -1, 63 for -.01, 61 for -100.
    5. Why? Because the exponent has to be byte-sortable.
    6. Digits. Positive digits are 1..100. Negative digits are 101..2. Again, to be byte-sortable. Base-100 digits use about 7 out of 8 bits of each byte, so this is a reasonably efficient way of storing numbers.
    7. Negative numbers have a trailing byte of value 102, unless the maximum number of digit bytes (20) are used. To make things byte sortable. -1 > -1.01, right? But (62,100) < (62,100,100). The trailing 102 corrects that: (62,100,102) > (62,100,100,102).
    8. But why isn't the exponent for 1 192, for -1 63, and why are the digits 1..100 and 101..2 instead of 0..99 and 99..0? Because, way back in prehistory, Oracle used 0 as a null terminator, so you couldn't store 0 on disk for any other purpose. So 1 was added to everything. There's no reason not to store 0 on disk anymore because all values store their lengths separately, but the number format hasn't changed.
    9. My guess is that negative infinity was originally (1,1), pushing the negative numbers out to 101..2 instead of 100..1. That's not what negative infinity is now, and SQL doesn't expose the infinities anyhow.
  11. To find out how a query is executed, create a plan table with UTLXPLAN.SQL, fill the table with
      explain plan for select ...  ;
    and then look at the results with a connect-by script, something like this:
      select substrb(to_char(cost),1,6) cost,
             substrb(to_char(cardinality),1,6) card,
             substrb(substr('  .  ,  .  ,  .  ',1,level) || operation,1,20) type,
             substrb(options,1,15) subtype,
             substrb(object_owner,1,5) owner,
             substrb(object_name,1,5) name,
             substrb(object_node,1,5) link from plan_table
        connect by parent_id = prior id
        start with id = 0;
      delete from plan_table;
  12. Insert and update with check option:
      insert into (select * from emp where deptno = 10 with check option)
        values (...);
      update (select * from (select sal, empno from emp, dept
                             where emp.deptno=dept.deptno and loc='AKRON')
              where sal > 100000 with check option)
        set sal = :1 where empno = :2;
  13. Mutating/Constraining errors.

    Before 8i:

    When you get the error, use your per-row trigger to fill a PL/SQL table instead. Then use an after-statement trigger to apply the changes based on that PL/SQL table. There are generic packages out there for generating the triggers, PL/SQL tables, and so forth needed to implement update cascade. Often you need more PL/SQL variables to keep track of whether you are already under a trigger, to avoid recursion.

    After 8i: Half the mutating errors go away. Specifically, it is still illegal to read or modify a mutating table, but modifying a table that is being read is fine. This allows the obvious before-row trigger implementation of update cascade, for example. The problem of delete cascade firing statement triggers under row triggers is fixed in Oracle8i too.

  14. How to enable constraints without locking tables for hours on end (using Oracle8 or later). First put all constraints in the ENABLE NOVALIDATE mode, then ENABLE (or VALIDATE) them individually. The ENABLE NOVALIDATE modifies only metadata. The VALIDATE hold no locks, can run in parallel, and many such enables can be run concurrently.
  15. How to store data case sensitive but to index it case insensitive (from 8i on): Set compatible=, query_rewrite_integrity=trusted, and query_rewrite_enabled=true.
      create table a (a1 varchar2(10));
      create index ai on a (upper(a1));
      analyze table a compute statistics;
    This will use the cost-based optimizer. From what I've heard, that's OK. As of Oracle8i the cost-based optimizer is a good thing, and it can use several access methods the rule-based optimizer doesn't know about.

A good hash function for Table Lookup
Ye Olde Catalogue of Boy Scout Skits
Simulations of some interesting orbits
Table of Contents