Oracle Data Table Partitioning Policy
Oracle Data Table Partitioning Policy
The ORACLE tutorial is: Oracle Data Table Partitioning policy. This article describes the table to be partitioned by the hospital information system through statistical analysis. Select the partition key for the table to be partitioned, that is, find the columns (table attributes) included in your partition key ), it makes sense to manage large data. This article is implemented under Oracle8.1.6.
Although Oracle is a large DBMS, if it does not process tables with many records, it still cannot use the powerful function of Oracle to manage large data. Therefore, it partitions some tables, it has the following advantages:
Each partition in a partition table can be logically considered as an independent object;
You can delete, move, analyze, and grade one or more partitions in a table without affecting other partitions;
If you select an appropriate partition policy, the data query speed will be greatly improved.
1. Find the table to be partitioned
This section describes the tables to be partitioned by the hospital's His system through statistical analysis. Find the columns (table attributes) included in your partition key for the tables to be partitioned, that is, select the partition key.
1. Find the table to be partitioned Based on the Access Frequency
Oracle8i allows access to audit information in the database. With the audit information collected, the designer can determine which tables have the most frequently accessed data, that is, to identify those tables and partition them.
Open Review: After logging on to the database as SYS or SYSSTEM, run the following script to enable the object review function.
These codes will generate an output file of "audon. SQL", which contains the statements in the format shown in the following list.
Run @ audon. SQL to activate the preceding code to enable the audit function to collect audit information and create a table to save the summary:
Extract the audit information from the dba_audit_object table and load it into the summary table:
Close Review:
These codes will generate an output file of "audoff. SQL.
Run @ audoff. SQL to activate the above Code to disable the object review function.
Clear audit information:
Analyze audit information
The preceding figure shows the table access status within 24 hours according to the clinical doctor's advice of HIS system. The table 1-1 is obtained from the preceding query.
Table 1-1
Group_order_master (master record of doctor's advice set) and group_order_item (doctor's advice set). The number of rows in a table is small and not suitable for partitioning. Although drug_stock (drug stock) has a high access frequency, however, the number of rows in a table is small, so it is not suitable for partitioning. We select a table with many rows and a High Access frequency for partitioning, such as doctor_orders, orders, and orders_costs. Considering that doctor_orders is prescribed by doctors on the doctor's workstation, orders is the doctor's advice generated by doctor_orders and executed on the nurse's workstation. The two tables have similar structures, while Orders is closely related to medicines, health materials, and billing, therefore, we will focus on the processing of table orders.
2. Select the partition key based on the column Value
Use the SQL * plus command Analyze to collect the statistics of the last partition table, Analyze the statistics according to the sample 20% recommended by Oracle, and save the statistical results in the data dictionary.
Query the DBA_TAB_COLUMNS data dictionary View
Generate table 1-2
Table 1-2
From table 1-2, we can see the distribution spectrum of each candidate partition key in the partitioned table. There is no even distribution of the ORDER_CODE key value, which is used as the partition key, obviously not suitable;
ORDER_CLASS (doctor's class code) and ORDERING_DEPT (doctor's class code) show even distribution of key values, for example, using each of its key values as range-based partitions, each partition has a uniform number of records, but this method is obviously not optimal for tables with tens of thousands of records added every day. If you use START_DATE_TIME (doctor's guide start time) to create a range partition, create a partition for each month's data, create a hash subpartition Based on ORDERING_DEPT in each partition, and form a combined partition for each month's data, the distribution of records in each partition is even, the query speed is improved, and the data can be easily backed up and deleted. Because most of the statistics and queries are within the range of one month, and the actual query results show that the statistics and query speed of cross-month and cross-year data are as follows, it is also much shorter than when there is no partition, and the effect is very obvious.
