Scalar Subquery Unnesting in Oracle 12c.

A Scalar Subquery is a subquery that appear in select clause and that will return only one records having one column.For record return more then one, it is bound be to give error.

select co1, (select max(col1) from table2 where col2= table1.col2)
from table1
where some conditions;

For every record getting returned, Scalar query would get executed for each and every rows projected.Hence how a scalar query is access or what are the access path for scalar query in any explain plan for a SQL is an IMPORTANT consideration.

Let’s create a sample dataset to understand it better.

CREATE TABLE TBL_TEST_OBJ AS SELECT * FROM DBA_OBJECTS;
CREATE TABLE TBL_TEST_USER AS SELECT * FROM DBA_USERS;

Now suppose we have a query as below.

SELECT U.USERNAME, (SELECT MAX(CREATED) FROM TBL_TEST_OBJ O 
WHERE O.OWNER = U.USERNAME)
FROM TBL_TEST_USER U
WHERE USERNAME LIKE 'S%';

Plan for above SQL in Ora 11.2.0.3 version :

SELECT STATEMENT Cost: 3 Cardinality: 4
SORT AGGREGATE Cardinality: 1
     TABLE ACCESS FULL TABLE TBL_TEST_OBJ Cost 200 Cardinality: 1,604
TABLE ACCESS FULL TABLE TBL_TEST_USER Cost: 3 Cardinality: 4

Total Logical IO Consumed :: 1428

For Current SQL, on every Row getting return oracle would  perform Full Table Scan on table TBL_TEST_OBJ. Hence it will impose High Block Reads.

Solution as performance mitigation, we use to follow in Oracle 11g\10g\9i version.

Unnesting the scalar subquery and converting it into a outer join, would remove necessity of evaluating it for every row in the outer query.

Change SQL :

SELECT U.USERNAME , MAX(O.CREATED)
FROM TBL_TEST_USER U , TBL_TEST_OBJ O
WHERE USERNAME LIKE 'S%' AND O.OWNER(+) = U.USERNAME
GROUP BY U.USERNAME;

Using Group-by guarantee to return a single row, just as the scalar subquery

An outer join is also added to the query to ensure every row from the TBL_TEST_USER  table will be returned even if the result of the join is NULL.

Post Plan :

SELECT STATEMENT Cost: 87 Cardinality: 1
 HASH GROUP BY Cost: 87 Cardinality: 1
 HASH JOIN OUTER Cost: 86 Cardinality: 9,219
   TABLE ACCESS FULL TABLE TBL_TEST_USER Cost: 3 Cardinality: 2
   TABLE ACCESS FULL TABLE TBL_TEST_OBJ Cost: 82 Cardinality: 13,021

Total Logical IO Consumed :: 288

Alternate solution with hints:

SELECT /*+ UNNEST(@SSQ)*/
U.USERNAME , (SELECT /*+ QB_NAME(SSQ)*/MAX(CREATED) FROM TBL_TEST_OBJ O WHERE O.OWNER = U.USERNAME)
FROM TBL_TEST_USER U
WHERE USERNAME LIKE 'S%';

Changing code or adding hint won’t be feasible on the fly in many production database.

With intelligence added in 12c optimizer , we neither have to alter code or add hints.

12c Optimizer , automatically transform SQL to Outer joined SQL as already suggested.Plan for problematic SQL in oracle will look as below.

SELECT STATEMENT Cost: 85 Cardinality: 12
HASH GROUP BY Cost: 85 Cardinality: 12
HASH JOIN OUTER 9,216
   TABLE ACCESS FULL TABLE TBL_TEST_USER Cardinality: 4
   TABLE ACCESS FULL TABLE TBL_TEST_OBJ Cardinality: 13,021

12c Optimizer automatically transform query for optimal plan.Transform query is same as explain in solution followed in oracle 11g version.

Feedback would be appreciated.

About Deepak Mahto

Having 8+ Years of relevant/exciting/fruitful/challenging/learning in Oracle Technology. Currently working as Oracle Performance Consultant in an MNC. Hold Expertise in Performance Engineering, SQL Tuning, Database Tuning, SQL, PLSQL Development/Design Considerations and Oracle Internals. I enjoy reading on Oracle internals, posting some of my finding or observations and playing football.
This entry was posted in Oracle 12c New Features and tagged , , . Bookmark the permalink.

3 Responses to Scalar Subquery Unnesting in Oracle 12c.

  1. Raviraj says:

    Thanks you Deepak. Nice article ..very descriptive…Keep sharing your knowledge with us!

    Like

  2. aniket says:

    Nice one Deepak. Please keep writing such blogs sure it will help us to get more knowledge(specially in tuning part).

    Like

  3. prem says:

    Nice Article Deepak. Thank you for sharing such valuable information with us. Please share more topics 🙂

    Like

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s