Three optimizations in Oracle data yard: partitioning, dimensions, and materialized views

turn Xiewmang Sina blog

This paper mainly introduces three kinds of optimization in Oracle Data yard: Optimization of partition, dimension optimization and materialized view, and gives detailed optimization code, hoping to help you.

When we do the database project, the Data yard optimization, about the principle of only two: one is the data block storage, convenient data dump and discipline, and the other is the processing, the speed of data supply. This paper mainly introduces three kinds of optimization in Oracle Data yard: optimization of partition , optimization of dimension and materialized view , two approximate principle above base, and the idea of data yard to list the optimization measures of database:
1. Partition
In the data yard, the fact table, the index table, and the dimension table are divided into three different table spaces (at the time of arrangement, good is arranged on different disks). The cause of such a child even if it is convenient for concurrent monopoly, in fact, the data yard and easy database between the strict boundaries, important or arranged, of course, Oracle itself will be in the data Yard "fact table" to generate a "knowledge base" and other monopolies, to ensure faster data supply efficiency, In fact, the use of job and surface process to mobilize the storage process ended. Based on this idea, the index in this project database will be maintained with the fact table, which is, of course, not zoned (). The kindliness partition refers to the following content.
Even if the partition is partition/subpartition, for the fact table itself, the partition is mounted on a different table space in months. An informative example is as follows:

1. create table t_lognode_record  
2. ( 
4. callin_hh number (2)  not null,  
5. callin_dd  Number (2)  not null,  
6. callin_month number (2)  not null,  
7. callin_year number (4)  not null,  
8. month_mod number (1)  not  Null  
10. partition by list  (month_mod)  
11. ( 
12. partition p0 values  (0)  tablespace ts0,  
13. partition p1 values  (1)  tablespace ts1,  
14. partition p2 values  (DEFAULT)  tablespace ts2  

In the code above, the Month_mod field is used as the partition specification, the T_lognode_record is divided into three different tablespace (TS0,TS1,TS2), here is a technique, Month_mod = Month mod3, So that the child can not be manually maintained which month to divide into which partition. Of course, it is easy to use the month as a partition thereafter, for our current database is still far from enough, because even if the one months of data is still very ambitious, then we must do subpartition, the example is:

2. CREATE TABLE T_lognode_record
4. (
6. ......
8. CALLIN_DD Number (2) is not NULL,
10. Month_mod Number (1) NOT NULL
12. )
14. PARTITION by RANGE (Month_mod)
16. Subpartition by LIST (CALLIN_DD)
18. Subpartition TEMPLATE
20. (
22. Subpartition SUBP1 VALUES (1),
24. Subpartition SUBP2 VALUES (2),
26. Subpartition SUBP3 VALUES (3)
28. )
30. (
32. PARTITION P0 VALUES less than (1),
34. PARTITION P1 VALUES less than (2),
36. PARTITION P2 VALUES less than (3)
38. );

There are three partitioning measures: range, List (partition), hash (hash partition), the hash partition that must be interpreted, and Oracle calls the built-in hash function to end the balanced distribution of the data, ensuring that the amount of data within the partition is identical, so that only the number of partitions must be established.
for composite partitions. Not all partition styles can be nested, Oracle only supports the scope list partition, the range hash partition, especially the range partition can support multiple fields range by (field1,field2). For the zoning measures that are available for this project, there are only two examples given, and for the benefits of partitioning, see Google, ^_^. Examples of SQL that give a monopoly are as follows:

1. SELECT * from T_lognode_record partition (P0)
2. Update T_lognode_record partition (P0) T set ...

The INSERT statement has not been affected. Of course, the above-mentioned monopoly can be like the SQL statement, do not specify the partition can also be held, but in the query when possible to determine the partition and sub-partition subpartition, so that the speed of the query to grow a hundredfold. Be able to exclude the specified partition drop may truncate, export and import partition data, but when the division of the exclusion of monopoly, will make the overall situation index effect, must be re-hing occurred.
2. Dimension of
Dimension this is the advanced function of Oracle, in Plsql Developer no measure to find this object (but can enforce the statement to generate the object, but can not hold the later maintenance), only in the Toad have its figure. It is an elucidation of the size relationship between data dimension fields. The generosity of the pacesetter is the following month and day.

2. CREATE TABLE Dim_time
4. (
6. D_year VARCHAR2 (4) NOT NULL,
8. D_quater VARCHAR2 (2) NOT NULL,
10. D_month VARCHAR2 (2) NOT NULL,
12. D_day DATE NOT NULL
14. )
16. CREATE DIMENSION Dim_time
18. Level year is (Dim_time. D_year)
20. Level quater is (dim_time. D_quater)
22. Level of MONTH is (dim_time. D_month)
24. Level-Is (dim_time. D_day)
26. HIERARCHY Y_q_m_d
28. (
30. Day Child of
32. MONTH Child of
34. quater Child of the year
36. )
38. HIERARCHY Y_m_d
40. (
42. Day Child of
44. MONTH Child of Year
46. )

The

has two inheritance contacts Y_q_m_d and Y_m_d. When Dim_time's own census, as well as joint queries with other tables and then aggregated, the monthly aggregated data is actively based on the daily aggregated data, so that the speed of data aggregation is greatly improved. The
can experience multiple dimension tables in a single table, and can have multiple inheritance links in one dimension. The dimension has greatly improved the speed of data summarization, which makes us a powerful tool for the census analysis of the target data.
3.  materialized view (snapshot)
materialized views materialized view. The cost-effective is that plsql developer and Toad have the same object at the same time (Toad might call this a snapshot snapshots), but the boring thing happened, plsql developer on materialized View in the Toad can not distinguish, it seems to have two of the ability to use enough. The
materialized view, words too literally with properties of the view, takes place on the basis of a practical table, revealing the data in the actual table in general, and even creating measures that are almost identical. However, it becomes materialized, which means that it is a physical object, which, from a certain meaning, is more like a table, able to query the data in it directly like a table (even if it is visible in the table list), but it is not only the table, because it can ripple the data of the original table, and Oracle can actively REDIRECT query summary statements against the original table directly to the materialized view to advance the query speed.

1. create materialized view mv_log_record  
2. REFRESH  force  
3. on demand  
4. enable query rewrite  
Li class= "alt" >as  
5. select tcmy8.com. node_id, count (*)  as call_times  
6. from t_lognode_record t  
7. Group by t. t_ lognode_record 

The above SQL statement has a materialized view on the T_lognode_record table. Refresh parameter force indicates forced refresh, but also can choose the parameters of fast and complete two kinds, fast is the fastest refresh measures, indicating an incremental refresh (to do incremental refresh, it is necessary to include a single character in the materialized view of the marked characters, such as primary key and ROWID, etc., Of course, even if there is no need to be able to quickly refresh, because our project has not affected the materialized view to be refreshed, there is no more talk about it; Complete represents a comprehensive data refresh to regenerate materialized views; Force is a choice refresh, The choice is force or complete according to the actual situation.
On demand indicates the condition that the refresh raises. The materialized view above only refreshes the data when the user launches the query, and the data in the original table is immediately taken to the materialized view table. There is another measure that, even on COMMIT, refreshes the corresponding materialized view when the user monopolies the original data, which is in line with the high-timeliness requirements of the project. In case of the need to do a timed refresh, will use the following measures lr.lrheicha.com:
REFRESH FAST START with Sysdate
NEXT sysdate + 1/48.
Materialized views are able to span multiple tables, and can also be directly queried for materialized attempts, such as you can:
Select * from Mv_log_record.

Induction
In the above three kinds of measures, belong to the database function optimization of important means, of course, there are many can do the details of optimization, such as try not to check the fields you want to be approved as empty, as far as possible to suppress the connection of joint query to number type and so on.
By the way, if you do a dimension table, do a materialized attempt, you go to query the original table when the speed will surely add? The answer is "no", an important thing you have to do, even if you tell Oracle that it can actively optimize the query, the following statements:

1. ALTER SESSION SET query_rewrite_enabled=TRUE;
2. ALTER SESSION SET query_rewrite_integrity=TRUSTED;

About three kinds of optimization knowledge of Oracle Data yard is introduced here, hope to bring you some harvest, thank you!

Three optimizations in Oracle data yard: partitioning, dimensions, and materialized views