Does a simple in-line view affect performance? 20188367001

Does a simple in-line view affect performance?

A while ago on the SQL and PL/SQL forum on Oracle Technology Network someone asked a question regarding a pivot query. Because the question was justed posted I was one of the first
to respond.

The original poster had a table with a content looking like:

EMP Type_Phone Phone_num
0 H 710708561317
0 O 2105882266863411
0 C 7016508275274
1 H 317477506584
1 O 0735133142675683
1 C 5246768388132
2 H 125390988899
2 O 6565227947420509
2 C 8678226809280

And he wanted to have the result set shown as:

EMP Home Phone Office Phone Cell Phone
0 710708561317 2105882266863411 7016508275274
1 317477506584 0735133142675683 5246768388132
2 125390988899 6565227947420509 8678226809280
3 167732762422 6071454382173824 9838339948069
4 379625973093 2215213824573053 7221004791860

 

To obtain a pivotted resultset, you could use a query like:

select emp
     , Max (Decode (type_phone, 'H', phone_num)) Home
     , Max (Decode (type_phone, 'O', phone_num)) Office
     , Max (Decode (type_phone, 'C', phone_num)) Cell
  from (select emp
             , type_phone
             , phone_num
          from t
       )
 group by emp

This query makes use of an in-line view and this result set is used to transpose it to the final result. However, another poster claimed it would be more performant if you would remove the in-line view and do it this way:

select emp
, Max (Decode (type_phone, 'H', phone_num)) Home
, Max (Decode (type_phone, 'O', phone_num)) Office
, Max (Decode (type_phone, 'C', phone_num)) Cell
from t
group by emp

This bold claim sparked a small discussion whether or not this would actually be the case. My gut feeling said it wouldn’t make any difference. But, to paraphrase Tom Kyte here, gut feelings don’t count.
To make a point, one way or the other, proof it. So, I created a testcase to test my theory.

The first thing I looked at was the execution plan for both query. As shown below, they are similar:

SQL> explain plan for
  2  select emp
  3  , Max (Decode (type_phone, 'H', phone_num)) Home
  4  , Max (Decode (type_phone, 'O', phone_num)) Office
  5  , Max (Decode (type_phone, 'C', phone_num)) Cell
  6  from t
  7  group by emp
  8  /

Explained.

SQL> @xplan

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------
Plan hash value: 1028120241

----------------------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes |TempSpc| Cost (%CPU)| Time
----------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |   100K|  2636K|       |  2425   (2)| 00:00:30
|   1 |  SORT GROUP BY     |      |   100K|  2636K|    15M|  2425   (2)| 00:00:30
|   2 |   TABLE ACCESS FULL| T    |   300K|  7910K|       |   312   (2)| 00:00:04
----------------------------------------------------------------------------------

9 rows selected.

SQL>

And here’s the other one:

SQL> explain plan for
  2  select emp
  3       , Max (Decode (type_phone, 'H', phone_num)) Home
  4       , Max (Decode (type_phone, 'O', phone_num)) Office
  5       , Max (Decode (type_phone, 'C', phone_num)) Cell
  6    from (select emp
  7               , type_phone
  8               , phone_num
  9            from t
 10         )
 11   group by emp
 12  /

Explained.

SQL> @xplan

PLAN_TABLE_OUTPUT
-----------------------------------------------------------------------------------
Plan hash value: 1028120241

-----------------------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |   100K|  2636K|       |  2425   (2)| 00:00:30 |
|   1 |  SORT GROUP BY     |      |   100K|  2636K|    15M|  2425   (2)| 00:00:30 |
|   2 |   TABLE ACCESS FULL| T    |   300K|  7910K|       |   312   (2)| 00:00:04 |
-----------------------------------------------------------------------------------

9 rows selected.

SQL>

As expected the execution plans for both queries are the same. Is there any difference in the statistics?
The statistics for the other query, without the in-line view, gave these statistics:

SQL> set autot trace stat
SQL> select emp
  2  , Max (Decode (type_phone, 'H', phone_num)) Home
  3  , Max (Decode (type_phone, 'O', phone_num)) Office
  4  , Max (Decode (type_phone, 'C', phone_num)) Cell
  5  from t
  6  group by emp
  7  /

100000 rows selected.


Statistics
----------------------------------------------------------
         13  recursive calls
          5  db block gets
       1371  consistent gets
       2993  physical reads
          0  redo size
    7723285  bytes sent via SQL*Net to client
      73834  bytes received via SQL*Net from client
       6668  SQL*Net roundtrips to/from client
          0  sorts (memory)
          1  sorts (disk)
     100000  rows processed

SQL>

The query with the in-line view, yielded these statistics:

SQL> set autot trace stat
SQL> select emp
  2       , Max (Decode (type_phone, 'H', phone_num)) Home
  3       , Max (Decode (type_phone, 'O', phone_num)) Office
  4       , Max (Decode (type_phone, 'C', phone_num)) Cell
  5    from (select emp
  6               , type_phone
  7               , phone_num
  8            from t
  9         )
 10   group by emp
 11  /

100000 rows selected.


Statistics
----------------------------------------------------------
         13  recursive calls
          5  db block gets
       1371  consistent gets
       2992  physical reads
          0  redo size
    7723285  bytes sent via SQL*Net to client
      73834  bytes received via SQL*Net from client
       6668  SQL*Net roundtrips to/from client
          0  sorts (memory)
          1  sorts (disk)
     100000  rows processed

SQL>

Hardly any differences here either. The same amount of work is done with both queries.

 

Finally, let’s create a trace file and look at the TkProf output. I expect these to be the same as well.
The trace file for the suggestion without the in-line view showed this:

********************************************************************************

select emp
, Max (Decode (type_phone, 'H', phone_num)) Home
, Max (Decode (type_phone, 'O', phone_num)) Office
, Max (Decode (type_phone, 'C', phone_num)) Cell
from t
group by emp

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.01       0.46          0          0          0           0
Execute      1      0.01       0.00          0          0          0           0
Fetch     6668      0.98       2.84       2999       1371          5      100000
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total     6670      1.01       3.31       2999       1371          5      100000

Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 57

Rows     Row Source Operation
-------  ---------------------------------------------------
 100000  SORT GROUP BY (cr=1371 pr=2999 pw=1633 time=8996358 us)
 300000   TABLE ACCESS FULL T (cr=1371 pr=1366 pw=0 time=3016024 us)


Elapsed times include waiting on following events:
  Event waited on                             Times   Max. Wait  Total Waited
  ----------------------------------------   Waited  ----------  ------------
  SQL*Net message to client                    6668        0.00          0.01
  db file scattered read                         96        0.06          0.85
  direct path write temp                        384        0.00          0.09
  direct path read temp                         395        0.06          0.94
  SQL*Net message from client                  6668        0.00          0.92
********************************************************************************

The version with the in-line view showed this TkProf report:

********************************************************************************

select emp
     , Max (Decode (type_phone, 'H', phone_num)) Home
     , Max (Decode (type_phone, 'O', phone_num)) Office
     , Max (Decode (type_phone, 'C', phone_num)) Cell
  from (select emp
             , type_phone
             , phone_num
          from t
       )
 group by emp

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     6668      0.95       2.60       2993       1371          5      100000
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total     6670      0.95       2.60       2993       1371          5      100000

Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 57

Rows     Row Source Operation
-------  ---------------------------------------------------
 100000  SORT GROUP BY (cr=1371 pr=2993 pw=1633 time=10104593 us)
 300000   TABLE ACCESS FULL T (cr=1371 pr=1360 pw=0 time=6909082 us)


Elapsed times include waiting on following events:
  Event waited on                             Times   Max. Wait  Total Waited
  ----------------------------------------   Waited  ----------  ------------
  SQL*Net message to client                    6668        0.00          0.01
  db file scattered read                         96        0.06          0.87
  direct path write temp                        367        0.00          0.09
  direct path read temp                         389        0.03          0.53
  SQL*Net message from client                  6668        0.01          0.86
********************************************************************************

No surprises here either. The differences are minimal, as aspected.

1028120241

I ran these tests on my laptop which has an Oracle 10g Release 1 on it. I noticed something about the explain plan for both queries. You may have noticed too, the plan hash value is the same.
I couldn’t find the meaning of this plan hash value in the documentation. My guess, but this falls under “gut feeling” and “gut feelings” don’t count remember?, is that the CBO rewrites the query
so both queries use the same execution plan. Like I said before it’s just a guess.
But, what if you would prevent the CBO doing this? Adding rownum to the in-line would cause the in-line view to be materialized prior to performing the outer query. This does change the execution plan and the plan hash value to:

SQL> explain plan for
  2  select emp
  3       , Max (Decode (type_phone, 'H', phone_num)) Home
  4       , Max (Decode (type_phone, 'O', phone_num)) Office
  5       , Max (Decode (type_phone, 'C', phone_num)) Cell
  6    from (select rownum
  7               , emp
  8               , type_phone
  9               , phone_num
 10            from t
 11         )
 12   group by emp
 13  /

Explained.

SQL> @xplan

PLAN_TABLE_OUTPUT
-------------------------------------------------------------------------------------
Plan hash value: 3016995210

-------------------------------------------------------------------------------------
| Id  | Operation            | Name | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT     |      |   100K|    49M|       | 54244   (1)| 00:10:51 |
|   1 |  SORT GROUP BY       |      |   100K|    49M|   209M| 54244   (1)| 00:10:51 |
|   2 |   VIEW               |      |   302K|   149M|       |   312   (2)| 00:00:04 |
|   3 |    COUNT             |      |       |       |       |            |          |
|   4 |     TABLE ACCESS FULL| T    |   302K|  7964K|       |   312   (2)| 00:00:04 |
-------------------------------------------------------------------------------------

11 rows selected.

SQL>

Conclusion

It really doesn’t make any difference whether or not you use an in-line view in this case. It’s just a matter of preference.
To read the entire discussion, it’s right here .
You can download my testscript, to verify my results, right here.

 

One Response

  1. Alex Nuijten April 3, 2006