Empty Strings

A developer recently asked me to review the changes made to some code. Part of the change being made included the following in an query:

SELECT ... NVL(column, '')...

Yep, there was no space between those single quotes. I was surprised. “Surely this developer knows that in Oracle an empty string is NULL?” I asked myself as the developer in question was fairly senior and quite competent. However, on reflection, it’s one of those things that is not logically obvious.

So, for anyone reading this that does not know it already, Oracle treats an empty string as NULL and with that comes all the normal handling of NULLs.

In case there are any doubters, a smple test will confirm this:

SELECT NVL(NULL,'I am null')      AS result_1
,      NVL('','... and so am I!') AS result_2
FROM   dual

which gives us an output of:

RESULT_1  RESULT_2
--------- ----------------
I am null ... and so am I!

Furthermore, this behaviour is not limited to SQL, it applies to PL/SQL too as the following illustrates:

DECLARE
   l_temp   VARCHAR2(10) := '';
BEGIN
   IF (l_temp = '') THEN
      dbms_output.put_line ('String is empty');
   ELSE
      dbms_output.put_line ('String is not empty');
   END IF;
END;
/

which displays the following:

String is not empty

Bummer… don’t you just hate working with NULL!

When I first started developing in Oracle I was rather taken aback by this situation. Logically and conceptually an empty string and NULL are not the same. “Oracle is stupid” was my initial thought.

However after working with Oracle for a number of years now I can’t recall ever encountering a situation where this behaviour caused a problem. I have never been required to store an empty string and not have it stored as NULL. Application developers may beg to differ though. I can imagine that there may have been times whereby an application stored an empty string, then went to read the data back only to discover that it had been translated to NULL and this caused all sorts of problems. For example, a search type query that is looking for a empty string will definitely fail, e.g.:

SELECT *
FROM   some_table
WHERE  some_column = ''

This query will not return the rows where some_column is NULL. Instead, a test for NULL is required:

SELECT *
FROM   some_table
WHERE  some_column IS NULL

However, on closer consideration, what is the value in storing an empty string? Consider what would be required if an empty string was different to NULL. Visually the data would look the same and so would be considered in equal light by end users. As a result, queries would need to be written along the lines of:

SELECT *
FROM   some_table
WHERE  some_column IS NULL
OR     some_column = ''

Yuck! Queries would be a nightmare to maintain and manage. Given the difficulty in separating out empty string and NULL, bugs would arise in code and once that happens then data quality would suffer… and if there’s one thing that people demand of their database systems it’s accurate data!

Furthermore, consider the simple NOT NULL constraint. If an empty string was different to NULL then an effective way of bypassing a NOT NULL constraint would be to store an empty string… which is exactly the type of thinking that leads to data problems. Constraints are there for a reason and so coming up with ways to try to bypass them is not overly helpful and ends up doing serious harm to the data. To prevent the storing of empty strings so as to enforce the intention that the column holds a real value then CHECK constraints such as the following would abound within the database:

CHECK (some_column <> '')

Keep in mind that the column would still require a NOT NULL constraint as well since NULL would pass the above check constraint.

So, while I would agree that Oracle’s inability to handle empty strings and to treat them as NULL is not logically correct, I would argue that it is quite pragmatic and helps with maintaining data accuracy.

Obtaining an Execution Plan

Whether you are a developer or a DBA it’s pretty much inevitable that you will have to look at query execution plans. Analysing even a moderate execution plan can be a complex and time consuming task but it would seem that the first hurdle that too many people fail on is simply obtaining the plan. Okay, maybe I’m exaggerating a little but far too frequently I see queries that clearly haven’t had their plan checked so maybe developers do know how to generate a plan but simply don’t bother…

In any case, this post takes a quick look at how to use the explain plan facilities built into some common tools: Quest TOAD, Oracle SQL Developer and SQL Plus.

The SQL example used below is a simple query against the classic DEPT and EMP tables that are found in the SCOTT schema:

SELECT d.deptno
,      d.dname
,      e.ename
,      e.job
FROM   dept d
,      emp e         
WHERE  d.deptno = e.deptno
AND    d.dname  = 'SALES'
ORDER  BY
       e.ename

Quest TOAD for Oracle

Quest TOAD for Oracle is a common tool for both developers and DBAs. I think I have only worked at one place where it wasn’t the defacto tool for database development. Running explain plan in TOAD is a matter of entering the query in the editor and clicking the ambulance button on the tool bar or using the hot-key of Ctrl-E.

TOAD Explain Plan button

TOAD will display the query execution plan in the Explain Plan window, which will normally be underneath the query window.

TOAD Explain Plan display

For the OLTP systems developer, TOAD will highlight in red any table scans it finds in the plan. This may or may not be a problem so don’t simply assume that anything in red is bad. However, it would be worth a closer examination to ensure it is an appropriate operation.

TOADs default display of the execution plan will show the operations being performed and the objects being access all neatly indented so as to illustrate the dependency order. However, the default display is lacking critical pieces of information: the access and filter predicates. In order to enable the display of these other details, right-click on the execution plan window and select ‘Adjust Content’ from the context menu. Scroll to the bottom of the resulting pop-up window and check the boxes next to ‘Access Predicates’ and ‘Filter Predicates’ as shown below then click the OK button.

TOAD Explain Plan options

Now when you generate an execution plan TOAD will display the access and filter predicates:

TOAD Explain Plan display

Oracle SQL Developer

Explain plan within Oracle SQL Developer is a matter of pressing the F10 key in a worksheet window containing the query to explain, as will right-clicking on the worksheet and choosing Explain Plan from the context menu. Alternately, the fourth button from the left on the worksheet button bar will do the trick too:

SQL Developer explain plan toolbar

SQL Developer will display an Explain Plan window below the editor window, as shown below.

SQL Developer explain plan display

Similar to TOAD, the query plan hierarchy is clearly visible in the display. Likewise, the child steps of any given level can easily be hidden, which is particularly useful for interpreting large plans. Right-clicking on the plan within SQL Developer displays a context menu that contains an option to export the plan to HTML.

The default display for SQL Developer includes the access and filter predicates as well as the cost for each line but not the cardinality. To display the cardinality for each operation, select Preferences from the Tools menu. In the Preferences window, expand the Database selection on the left-hand listing and choose the Autotrace/Explain Plan option. In the right-hand pane, under the Explain Plan options, check the Cardinality option as shown below.

SQL Developer explain plan options

Once checked, generating an explain plan will display an extra column containing the cardinality.

SQL Developer explain plan cardiality

SQL Plus

Generating an explain plan in SQL Plus involves running the EXPLAIN PLAN command on the query in question and then selecting the results from dbms_xplan.display, as shown below:

EXPLAIN PLAN FOR
SELECT d.deptno
,      d.dname
,      e.ename
,      e.job
FROM   dept d
,      emp e
WHERE  d.deptno = e.deptno
AND    d.dname  = 'SALES'
ORDER  BY
       e.ename
/

SELECT *
FROM   TABLE(dbms_xplan.display())
/

which gives the following output:

SQL> EXPLAIN PLAN FOR
  2  SELECT d.deptno
  3  ,      d.dname
  4  ,      e.ename
  5  ,      e.job
  6  FROM   dept d
  7  ,      emp e
  8  WHERE  d.deptno = e.deptno
  9  AND    d.dname  = 'SALES'
 10  ORDER  BY
 11         e.ename
 12  /

Explained.

SQL> SELECT *
  2  FROM   TABLE(dbms_xplan.display())
  3  /

PLAN_TABLE_OUTPUT
-----------------------------------------------------------------------------
Plan hash value: 3357797783

----------------------------------------------------------------------------
| Id  | Operation           | Name | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------
|   0 | SELECT STATEMENT    |      |     5 |   150 |     6  (34)| 00:00:01 |
|   1 |  SORT ORDER BY      |      |     5 |   150 |     6  (34)| 00:00:01 |
|*  2 |   HASH JOIN         |      |     5 |   150 |     5  (20)| 00:00:01 |
|*  3 |    TABLE ACCESS FULL| DEPT |     1 |    13 |     2   (0)| 00:00:01 |
|   4 |    TABLE ACCESS FULL| EMP  |    14 |   238 |     2   (0)| 00:00:01 |
----------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("D"."DEPTNO"="E"."DEPTNO")
   3 - filter("D"."DNAME"='SALES')

17 rows selected.

Perhaps not quite a pretty as the GUI tools but it’s quite understandable. The PLAN_HASH_VALUE is displayed near the top, which can be used to obtain details from various data dictionary views, including v$sql_plan.

Within the table of plan details, the asterix (*) next to a line denotes that an entry exists in the subsequent Predicate Information section, which is where the specific access and filter predicates are listed.

The call to dbms_xplan.display can be customised using the format parameter to include or suppress details. For a full list of the details that can be shown see the documentation on dbms_xplan. As an example, the following removes the bytes column from the display:

       
SELECT *
FROM   TABLE(dbms_xplan.display(format=>'-BYTES'))
/

Note, the use of named parameters in SQL is a feature of Oracle 11. In earlier versions parameters to functions within SQL can only be passed by position.

In all of the above it needs to be remembered that the plan displayed is Oracle’s best guess of what the plan will be when the query is actually executed. Generally speaking this will be accurate but certain features will cause Oracle to execute the query differently. I have previously noted bind variable peeking as a common cause for why the actual execution plan may differ from the plan described via the explan plan facility. To obtain the execution plan that was actually used when a query was executed you should use the dbms_xplan.display_cursor function, as described in the basic process monitoring post.

In parting, running explain plan over a statement whilst in development is quite trivial and should be done for virtually every statement, even if it simply confirming an obvious plan.