Oracle Data Warehouse query optimization technology (1)

I. Runtime Environment

SQL> select * from v $ version; BANNER ---------------------------------------------------------------- Oracle Database 10g Enterprise Edition Release 10.2.0.1.0-Prod PL/SQL Release 10.2.0.1.0-Production CORE 10.2.0.1.0 Production TNS for 32-bit Windows: Version 10.2.0.1.0-Production NLSRTL Version 10.2.0.1.0-Production SQL> show parameter query NAME TYPE VALUE ----------------------------------------------------------------------------- Query_rewrite_enabled string TRUE Query_rewrite_integrity string TRUSTED SQL> conn/as sysdba Connected. SQL> create user ning identified by ning; The user has been created. SQL> grant dba to ning; Authorization successful. SQL> conn ning/ning Connected.

This document uses the sample data of a simple star model and only contains one fact table fact_sales and one time dimension table time_dim. For the specific generation script, see the appendix.

Ii. Materialized View

Generally, you can create a summary in a data warehouse to improve performance. Here the abstract refers to calculating some join and aggregation in advance and saving the results, you can use the saved summary information to generate a report during subsequent queries. In oracle, you can use the materialized view (materialized view) to create a summary in the data warehouse. Combined with the query rewrite function of the oracle optimizer, you can use materialized views to improve query performance without rewriting applications. Obviously, materialized views require a refresh mechanism to ensure data synchronization between the base table and the base table. Oracle provides two refresh Methods: Incremental refresh fast refresh and complete refresh ). The incremental refresh method must meet a series of conditions. For specific restrictions, see Metalink: Doc ID: Note: 222843.1). For simplicity, the materialized view in this example adopts the full refresh method.

If we want to obtain the total sales volume of each month, we can perform the following query:

SQL> set autot trace exp SQL> select t. t_month, sum (f. amount1), sum (f. amount2) 2 from time_dim t, fact_sales f 3 where t. time_id = f. time_id 4 group by t. t_month; 33 rows have been selected. Execution Plan ---------------------------------------------------------- Plan hash value: 53462861 ---------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (% CPU) | Time | ---------------------------------------------------------------------------------- | 0 | select statement | 1000 | 61000 | 9 (23) | 00:00:01 | | 1 | hash group by | 1000 | 61000 | 9 (23) | 00:00:01 | | * 2 | hash join | 1000 | 61000 | 8 (13) | 00:00:01 | | 3 | table access full | TIME_DIM | 1000 | 22000 | 4 (0) | 00:00:01 | | 4 | table access full | FACT_SALES | 1000 | 39000 | 3 (0) | 00:00:01 | ----------------------------------------------------------------------------------

Create a materialized view of monthly statistics:

SQL> create materialized view mv_month 2 refresh complete 3 enable query rewrite 4 as 5 select t.t_month, sum(f.amount1),sum(f.amount2) 6 from time_dim t,fact_sales f 7 where t.time_id=f.time_id 8 group by t.t_month;

The materialized view has been created.

Execute the same query again and find that the execution plan has changed. The optimizer automatically uses the materialized view just created to replace the query of the two base tables:

SQL> select t. t_month, sum (f. amount1), sum (f. amount2) 2 from time_dim t, fact_sales f 3 where t. time_id = f. time_id 4 group by t. t_month; 33 rows have been selected. Execution Plan ---------------------------------------------------------- Plan hash value: 3083828679 ---------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (% CPU) | Time | ---------------------------------------------------------------------------- | 0 | select statement | 33 | 1155 | 3 (0) | 00:00:01 | | 1 | MAT_VIEW rewrite access full | MV_MONTH | 33 | 1155 | 3 (0) | 00:00:01 | ----------------------------------------------------------------------------