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