Oracle Gotchas for An Experienced Newb

Question

What are some Oracle gotchas for someone new to the platform, but not new to relational databases (MySQL, MS SQL Server, Postgres, etc.) in general.

Two examples of the kind of things I'm looking for

1. Many relational database products handle creating an auto_increment key for you. Oracle does not, you must manually create the sequence, then create the trigger

2. When INSERTING data via the SQL Developer interface, you have to manually commit the data

Bonus points for PHP related gotchas, as that's the platform I'll this hypothetical experienced newb will be using.

Answer 1

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  1
  FROM    dual
  

• Empty string and NULL are the same thing.

  SELECT  *
  FROM    dual
  WHERE   '' = ''
  

  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
          ) q
  ORDER BY
          id
  

  will return

  id
  ---
  1
  NULL
  

  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
          ) q
  ORDER 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.