Oracle - Instructor Insights

Our instructors have real-world experience in the technologies that they teach. Here they share their insights and discoveries on working with each of these technologies.

Use IS NULL and IS NOT NULL to check for NULL values, as the = and < > comparison operators will always return false when comparing against NULL.

Assume we have a table PERSON, with a column AGE that can contain NULL values. A NULL in the AGE column means we know nothing about that person's age - it could be 8, or it could be 60. However, a person will have an age - it's just we we do not know what it is when the value is NULL. Therefore, we cannot ask if the AGE matches any given value - we just don't know. In addition, asking if two NULL entries are the same does not work, as we do not know the value of either entry.

Thus, the SQL query :

	SELECT * FROM PERSON WHERE AGE = NULL;

will always return no entries. Instead, what we probably want in this case is :

	SELECT * FROM PERSON WHERE AGE IS NULL;

This will return all the entries that have an unknown AGE field.

Bind variables in prepared statements can improve performance many times over.

Dynamic SQL is useful for cases where the SQL statement is unknown at compile time. However, in cases where the only dynamic portion of the statement is a value, the overhead of completely parsing the SQL can become the dominant performance factor. Examine the pseudo-code:

        foreach ID in LIST_OF_IDs
            SQL = "select sum(sales) from sales_table where id = '" + ID + "'";
            execute SQL;

Since the parser only considers statements to have been cached in they have the exact same text, none of the resulting statements will use the parsing information already in the statement cache. If we use bind variables, we get:

        SQL =  prepare("select sum(sales) from sales_table where if = ?");
        foreach ID in LIST_OF_IDs
            bind ID to first parm in SQL;
            execute prepared SQL;