You can greatly improve query performance without changing SQL queries.
Do you feel tired for waiting for your query? Have you tired of enhanced indexes and tuning SQL, but still can't improve query performance? So, have you considered to create a materialized view? With a materialized view, those who have taken a few hours in the past can be completed in a few minutes. The materialization view can include Join and a set (AGGREGATE), which provides a method of storing pre-computed results.
When performing a query, the optimizer determines whether the base table of the access materialization view or the data resident is faster. If the optimizer determines that the query materialization view is a better solution, the optimizer will overwrite the SQL query in a Query Rewrite. In this process, you do not need to modify any SQL or application code, so any application or specific query tool that uses SQL access to the database can benefit from the usualized view. When the number of data required to access is much larger than the result (such as a collection), it is best for the query overwriting, but it can also be used to accelerate expensive coupling or planning.
This article first introduces the type of query overwritten that the optimizer can perform. It then discusses the tool that helps determine the creation of the best primary chemistry, enabling the optimizer to override multiple queries. The materialized view created with these tools can be quickly refreshed when its underlying data changes. If you don't know which one is created, an index or creation is better, the SQL Access Advisor introduced in Oracle Database 10g can help you make a decision by analyzing a given workload.
Query rewrite type
There may be many types of query rewrites; when the definition of the materialization view is completely matched with the query text, the simplest and most significant type of query rewrite will occur. However, when the same physical chemical view can be used for a plurality of queries, the biggest benefit of the query rewest can be implemented. Now, we will illustrate the rules used by the Oracle optimizer to determine if it will use the materialized view to respond.
For the examples in this article, consider considering the Purchases table in a star mode as an fact table (FACT TABLE), which is divided by Time_Key. Dimension Table - Time, Product and Customers - Contains Primary Keys Time_Key, Product_ID, and Cust_ID. There is a foreign key constraint that references each dimension table in the Purchases table.
Consider the materialized view created in Listing 1, the view is calculated on the total number of sales and sales total sales in PRODUCT_ID on the month. Note: For the materialized view used to query overwritten, there must be an Enable Query Rewrite clause. Also, the initialization parameter query_rewrite_enable must be set to True.
Code List 1: Creating a monthly sales material
Create Materialized View Monthly_Sales_mv
Enable Query Rewrite
AS
SELECT T.MONTH, P.PRODUCT_ID, SUM (ps.purchase_price) as sum_of_sales,
Count (ps.purchase_price) as total_sales
From time t, product p, purchases ps
Where t.time_key = ps.time_key and
ps.product_id = p.Product_ID
Group by t.month, p.Product_ID;
Collection calculation
In the examples of this article, we will explain the query of the materialized view and display the execution plan obtained by the Explain Plan. The query required in Listing 2 requires a month and according to the average purchase price. The optimizer can use the materialized view monthly_sales_mv, and use SUM and COUNT collections to calculate the average purchase price. This example illustrates a technology called "set calculation". Code List 2: Get average (AVG) purchase price
SELECT T.MONTH, P.PRODUCT_ID, AVG (ps.purchase_price) AS AVG_SALES
From time t, product p, purchases ps
Where t.time_key = ps.time_key and
ps.product_id = p.Product_ID
Group by t.month, p.Product_ID;
ID Operation Name
______________________________________________
Select Statement
MAT_VIEW REWRITE Access Full Monthly_sales_mv
Joinback
JoinBack technology is very useful because it allows query overwriting when there is no column in the materialization view. The query in Listing 3 requires the total sales of the product category, and there is no Product.category column in the material view. However, the primary key Product_ID column of the product table is located in the materialized view. Therefore, the optimizer can connect the materialized view to the product table to obtain the product category.
Code List 3: Total sales through Joinback
SELECT T.MONTH, P.Category, SUM (ps.purchase_price) as sum_of_sales
From time t, product p, purchases ps
Where t.time_key = ps.time_key and
ps.product_id = p.Product_ID
GROUP BY T.MONTH, P.CATEGORY;
ID Operation Name
_______________________________________________
0 SELECT STATEMENT
1 sort group by
2 Hash Join
3 Table Access Full Product
4 MAT_VIEW REWRITE ACCESS FULL MONTHLY_SALES_MV
Use dimensions to rewrite
In a typical data warehouse designed using dimension modeling skills, there is a famous "hierarchical relationship" in the data. For example, in the time level, "Day" accumulates into "month", "month" has accumulated "year". In the Oracle database, you can create an object called "Diemnsion" using the Create Dimension statement to declare this relationship to the optimizer. The dimension object is a descriptive object, except for its metadata, it does not take up space. The relationship with the Dimension object declaration is said to be credible. Oracle does not verify that this relationship is to be established for your data, it is only assumed that the database administrator has determined that these relationships are correct. Other examples of trusted information are constraints using Novalidate Rely tags and registration as a primary and quotation table.
For the query rewrite with trusted information, including the dimension, the initialization parameter Query_ Rewrite_integrity must be set to trusted, as shown below:
Alter session set query_rewrite_integrity = trusted; for example, it is assumed to have a time dimension, its declaration is as follows:
Create Dimension Time_dim
Level Time_Key Is Time.Time_Key
Level Month is Time.month
Level quarter is time.quarter
Level Year is time.year
Hierarchy Calendar_rollup
Time_Key Child of
Month Child of
Quarter Child of
Year
)
Attribute Time_Key Determines (Day_of_Week, Holiday)
Attribute Month Determines; Month_name
Now, if you request a query for sales of sales in Listing 4, you can still use the monthly_sales_mv materialization view, because the Hierarchy clause in the dimension object tells the Oracle database monthly sales can accumulate adult sales. It utilizes the Joinback tips previously described by the "Month" column in the materialized view "Year" column.
Code List 4: Get Total Sales through Joinback and Hierarchy
SELECT T.YEAR, P.Category, Sum (ps.purchase_price) as sum_of_sales
From time t, product p, purchases ps
Where t.time_key = ps.time_key and
ps.product_id = p.Product_ID
Group by t.year, p.category;
ID Operation Name
_______________________________________________
0 SELECT STATEMENT
1 sort group by
2 Hash Join
3 Hash Join
4 view
5 Sort Unique
6 Table Access Full Time
7 MAT_VIEW REWRITE Access Full Monthly_sales_mv
8 Table Access Full Productu
The Attribute clause of the dimension indicates a one-on-one relationship. For example, you can determine which day starting from time_key to a week. Suppose you want to get the total sales total of each year: You can still use the Monthly_Sales_mv materialized view shown in Listing 5. Note how the WHERE clause of the query has a selection condition that does not appear in the materialized view.
Code Listing 5: Get the total sales through Joinback and Attribute
SELECT T.YEAR, P.Category, Sum (ps.purchase_price) as sum_of_sales
From time t, product p, purchases ps
Where t.time_key = ps.time_key and
ps.product_id = p.Product_ID and
T.MONTH_NAME = 'January'
Group by t.year, p.category;
ID Operation Name
_______________________________________________
0 SELECT STATEMENT
1 sort group by
2 Hash Join
3 Hash Join
4 view
5 Sort Unique
6 Table Access Full Time
7 MAT_VIEW REWRITE Access Full Monthly_sales_mv
8 Table Access Full Productu
If the optimizer does not rewrite a query as scheduled, you can use the dbms_mview .explain_rewrite process to diagnose the problem. This feature appears in the Oracle9i database and later.
Filtered data
So far, all examples we give use the materialized view corresponding to all the data in the purchasing table. The Oracle9i database has the ability to rewrite the query in the case where the material is only one data subset. For example, if you are only interested in sales of sales in 1997 to 2002, you can modify the materialization view as follows:
CREATE MATERIALIZED VIEW FIVE_YR_MONTHLY_SALES_MV
Enable Query Rewrite
AS
SELECT T.MONTH, P.PRODUCT_ID,
SUM (ps.purchase_price) as sum_of_sales,
Count (ps.purchase_price) as total_sales
From time t, product p, purchases ps
Where t.time_key = ps.time_key and
ps.product_id = p.Product_ID and
T. Year Between 1997 and 2002
Group by t.month, p.Product_ID;
This materialization view can be used in response requests from 1997 to 2002 data, for example, inventory 6 queries require 2000 sales.
Code List 6: Query only the material view
SELECT T.MONTH, P.PRODUCT_ID, SUM (ps.purchase_price) AS SUM_OF_SALES
From time t, product p, purchases ps
Where t.time_key = ps.time_key and
ps.product_id = p.Product_ID and
T.Year = 2000
Group by t.month, p.Product_ID;
ID Operation Name
_______________________________________________
Select Statement
1 Hash Join
2 view
3 Sort Unique
4 Table Access Full Time
5 MAT_VIEW REWRITE Access Full Five_YR_MONTHLY_SALES_MV
In the Oracle9i database, if there is no query required in the material view, the query does not use the materialized view. In the Oracle Database 10g, this limit has been relaxed, so the query rewrite can be obtained as possible from the materialized view and utilize the items that are not available in the primary view. As usual, the optimizer considers the cost of rewriting and no change in the case when making this decision to perform this.
For example, the query in Listing 7 requires a monthly sales between 2000 to 2003, which will use the materialized view from 2000 to 2002, and only a delegation table in 2003. Code List 7: Query the materialization view and a dellistic table
SELECT T.MONTH, P.PRODUCT_ID, SUM (ps.purchase_price) AS SUM_OF_SALES
From time t, product p, purchases ps
Where t.time_key = ps.time_key and
ps.product_id = p.Product_ID and
T.Year Between 2000 and 2003
Group by t.month, p.Product_ID;
ID Operation Name
_______________________________________________
0 SELECT STATEMENT
1 sort group by
2 view
3 union-all
4 Hash Join
5 view
6 Sort Unique
7 Table Access Full Time
8 MAT_VIEW REWRITE ACCESS FULL FIVE_YR_MONTHLY_SALES_MV
9 sort group by
10 NESTED LOOPS
11 Hash Join
12 Table Access Full Time
13 Partition Range All
14 Table Access Full Purchases
15 Index Range Scan Product_PK_Index
Inquiry overwriting with a failure of materialized view
You may want to know what happens if the data in a delegate table changes. Do you still use a physical chemical view? The answer is determined in the initialization parameter query_rewrite_ integrity setting. The query_rewrite_integrity parameter has three values:
Stale_Tolerated indicates that the materialized view is still used even if the data in the delegation table has changed. Trusted indicates that the materialized view does not fail when it is not failed. However, inquiry rewriting can use trust relationships, such as those declared by dimension objects or constraints that have not yet taken effect. Enforced (default) indicates that when the material of the primary view guarantees the same result as the use of a designer table. Using this parameter means that the query rewrite will not use the failure of materialized view or trust relationship.
The correct settings are determined by the application's data needs. Rewriting with the query of the failure material can produce different results when there is no use query. However, if detail data is used, performance may deteriorate due to a large amount of data to be processed in response to query. In a data warehouse, typically use the Trusted full level, because this can guarantee that you only use the materialized views of the latest data; however, the relationship that is declared as correct (trusted) can also be used to rewrite. In most data warehouses, these relationships have been verified in the extraction, conversion, and loading (ETL) procedures, so no verification is required.
Partition change tracking
In the Oracle9i database, Oracle introduces partition change tracking (PCT, Partition Change TRACKING). Using this feature, the Oracle9i database can track which part of the materialized view corresponds to the updated part of the partition base table. Therefore, if the query does not need to update the table, then the materialization view is still available. In order to track changes in a base table in the materialized view, the table must be partitioned, and the materialization view (in the SELECT list) must include a subcircle key or a special function of a levery: dbms_mview.pmarker. This function generates a unique identifier for each partition in the designer table.
For example, the purchasing table is partitioned by Time_Key. The materialized view created in Listing 8 is almost identical to the monthly_sales_mv materialized view used in the previously used, just the materialized view contains an additional dbms_mview.pmarker function on the purchase table. By including this function, the materialization view allows the PCT when the procurement table is updated. Note: The materialization view does not need to be partitioned. Code List 8: Propertyized View with DBMS_MVIEW.PMARKER Function
CREATE MATERIALIZED View Monthly_Sales_PCT_MV
Enable Query Rewrite
AS
SELECT DBMS_MVIEW.PMARKER (PS.Rowid) PM, T.MOWID, P.PRODUCT_ID,
SUM (ps.purchase_price) as sum_of_sales,
Count (ps.purchase_price) as total_sales
From time t, product p, purchases ps
Where t.time_key = ps.time_key and
ps.product_id = p.Product_ID
Group by dbms_mview.pmarker (ps.rowid), t.month, p.Product_ID;
Now, we have added a new partition to the purchase table, and a user issued a query requesting data in March 2002, as shown in Listing 9. In this query, we don't care about the data that has been updated in April 2003, so it will be rewritten by the materialization map, even if the materialization view has failed.
Code List 9: Inquiry Rewriting
SELECT T.MONTH, P.PRODUCT_ID, SUM (ps.purchase_price)
From time t, product p, purchases ps
Where t.time_key = ps.time_key and
ps.product_id = p.Product_ID and
ps.time_key> = to_date ('01 -03-2003 ',' DD-mm-yyyy ') and
ps.time_key Group by t.month, p.Product_ID; ID Operation Name _______________________________________________ 0 SELECT STATEMENT 1 sort group by 2 MAT_VIEW REWRITE ACCESS FULL MONTHLY_SALES_PCT_MV If the query requires data from January to April, in Orcale9i, it will not be rewritten for the use of the materialized view. However, in Oracle Database 10g, the combination of Monthly_Sales_ PCT_MV and the description table can be used to overwrite. Use multiple materialized views to rewrite As mentioned earlier, in the Oracle10G database, the query rewrite has been enhanced, so it can use some of the materialized views of the data and the remaining data of the designer to respond to the query. In fact, the query rewrite can use two or more materialized views. For example, suppose you maintain a separate materialized view for every 5 years of data value: Monthly_Sales_1990-1994, Monthly_Sales_1995_to_2000, Monthly_Sales_2001_to_2005, and more. Then, for the query inventory 10 from 1993 to 2003, the query rewrite can utilize all three materialization views. Code list 10 SELECT T.MONTH, P.PRODUCT_ID, SUM (ps.purchase_price) as sum_of_sales, From time t, product p, purchases ps Where t.time_key = ps.time_key and ps.product_id = p.Product_ID and T.Year Between 1993 and 2003 Group by t.month, p.Product_ID; ID Operation Name -------------------------------------------------- ------- 0 SELECT STATEMENT 1 sort group by 2 view 3 union-all 4 MAT_VIEW REWRITE ACCESS FULL MONTHLY_SALES_2001_TO_2005 5 MAT_VIEW REWRITE ACCESS FULL MONTHLY_SALES_1995_TO_2000 6 MAT_VIEW REWRITE ACCESS FULL MONTHLY_SALES_1990_TO_1994 Code List 11 SELECT T.MONTH, P.PRODUCT_ID, SUM (ps.purchase_price) AS SUM_OF_SALES From time t, product p, purchases ps Where t.time_key = ps.time_key and ps.product_id = p.Product_ID and T.Year Between 1989 and 1999 Group by t.month, p.Product_ID; ID Operation Name -------------------------------------------------- ------- 1 Select Statement 1 sort group by 2 view 3 union-all 4 MAT_VIEW REWRITE Access Full Monthly_sales_1995_to_2000 5 MAT_VIEW REWRITE ACCESS FULL MONTHLY_SALES_1990_TO_1994 6 sort group by 7 nested loops 8 NESTED LOOPS 9 Table Access Full Time10 Partition Range Iterator 11 Table Access Full Purchases 12 INDEX RANGE SCAN PRODUCT_PK_INDEX The query in Listing 11 requires data from 1989 to 1999, so the query rewrite can use the materialized view monthly_sales_1990_to_1994 and the monthly_sales_1995_to_2000, and obtain 1989 data by the designer. This process will essentially be faster than getting all data from a description table. Oracle10G database has several other improvements in query overwriting. In these improvements, it is worth noting that the Oracle10G database can better support the set operator (Union, UnionAll, etc.) enhanced the enhancement of multiple table instances, and provides List and Range-List in partition change tracking. Support for partition types. tool You may read this article while reading itself: "Well, I think I understand what you mean, but if some tools can do all these work for me?" The answer is certain. In fact, these tools are quite more. The Oracle9i database references Explain_MView and Explain_rewRite. Application Programming Interface (API) EXPLAIN_MVIEW adopts a materialized view definition, and it is recommended to use what type of partition change tracking operation, whether it is possible to quickly refresh, and how to complete what type of query rewrite. When a query is not overwriting, the API Explain_rewrite will tell you why SQL query does not use queries to write. In both cases, the toolkit will tell you the question - for example, you cannot connect on a specific column, but both packs will not tell you if this problem is resolved. At this time, you can use two new tools contained in the Oracle10G database --Tune_MView and SQL Access Advisor to help you solve this problem. The Tune_MView API will tell you how to write a materialized view so that it can quickly refresh and you can use as many advanced queries described in this article. The use of the Tune_MView API is very simple: just give your materialized view statement to it, it will determine the best form of the materialization view. However, if you see your original materialized view has been converted to multiple new versions, don't feel strange. Let's take a look at how tune_mview can convert your materialization view. Suppose we have a simple query and passed it to the explain_mview, as shown in Listing 12, determining whether the materialization view can be quickly refreshed in the current form. Code list 12 Begin DBMS_MVIEW.EXPLAIN_MVIEW ( 'CREATE MATERIALIZED View Customer_mv Build Immediate Refresh Fast Enable Query Rewrite AS Select C.Customer_ID, C.Town, Count (DistUct_ID)) AS DIST_PROMO_CNT From Purchases PS, Customer C Where ps.customer_id = c.customer_id Group by c.customer_id, c.town ',' id1 '); END; / - See if refresh Fast Capability Is Allowed (Y) or Not (n) Select Capability_name, Possible From mv_capabilities_table WHERE CAPABILITY_NAME = 'refresh_fast' and statement_id = 'id1'; Capability_name P --------------------------------- Refresh_fast n Now let's use the same query and pass it to tune_mview, as shown in the following code: Variable task_name varchar2 (2000); Begin DBMS_ADVISOR.TUNE_MVIEW (: task_name, 'CREATE MATERIALIZED View Customer_mv Build Immediate Refresh Fast Enable Query Rewrite AS Select C.Customer_ID, C.Town, Count (DistUct_ID)) AS DIST_PROMO_CNT From Purchases PS, Customer C Where ps.customer_id = c.customer_id Group by c.customer_id, c.town '); END; / Code List 13 Select Statement from user_tune_mview where task_name =: task_name; Create Materialized View Easydw.customer_mv Build Immediate Refresh Fast with RowId Enable Query Rewrite As select easydw.purchases.product_id c1, easydw.customer.town C2, Easydw.customer.customer_id C3, count (*) m1 From easydw.purchases, easydw.customer Where easydw.customer.customer_id = easydw.purchases.customer_id Group by easydw.purchases.product_id, easydw.customer.town, Easydw.customer.customer_id; Directory View USER_TUNE_MVIEW will display the resulting material, as shown in Listing 13. Although it looks like It is a bit different from our original materialization view, but it can still be used in places where the original materialization view can be used. The materialization view rewrites any query, in addition, it can be quickly refreshed. You can also generate a script to perform these suggestions, and you may want to do only modification is to change the name of the primary and chemical view, and where the specified materialization view should be placed where storage statements and table space. Now, we already have a materialized view, but if we don't know what materialized view created, what should I do? At this time, SQL Access Advisor can help you because it browses your system and it thinks the needs of the index and materialization view. These recommendations are based on actual workloads or assumptions made by your model. The best results will be obtained when the actual workload of the SQL statement is provided. This workload can be obtained by the current content of SQL cache, SQL tuning set (TUNING SET), ORACLE9I SUMMARY ADVOSOR workload, or workload table provided by users (including the SQL statement you have defined). SQL Access Advisor can be used both by command line API or through a part of Enterprise Manager - SQL Access Advisor wizard. Using this wizard, only three steps are required before displaying these recommendations. Let's take a look at how to use SQL Access Advisor using SQL Access Advisor: First, create a task containing all information about this tuning process. This task will then use workload information to generate tuning recommendations as part of the task. Therefore, the entire process is completely independent, and each task is allowed to be slightly different so that people can see the effects of modifying the configuration. In the example shown in Listing 14, the workload is defined by manually defining the SQL statement. Code list 14 Declare Task_desc varcha2 (100); Task_id number; Task_name varcha2 (30); Workload_name varcha2 (30); Begin Task_name: = 'Task_mag'; DBMS_ADVISOR.CREATE_TASK (dbms_advisor.sqlaccess_advisor, Task_id, task_name, 'my advisor task', dbms_advisor.sqlaccess_warehouse; DBMS_ADVISOR.SET_TASK_PARAMETER ('task_mag', 'evAlution_only', 'false'); DBMS_ADVISOR.SET_TASK_PARAMETER ('Task_mag', 'Execution_Type', 'Full'); - CREATE The WORKLOAD Workload_name: = 'WORKLOAD_MV'; DBMS_ADVISOR.CREATE_SQLWKLD (Workload_name, 'MV Workload', NULL); - Now Link The Two together DBMS_ADVISOR.ADD_SQLWKLD_REF (task_name, workload_name); - Add A SQL Statement DBMS_ADVISOR.ADD_SQLWKLD_STATEMENT (Workload_name, 'App', 'Action ", NULL, 15, 3000, 423, 507, 60, 704, 3, '16-FeB-2002 ', 80, 'Easydw', 'Select C.Customer_ID, C.Town, Count (DistUct_ID)) AS DIST_PROMO_CNT From Purchases PS, Customer C Where ps.customer_id = c.customer_id Group by c.customer_id, c.town '); END; / Once the workload and tasks are defined, the recommendations shown below can be generated. The recommendation uses Execute_Task and specifies the name of the created task - Task_mag: Execute DBMS_ADvisor.execute_task ('task_mag'); Depending on the complexity of the workload, it can be generated from a few seconds to a few minutes. Therefore, although this process can be run interactively, you may want to consider submitting a task, which is the work to be completed in the Enterprise Manager. You can quickly check if there is a suggestion for Task_name by quering user_advisor_recomendations. We will see a suggestion that has been proposed in this operation. Select 'NO of Recommendations:', count (*) From user_advisor_recomendations r WHERE task_name = 'task_mag'; 'NOOFRECOMMENDATIONS:' count (*) -------------------------------- NO of Recommendations: 1 Single suggestions can cause multiple operations. For this example, SQL Access Advisor recommends creating a materialized view log, a CREATE MATERIALIZED VIEW, and a call to analyze the materialization view (subject to the layout, not given here). Although you can check these operations, you can find these operations, but the simplest way to see them is to generate a script, as shown below: EXECUTE DBMS_ADVISOR.CREATE_FILE (dbms_advisor.get_task_script ('task_mag'), 'Advisor_Results', 'MAG_EXAMPLE.SQL'); In Listing 15, you can see an excerpt of the script showing the materialized view established for our query. Code List 15 Rem Access Advisor REM REM Username: easydw Rem task: my_task Rem Execution Date: 20/05/2003 14:36 REM ... Create Materialized View "Easydw". "MV $$ _ 002D0000" Refresh Fast with RowId Enable Query Rewrite As select easydw.purchases.product_id C1, Easydw.customer.town C2, Easydw.customer.customer_id C3, count (*) m1 From easydw.purchases, easydw.customer Where easydw.customer.customer_id = easydw.purchases.customer_id Group by easydw.purchases.product_id, easydw.customer.town, Easydw.customer.customer_id; ... in conclusion By using query rewriting, you can use several materialized views to significantly improve the performance of many queries, thereby reducing the disk space occupied by maintaining the materialized view and the foundation details data synchronization and refresh time.
