The Oracle tutorial you are looking at is the strategy for Oracle datasheet partitioning.
This paper describes a statistical analysis of the hospital information system needs to partition 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), for large data management more meaningful, the work of this article in Oracle8.
1.6 under implementation.
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.
Set echo off feed off ver off pages 0
Spool Audon.sql
Select ' Audit select on ' | | owner| | '. ' | | object_name| | ' by
Access; '
From Dba_objects
where object_type in (' VIEW ', ' TABLE ') and owner in
(' Ordadm ');
Spool off
Set echo on feeds on ver on
The running of the code will produce a "audon.sql" output file that contains statements in the format shown in the following list
Audit select on Ordadm. Doctor_orders by Access;
Audit select on Ordadm. Group_order_items by Access;
Audit select on Ordadm. Group_order_master by Access;
Audit select on Ordadm. ORDERS by Access;
Audit select on Ordadm. Orders_costs by Access;
Audit select on Ordadm. Orders_sheet_image by Access;
Audit select on Ordadm. Vital_signs_rec by Access;
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:
CREATE TABLE Aud_summary (
Obj_name VARCHAR2 (30),
Owner VARCHAR2 (30),
Hits number);
Take the audit information out of the Dba_audit_object table and load it into the summary table:
INSERT INTO Aud_summary
Select Obj_name,owner,count (*)
From Dba_audit_object
Group BY Obj_name,owner;
Turn off auditing:
Set echo off feed off ver off pages 0
Spool Audoff.sql
Select ' Noaudit select on ' | | owner| | '. ' | | object_name| | '
by Access; '
From Dba_objects
where object_type in (' VIEW ', ' TABLE ') and owner in
(' Ordadm ');
Spool off
Set echo on feeds on ver on
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:
Delete sys.aud$
Analyze Audit information
Col obj_name form A30
Col owner Form A20
Col hits form 99,990
Selec obj_name,owner,hits from Aud_summary;
Obj_name OWNER COUNT (*)
----------- ------------- ----------
Doctor_orders Ordadm 30309
Drug_stock Pharmacy 11094
Group_order_items Ordadm 1030
Group_order_master Ordadm 1196
ORDERS Ordadm 40421
Orders_costs Ordadm 10109
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
Table row number hit Doctor_orders 2052709 30309 drug_stock 2511 11094 group_order_item 3800 1030 group_order_master 186 1196 orders 1 633010 40421 orders_costs 2403214 10109
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.
Analyze table Ordadm. ORDERS Estimate statistics
Sample percent;
Querying the Dba_tab_columns data dictionary view
[NextPage]
Generate Table 1-2
Select Table_name,column_name,num_distinct
From Dba_tab_columns
Where owner like ' Ordadm ';
Table 1-2
Column different values patient_id 28720 visit_id 2 order_no 395 order_sub_no order_class 9 order_code 825 Order_text 1551 ORDERING_DEPT Start_date_time 1194176 Stop_date_time 636798
From table 1-2, we can see that each candidate partitioning key for the partition table
[1] [2] Next page
The Oracle tutorial you are looking at is the strategy for Oracle datasheet partitioning. Distribution Spectrum, Order_code (doctor's order Code) key value does not appear a uniform distribution, using it as a partitioning key, obviously inappropriate;
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.
definition of two partition table
Create Table Orders (
patient_id VARCHAR2 (a) not NULL, Order_no number (4), order_sub_no number (2),
..............
Order_class char (1),
Order_text VARCHAR2 (80),
Order_code VARCHAR2 (10),
.............
Start_date_time DATE
Stop_date_time DATE
.................
Ordering_dept VARCHAR2 (8)
Storage (initial 100M next 1M)
PARTITION by RANGE (start_date_time)
Subpartition by HASH (Order_code)
Subpartitions 3 STORE in (Ordersub1, Ordersub 2, ordersub 3)
(PARTITION orders200212 VALUES less THAN (to_date (' 2003-01-01 00:00:00 ', ' yyyy-mm-dd hh24:mi:ss '))
Tablespace Tsp_orders,
PARTITION orders200301
VALUES less THAN (to_date (' 2003-02-01 00:00:00 ', ' yyyy-mm-dd hh24:mi:ss '))
Tablespace Tsp_orders,
PARTITION orders 200302
VALUES less THAN (to_date (' 2003-03-01 00:00:00 ', ' yyyy-mm-dd hh24:mi:ss '))
Tablespace Tsp_orders,
.....................
PARTITION orders200402
VALUES less THAN (to_date (' 2004-03-01 00:00:00 ', ' yyyy-mm-dd hh24:mi:ss '))
Tablespace tsp_orders
(Subpartition ordersub1 tablespace tsp_orders, subpartition ordersub 2 tablespace tsp_orders,
Subpartition ordersub3 tablespace tsp_orders));
In the Established Orders table, one partition per month, starting from December 2002, February 2004, a total of 15 partitions, PARTITION by RANGE (start_date_time) clause, representing the start_date_ of the table The time attribute establishes the range partition, the specific partition name, the timeframe, the definition in the partition clause, the subpartition by hash (order_code) clause, the Order_code attribute of the table, and the partition under each partition. The specific name of each sub partition, the table space in which it resides, is defined in the ubpartition clause.
Set up constraints and indexes for orders with the following ALTER TABLE data definition statement.
ALTER TABLE Orders ADD CONSTRAINT pk_orders KEY (patient_id,
VISIT_ID,ORDER_NO,ORDER_SUB_NO) USING INDEX PCTFREE 5 tablespace tsp_orders;
Table 1-3 is the number of records in the named area that are queried with the select COUNT (*) from the orders partition (partition name) statement.
Table 1-3
Number of partition name records orders200301 87311 orders2200302 87553 orders2200303 107533 ... orders200401 124812
maintenance operations for three-partition tables
Add Partition:
Alter Table orders ADD partition orders200403
Aalues Less (to_date (' 2004-04-01 00:00:00 ', ' yyyy-mm-dd hh24:mi:ss '))
Tablespace tsp_orders;
To delete a partition:
Alter Table table_name Drop Partition partition_name;
Cutting Division:
Alter Table table_name Truncate Partition partition_name Storage;
Oracle Data Table Partitioning small knowledge:
To simplify the management of database large tables, the O R a C l e 8 and later releases the partitioning option. Partitioning separates tables in a number of different tablespaces, using a divide-and-conquer approach to support large tables that are infinitely swollen, and to the manageability of large tables at the physical level. Splitting a large table into smaller partitions can improve the maintenance, backup, recovery, transaction, and query performance of the table. This option can be recommended for a large number of daily business data in the current social security and telecommunications industry, ORACLE8.
Advantages of Partitioning:
1. Enhance usability: If one partition of a table is not usable due to system failure, the rest of the table can still be used;
2, reduce the shutdown time: If the system failure affects only part of the table partition, then only this part of the partition needs to be repaired, so can be repaired than the whole large table to spend less time;
3. Easy maintenance: If you need to rebuild the table, it is much easier to manage each partition independently than to manage a single large table;
4, balanced I/O: The table can be divided into different partitions to separate disk to balance I/O performance improvement;
5, improve performance: the large table query, add, modify and other operations can be decomposed into the table of different partitions to execute in parallel, can make running faster;
6, the partition is transparent to the user, the end user does not feel the existence of the partition.
Previous page
prev [1] [2]
