Three Optimizations in Oracle Data Store

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: optimization of partition, dimension, and materialized view. Based on the above two principles, with the help of the concept of data freight yard, list database optimization measures:

1. Partition

In the data store, fact tables, index tables, and dimension tables are divided into three different tablespaces (when arranged, it is well 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. Of course, this is not a partition (). 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 divided into three different tablespaces (TS0, TS1, 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)
);
Partition measures include RANGE, LIST, HASH, and HASH partitions, it is the balance distribution of data when oracle calls the built-in hash function to ensure that the data volume inside the partition is the same. Therefore, 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, or IMPORT data in the partition. However, when a partition is held to remove the monopoly, the index of the overall situation will be effective and must happen again.

2. Dimension

Dimension is an advanced oracle function. In PLSQL Developer, this object has never been found (however, it can be generated by executing statements, but 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
MONTH CHILD
QUATER CHILD OF YEAR
)
HIERARCHY Y_M_D
(
DAY CHILD
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 (or even view it in the table list), but 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 incremental REFRESH (to achieve 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.

Summarized in the above three measures, it is an important means of database function optimization, of course, there are many optimizations that can be done in detail, for example, try not to approve the blank above the field 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!