Analytic Views, in combination with Attribute Dimensions and Hierarchies, are very useful for ad-hoc queries in a Star Schema. But how about the performance of this Oracle 12.2 feature? I wanted to know it and analyzed the execution plans of some simple queries.
As already mentioned in previous blog posts, I’m a fan of Analytic Views. These new database objects are very powerful and practical for queries in a typical Star Schema. Because I had not the chance yet to use them in real-life BI projects, I have no idea about the performance of queries on Analytic Views. On the Sales History (SH) sample schema, the response times are not an issue, but the execution plans look quite complex. To learn more about Analytic Views, I examined some execution plans in detail.
Execution Plans for Hierarchies
First, I will have a look on Attribute Dimensions and Hierarchies. Attribute Dimensions cannot be used directly in SQL queries, but are the basis for Hierarchies. A query on a Hierarchy is typically used to show the hierarchical tree of a dimension. For the following example, I use the PRODUCTS dimension of the SH schema. The definition of the corresponding Attribute Dimension and Hierarchy is described in the blog post Attribute Dimensions and Hierarchies in Oracle 12.2.
The following simple query returns the full product hierarchy with 5 categories, 22 subcategories and 72 products, displayed in the correct sort order and indent level:
SELECT RPAD(‘ ‘, depth * 2) || member_name
FROM sh_products_hier
ORDER BY hier_order;
To reduce the complexity for the execution plans, I dropped all indexes on the SH schema except the primary key indexes on the dimension tables. The execution plan for the simple query above consists of several parts, each of the reads an aggregates a specific hierarchy level:
- Lines 6 and 7 read the whole table and aggregates it on ‘PRODUCT’ level
- Lines 8 and 9 read the whole table and aggregates it on ‘SUBCATEGORY’ level
- Lines 10 and 11 read the whole table and aggregates it on ‘CATEGORY’ level
- Lines 12 to 14 read one row of the table to create the top level ‘ALL PRODUCTS’
-------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers |
-------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 100 |00:00:00.06 | 22 |
| 1 | SORT ORDER BY | | 1 | 99 | 100 |00:00:00.06 | 22 |
| 2 | VIEW | | 1 | 99 | 100 |00:00:00.06 | 22 |
| 3 | WINDOW SORT | | 1 | 99 | 100 |00:00:00.06 | 22 |
| 4 | VIEW | | 1 | 99 | 100 |00:00:00.01 | 22 |
| 5 | UNION-ALL | | 1 | | 100 |00:00:00.01 | 22 |
| 6 | HASH GROUP BY | | 1 | 72 | 72 |00:00:00.01 | 7 |
| 7 | TABLE ACCESS FULL| PRODUCTS | 1 | 72 | 72 |00:00:00.01 | 7 |
| 8 | HASH GROUP BY | | 1 | 21 | 22 |00:00:00.03 | 7 |
| 9 | TABLE ACCESS FULL| PRODUCTS | 1 | 21 | 72 |00:00:00.01 | 7 |
| 10 | HASH GROUP BY | | 1 | 5 | 5 |00:00:00.02 | 7 |
| 11 | TABLE ACCESS FULL| PRODUCTS | 1 | 5 | 72 |00:00:00.01 | 7 |
| 12 | VIEW | | 1 | 1 | 1 |00:00:00.01 | 1 |
|* 13 | COUNT STOPKEY | | 1 | | 1 |00:00:00.01 | 1 |
| 14 | INDEX FULL SCAN | PRODUCTS_PK | 1 | 1 | 1 |00:00:00.01 | 1 |
-------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
13 - filter(ROWNUM=1)
Three full table scans must be performed on the dimension table to show the complete hierarchy. But what happens if I want to display only two levels? I run the same query again, but this time with filtering two hierarchy levels:
SELECT RPAD(' ', depth * 2) || member_name
FROM sh_products_hier
WHERE level_name IN ('CATEGORY', 'SUBCATEGORY')
ORDER BY hier_order;
The only difference in the execution plan is the additional filter in line 2:
-------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers |
-------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 27 |00:00:00.08 | 22 |
| 1 | SORT ORDER BY | | 1 | 99 | 27 |00:00:00.08 | 22 |
|* 2 | VIEW | | 1 | 99 | 27 |00:00:00.08 | 22 |
| 3 | WINDOW SORT | | 1 | 99 | 100 |00:00:00.08 | 22 |
| 4 | VIEW | | 1 | 99 | 100 |00:00:00.01 | 22 |
| 5 | UNION-ALL | | 1 | | 100 |00:00:00.01 | 22 |
| 6 | HASH GROUP BY | | 1 | 72 | 72 |00:00:00.01 | 7 |
| 7 | TABLE ACCESS FULL| PRODUCTS | 1 | 72 | 72 |00:00:00.01 | 7 |
| 8 | HASH GROUP BY | | 1 | 21 | 22 |00:00:00.04 | 7 |
| 9 | TABLE ACCESS FULL| PRODUCTS | 1 | 21 | 72 |00:00:00.01 | 7 |
| 10 | HASH GROUP BY | | 1 | 5 | 5 |00:00:00.04 | 7 |
| 11 | TABLE ACCESS FULL| PRODUCTS | 1 | 5 | 72 |00:00:00.01 | 7 |
| 12 | VIEW | | 1 | 1 | 1 |00:00:00.01 | 1 |
|* 13 | COUNT STOPKEY | | 1 | | 1 |00:00:00.01 | 1 |
| 14 | INDEX FULL SCAN | PRODUCTS_PK | 1 | 1 | 1 |00:00:00.01 | 1 |
-------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter(("H"."SH_PRODUCTS_HIER#LEVEL_NAME"='CATEGORY' OR
"H"."SH_PRODUCTS_HIER#LEVEL_NAME"='SUBCATEGORY'))
13 - filter(ROWNUM=1)
If you are familiar with execution plans on views, you will see that this is a non-mergeable view, and no predicate pushing is used here. In other words: The filter is applied at the end, when all hierarchy levels were read – even those that are not needed for the result set.
SELECT RPAD(' ', depth * 2)||member_name
FROM sh_customers_hier
WHERE level_name IN ('REGION', 'SUBREGION')
ORDER BY hier_order;
----------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers |
----------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 14 |00:00:02.47 | 1556 |
| 1 | SORT ORDER BY | | 1 | 1 | 14 |00:00:02.47 | 1556 |
| 2 | VIEW | SH_CUSTOMERS_HIER | 1 | 1 | 14 |00:00:02.38 | 1556 |
| 3 | TEMP TABLE TRANSFORMATION | | 1 | | 14 |00:00:02.38 | 1556 |
| 4 | LOAD AS SELECT (CURSOR DURATION MEMORY)| SYS_TEMP_0FD9D6ADD_4492F0 | 1 | | 0 |00:00:00.30 | 1556 |
|* 5 | HASH JOIN | | 1 | 55500 | 55500 |00:00:00.29 | 1556 |
| 6 | TABLE ACCESS FULL | COUNTRIES | 1 | 23 | 23 |00:00:00.01 | 7 |
| 7 | TABLE ACCESS FULL | CUSTOMERS | 1 | 55500 | 55500 |00:00:00.02 | 1546 |
|* 8 | VIEW | | 1 | 175K| 14 |00:00:02.08 | 0 |
| 9 | WINDOW SORT | | 1 | 175K| 56299 |00:00:02.37 | 0 |
| 10 | VIEW | | 1 | 175K| 56299 |00:00:02.07 | 0 |
| 11 | UNION-ALL | | 1 | | 56299 |00:00:01.52 | 0 |
| 12 | HASH GROUP BY | | 1 | 55500 | 55500 |00:00:00.52 | 0 |
| 13 | VIEW | | 1 | 55500 | 55500 |00:00:00.01 | 0 |
| 14 | TABLE ACCESS FULL | SYS_TEMP_0FD9D6ADD_4492F0 | 1 | 55500 | 55500 |00:00:00.01 | 0 |
| 15 | HASH GROUP BY | | 1 | 55500 | 620 |00:00:00.05 | 0 |
| 16 | VIEW | | 1 | 55500 | 55500 |00:00:00.01 | 0 |
| 17 | TABLE ACCESS FULL | SYS_TEMP_0FD9D6ADD_4492F0 | 1 | 55500 | 55500 |00:00:00.01 | 0 |
| 18 | HASH GROUP BY | | 1 | 55500 | 145 |00:00:00.03 | 0 |
| 19 | VIEW | | 1 | 55500 | 55500 |00:00:00.01 | 0 |
| 20 | TABLE ACCESS FULL | SYS_TEMP_0FD9D6ADD_4492F0 | 1 | 55500 | 55500 |00:00:00.01 | 0 |
| 21 | HASH GROUP BY | | 1 | 8978 | 19 |00:00:00.01 | 0 |
| 22 | VIEW | | 1 | 55500 | 55500 |00:00:00.01 | 0 |
| 23 | TABLE ACCESS FULL | SYS_TEMP_0FD9D6ADD_4492F0 | 1 | 55500 | 55500 |00:00:00.01 | 0 |
| 24 | HASH GROUP BY | | 1 | 34 | 8 |00:00:00.01 | 0 |
| 25 | VIEW | | 1 | 55500 | 55500 |00:00:00.01 | 0 |
| 26 | TABLE ACCESS FULL | SYS_TEMP_0FD9D6ADD_4492F0 | 1 | 55500 | 55500 |00:00:00.01 | 0 |
| 27 | HASH GROUP BY | | 1 | 6 | 6 |00:00:00.01 | 0 |
| 28 | VIEW | | 1 | 55500 | 55500 |00:00:00.01 | 0 |
| 29 | TABLE ACCESS FULL | SYS_TEMP_0FD9D6ADD_4492F0 | 1 | 55500 | 55500 |00:00:00.01 | 0 |
| 30 | VIEW | | 1 | 1 | 1 |00:00:00.01 | 0 |
|* 31 | COUNT STOPKEY | | 1 | | 1 |00:00:00.01 | 0 |
| 32 | VIEW | | 1 | 1 | 1 |00:00:00.01 | 0 |
| 33 | TABLE ACCESS FULL | SYS_TEMP_0FD9D6ADD_4492F0 | 1 | 55500 | 1 |00:00:00.01 | 0 |
----------------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
5 - access("A"."COUNTRY_ID"="B"."COUNTRY_ID")
8 - filter(("H"."SH_CUSTOMERS_HIER#LEVEL_NAME"='REGION' OR "H"."SH_CUSTOMERS_HIER#LEVEL_NAME"='SUBREGION'))
31 - filter(ROWNUM=1)
Another interesting detail can be seen in this execution plan (blue marked lines 3 to 7): Because the Attribute Dimension is built on a view that joins the two tables CUSTOMERS and COUNTRIES, a temporary table is created first to avoid the the join has to be executed for each hierarchy level. This is a good approach, but the real performance issue is that the complete dimension table must be scanned for each hierarchy level. This may cause poor performance for large dimension tables with a high number of hierarchy levels.
Execution Plans for Analytic Views
More interesting than simple queries on a single Hierarchy are queries on an Analytic View. Next, I will have a look at the execution plan of a query on SH_SALES_HISTORY_AV. The description of this Analytic View can be found in the blog post Analytic Views: Powerful Oracle 12.2 Feature for Business Intelligence. I start with a simple query that uses only one dimension hierarchy (SH_PRODUCTS_HIER).
SELECT RPAD(' ', sh_products_hier.depth * 2)
||sh_products_hier.member_name AS products_hier
, amount_sold
, quantity_sold
FROM sh_sales_history_av
HIERARCHIES (sh_products_hier)
WHERE sh_products_hier.level_name IN ('CATEGORY', 'SUBCATEGORY')
ORDER BY sh_products_hier.hier_order;
The SQL statement looks simple, but what happens in the background? The execution plan shows some interesting details:
----------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers |
----------------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 27 |00:00:00.60 | 4724 |
| 1 | SORT ORDER BY | | 1 | 1 | 27 |00:00:00.60 | 4724 |
| 2 | VIEW | SH_SALES_HISTORY_AV | 1 | 1 | 27 |00:00:00.60 | 4724 |
| 3 | TEMP TABLE TRANSFORMATION | | 1 | | 27 |00:00:00.60 | 4724 |
| 4 | LOAD AS SELECT (CURSOR DURATION MEMORY) | SYS_TEMP_0FD9D6B37_4492F0 | 1 | | 0 |00:00:00.06 | 15 |
| 5 | WINDOW SORT | | 1 | 26 | 27 |00:00:00.06 | 14 |
| 6 | VIEW | | 1 | 26 | 27 |00:00:00.04 | 14 |
| 7 | UNION-ALL | | 1 | | 27 |00:00:00.04 | 14 |
| 8 | HASH GROUP BY | | 1 | 21 | 22 |00:00:00.04 | 7 |
| 9 | TABLE ACCESS FULL | PRODUCTS | 1 | 21 | 72 |00:00:00.01 | 7 |
| 10 | HASH GROUP BY | | 1 | 5 | 5 |00:00:00.02 | 7 |
| 11 | TABLE ACCESS FULL | PRODUCTS | 1 | 5 | 72 |00:00:00.01 | 7 |
| 12 | LOAD AS SELECT (CURSOR DURATION MEMORY) | SYS_TEMP_0FD9D6B38_4492F0 | 1 | | 0 |00:00:00.01 | 0 |
|* 13 | VIEW | | 1 | 26 | 27 |00:00:00.01 | 0 |
| 14 | TABLE ACCESS FULL | SYS_TEMP_0FD9D6B37_4492F0 | 1 | 26 | 27 |00:00:00.01 | 0 |
|* 15 | HASH JOIN | | 1 | 10 | 27 |00:00:00.54 | 4708 |
| 16 | VIEW | | 1 | 2 | 27 |00:00:00.46 | 4708 |
| 17 | HASH GROUP BY | | 1 | 2 | 27 |00:00:00.46 | 4708 |
| 18 | NESTED LOOPS | | 1 | 31 | 46 |00:00:00.38 | 4708 |
| 19 | VIEW | | 1 | 26 | 22 |00:00:00.38 | 4708 |
| 20 | TEMP TABLE TRANSFORMATION | | 1 | | 22 |00:00:00.38 | 4708 |
| 21 | LOAD AS SELECT (CURSOR DURATION MEMORY)| SYS_TEMP_0FD9D6B36_4492F0 | 1 | | 0 |00:00:00.16 | 14 |
| 22 | HASH GROUP BY | | 1 | 4 | 22 |00:00:00.16 | 14 |
| 23 | KEY VECTOR CREATE BUFFERED | :KV0000 | 1 | 4 | 22 |00:00:00.10 | 14 |
| 24 | VIEW | | 1 | 73 | 72 |00:00:00.04 | 14 |
| 25 | UNION-ALL | | 1 | | 72 |00:00:00.04 | 14 |
|* 26 | HASH JOIN SEMI | | 1 | 1 | 72 |00:00:00.04 | 7 |
| 27 | TABLE ACCESS FULL | PRODUCTS | 1 | 72 | 72 |00:00:00.01 | 7 |
| 28 | VIEW | VW_NSO_1 | 1 | 26 | 22 |00:00:00.01 | 0 |
|* 29 | VIEW | | 1 | 26 | 22 |00:00:00.01 | 0 |
| 30 | TABLE ACCESS FULL | SYS_TEMP_0FD9D6B38_4492F0 | 1 | 26 | 27 |00:00:00.01 | 0 |
|* 31 | HASH JOIN SEMI | | 1 | 72 | 0 |00:00:00.06 | 7 |
|* 32 | HASH JOIN ANTI NA | | 1 | 72 | 0 |00:00:00.06 | 7 |
| 33 | TABLE ACCESS FULL | PRODUCTS | 1 | 72 | 72 |00:00:00.01 | 7 |
|* 34 | VIEW | | 1 | 26 | 22 |00:00:00.01 | 0 |
| 35 | TABLE ACCESS FULL | SYS_TEMP_0FD9D6B38_4492F0 | 1 | 26 | 27 |00:00:00.01 | 0 |
| 36 | VIEW | VW_NSO_2 | 0 | 26 | 0 |00:00:00.01 | 0 |
|* 37 | VIEW | | 0 | 26 | 0 |00:00:00.01 | 0 |
| 38 | TABLE ACCESS FULL | SYS_TEMP_0FD9D6B38_4492F0 | 0 | 26 | 0 |00:00:00.01 | 0 |
| 39 | HASH GROUP BY | | 1 | 3 | 22 |00:00:00.22 | 4694 |
|* 40 | HASH JOIN | | 1 | 4 | 22 |00:00:00.14 | 4694 |
| 41 | VIEW | VW_VT_C9E7DF0B | 1 | 4 | 22 |00:00:00.08 | 4694 |
| 42 | VECTOR GROUP BY | | 1 | 4 | 22 |00:00:00.08 | 4694 |
| 43 | HASH GROUP BY | | | 4 | | | |
| 44 | KEY VECTOR USE | :KV0000 | 1 | 51047 | 918K|00:00:00.12 | 4694 |
| 45 | PARTITION RANGE ALL | | 1 | 918K| 918K|00:00:00.08 | 4694 |
|* 46 | TABLE ACCESS FULL | SALES | 20 | 918K| 918K|00:00:00.06 | 4694 |
| 47 | TABLE ACCESS FULL | SYS_TEMP_0FD9D6B36_4492F0 | 1 | 4 | 22 |00:00:00.01 | 0 |
|* 48 | VIEW | | 22 | 1 | 46 |00:00:00.01 | 0 |
| 49 | TABLE ACCESS FULL | SYS_TEMP_0FD9D6B38_4492F0 | 22 | 26 | 594 |00:00:00.01 | 0 |
|* 50 | VIEW | | 1 | 26 | 27 |00:00:00.01 | 0 |
| 51 | TABLE ACCESS FULL | SYS_TEMP_0FD9D6B37_4492F0 | 1 | 26 | 27 |00:00:00.01 | 0 |
----------------------------------------------------------------------------------------------------------------------------------------
First, I realized that only the required levels of the hierarchy are selected. Unlike in the previous queries on Hierarchies, the optimizer is now able to read only the levels ‘SUBCATEGORY’ (line 9) and ‘CATEGORY’ (line 11) from the dimension table PRODUCTS. Lines 4 to 11 create a temporary table with the information from SH_PRODUCTS_HIER and filters the result to the required levels.
Predicate Information (identified by operation id):
---------------------------------------------------
13 - filter(("H"."SH_PRODUCTS_ATTR_DIM#SH_PRODUCTS_HIER#LEVEL_NAME"='CATEGORY' OR
"H"."SH_PRODUCTS_ATTR_DIM#SH_PRODUCTS_HIER#LEVEL_NAME"='SUBCATEGORY'))
Even more interesting is the second part of the execution plan (lines 15 to 51). It contains a Vector Transformation – a special query transformation for efficient joins in a Star Schema. It is performed in two phases:
- In phase 1, the dimension table is scanned to build a so called key vector (here :KV0000). The key vector is used to derive and aggregate the required rows from the dimension table. The result is stored in a temporary table. If more than one dimension with filters occurs in the query, this step would be performed for each of the dimensions. Phase 1 is marked blue in the execution plan above (lines 21 to 38).
- In phase 2, a full table scan on the fact table is performed with a filter on the key vector (green part of the execution plan). Finally, a join back to to the temporary tables is needed to get the dimension columns for the result set (orange part of the execution plan.
Although a full table scan on all partitions of the fact table SALES is executed, this is very efficient because of the key vector filter in line 46:
46 – filter(SYS_OP_KEY_VECTOR_FILTER(“F”.”PROD_ID”,:KV0000))
Why is a Vector Transformation Used?
A Vector Transformation is normally used for In-Memory aggregations when the Oracle Database In-Memory option is activated (i.e. inmemory_size > 0). On my test database, this is the case, but none of the tables are currently defined with the INMEMORY clause. And even if this would be the case, the Vector Transformation usually works only if the fact table contains more than 10 million rows (undocumented parameter “_optimizer_vector_min_fact_rows”). Why is a Vector Transformation used here on a fact table with less than 1 million rows that are not populated into the In-Memory Column Store?
To find the reason, I used the PL/SQL procedure DBMS_UTLITY.EXPAND_SQL_TEXT that was introduced in Oracle 12c. It expands the SQL statement that is executed behind the Analytic View. The following statements can be used for this purpose:
CREATE GLOBAL TEMPORARY TABLE clob_output (text CLOB);
DECLARE
v_expanded_query CLOB;
v_original_query CLOB := '
SELECT sh_products_hier.member_name AS products_hier
, amount_sold
, quantity_sold
FROM sh_sales_history_av
HIERARCHIES (sh_products_hier)
WHERE sh_products_hier.level_name IN (''CATEGORY'', ''SUBCATEGORY'')
ORDER BY sh_products_hier.hier_order';
BEGIN
DBMS_UTILITY.EXPAND_SQL_TEXT(v_original_query, v_expanded_query);
INSERT INTO clob_output VALUES (v_expanded_query);
END;
/
SELECT * FROM clob_output;
After formatting the SQL statement with the Formatter in SQL Developer, I found the /*+ VECTOR_TRANSFORM */ hint in the code:
SELECT "A1"."SH_PRODUCTS_ATTR_DIM#SH_PRODUCTS_HIER#MEMBER_NAME" "PRODUCTS_HIER"
FROM (
(some more lines)
FROM
(
SELECT
/*+ VECTOR_TRANSFORM */
MAX(CAST(NULL AS NUMBER(1))) "NULL_COL$"
,"A4"."SH_PRODUCTS_ATTR_DIM#SH_PRODUCTS_HIER#PROD_CATEGORY"
(around 1500 more lines)
This hint is always there, even if the Oracle Database In-Memory option is disabled. In this case, the hint is ignored, and a different execution plan is generated. Because of the /*+ VECTOR_TRANSFORM */ hint, my assumption is that Oracle implemented Analytic Views mainly for environments with Oracle Database In-Memory. But can I still use them efficiently without this option? To find an answer, I finally examine the execution plans of a more complex query in two different environments.
A Realistic Example
The following query uses three dimension hierarchies and two selections – one on level ‘Country’ of the CUSTOMERS dimension and one on level ‘Product Category’ of the PRODUCTS dimension. If I would run this kind of query on a Star Schema without an Analytic View, I would expect a Star Transformation (or a Vector Transformation if the In-Memory option is available). The important question for me is: Do I have to change the physical design of my data marts because of Analytic views, or can I still use the existing rules?
SELECT sh_customers_hier.member_name state_name
, RPAD(' ', sh_products_hier.depth * 2)
||sh_products_hier.member_name AS products_hier
, RPAD(' ', sh_times_calendar_hier.depth * 2)
||sh_times_calendar_hier.member_name AS calendar_hier
, amount_sold
, quantity_sold
FROM sh_sales_history_av
HIERARCHIES (sh_times_calendar_hier, sh_products_hier, sh_customers_hier)
WHERE sh_times_calendar_hier.level_name IN ('CALENDAR_YEAR', 'CALENDAR_QUARTER')
AND sh_customers_hier.level_name = 'STATE_PROVINCE'
AND sh_customers_hier.country_name = 'Germany'
AND sh_products_hier.prod_category = 'Electronics'
ORDER BY
sh_customers_hier.hier_order
, sh_products_hier.hier_order
, sh_times_calendar_hier.hier_order;
In the first environment, In-Memory is disabled. Bitmap indexes are created on all dimension keys of the fact table, and the parameter star_transformation_enabled is set to TRUE. These are the typical recommendations for a Star Schema.
The execution plan for the query above would blow up this blog post, so I do not copy the complete plan into the post. Some parts of the plan are replaced by short comments, but the most important message is: Star Transformation works as expected. Of course, some additional overhead is needed to drill-up the the upper hierarchy levels, but as we have seen before, this works fine.
--------------------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers |
--------------------------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 2893 |00:00:00.47 | 32271 |
| 1 | SORT ORDER BY | | 1 | 1 | 2893 |00:00:00.47 | 32271 |
| 2 | VIEW | SH_SALES_HISTORY_AV | 1 | 1 | 2893 |00:00:00.47 | 32271 |
| 3 | TEMP TABLE TRANSFORMATION | | 1 | | 2893 |00:00:00.47 | 32271 |
| 4 | LOAD AS SELECT (CURSOR DURATION MEMORY) | SYS_TEMP_0FD9D6717_3233B9 | 1 | | 0 |00:00:00.01 | 123 |
____________ ...load SH_TIMES_CALENDAR_HIER
| 14 | LOAD AS SELECT (CURSOR DURATION MEMORY) | SYS_TEMP_0FD9D6718_3233B9 | 1 | | 0 |00:00:00.01 | 16 |
____________ ...load SH_PRODUCTS_HIER
| 27 | LOAD AS SELECT (CURSOR DURATION MEMORY) | SYS_TEMP_0FD9D6719_3233B9 | 1 | | 0 |00:00:00.09 | 1556 |
____________ ...join CUSTOMERS and COUNTRIES
| 39 | LOAD AS SELECT (CURSOR DURATION MEMORY) | SYS_TEMP_0FD9D671C_3233B9 | 1 | | 0 |00:00:00.01 | 0 |
____________ ...load SH_CUSTOMERS_HIER
|* 42 | HASH JOIN | | 1 | 1 | 2893 |00:00:00.36 | 30575 |
|* 43 | HASH JOIN | | 1 | 1 | 2893 |00:00:00.35 | 30575 |
|* 44 | HASH JOIN | | 1 | 1 | 2893 |00:00:00.35 | 30575 |
______________ ...some more joins and aggregations
| 72 | VIEW | VW_ST_F064CFCC | 1 | 52 | 116K|00:00:00.15 | 30423 |
| 73 | NESTED LOOPS | | 1 | 52 | 116K|00:00:00.13 | 30423 |
| 74 | PARTITION RANGE ALL | | 1 | 52 | 116K|00:00:00.03 | 29751 |
| 75 | BITMAP CONVERSION TO ROWIDS | | 28 | 52 | 116K|00:00:00.08 | 29751 |
| 76 | BITMAP AND | | 28 | | 16 |00:00:00.07 | 29751 |
| 77 | BITMAP MERGE | | 28 | | 16 |00:00:00.07 | 29495 |
________________________________ ...bitmap merge for TIMES dimension
| 99 | BITMAP MERGE | | 28 | | 16 |00:00:00.01 | 256 |
________________________________ ...bitmap merge for PRODUCTS dimension
|*142 | BITMAP INDEX RANGE SCAN | SALES_PROD_BIX | 208 | | 180 |00:00:00.01 | 228 |
| 143 | TABLE ACCESS BY USER ROWID | SALES | 116K| 1 | 116K|00:00:00.05 | 672 |
| 144 | VIEW | | 1 | 74 | 13 |00:00:00.01 | 30 |
| 145 | UNION-ALL | | 1 | | 13 |00:00:00.01 | 30 |
| 146 | NESTED LOOPS | | 1 | 1 | 13 |00:00:00.01 | 17 |
| 147 | NESTED LOOPS | | 1 | 4 | 13 |00:00:00.01 | 4 |
_____________________________ ...join back to several temporary tables
|*184 | VIEW | | 1 | 55500 | 16 |00:00:00.01 | 0 |
| 185 | TABLE ACCESS FULL | SYS_TEMP_0FD9D671A_3233B9 | 1 | 55500 | 145 |00:00:00.01 | 0 |
|*186 | VIEW | | 1 | 25 | 25 |00:00:00.01 | 0 |
| 187 | TABLE ACCESS FULL | SYS_TEMP_0FD9D6717_3233B9 | 1 | 25 | 25 |00:00:00.01 | 0 |
|*188 | VIEW | | 1 | 98 | 18 |00:00:00.01 | 0 |
| 189 | TABLE ACCESS FULL | SYS_TEMP_0FD9D6718_3233B9 | 1 | 98 | 99 |00:00:00.01 | 0 |
--------------------------------------------------------------------------------------------------------------------------------------------------
Note
-----
- star transformation used for this statement
In the second environment, In-Memory is enabled and all tables of the SH schema are populated into the In-Memory Column Store. No indexes except the primary key indexes on the dimension tables are created.
Because of the hint explained above, a Vector Transformation in executed here, even if the fact table is actually too small for this. But it works fine: three key vectors are created, one for each dimension. In the second phase, the key vectors are used to derive the required rows of the fact table. Finally, several join backs to the temporary tables of the dimensions are performed.
-----------------------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | Reads |
-----------------------------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 2893 |00:00:00.58 | 2178 | 3 |
| 1 | SORT ORDER BY | | 1 | 1 | 2893 |00:00:00.58 | 2178 | 3 |
| 2 | VIEW | SH_SALES_HISTORY_AV | 1 | 1 | 2893 |00:00:00.61 | 2178 | 3 |
| 3 | TEMP TABLE TRANSFORMATION | | 1 | | 2893 |00:00:00.59 | 2178 | 3 |
| 4 | LOAD AS SELECT (CURSOR DURATION MEMORY) | SYS_TEMP_0FD9D6E3E_4492F0 | 1 | | 0 |00:00:00.01 | 123 | 0 |
____________ ...load SH_TIMES_CALENDAR_HIER
| 13 | TABLE ACCESS INMEMORY FULL | TIMES | 1 | 5 | 1826 |00:00:00.01 | 61 | 0 |
| 14 | LOAD AS SELECT (CURSOR DURATION MEMORY) | SYS_TEMP_0FD9D6E3F_4492F0 | 1 | | 0 |00:00:00.01 | 21 | 0 |
| 15 | WINDOW SORT | | 1 | 98 | 99 |00:00:00.01 | 21 | 0 |
| 16 | VIEW | | 1 | 98 | 99 |00:00:00.01 | 21 | 0 |
| 17 | UNION-ALL | | 1 | | 99 |00:00:00.01 | 21 | 0 |
| 18 | HASH GROUP BY | | 1 | 72 | 72 |00:00:00.01 | 7 | 0 |
| 19 | TABLE ACCESS INMEMORY FULL | PRODUCTS | 1 | 72 | 72 |00:00:00.01 | 7 | 0 |
| 20 | HASH GROUP BY | | 1 | 21 | 22 |00:00:00.01 | 7 | 0 |
| 21 | TABLE ACCESS INMEMORY FULL | PRODUCTS | 1 | 21 | 72 |00:00:00.01 | 7 | 0 |
| 22 | HASH GROUP BY | | 1 | 5 | 5 |00:00:00.01 | 7 | 0 |
| 23 | TABLE ACCESS INMEMORY FULL | PRODUCTS | 1 | 5 | 72 |00:00:00.01 | 7 | 0 |
| 24 | LOAD AS SELECT (CURSOR DURATION MEMORY) | SYS_TEMP_0FD9D6E40_4492F0 | 1 | | 0 |00:00:00.10 | 1556 | 3 |
____________ ...join CUSTOMERS and COUNTRIES
| 28 | LOAD AS SELECT (CURSOR DURATION MEMORY) | SYS_TEMP_0FD9D6E41_4492F0 | 1 | | 0 |00:00:00.13 | 0 | 0 |
____________ ...load SH_CUSTOMERS_HIER
|* 39 | HASH JOIN | | 1 | 1 | 2893 |00:00:00.34 | 477 | 0 |
|* 40 | HASH JOIN | | 1 | 1 | 2893 |00:00:00.34 | 477 | 0 |
|* 41 | HASH JOIN | | 1 | 1 | 2893 |00:00:00.33 | 477 | 0 |
| 42 | VIEW | | 1 | 1 | 2893 |00:00:00.35 | 477 | 0 |
| 43 | HASH GROUP BY | | 1 | 1 | 2893 |00:00:00.33 | 477 | 0 |
| 44 | NESTED LOOPS | | 1 | 1 | 8076 |00:00:00.48 | 477 | 0 |
| 45 | NESTED LOOPS | | 1 | 1 | 2692 |00:00:00.27 | 477 | 0 |
|* 46 | HASH JOIN | | 1 | 1 | 1346 |00:00:00.15 | 477 | 0 |
| 47 | VIEW | | 1 | 1035 | 1346 |00:00:00.16 | 477 | 0 |
| 48 | TEMP TABLE TRANSFORMATION | | 1 | | 1346 |00:00:00.15 | 477 | 0 |
| 49 | LOAD AS SELECT (CURSOR DURATION MEMORY)| SYS_TEMP_0FD9D6E3D_4492F0 | 1 | | 0 |00:00:00.01 | 122 | 0 |
| 50 | HASH GROUP BY | | 1 | 2 | 20 |00:00:00.01 | 122 | 0 |
| 51 | KEY VECTOR CREATE BUFFERED | :KV0000 | 1 | 2 | 20 |00:00:00.01 | 122 | 0 |
________________________ ...build key vector for TIMES dimension
| 71 | LOAD AS SELECT (CURSOR DURATION MEMORY)| SYS_TEMP_0FD9D6E3B_4492F0 | 1 | | 0 |00:00:00.01 | 31 | 0 |
| 72 | HASH GROUP BY | | 1 | 13 | 13 |00:00:00.01 | 31 | 0 |
| 73 | KEY VECTOR CREATE BUFFERED | :KV0001 | 1 | 13 | 13 |00:00:00.01 | 31 | 0 |
________________________ ...build key vector for PRODUCTS dimension
| 103 | LOAD AS SELECT (CURSOR DURATION MEMORY)| SYS_TEMP_0FD9D6E3C_4492F0 | 1 | | 0 |00:00:00.01 | 0 | 0 |
| 104 | HASH GROUP BY | | 1 | 23 | 16 |00:00:00.01 | 0 | 0 |
| 105 | KEY VECTOR CREATE BUFFERED | :KV0002 | 1 | 23 | 16 |00:00:00.01 | 0 | 0 |
________________________ ...build key vector for CUSTOMERS dimension
| 108 | HASH GROUP BY | | 1 | 1035 | 1346 |00:00:00.13 | 324 | 0 |
|*109 | HASH JOIN | | 1 | 5384 | 1346 |00:00:00.22 | 324 | 0 |
| 110 | TABLE ACCESS FULL | SYS_TEMP_0FD9D6E3D_4492F0 | 1 | 20 | 20 |00:00:00.01 | 0 | 0 |
|*111 | HASH JOIN | | 1 | 1346 | 1346 |00:00:00.20 | 324 | 0 |
| 112 | TABLE ACCESS FULL | SYS_TEMP_0FD9D6E3C_4492F0 | 1 | 23 | 16 |00:00:00.01 | 0 | 0 |
|*113 | HASH JOIN | | 1 | 1346 | 1346 |00:00:00.18 | 324 | 0 |
| 114 | TABLE ACCESS FULL | SYS_TEMP_0FD9D6E3B_4492F0 | 1 | 3 | 13 |00:00:00.01 | 0 | 0 |
| 115 | VIEW | VW_VT_E0C1EF3A | 1 | 1346 | 1346 |00:00:00.12 | 324 | 0 |
| 116 | VECTOR GROUP BY | | 1 | 1346 | 1346 |00:00:00.12 | 324 | 0 |
| 117 | HASH GROUP BY | | | 1346 | | | | |
| 118 | KEY VECTOR USE | :KV0000 | 1 | 1346 | 1346 |00:00:00.02 | 324 | 0 |
| 119 | KEY VECTOR USE | :KV0001 | 1 | 1346 | 1346 |00:00:00.01 | 324 | 0 |
| 120 | KEY VECTOR USE | :KV0002 | 1 | 13017 | 1346 |00:00:00.01 | 324 | 0 |
| 121 | PARTITION RANGE ITERATOR | | 1 | 918K| 1346 |00:00:00.01 | 324 | 0 |
|*122 | TABLE ACCESS INMEMORY FULL | SALES | 16 | 918K| 1346 |00:00:00.12 | 324 | 0 |
|*123 | VIEW | | 1 | 55500 | 16 |00:00:00.01 | 0 | 0 |
| 124 | TABLE ACCESS FULL | SYS_TEMP_0FD9D6E41_4492F0 | 1 | 55500 | 145 |00:00:00.01 | 0 | 0 |
|*125 | VIEW | | 1346 | 1 | 2692 |00:00:00.04 | 0 | 0 |
| 126 | TABLE ACCESS FULL | SYS_TEMP_0FD9D6E42_4492F0 | 1346 | 25 | 33650 |00:00:00.11 | 0 | 0 |
|*127 | VIEW | | 2692 | 25 | 8076 |00:00:00.11 | 0 | 0 |
| 128 | TABLE ACCESS FULL | SYS_TEMP_0FD9D6E43_4492F0 | 2692 | 98 | 48456 |00:00:00.16 | 0 | 0 |
|*129 | VIEW | | 1 | 55500 | 16 |00:00:00.01 | 0 | 0 |
| 130 | TABLE ACCESS FULL | SYS_TEMP_0FD9D6E41_4492F0 | 1 | 55500 | 145 |00:00:00.01 | 0 | 0 |
|*131 | VIEW | | 1 | 25 | 25 |00:00:00.01 | 0 | 0 |
| 132 | TABLE ACCESS FULL | SYS_TEMP_0FD9D6E3E_4492F0 | 1 | 25 | 25 |00:00:00.01 | 0 | 0 |
|*133 | VIEW | | 1 | 98 | 18 |00:00:00.01 | 0 | 0 |
| 134 | TABLE ACCESS FULL | SYS_TEMP_0FD9D6E3F_4492F0 | 1 | 98 | 99 |00:00:00.01 | 0 | 0 |
-----------------------------------------------------------------------------------------------------------------------------------------------------
Note
-----
- vector transformation used for this statement
Both execution plans show that a typical query on a Star Schema can be performed in the same way as if the query is executed on the tables directly. The overhead with the Analytic View reduces the complexity of the query, but does not slow down the performance.
Conclusion
When I did my first attempts with Analytic Views, I was surprised about the complexity of the execution plans and was scared a bit about the performance aspects in real projects. But after examining the plans in detail, I am optimistic that Analytic Views do not only give us a lot of new possibilities for queries on a Star Schema. It also allows to run the queries with the same or even better performance than with “hand-made” SQL statements on the fact and dimension tables directly.
The only issue I found are for queries that are executed on a Hierarchy directly. This could slow down the performance for large dimensions with many hierarchy levels. But as soon as the Hierarchies are used in combination with an Analytic View, this problem does not occur anymore.
During my tests, I had several long-running queries, and more than once I had to cancel a query or kill the database session. This was usually the case when I forgot to add some restrictions. Of course, a query that selects all levels of all hierarchies and joins it with all facts will run much too long. So, a statement like “SELECT * FROM sh_history_sales_av“ is not a good idea. When using Analytic Views, you can expect a good performance if you use them properly.
Data Warehousing with Oracle 
Reblogged this on Oracle PL/SQL for Developers and commented:
Cool must read
LikeLiked by 2 people