Three Optimizations in Oracle Data Store: partition, dimension, and materialized view

When we are working on database projects, there are only two principles to optimize the data store: one is Data Storage in blocks, which facilitates data dumping and discipline; the other is processing, the speed at which data is provided. This article mainly introduces three Optimizations in Oracle Data freight yard:PartitionOptimization,DimensionOptimization andMaterialized ViewBased on the above two principles, with the help of the concept of data store, list the database optimization measures:
1. Partition
In the data store, fact tables, index tables, and dimension tables are divided into three different tablespaces, which are arranged on different disks ). Even if it is easy to monopolize concurrency, there is no strict boundary between the data store and the idle database. It is important to make arrangements, of course, oracle itself will generate "Knowledge Base" and other monopoly for "fact tables" in the data store to ensure faster data supply efficiency. In fact, it can use job and surface processes to mobilize the storage process. Based on this idea, indexes in the database of this project will be maintained separately from fact tables, which of course cannot be called partitions ). The main partition refers to the following content.
Even if the partition is partition/subpartition, for the fact table itself, the partition is mounted to different tablespaces in the unit of month. Detailed examples are as follows:

 
 

  
  
create table T_LOGNODE_RECORD  
  
  
(  
  
  
……  
  
  
CALLIN_HH NUMBER(2) not null,  
  
  
CALLIN_DD NUMBER(2) not null,  
  
  
CALLIN_MONTH NUMBER(2) not null,  
  
  
CALLIN_YEAR NUMBER(4) not null,  
  
  
MONTH_MOD NUMBER(1) not null  
  
  
)  
  
  
PARTITION BY LIST (MONTH_MOD)  
  
  
(  
  
  
PARTITION P0 VALUES (0) TABLESPACE TS0,  
  
  
PARTITION P1 VALUES (1) TABLESPACE TS1,  
  
  
PARTITION P2 VALUES (DEFAULT) TABLESPACE TS2  
  
  
); 
 
 

In the above Code, the MONTH_MOD field is used as the partition specification and T_LOGNODE_RECORD is assigned to three different tablespaces TS0, TS1, and TS2. Here is a tip: MONTH_MOD = month mod3, in this way, the sub-account does not need to manually maintain Which partition should be added by month. Of course, it is easy to use a month as a partition. Later, it is far from enough for our current database, because even if the data for a month is still very magnificent, We must SUBPARTITION, example:

 
 

  
  
create table T_LOGNODE_RECORD  
  
  
(  
  
  
……  
  
  
CALLIN_DD NUMBER(2) not null,  
  
  
MONTH_MOD NUMBER(1) not null  
  
  
)  
  
  
PARTITION BY RANGE(MONTH_MOD)  
  
  
SUBPARTITION BY LIST(CALLIN_DD)  
  
  
SUBPARTITION TEMPLATE  
  
  
(  
  
  
SUBPARTITION SUBP1 values (1),  
  
  
SUBPARTITION SUBP2 values (2),  
  
  
SUBPARTITION SUBP3 values (3)  
  
  
)  
  
  
(  
  
  
PARTITION P0 VALUES less than (1),  
  
  
PARTITION P1 VALUES less than (2),  
  
  
PARTITION P2 VALUES less than (3)  
  
  
); 
 
 

There are three partition measures: RANGE), LIST partition), and HASH partition. HASH partition must be interpreted. It is a balanced distribution of data when oracle calls the built-in hash function, ensure that the data volume inside the partition is the same, so you only need to specify the number of partitions.
For composite partitions. Not all partition styles can be nested. oracle only supports range list partitions and range hash partitions. In particular, range partitions support multiple fields range by (field1, field2 ). Only the two partition measures used in this project are provided in the example. For the benefits of the partition, check google, ^ _ ^. The following is an example of a monopolized SQL statement:

 
 

  
  
select * from T_LOGNODE_RECORD partition(P0)  
  
  
update T_LOGNODE_RECORD partition(P0) t set…… 
 
 

The insert statement is not affected. Of course, the monopoly above can be held like an idle SQL statement without specifying a partition, but please try to determine the partition and subpartition during query, this will increase the query speed by hundreds of times. The drop of a specified partition may be truncate, EXPORT, and IMPORT data in the partition. However, when the partition is removed from the monopoly, the index of the overall situation will be effective and must happen again.
2. Dimension
Dimension is an advanced feature of oracle. In PLSQL, the Developer has not been able to find this object, but can execute statements to generate this object. However, it cannot be maintained later ), it can only appear in Toad. It clarifies the size relationship between data dimension fields. The generosity of the pacesetter is the following year, month, and day.

 
 

  
  
create table DIM_TIME  
  
  
(  
  
  
D_YEAR VARCHAR2(4) not null,  
  
  
D_QUATER VARCHAR2(2) not null,  
  
  
D_MONTH VARCHAR2(2) not null,  
  
  
D_DAY DATE not null  
  
  
)  
  
  
CREATE DIMENSION DIM_TIME  
  
  
LEVEL YEAR IS (DIM_TIME.D_YEAR)  
  
  
LEVEL QUATER IS (DIM_TIME.D_QUATER)  
  
  
LEVEL MONTH IS (DIM_TIME.D_MONTH)  
  
  
LEVEL DAY IS (DIM_TIME.D_DAY)  
  
  
HIERARCHY Y_Q_M_D  
  
  
(  
  
  
DAY CHILD OF  
  
  
MONTH CHILD OF  
  
  
QUATER CHILD OF YEAR  
  
  
)  
  
  
HIERARCHY Y_M_D  
  
  
(  
  
  
DAY CHILD OF  
  
  
MONTH CHILD OF YEAR  
  
  
) 
 
 

Two inheritance links Y_Q_M_D and Y_M_D occur. When the DIM_TIME census is performed in conjunction with other tables and then summarized, the monthly summary data is actively based on the total daily summary data, which greatly increases the Data Summary speed.
You can see that a table has multiple dimension tables, and one dimension can have multiple inheritance links. Dimensions greatly increase the speed of data aggregation, making it a powerful tool for census and analysis of this target data.
3. materialized view snapshot)
Materialized view. The good news is that PLSQL Developer and Toad both have the existence of this object. Toad may call this a snapshot snapshots). However, this is annoying, the materialized view written on PLSQL Developer cannot be distinguished in Toad. It seems that both of them are sufficient.
Materialized views have the attributes of views. They are based on real tables, which reveal data in real tables in general, and even create almost the same way. However, if it becomes materialized, it means that it is an object. In a sense, it is more like a table, you can directly query the data in a table, even in the table list). However, it is not only a table, because it can affect the data of the original table, in addition, oracle can actively redirect the query summary statements for the original table to the materialized view to promote the query speed.

 
 

  
  
CREATE MATERIALIZED VIEW MV_LOG_RECORD  
  
  
REFRESH FORCE  
  
  
ON DEMAND  
  
  
ENABLE QUERY REWRITE  
  
  
AS  
  
  
SELECT tcmy8.com. NODE_ID, Count(*) as Call_Times  
  
  
from T_LOGNODE_RECORD t  
  
  
GROUP BY t. T_LOGNODE_RECORD 
 
 

The preceding SQL statement has a materialized view on the T_LOGNODE_RECORD table. The REFRESH parameter FORCE indicates that the REFRESH is forced to be refreshed. You can also choose between fast and complete parameters. fast is the fastest REFRESH measure, indicating that incremental REFRESH is required for incremental REFRESH, certainly, the materialized view contains single-character characters, such as the primary key and rowid. Of course, even if it includes, it does not need to be refreshed quickly, because the materialized views to be refreshed have not been affected in our project, we won't talk about them here); complete indicates a comprehensive data refresh and a new materialized view; force indicates an optional refresh, choose force or complete based on the actual situation.
On demand indicates the condition caused by refresh. The materialized view above refreshes the data only when the user initiates a query request. The data in the original table is immediately retrieved from the Materialized View table. There is also a way to refresh the materialized view when the user monopolizes the raw data, even if on commit, which meets the requirements for timeliness. In case of regular refresh, the following measures will be used: lr.lrheicha.com:
REFRESH FAST START WITH SYSDATE
Next sysdate + 1/48.
Materialized views can span multiple tables and directly query materialized views. For example, you can:
Select * from MV_LOG_RECORD.

Induction
Among the above three measures, they are an important means of optimizing database functions. Of course, there are still many optimizations that can be done in detail. For example, try not to leave blank approval on the fields you want to query, try to limit the Contact Type of the joint query to number.
By the way, if a dimension table is created and materialized, will the speed increase when you query the original table? The answer is "no". Even if you tell oracle that it can actively optimize the query, the statement is as follows:

 
 

  
  
ALTER SESSION SET QUERY_REWRITE_ENABLED=TRUE;  
  
  
ALTER SESSION SET QUERY_REWRITE_INTEGRITY=TRUSTED; 
 
 

Here is an introduction to the three Optimizations in the Oracle Data Store. I hope this will bring you some benefits. Thank you!