With the development of information industry, some tens of GB and hundreds of GB data tables often appear in enterprise database applications. The design, maintenance and backup of these large data tables are the key and difficult points in database management. This paper discusses the management of large data table from the aspects of design, maintenance and backup.
design
Timeliness of large table
Generally speaking, the amount of data in a large data table is proportional to time. The longer the time, the greater the amount of data. In the design stage, the timeliness of these large tables should be considered first.
Usually in a certain time interval, the frequency of data access is relatively large, and the frequency of data access is extremely small. This time interval varies according to different application types, usually several months. Data beyond this time interval can be considered as historical data. In enterprise applications, not all data needs to be stored in the production database. For these historical data, we can consider storing them offline or in another database, such as a data warehouse.
The timeliness of a large table can be achieved by adding a timestamp column to the table.
Use partition table
Oracle later provided the function of partition table, which can physically and logically divide the data of a table into small areas. Oracle supports very large partitioned tables, and an object can allow multiple partitions. For large tables, using partitioned tables is the first choice, which can improve the performance of table maintenance, backup, recovery and query.
There are two ways to partition a partitioned table.
Scope division
Hash partition
Composite partition
List segmentation
For large time-sensitive tables, we can use a range partition table that is partitioned by time, such as a partition table that is partitioned by day.
Create a table test (
Data time? Dating? Not empty
P number? empty
P number? empty
P number? empty
P number? empty
P number? empty
P number? empty
P number? empty
P number? empty
Use the constraint PK_TEST primary key (datatime p p) of the index local tablespace USERINDEX.
)
Partition by range (data time)
(The partition test value is less than (deadline (year, month and day))
(The partition test value is less than (deadline (year, month and day))
……
);
For tables that still can't meet the performance requirements of time partition, sub-partition can be used to further refine the table according to the application requirements.
In application design, we should make full use of the characteristics of partitioned tables to access large tables, completely avoid full table access and narrow access scope, and try to use partitioned columns in query conditions.
maintain
The maintenance of large tables is complicated, such as index maintenance, storage space maintenance and historical data cleaning. Using partitioned tables can simplify the maintenance of large tables, but if there are many tables, manually creating and deleting partitions is also very complicated and error-prone.
This chapter takes partitioned tables partitioned by days as an example to discuss the automatic maintenance of large tables.
Naming rules of partitioned tables
The naming of partition table should be based on certain rules, so as to realize automatic maintenance. In this example, the partition table partition by day is named TABLENAME_YYMMDD. For example, the year, month and day partition of the test table is named TEST _.
Maintain dictionary
Create a maintenance dictionary table in the database to store the information of the partition table that needs automatic maintenance, including the table name, the type of schema table, the retention time of data in the database and so on.
Table name: H_RETENTION
Column type is empty? describe
Tablename Varchar () is not an empty table name.
Schemaname Varchar () is not an empty schema.
Typeid Varchar () Not null table type partition is normal …
Retention Number() Not null The number of days to save this table.
Automatically create partitions
For the partitioned table divided by time, if the new data partition cannot be created in time, it will lead to the serious consequence that the data cannot be inserted into the partitioned table, and the database will generate the error message ora: inserted partition key is not mapped to any partition.
Partitions can be created manually or by system task scheduling according to the maintenance dictionary, usually at the end of the month.
Automatic partition creation is realized as follows
/**************************************************************************
Program Name: Add Partition
Description:
Create all partitions for users for the next month.
***************************************************************************/
Procedure add _ partition (v_schema in varchar)
be
Cursor c_td_table
be
Select table name
From where? H _ reservation
Where typeid = partition
schemaname = UPPER (v_schema)
Sort by table name;
v _ cur BINARY _ INTEGER
V_int binary _ integer;
v _ partition VARCHAR()；
v_date？ Date;
v_days？ Number;
sql_stmt？ VARCHAR()； ? A string used to save sql statements.
err _ msg VARCHAR()；
begin
v _ date:= TRUNC(ADD _ MONTHS(SYSDATE)MM)；
v_days :=
TO _ NUMBER(TO _ CHAR(LAST _ DAY(ADD _ MONTHS(SYSDATE))DD))；
v _ cur:= DBMS _ SQL open _ cursor；
For v_table in c_td_table.
ring
v _ date:= TRUNC(ADD _ MONTHS(SYSDATE)MM)；
v _ partition:= v _ table tablename；
For me in v_days
ring
begin
sql_stmt :=
Change form
|| v_schema
||
|| v_table table name
|| Add partition
| | v _ partition
|| _
|| TO_CHAR (v_date YYMMDD)
||
|| Less than (Deadline (
|| TO_CHAR (v_date + YYYY MM DD)
| | YYYY MM DD))；
DBMS _ SQL parse(v _ cur SQL _ stmt DBMS _ SQL native)；
v _ int:= DBMS _ SQL EXECUTE(v _ cur)；
exception
When others
then
err_msg :=
V _ partition
||: Create
|| TO_CHAR (v_date YYMMDD)
|| Partition failed! Error message:
| | SQLERRM
Log_insert (error message); ? You can define your own log_insert function.
Massachusetts Institute of Technology;
End;
v _ date:= v _ date+；
End the cycle;
End the cycle;
DBMS _ SQL close _ cursor(v _ cur)；
End;
Automatically delete expired partitions
In order to release the storage space and improve the performance of large tables, it is necessary to delete the expired historical data from the database. The deletion operation can be performed manually or automatically through the task scheduling of the system. Data deletion only needs to delete the corresponding data partition, which has the following advantages compared with delete.
U soon
U takes up less rollback tablespace.
U generate fewer logs.
U release space
If you have a global index, you need to rebuild the index after deleting the partition.
The automatic deletion of partitions is realized as follows.
Lishi Xinzhi/Article/program/Oracle/20 13 1 1/ 18275