The Oracle tutorial you are looking at is the strategy for Oracle datasheet partitioning. This article describes the need to partition the hospital information system by statistical analysis of the table, the need to partition the table to select the partition key, that is, to find the columns included in your Partition key (table properties), the management of large data more meaningful, the work of this article is implemented under the Oracle8.1.6.
Oracle is a large DBMS, but if you do not work with more tables than you do, you still cannot perform Oracle's powerful ability to manage large data, so partitioning some tables has the following advantages:
Each partition in a partitioned table can logically be considered an independent object;
It is possible to perform maintenance operations on one or more partitions of a table, such as delete, move, and partition, without affecting the other partitions, with zoning independence;
If you choose the appropriate partitioning strategy, you will greatly speed up the query speed of the data.
Find a table that needs to be partitioned
This section describes a statistical analysis of the table to be partitioned by the hospital's his system, identifying the columns to be included in your partitioning key (the table's attributes), that is, selecting the partitioning key.
1, based on the frequency of access to identify the partition needs of the table
Oracle8i allows access to audit information in the database, and with the collection of audit information, the designer is able to determine which table data is truly the most frequently accessed, that is, to identify those tables and to partition them.
Turn on auditing: After you log on to the database as SYS or SYSSTEM, move to the following script to open the auditing function for the object.
The running of the code will produce a "audon.sql" output file that contains statements in the format shown in the following list
Use command @audon.sql to activate the above code to turn on auditing to collect audit information and create a table to hold the profile:
Take the audit information out of the Dba_audit_object table and load it into the summary table:
Turn off auditing:
The operation of these codes will produce a "audoff.sql" output file.
Use the command @ audoff.sql to activate the above code to turn off the auditing functionality of the above object.
To clear audit information:
Analyze Audit information
The above is the his system clinical advice part of 24 hours of access to the table, from the above query table 1-1.
Table 1-1
Group_order_master (Doctor's 攴 master record), Group_order_item (medical order set 攴 detail), the table of fewer rows, not suitable for zoning; Drug_stock (drug stock) Although the access frequency is relatively high, the table has fewer rows and therefore is not suitable for partitioning. We choose the table more rows, more frequent access to the table for partition processing, such as doctor_orders, orders, orders_costs, taking into account doctor_orders is the doctor's workstation on the doctor's advice, orders are by doctor_ Orders generated, nurses on the workstation to carry out the order, two table structure is similar, and the doctor's advice and medicine, health materials, billing links more closely is orders, so focus on the table orders to deal with.
2. Select partition key based on column value
Use the Sql*plus command analyze to collect statistics on the last partition table, analyze it according to Oracle's recommended sampling 20%, and save the statistical results in the data dictionary.
Querying 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 of partition table, the Order_code (order code) key value does not appear a kind of uniform distribution, use it as partitioning key, obviously unsuitable;
Order_class (order type Code), ordering_dept (prescribe department code), the key values appear evenly distributed, such as the use of its various key values for the range based partition, each partition has a relatively uniform number of records, but this method for the daily increase of tens of thousands of records of the table to see, Obviously not the optimal. If you choose Start_date_time (physician start time) to establish a range of partitions, the monthly data to establish a partition, in each partition based on the ordering_dept set up the division, the monthly data into a composite partition, the number of records in each partition evenly distributed, query speed increased , easy to back up and delete. Because most of the statistics and queries in the one-month range, and from the actual query effect, across the month and across the year data statistics and query speed, but also significantly shorter than the partition, the effect is very obvious.
