Recently, I’ve been asked to optimize several poorly performing queries that were doing scalar subselects in the select list.  In case you’re asking yourself what a scalar subselect is, it is simply a select statement that returns a single value.  Ever since Oracle 8i, we have been able to use one of these queries anywhere that you could use a literal value.  It is a powerful feature and can greatly simplify the design of some queries.  However, with great power comes great responsibility and they should not be used in some situations as we will see.

Here’s an example of a scalar subselect being used in a select list:

-- Create a few dummy tables for example purposes
CREATE TABLE test_ids AS SELECT object_id FROM all_objects;
CREATE TABLE test_id_names AS SELECT object_id, object_name FROM all_objects;
CREATE INDEX test_id_names_idx ON test_id_names(object_id);

-- Use a scalar subselect to find the object_name for a given object_id
SELECT object_id,
 (SELECT object_name FROM test_id_names t WHERE t.object_id = i.object_id) AS object_name
FROM test_ids i

This query is logically no different than a query that performs an outer join between the two tables:

SELECT i.object_id,
      t.object_name
FROM test_ids i
LEFT JOIN test_id_names t ON i.object_id = t.object_id

Lets compare the explain plans for these two queries:

SQL> set linesize 127

SQL> EXPLAIN PLAN FOR SELECT object_id,
 (SELECT object_name FROM test_id_names t WHERE t.object_id = i.object_id) AS object_name
FROM test_ids i;

SQL> SELECT * FROM TABLE(dbms_xplan.display);

-------------------------------------------------------------------------------------------------
| Id  | Operation                   | Name              | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |                   | 95982 |  1218K|    16  (19)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| TEST_ID_NAMES     |  1154 | 34620 |     2   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN          | TEST_ID_NAMES_IDX |   461 |       |     1   (0)| 00:00:01 |
|   3 |  TABLE ACCESS FULL          | TEST_IDS          | 95982 |  1218K|    16  (19)| 00:00:01 |
-------------------------------------------------------------------------------------------------

SQL> EXPLAIN PLAN FOR SELECT i.object_id,
      t.object_name
FROM test_ids i
LEFT JOIN test_id_names t ON i.object_id = t.object_id;

SQL> SELECT * FROM TABLE(dbms_xplan.display);

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------
Plan hash value: 2147807598

--------------------------------------------------------------------------------------------
| Id  | Operation          | Name          | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |               | 95982 |  4030K|       |   208   (7)| 00:00:01 |
|*  1 |  HASH JOIN OUTER   |               | 95982 |  4030K|  2344K|   208   (7)| 00:00:01 |
|   2 |   TABLE ACCESS FULL| TEST_IDS      | 95982 |  1218K|       |    16  (19)| 00:00:01 |
|   3 |   TABLE ACCESS FULL| TEST_ID_NAMES |   115K|  3379K|       |    39  (11)| 00:00:01 |
--------------------------------------------------------------------------------------------

The query that uses the scalar subselect has a cost of 16, while the query doing the outer join has a cost of 208. Seems like a no brainer as to which query would be faster, right? Lets test that theory out using runstats and prove it to ourselves:

SQL> set serveroutput on

SQL> BEGIN
  nehresman.runstats_pkg.rs_start;

  FOR cur IN (
    SELECT object_id,
      (SELECT object_name FROM test_id_names t WHERE t.object_id = i.object_id) AS object_name
    FROM test_ids i
  ) LOOP
    NULL;
  END LOOP;

  nehresman.runstats_pkg.rs_middle;

  FOR cur IN (
    SELECT i.object_id,
      t.object_name
    FROM test_ids i
    LEFT JOIN test_id_names t ON i.object_id = t.object_id
  ) LOOP
    NULL;
  END LOOP;

  nehresman.runstats_pkg.rs_stop;
END;
/

Run1 ran in 187 hsecs
Run2 ran in 24 hsecs
run 1 ran in 779.17% of the time

....
Run1 latches total versus runs -- difference and pct
Run1        Run2        Diff       Pct
2,322,665   340,222  -1,982,443    682.69%

Wow, so the query with the higher cost is nearly 8 times FASTER (hsecs == hundredths of a second)? Yes! The reason is that the scalar subselect is run once for each row in the result set. This particular subselect is doing an index scan on TEST_ID_NAMES_IDX and then a row fetch for the rowid found in the index, 118218 times (that’s the number of rows in the TEST_IDS table). Turns out that this is significantly slower than doing an outer join between the two tables and hash joining the result together. Imagine the performance difference if the result set had 250k rows. Or a million rows.

Oracle’s Cost Based Optimizer (CBO) is pretty darn smart with most of its projections, but it does not accurately assess the cost of scalar subselects that are used in the select list and so it gives you a false sense of security thinking that you have optimized the query as much as possible. I mean 16 is less than 208 right?

So 2 things can be learned here:

  1. Don’t just assume that the costs of a query correlate to real world performance. Try writing the query several different ways and compare the results using runstats to give you proof that one way outperforms another.
  2. Be cautious when using scalar subselects in your select lists. It can be faster to do an outer join.  But again, prove it with runstats to be sure.