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 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 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.

Accepted Answer

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 `NULL`s). 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, `NULL`s 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.

Oracle Gotchas for An Experienced Newb

Solution

Problem

Related problems