Skip to Main Content
  • Questions

  • Why Scalar sub query is reducing my query cost to 50%

Breadcrumb

XMAS_TREE

The AskTOM team is taking a break over the holiday season, so we're not taking questions or responding to comments. Please have a happy and safe festive season and as always, thanks for being a member of the AskTOM community.

Question and Answer

Connor McDonald

Thanks for the question, santhosh.

Asked: July 15, 2016 - 12:38 pm UTC

Last updated: August 08, 2016 - 4:57 am UTC

Version: Oracle 11g

Viewed 1000+ times

You Asked

Hi Tom,

Please have a look:

Below are the 2 queries with the same result set but differing in the cost.

When i used scalar sub query instead of inner join its cost is low when compared with the other.

Could you please explain.

Please use the notepad to open the explain plan:

SELECT * FROM (
SELECT
(SELECT A.SRVCR_LOAN_ID FROM ACCT A WHERE A.ACCT_KY= CR.ACCT_KY) AS SRVCR_ID,
(SELECT MA.FUND_DT FROM MRTG_AGREE MA WHERE MA.ACCT_KY=CR.ACCT_KY AND MA.FUND_DT>'17-SEP-2015' ) AS FUND_DATE,
CR.CR_RPSTR_ID,
CRD.CR_RPSTR_DESC
FROM CR_RPT CR
INNER JOIN CR_RPSTR_NM_DMN_LIST CRD
ON CR.CR_RPSTR_ID=CRD.CR_RPSTR_ID
)WHERE FUND_DATE IS NOT NULL;


SQL_ID fnbu1vj8kcf0r, child number 0
-------------------------------------
SELECT /*TOM*/* FROM ( SELECT (SELECT A.SRVCR_LOAN_ID FROM ACCT A
WHERE A.ACCT_KY= CR.ACCT_KY) AS SRVCR_ID, (SELECT MA.FUND_DT FROM
MRTG_AGREE MA WHERE MA.ACCT_KY=CR.ACCT_KY AND MA.FUND_DT>'17-SEP-2015'
) AS FUND_DATE, CR.CR_RPSTR_ID, CRD.CR_RPSTR_DESC FROM CR_RPT CR
INNER JOIN CR_RPSTR_NM_DMN_LIST CRD ON CR.CR_RPSTR_ID=CRD.CR_RPSTR_ID
)WHERE FUND_DATE IS NOT NULL

Plan hash value: 3090924610


| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 56641 (100)| |
| 1 | TABLE ACCESS BY INDEX ROWID| ACCT | 1 | 17 | 3 (0)| 00:00:01 |
|* 2 | INDEX UNIQUE SCAN | PK_ACCT | 1 | | 2 (0)| 00:00:01 |
|* 3 | TABLE ACCESS BY INDEX ROWID| MRTG_AGREE | 1 | 14 | 3 (0)| 00:00:01 |
|* 4 | INDEX UNIQUE SCAN | PK_MRTG_AGREE | 1 | | 2 (0)| 00:00:01 |
|* 5 | VIEW | | 18M| 1049M| 56641 (1)| 00:11:20 |
|* 6 | HASH JOIN | | 18M| 344M| 56641 (1)| 00:11:20 |
| 7 | TABLE ACCESS FULL | CR_RPSTR_NM_DMN_LIST | 4 | 44 | 5 (0)| 00:00:01 |
| 8 | TABLE ACCESS FULL | CR_RPT | 18M| 154M| 56582 (1)| 00:11:19 |

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

2 - access("A"."ACCT_KY"=:B1)
3 - filter("MA"."FUND_DT">'17-SEP-2015')
4 - access("MA"."ACCT_KY"=:B1)
5 - filter("FUND_DATE" IS NOT NULL)
6 - access("CR"."CR_RPSTR_ID"="CRD"."CR_RPSTR_ID")



56641 is the cost.

Record count is 8485006

SELECT
A.SRVCR_LOAN_ID,
MA.FUND_DT,
CR.CR_RPSTR_ID,
CRD.CR_RPSTR_DESC
FROM
CR_RPT CR
INNER JOIN ACCT A
ON CR.ACCT_KY=A.ACCT_KY
INNER JOIN MRTG_AGREE MA
ON CR.ACCT_KY=MA.ACCT_KY
INNER JOIN CR_RPSTR_NM_DMN_LIST CRD
ON CR.CR_RPSTR_ID=CRD.CR_RPSTR_ID
WHERE MA.FUND_DT>'17-SEP-2015';


SQL_ID 821chz5f2sr7y, child number 0
-------------------------------------
SELECT /*TOM2*/ A.SRVCR_LOAN_ID, MA.FUND_DT, CR.CR_RPSTR_ID,
CRD.CR_RPSTR_DESC FROM CR_RPT CR INNER JOIN ACCT A ON
CR.ACCT_KY=A.ACCT_KY INNER JOIN MRTG_AGREE MA ON
CR.ACCT_KY=MA.ACCT_KY INNER JOIN CR_RPSTR_NM_DMN_LIST CRD ON
CR.CR_RPSTR_ID=CRD.CR_RPSTR_ID WHERE MA.FUND_DT>'17-SEP-2015'

Plan hash value: 2686946992

----------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time |
----------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | | 100K(100)| |
|* 1 | HASH JOIN | | 8684K| 422M| | 100K (1)| 00:20:10 |
| 2 | TABLE ACCESS FULL | CR_RPSTR_NM_DMN_LIST | 4 | 44 | | 5 (0)| 00:00:01 |
|* 3 | HASH JOIN | | 8684K| 331M| 35M| 100K (1)| 00:20:10 |
| 4 | TABLE ACCESS FULL | ACCT | 1280K| 20M| | 2091 (1)| 00:00:26 |
|* 5 | HASH JOIN | | 8684K| 190M| 15M| 82481 (1)| 00:16:30 |
|* 6 | VIEW | index$_join$_004 | 609K| 8336K| | 7124 (1)| 00:01:26 |
|* 7 | HASH JOIN | | | | | | |
|* 8 | INDEX RANGE SCAN | FUND_DT_IDX | 609K| 8336K| | 1679 (1)| 00:00:21 |
| 9 | INDEX FAST FULL SCAN| PK_MRTG_AGREE | 609K| 8336K| | 3597 (1)| 00:00:44 |
| 10 | TABLE ACCESS FULL | CR_RPT | 18M| 154M| | 56582 (1)| 00:11:19 |
----------------------------------------------------------------------------------------------------------

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

1 - access("CR"."CR_RPSTR_ID"="CRD"."CR_RPSTR_ID")
3 - access("CR"."ACCT_KY"="A"."ACCT_KY")
5 - access("CR"."ACCT_KY"="MA"."ACCT_KY")
6 - filter("MA"."FUND_DT">'17-SEP-2015')
7 - access(ROWID=ROWID)
8 - access("MA"."FUND_DT">'17-SEP-2015')



100417 is the cost.

Regards,
Santhosh

and Connor said...

If you look at the first execution plan, notice

filter("FUND_DATE" IS NOT NULL)

appears at line 5. So the optimizer has identified that it can the do the following:

- first join CR_RPT and CR_RPSTR_NM_DMN_LIST
- then apply "FUND_DATE" IS NOT NULL
- and then the two scalar queries

So it is assuming that the two scalar queries will be done on a reduced set of rows (ie, *after* the join, and *after* the fund_date predicate).

In your second query, the optimizer is driving off the tables in a different order, so its quite likely the costs will be different. Don't forget that these are logically *different* queries, because a join must assume that you might be getting lots of rows back from a source, whereas a scalar will only return a single row.

*You* might know that they are the "same" but the optimizer does not. I would also look at seeing whether its estimate of 600k rows for "fund_dt > sep-2015" is reasonable. If not, look at a histogram on that column to give the optimizer better information.

Hope this helps.

Rating

  (2 ratings)

We're not taking comments currently, so please try again later if you want to add a comment.

Comments

SSQ in 11g and 12c

Rajeshwaran, Jeyabal, August 08, 2016 - 3:07 pm UTC

When i used scalar sub query instead of inner join its cost is low when compared with the other.

By default scalar sub-queries are optimized for response time, where as join are optimized for throughput.


demo@ORA11G> set feedback off
demo@ORA11G> drop table t2 purge;
demo@ORA11G> drop table t1 purge;
demo@ORA11G>
demo@ORA11G> create table t2 as
  2  select a.owner,a.table_name,a.column_name,a.data_type,
  3    rpad('*',80,'*') as datas
  4  from all_tab_columns  a,
  5     all_tables b
  6  where a.table_name = b.table_name
  7  and a.owner = b.owner;
demo@ORA11G>
demo@ORA11G> create table t1 as select * from all_tables ;
demo@ORA11G>
demo@ORA11G> alter table t1 add constraint t1_pk
  2  primary key(owner,table_name);
demo@ORA11G>
demo@ORA11G> alter table t2 add constraint t2_fk
  2  foreign key(owner,table_name)
  3  references t1;
demo@ORA11G>
demo@ORA11G> create index t2_idx on t2(owner,table_name);
demo@ORA11G>
demo@ORA11G> begin
  2     dbms_stats.gather_table_stats(user,'T1');
  3     dbms_stats.gather_table_stats(user,'T2',
  4     method_opt=>'for all indexed columns size 254',
  5     cascade=>true);
  6  end;
  7  /
demo@ORA11G> set feedback on
demo@ORA11G>
demo@ORA11G> set autotrace traceonly explain
demo@ORA11G> select t1.owner,t1.table_name,max(t2.column_name) cname
  2  from t1, t2
  3  where t1.owner = t2.owner (+)
  4  and t1.table_name = t2.table_name (+)
  5  group by t1.owner,t1.table_name ;

Execution Plan
----------------------------------------------------------
Plan hash value: 3351207033

------------------------------------------------------------------------------
| Id  | Operation            | Name  | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------
|   0 | SELECT STATEMENT     |       |   146 |  9636 |    19   (6)| 00:00:01 |
|   1 |  SORT GROUP BY NOSORT|       |   146 |  9636 |    19   (6)| 00:00:01 |
|   2 |   MERGE JOIN OUTER   |       |  1414 | 93324 |    19   (6)| 00:00:01 |
|   3 |    INDEX FULL SCAN   | T1_PK |   153 |  3672 |     1   (0)| 00:00:01 |
|*  4 |    SORT JOIN         |       |  1414 | 59388 |    18   (6)| 00:00:01 |
|   5 |     TABLE ACCESS FULL| T2    |  1414 | 59388 |    17   (0)| 00:00:01 |
------------------------------------------------------------------------------

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

   4 - access("T1"."OWNER"="T2"."OWNER"(+) AND
              "T1"."TABLE_NAME"="T2"."TABLE_NAME"(+))
       filter("T1"."TABLE_NAME"="T2"."TABLE_NAME"(+) AND
              "T1"."OWNER"="T2"."OWNER"(+))

demo@ORA11G>
demo@ORA11G> select t1.owner,t1.table_name ,( select max(t2.column_name) from t2
  2  where t1.owner = t2.owner
  3  and t1.table_name = t2.table_name ) cname
  4  from t1 ;

Execution Plan
----------------------------------------------------------
Plan hash value: 3479339389

---------------------------------------------------------------------------------------
| Id  | Operation                    | Name   | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |        |   153 |  3672 |     1   (0)| 00:00:01 |
|   1 |  SORT AGGREGATE              |        |     1 |    42 |            |          |
|   2 |   TABLE ACCESS BY INDEX ROWID| T2     |     1 |    42 |     2   (0)| 00:00:01 |
|*  3 |    INDEX RANGE SCAN          | T2_IDX |     1 |       |     1   (0)| 00:00:01 |
|   4 |  INDEX FULL SCAN             | T1_PK  |   153 |  3672 |     1   (0)| 00:00:01 |
---------------------------------------------------------------------------------------

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

   3 - access("T2"."OWNER"=:B1 AND "T2"."TABLE_NAME"=:B2)

demo@ORA11G> set autotrace off
demo@ORA11G>
demo@ORA11G>


Tkprof shows this.

SELECT T1.OWNER,T1.TABLE_NAME,MAX(T2.COLUMN_NAME) CNAME 
FROM
 T1, T2 WHERE T1.OWNER = T2.OWNER (+) AND T1.TABLE_NAME = T2.TABLE_NAME (+) 
  GROUP BY T1.OWNER,T1.TABLE_NAME 


call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.00          0          0          0           0
Execute      1      0.00       0.00          0          0          0           0
Fetch        2      0.00       0.00          0         30          0         153
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        4      0.00       0.00          0         30          0         153

Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 110     (recursive depth: 1)
Number of plan statistics captured: 1

Rows (1st) Rows (avg) Rows (max)  Row Source Operation
---------- ---------- ----------  ---------------------------------------------------
       153        153        153  SORT GROUP BY NOSORT (cr=30 pr=0 pw=0 time=3442 us cost=19 size=9636 card=146)
      1421       1421       1421   MERGE JOIN OUTER (cr=30 pr=0 pw=0 time=4938 us cost=19 size=93324 card=1414)
       153        153        153    INDEX FULL SCAN T1_PK (cr=2 pr=0 pw=0 time=177 us cost=1 size=3672 card=153)(object id 106714)
      1414       1414       1414    SORT JOIN (cr=28 pr=0 pw=0 time=3369 us cost=18 size=59388 card=1414)
      1414       1414       1414     TABLE ACCESS FULL T2 (cr=28 pr=0 pw=0 time=872 us cost=17 size=59388 card=1414)


SELECT T1.OWNER,T1.TABLE_NAME ,( SELECT MAX(T2.COLUMN_NAME) 
FROM
 T2 WHERE T1.OWNER = T2.OWNER AND T1.TABLE_NAME = T2.TABLE_NAME ) CNAME FROM 
  T1 


call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.00          0          0          0           0
Execute      1      0.00       0.00          0          0          0           0
Fetch        2      0.01       0.00          0        172          0         153
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        4      0.01       0.00          0        172          0         153

Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 110     (recursive depth: 1)
Number of plan statistics captured: 1

Rows (1st) Rows (avg) Rows (max)  Row Source Operation
---------- ---------- ----------  ---------------------------------------------------
       153        153        153  SORT AGGREGATE (cr=170 pr=0 pw=0 time=3951 us)
      1414       1414       1414   TABLE ACCESS BY INDEX ROWID T2 (cr=170 pr=0 pw=0 time=4116 us cost=2 size=42 card=1)
      1414       1414       1414    INDEX RANGE SCAN T2_IDX (cr=47 pr=0 pw=0 time=1964 us cost=1 size=0 card=1)(object id 106715)
       153        153        153  INDEX FULL SCAN T1_PK (cr=2 pr=0 pw=0 time=175 us cost=1 size=3672 card=153)(object id 106714)


But things got changed in 12c, Optimizer has the ability to transform the Scalar sub-queries into joins for Optimal performance.

running the above test in 12c (12.1.0.2) shows up this.

demo@ORA12C> set autotrace traceonly explain
demo@ORA12C> select t1.owner,t1.table_name,max(t2.column_name) cname
  2  from t1, t2
  3  where t1.owner = t2.owner (+)
  4  and t1.table_name = t2.table_name (+)
  5  group by t1.owner,t1.table_name ;

Execution Plan
----------------------------------------------------------
Plan hash value: 3351207033

------------------------------------------------------------------------------
| Id  | Operation            | Name  | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------
|   0 | SELECT STATEMENT     |       |   158 |  9322 |    14  (22)| 00:00:01 |
|   1 |  SORT GROUP BY NOSORT|       |   158 |  9322 |    14  (22)| 00:00:01 |
|   2 |   MERGE JOIN OUTER   |       |  1492 | 88028 |    14  (22)| 00:00:01 |
|   3 |    INDEX FULL SCAN   | T1_PK |   162 |  3726 |     1   (0)| 00:00:01 |
|*  4 |    SORT JOIN         |       |  1488 | 53568 |    13  (24)| 00:00:01 |
|   5 |     TABLE ACCESS FULL| T2    |  1488 | 53568 |    11  (10)| 00:00:01 |
------------------------------------------------------------------------------

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

   4 - access("T1"."OWNER"="T2"."OWNER"(+) AND
              "T1"."TABLE_NAME"="T2"."TABLE_NAME"(+))
       filter("T1"."TABLE_NAME"="T2"."TABLE_NAME"(+) AND
              "T1"."OWNER"="T2"."OWNER"(+))

demo@ORA12C>
demo@ORA12C> select t1.owner,t1.table_name ,( select max(t2.column_name) from t2
  2  where t1.owner = t2.owner
  3  and t1.table_name = t2.table_name ) cname
  4  from t1 ;

Execution Plan
----------------------------------------------------------
Plan hash value: 1823981746

----------------------------------------------------------------------------------
| Id  | Operation             | Name     | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------
|   0 | SELECT STATEMENT      |          |   162 | 35802 |    16  (32)| 00:00:01 |
|   1 |  MERGE JOIN OUTER     |          |   162 | 35802 |    16  (32)| 00:00:01 |
|   2 |   INDEX FULL SCAN     | T1_PK    |   162 |  3726 |     1   (0)| 00:00:01 |
|*  3 |   SORT JOIN           |          |   158 | 31284 |    15  (34)| 00:00:01 |
|   4 |    VIEW               | VW_SSQ_1 |   158 | 31284 |    13  (24)| 00:00:01 |
|   5 |     HASH GROUP BY     |          |   158 |  5688 |    13  (24)| 00:00:01 |
|   6 |      TABLE ACCESS FULL| T2       |  1488 | 53568 |    11  (10)| 00:00:01 |
----------------------------------------------------------------------------------

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

   3 - access("T1"."OWNER"="ITEM_1"(+) AND "T1"."TABLE_NAME"="ITEM_2"(+))
       filter("T1"."TABLE_NAME"="ITEM_2"(+) AND "T1"."OWNER"="ITEM_1"(+))

demo@ORA12C> set autotrace off
demo@ORA12C>


Tkprof from 12c show this.

SELECT T1.OWNER,T1.TABLE_NAME,MAX(T2.COLUMN_NAME) CNAME 
FROM
 T1, T2 WHERE T1.OWNER = T2.OWNER (+) AND T1.TABLE_NAME = T2.TABLE_NAME (+) 
  GROUP BY T1.OWNER,T1.TABLE_NAME 


call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.01       0.00          0          0          0           0
Execute      1      0.00       0.00          0          0          0           0
Fetch        2      0.00       0.04          0         32          0         162
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        4      0.01       0.04          0         32          0         162

Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 135     (recursive depth: 1)
Number of plan statistics captured: 1

Rows (1st) Rows (avg) Rows (max)  Row Source Operation
---------- ---------- ----------  ---------------------------------------------------
       162        162        162  SORT GROUP BY NOSORT (cr=32 pr=0 pw=0 time=1418 us cost=14 size=9322 card=158)
      1492       1492       1492   MERGE JOIN OUTER (cr=32 pr=0 pw=0 time=1560 us cost=14 size=88028 card=1492)
       162        162        162    INDEX FULL SCAN T1_PK (cr=2 pr=0 pw=0 time=11 us cost=1 size=3726 card=162)(object id 113001)
      1488       1488       1488    SORT JOIN (cr=30 pr=0 pw=0 time=1297 us cost=13 size=53568 card=1488)
      1488       1488       1488     TABLE ACCESS FULL T2 (cr=30 pr=0 pw=0 time=400 us cost=11 size=53568 card=1488)


SELECT T1.OWNER,T1.TABLE_NAME ,( SELECT MAX(T2.COLUMN_NAME) 
FROM
 T2 WHERE T1.OWNER = T2.OWNER AND T1.TABLE_NAME = T2.TABLE_NAME ) CNAME FROM 
  T1 


call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.01       0.00          0          0          0           0
Execute      1      0.00       0.00          0          0          0           0
Fetch        2      0.00       0.01          0         32          0         162
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        4      0.01       0.02          0         32          0         162

Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 135     (recursive depth: 1)
Number of plan statistics captured: 1

Rows (1st) Rows (avg) Rows (max)  Row Source Operation
---------- ---------- ----------  ---------------------------------------------------
       162        162        162  MERGE JOIN OUTER (cr=32 pr=0 pw=0 time=15718 us cost=16 size=35802 card=162)
       162        162        162   INDEX FULL SCAN T1_PK (cr=2 pr=0 pw=0 time=344 us cost=1 size=3726 card=162)(object id 113001)
       158        158        158   SORT JOIN (cr=30 pr=0 pw=0 time=14480 us cost=15 size=31284 card=158)
       158        158        158    VIEW  VW_SSQ_1 (cr=30 pr=0 pw=0 time=14049 us cost=13 size=31284 card=158)
       158        158        158     HASH GROUP BY (cr=30 pr=0 pw=0 time=13809 us cost=13 size=5688 card=158)
      1488       1488       1488      TABLE ACCESS FULL T2 (cr=30 pr=0 pw=0 time=823 us cost=11 size=53568 card=1488)


looking into 10053 trace from 12c database for the Scalar sub-queries got transformed like this.

Final query after transformations:******* UNPARSED QUERY IS *******
SELECT "T1"."OWNER" "OWNER",
  "T1"."TABLE_NAME" "TABLE_NAME",
  "VW_SSQ_1"."MAX(T2.COLUMN_NAME)" "CNAME"
FROM
  (SELECT MAX("T2"."COLUMN_NAME") "MAX(T2.COLUMN_NAME)",
    "T2"."OWNER" "ITEM_1",
    "T2"."TABLE_NAME" "ITEM_2"
  FROM "DEMO"."T2" "T2"
  GROUP BY "T2"."OWNER",
    "T2"."TABLE_NAME"
  ) "VW_SSQ_1",
  "DEMO"."T1" "T1"
WHERE "T1"."OWNER"   ="VW_SSQ_1"."ITEM_1"(+)
AND "T1"."TABLE_NAME"="VW_SSQ_1"."ITEM_2"(+)


You could see the 12c optimizer has transformed the Scalar subquries into an equivalent joins.


thanks for the proof

George Joseph, August 09, 2016 - 6:44 am UTC


more

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's channel and Chris's channel from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.

More to Explore

Performance

Get all the information about database performance in the Database Performance guide.