![Does a simple in-line view affect performance? 20188367001]({"aperture":"0","credit":"","camera":"","caption":"","created_timestamp":"0","copyright":"http:\/\/seattletimes.nwsource.com\/ABPub\/2012\/08\/02\/2018836700.jpg","focal_length":"0","iso":"0","shutter_speed":"0","title":"","orientation":"0"} "Does a simple in-line view affect performance? 1")
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.
Read on OTN (posted by cd):
> I did a more extensive test to verify our
> assumptions. I posted my findings here:
> http://technology.amis.nl/blog/?p=1055
Just as a follow up: well done. … *twothumbsup*
C.