Oracle Gotchas for An Experienced Newb

Note: I'm explaining only the gotchas here, i. e. situations when Oracle behaves not as other systems do. Oracle has numerous benefits over other RDBMS's, but they are not the topic of the post.

• You cannot SELECT without FROM.

  SELECT  1

  will fail, you need to:

  SELECT  1FROM    dual

• Empty string and NULL are the same thing.

  SELECT  *FROM    dualWHERE   '' = ''

  returns nothing.

• There are neither TOP nor LIMIT. You limit your results in the WHERE clause:

  SELECT  *FROM    (        SELECT  *        FROM    mytable        ORDER BY                col        )WHERE   rownum < 10

  exactly this way, using a subquery, since ROWNUM is evaluated before ORDER BY.

• You cannot nest the correlated subqueries more than one level deep. This one will fail:

  SELECT  (        SELECT  *        FROM    (                SELECT  dummy                FROM    dual di                WHERE   di.dummy = do.dummy                ORDER BY                        dummy                )        WHERE   rownum = 1        )FROM    dual do

  This is a problem.

• NULL values are not indexed. This query will not use an index for ordering:

  SELECT  *FROM    (        SELECT  *        FROM    mytable        ORDER BY                col        )WHERE   rownum < 10

  , unless col is marked as NOT NULL.

  Note than it's NULL values that are not indexed, not columns. You can create an index on a nullable column, and non-NULL values will get into the index.

  However, the index will not be used when the query condition assumes that NULL values can possibly satisfy it.

  In the example above you want all value to be returned (including NULLs). Then index doesn't know of non-NULL values, hence, cannot retrieve them.

  SELECT  *FROM    (        SELECT  *        FROM    mytable        ORDER BY                col        )WHERE   rownum < 10

  But this query will use the index:

  SELECT  *FROM    (        SELECT  *        FROM    mytable        WHERE   col IS NOT NULL        ORDER BY                col        )WHERE   rownum < 10

  , since non-NULL values cannot ever satisfy the condition.

• By default, NULLs are sorted last, not first (like in PostgreSQL, but unlike MySQL and SQL Server)

  This query:

  SELECT  *FROM    (        SELECT  1 AS id        FROM    dual        UNION ALL        SELECT  NULL AS id        FROM    dual        ) qORDER BY        id

  will return

  id---1NULL

  To sort like in SQL Server and MySQL, use this:

  SELECT  *FROM    (        SELECT  1 AS id        FROM    dual        UNION ALL        SELECT  NULL AS id        FROM    dual        ) qORDER BY        id NULLS FIRST

  Note that it breaks rownum order unless the latter is not used out of the subquery (like explained above)

• "MYTABLE" and "mytable" (double quotes matter) are different objects.

  SELECT  *FROM    mytable -- wihout quotes

  will select from the former, not the latter. If the former does not exist, the query will fail.

  CREATE TABLE mytable

  creates "MYTABLE", not "mytable".

• In Oracle, all implicit locks (that result from DML operations) are row-level and are never escalated. That is no row not affected by the transaction can be implicitly locked.

  Writers never block readers (and vice versa).

  To lock the whole table, you should issue an explicit LOCK TABLE statement.

  Row locks are stored on the datapages.

• In Oracle, there are no "CLUSTERED indexes", there are "index-organized tables". By default, tables are heap organized (unlike SQL Server and MySQL with InnoDB).

  In Oracle world, a "clustered storage" means organizing several tables so that the rows which share a common key (from several tables) also share a datapage.

  A single datapage hosts multiple rows from multiple tables which makes joins on this key super fast.

SELECT 1 wont work, do select 1 from dual instead.

If you work with hierarchical data, connect by is great.

One comment: You don't have to create a trigger in order to use sequences, unless you're adamant about replicating the behavior of the Sybase/SQL Server IDENTITY column. I find it more useful to just use the sequence directly in the actual insert statements, e.g.

INSERT  INTO MyTable     ( KeyCol     , Name     , Value     )SELECT Seq_MyTable.NextVal     , 'some name'     , 123  FROM dual;

You don't need to worry about the overhead of trigger execution, and you have the flexibility to deal with inserting rows into the table without having to worry about sequence values being assigned (such as when moving data from schema to another). You can also pre-select values from the sequence for inserting ranges of data and other techniques that the IDENTITY feature either makes difficult or impossible.