Columns to Rows

Performance Comparison

The following tables show performance metrics for one run each of the three Column to Row techniques described in the preceeding tutorials.

Each run transforms 10 columns from an 800 row table into a 8,000 row result set. See the log file from these tests for more details.

The tests were performed using SQL*Plus against an Oracle 11g Enterprise Edition Release 11.1.0.6.0 database running on Oracle Enterprise Linux 5.

Statistics

The following table shows database statistics where values for one method differ by more than 100 from another method.



METRIC_NAME                         UNPIVOT    UNION ALL   Collection        MODEL
------------------------------ ------------ ------------ ------------ ------------
Elapsed Time (1/100 sec)                 30           32           32           43
table scan rows gotten                8,800      157,493      142,869        1,600
session uga memory max              123,452      123,452      123,452      246,904
bytes sent via SQL*Net to clie      127,123      118,454      118,719      127,935
session logical reads                   135          755          641           72
consistent gets                          83          703          588           20
consistent gets from cache               83          703          588           20
consistent gets from cache (fa           82          691          551           19
no work - consistent read gets           44          635          540            8
table scan blocks gotten                 44          614          540            8
recursive calls                          36          295            9            9
session uga memory                   65,464            0       65,464       65,464
sorts (rows)                          8,000            0            0            0

 

See Statistics Descriptions for a description of each metric.

Latch Gets

The following table shows total latch gets for each method.



     UNPIVOT    UNION ALL   Collection        MODEL
------------ ------------ ------------ ------------
       2,150        3,771        2,463        1,863

 

Techniques that use a small number of latches scale better than techniques that use a large number of latches.

Warning: Results on your own systems with your own data will differ from these results. Results will even differ from one set of test runs to the next on the same machine. Run your own tests and average the results from multiple runs before making performance decisions.




Linking to SQL Snippets ™

To link to this page in Oracle Technology Network Forums or OraFAQ Forums cut and paste this code.

  • [url=http://www.sqlsnippets.com/en/topic-12040.html]SQL Snippets: Columns to Rows - Performance Comparison[/url]

To link to this page in HTML documents or Blogger comments cut and paste this code.

  • <a href="http://www.sqlsnippets.com/en/topic-12040.html">SQL Snippets: Columns to Rows - Performance Comparison</a>

To link to this page in other web sites use the following values.

  • Link Text : SQL Snippets: Columns to Rows - Performance Comparison
  • URL (href): http://www.sqlsnippets.com/en/topic-12040.html

Revision Notes

Date Category Note
2007-11-18 Revision added UNPIVOT method