Monday, April 26, 2010

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.

4 comments:

Narendra said...

Chet,

Nice one. Learned something new today. So does it mean oracle actually rewrites the query
SELECT QUANTITY_SOLD, AMOUNT_SOLD FROM vw_sales;
as
SELECT QUANTITY_SOLD, AMOUNT_SOLD FROM sales;
?

oraclenerd said...

Narendra,

Good question...I'm not sure if Oracle rewrites it or not. Not really sure how to check that either. Maybe someone out there can answer that question...

chet

Narendra said...

Well, it turns out that oracle does rewrite the query. A 10053 trace shows following details that confirm the query rewrite.
Here is a section in the beginning

Current SQL statement for this session:
select owner, object_name from t_view
*******************************************

and here is the one at the end, which shows the query is rewritten BEFORE the plan is generated.

******* UNPARSED QUERY IS *******
SELECT "T"."OWNER" "OWNER","T"."OBJECT_NAME" "OBJECT_NAME" FROM HR."T" "T"
kkoqbc-subheap (delete addr=ffffffff7af1bf90, in-use=11192, alloc=11656)
kkoqbc-end
: call(in-use=10232, alloc=32712), compile(in-use=44232, alloc=46360)
apadrv-end: call(in-use=10232, alloc=32712), compile(in-use=45016, alloc=46360)

sql_id=aujacrknywd5u.
Current SQL statement for this session:
select owner, object_name from t_view

============
Plan Table
============
-------------------------------------+-----------------------------------+
| Id | Operation | Name | Rows | Bytes | Cost | Time |
-------------------------------------+-----------------------------------+
| 0 | SELECT STATEMENT | | | | 163 | |
| 1 | TABLE ACCESS FULL | T | 50K | 1502K | 163 | 00:00:02 |
-------------------------------------+-----------------------------------+
Predicate Information:
----------------------

Content of other_xml column
===========================
db_version : 10.2.0.4
parse_schema : HR
plan_hash : 2153619298
Outline Data:
/*+
BEGIN_OUTLINE_DATA
IGNORE_OPTIM_EMBEDDED_HINTS
OPTIMIZER_FEATURES_ENABLE('10.2.0.4')
ALL_ROWS
OUTLINE_LEAF(@"SEL$F5BB74E1")
MERGE(@"SEL$2")
OUTLINE(@"SEL$1")
OUTLINE(@"SEL$2")
FULL(@"SEL$F5BB74E1" "T"@"SEL$2")
END_OUTLINE_DATA
*/

Hope it helps.

Stewart Bryson said...

Good stuff Chet. Analytics inside views always gets my head spinning... I can never remember all the rules. It's like the "'i' before 'e' except after 'c'" rule... I always have to fire up a few queries to remind myself.

But this one... this was uncharted waters for me. Excellent to know.

@Narendra: nice addition.