Oracle data table partitioning policy

The ORACLE tutorial you are looking at is :Oracle table partitioning policy. This paper describes the statistical analysis of the table to be partitioned in the hospital information system, select the partitioning key for the table to be partitioned, that is, find out the columns (attributes of the table) included in your partitioning key, which is meaningful for the management of large data. The work of this paper is implemented under Oracle8.1.6.

Although Oracle is a large DBMS, it still cannot play the powerful function of Oracle in managing large data without processing the tables with a large number of records. Therefore, partitioning some tables has the following advantages:

Each partition in a partitioned table can logically be considered a separate object.

Maintenance operations such as delete, move and dissect can be performed on one or more partitions in one table without affecting other partitions.

If the appropriate partitioning strategy is chosen, the query speed of the data will be greatly accelerated.

Find the table to partition

This section describes the statistical analysis of the table to be partitioned in the hospital His system. For the table to be partitioned, find the columns (attributes of the table) included in your partitioning key, that is, select the partitioning 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 is able to determine which tables are truly most frequently accessed, that is, find out which tables need to be partitioned.

Open auditing: after logging in to the database as SYS or SYSSTEM, activate the auditing function of the object with the following script.

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 feed on ver on
Running this code produces an output file for "audon.sql," which contains statements in the format shown in the following listing

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;
Activate the above code with the command @audon.sql to turn on the audit function to collect audit information, and create a table to save the profile information:

create table aud_summary (
obj_name varchar2 (30),
owner varchar2 (30),
hits number);
Extract the audit information from the dba_audit_object table and load it into the profile table:

insert into aud_summary
select obj_name, owner, count(*)
from dba_audit_object
group by obj_name, owner;
Closing audit:

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 feed on ver on
Running this code will produce an output file of "audoff.sql".

Activate the above code with the command @audoff.sql to turn off the auditing capability for the above object.

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 24-hour access to the table in the clinical orders section of HIS system. Table 1-1 is obtained from the above query.

Table 1-1

table The number of rows shooting doctor_orders 2052709 30309 drug_stock 2511 11094 group_order_item 3800 1030 group_order_master 186 1196 orders 1633010 40421 orders_costs 2403214 10109
group_order_master (advice sets 攴 master record), group_order_item (advice sets 攴 detail), less the number of rows in the table, not suitable for partition; Although the access frequency of drug_stock (drug inventory) is relatively high, the number of rows in the table is relatively small, so it is not suitable for partitioning. We choose the number of rows in the table more, access to higher frequency table partition processing, such as doctor_orders, orders, orders_costs, considering doctor_orders is a doctor's advice, doctor workstation doctor orders is generated by doctor_orders, nurse workstation for the orders, two table structure, and the doctor's advice and medicine, health materials, contact more closely is Orders billing, so focus on handling of table orders.

2. Select the partitioning key based on the column value

Collect statistics for the end partition table using the command Analyze using Sql*plus, analyze the sample by 20% as recommended by Oracle, and save the statistics in a data dictionary.

Analyze table ORDADM.ORDERS estimate statistics
sample 20 percent;

Query the DBA_TAB_COLUMNS data dictionary view

[NextPage]

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 10 ORDER_CLASS 9 ORDER_CODE 825 ORDER_TEXT 1551 ORDERING_DEPT 15 START_DATE_TIME 1194176 STOP_DATE_TIME 636798

From table 1-2, we can see the candidate partitioning keys for the table to be partitioned

[1] [2] next page

The ORACLE tutorial you are looking at is :Oracle data table partitioning policy. Distribution spectrum, ORDER_CODE (doctor's order code) key value does not appear a uniform distribution, it is obviously inappropriate to use it as a partitioning key;

ORDER_CLASS (doctor's order category code), ORDERING_DEPT (doctor's order department code), the key values appear uniform distribution, such as the use of its each key value as a range-based partition, each partition has a relatively uniform number of records, but this method is obviously not optimal for the table with tens of thousands of records added every day. If START_DATE_TIME (doctor's order start time) is selected to establish the range partition, one partition is established for the monthly data, and a hash sub-partition is established based on ORDERING_DEPT in each partition. A composite partition is formed for the monthly data, which will make the records of each partition evenly distributed, increase the query speed, and make it easy to backup and delete. Because most of the statistics and queries are within the range of 1 month, and from the actual query effect, cross-month and cross-year data statistics and query speed, is also significantly shorter than when not partitioned, the effect is very obvious.

Definition of partition table

Create Table orders (
PATIENT_ID varchar2(10) not 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, there are 15 partitions per month, starting from December 2002 and ending from February 2004. The PARTITION BY RANGE(START_DATE_TIME) clause indicates that the scope partition is established with the START_DATE_TIME attribute. The specific partition name and time range are defined in the PARTITION clause. The SUBPARTITION BY HASH(ORDER_CODE) clause, which refers to the ORDER_CODE attribute of the table, establishes a hash subpartition under each partition. The name and table space of each subpartition are defined in UBPARTITION clause.

Create constraints and indexes for Orders using 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 Tsp_Orders;
Table 1-3 shows the number of records of named partitions queried using the select count(*) from orders partition (partition name) statement.

Table 1-3

The partition name Record number orders200301 87311 orders2200302 87553 orders2200303 107533 ............ orders200401 124812
3. Maintenance of partitioned tables

Add partitions:

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;
Delete partition:

Alter Table table_name Drop Partition partition_name;
Cutting district:

Alter Table table_name Truncate Partition partition_name Storage;
Oracle data table partitioning tips:

To simplify the management of large tables in a database, O r a c l e 8 and later introduced partitioning options. Partitioning separates the tables into several different table Spaces, using a divide-and-conquer approach to support infinitely expanding large tables, giving large tables manageability at physical level 1. Splitting large tables into smaller partitions improves table maintenance, backup, recovery, transaction, and query performance. This option of ORACLE8 can be recommended for the current social security and telecommunications industries with a large amount of daily business data.

Advantages of partitioning:

1. Enhanced availability: if one partition of the table cannot be used due to system failure, the remaining good partitions of the table can still be used;

2. Reduce shutdown time: if the system failure only affects part 1 of the table partition, then only this part of the partition needs to be repaired, so it can take less time to repair than the whole big table;

3. Easy maintenance: if you need to rebuild the tables, it is much easier to manage each partition independently than to manage a single large table;

4. Balanced I/O: different partitions of the table can be allocated to different disks to balance I/O to improve performance;

5, improve performance: query, add, modify and other operations of the large table can be decomposed into different partitions of the table for parallel execution, which can make the running speed faster;

6. The partition is transparent to users, and the end user cannot feel the existence of the partition.

On 1 page

Previous page [1] [2]