Analytics in Views

The question:

Does embedding an analytic function into a view cause a WINDOW SORT every time the view is called?

I say no, only if it is specifically referenced.

SH@TESTING>SELECT * FROM v$version;

BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - Prod
PL/SQL Release 10.2.0.3.0 - Production
CORE    10.2.0.3.0      Production
TNS for 32-bit Windows: Version 10.2.0.3.0 - Production
NLSRTL Version 10.2.0.3.0 - Production

The view definition:

CREATE OR REPLACE
VIEW vw_sales
AS
SELECT
  prod_id,
  cust_id,
  time_id,
  channel_id,
  promo_id,
  quantity_sold,
  amount_sold,
  SUM( amount_sold ) OVER
    ( PARTITION BY
        prod_id,
        cust_id
      ORDER BY time_id ) amount_sold_running
FROM sales;

I run an explain plan as SELECT *

SH@TESTING>EXPLAIN PLAN FOR SELECT * FROM VW_SALES;

Explained.

Elapsed: 00:00:02.19
SH@TESTING>@EXPLAIN

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------
Plan hash value: 2700574370

----------------------------------------------------------------------------------------------------
| Id  | Operation             | Name     | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     | Pstart| Pstop |
----------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT      |          |   918K|    87M|       |  7841   (3)| 00:01:35 |       |       |
|   1 |  VIEW                 | VW_SALES |   918K|    87M|       |  7841   (3)| 00:01:35 |       |       |
|   2 |   WINDOW SORT         |          |   918K|    25M|    84M|  7841   (3)| 00:01:35 |       |       |
|   3 |    PARTITION RANGE ALL|          |   918K|    25M|       |   389  (12)| 00:00:05 |     1 |    28 |
|   4 |     TABLE ACCESS FULL | SALES    |   918K|    25M|       |   389  (12)| 00:00:05 |     1 |    28 |
----------------------------------------------------------------------------------------------------

Now if I just select the columns I want/need, will Oracle perform the WINDOW SORT?

SH@TESTING>EXPLAIN PLAN FOR
  2  SELECT QUANTITY_SOLD, AMOUNT_SOLD
  3  FROM vw_sales;

Explained.

Elapsed: 00:00:00.02
SH@TESTING>@explain

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------
Plan hash value: 1550251865

---------------------------------------------------------------------------------------------
| Id  | Operation           | Name  | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
---------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT    |       |   918K|  7178K|   389  (12)| 00:00:05 |       |       |
|   1 |  PARTITION RANGE ALL|       |   918K|  7178K|   389  (12)| 00:00:05 |     1 |    28 |
|   2 |   TABLE ACCESS FULL | SALES |   918K|  7178K|   389  (12)| 00:00:05 |     1 |    28 |
---------------------------------------------------------------------------------------------

Oracle is smart enough not to perform the WINDOW SORT if the column is not selected.

Nice.