Select Journal - First Quarter 2013 - (Page 26)

Retrieving Large Volumes of Data continued from page 25 This output provides information about a successful query rewrite performed by the optimizer. A simple execution plan check confirms that query rewrite occurs: explain plan for SELECT t.fiscal_year , SUM(s.quantity_sold) , SUM(s.amount_sold) , count(distinct promo_id) FROM sales s , times t , channels c WHERE s.time_id = t.time_id AND s.channel_id = c.channel_id and t.fiscal_year = 1998 group by t.fiscal_year , c.channel_desc; select * from table(dbms_xplan.display); --------------------------------------------------------------------------------------| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ---------------------------------------------------------------------------------------| 0 | SELECT STATEMENT | | 3 | 93 | 4 (25)| 00:00:01 | | 1 | HASH GROUP BY | | 3 | 93 | 4 (25)| 00:00:01 | |* 2 | MAT_VIEW REWRITE ACCESS FULL| MV_RW1 | 14 | 434 | 3 (0)| 00:00:01 | ---------------------------------------------------------------------------------------Predicate Information (identified by operation id): --------------------------------------------------2 - filter(“MV_RW1”.”FISCAL_YEAR”=1998) The execution plan shows that the materialized view we’ve created is picked up by the optimizer instead of tables listed in the query code. Case 2: Query equivalence Sometimes systems have fact metrics, defined as unique numbers of something. In such cases, it is very hard to apply a query rewrite, as report drilldowns will require such metrics to be aggregated exactly to the selected level of hierarchy. Considering the following query as an example: One of the fact metrics had been designed as COUNT(DISTINCT PROMO_ID). The appearance of this expression in the query code will invalidate the materialized view MV_RW1 created earlier. The query below illustrates how the query only changes slightly but has a significant impact to the way which Oracle executes the query. This change results in the query not using the Materialized View and now executing as a standard SQL statement as you may see when you look at the explain plan following the query. explain plan for SELECT t.fiscal_year , c.channel_desc , SUM(s.quantity_sold) , SUM(s.amount_sold) , count(distinct promo_id) FROM sales s , times t Page 26 ■ 1st Qtr 2013 , channels c WHERE s.time_id = t.time_id AND s.channel_id = c.channel_id and t.fiscal_year = 1998 group by t.fiscal_year , c.channel_desc; select * from table(dbms_xplan.display); ------------------------------------------------------------------------------------------------------------------| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop | ------------------------------------------------------------------------------------------------------------------| 0 | SELECT STATEMENT | | 4 | 212 | 520 (4)| 00:00:07 | | | | 1 | HASH GROUP BY | | 4 | 212 | 520 (4)| 00:00:07 | | | | 2 | VIEW | VW_DAG_0 | 8 | 424 | 520 (4)| 00:00:07 | | | | 3 | HASH GROUP BY | | 8 | 400 | 520 (4)| 00:00:07 | | | | 4 | MERGE JOIN | | 8 | 400 | 519 (4)| 00:00:07 | | | | 5 | TABLE ACCESS BY INDEX ROWID | CHANNELS | 5 | 65 | 2 (0)| 00:00:01 | | | | 6 | INDEX FULL SCAN | CHANNELS_PK | 5 | | 1 (0)| 00:00:01 | | | |* 7 | SORT JOIN | | 8 | 296 | 517 (4)| 00:00:07 | | | | 8 | VIEW | VW_GBC_10 | 8 | 296 | 516 (4)| 00:00:07 | | | | 9 | HASH GROUP BY | | 8 | 280 | 516 (4)| 00:00:07 | | | |* 10 | HASH JOIN | | 191K| 6546K| 510 (3)| 00:00:07 | | | | 11 | PART JOIN FILTER CREATE | :BF0000 | 304 | 3648 | 18 (0)| 00:00:01 | | | |* 12 | TABLE ACCESS FULL | TIMES | 304 | 3648 | 18 (0)| 00:00:01 | | | | 13 | PARTITION RANGE JOIN-FILTER| | 918K| 20M| 489 (2)| 00:00:06 |:BF0000|:BF0000| | 14 | TABLE ACCESS FULL | SALES | 918K| 20M| 489 (2)| 00:00:06 |:BF0000|:BF0000| ------------------------------------------------------------------------------------------------------------------- The fact that the query changes the execution plan means that we need to improve our previous definition for the materialized view. The COUNT(DISTINCT) metric allows query rewrite for all business scenarios in only one case, so by moving the field used in the expression into a list of fields used in selection criteria and grouping by part of the query we can improve performance and maximize query rewrite. The materialized view creation code with the S.PROMO_ID column added into the list of fields of materialized view is below: create materialized view MV_RW2 build immediate using index refresh force on demand enable query rewrite as SELECT t.fiscal_year , t.fiscal_quarter_desc , c.channel_desc , s.promo_id , SUM(s.quantity_sold) , SUM(s.amount_sold) FROM sales s , times t , channels c WHERE s.time_id = t.time_id AND s.channel_id = c.channel_id group by t.fiscal_year, fiscal_quarter_desc , c.channel_desc , s.promo_id ;

Table of Contents for the Digital Edition of Select Journal - First Quarter 2013

Select Journal - First Quarter 2013
Table of Contents
From the Editor
From the IOUG President
Enterprise Manager 12c Cloud Control: What’s Changed, What’s New
Introduction to Oracle Enterprise Manager Command Line Interface
Users Group Calendar
Retrieving Large Volumes of Data
A Multilayered Approach to Oracle Database Availability
Advertisers’ Index
Ask an Oracle ACE

Select Journal - First Quarter 2013