2022年Oracle 数据表分区的策略ORACLE教程.docx

《2022年Oracle 数据表分区的策略ORACLE教程.docx》由会员分享，可在线阅读，更多相关《2022年Oracle 数据表分区的策略ORACLE教程.docx（12页珍藏版）》请在淘文阁 - 分享文档赚钱的网站上搜索。

1、2022年Oracle 数据表分区的策略ORACLE教程本文描述通过统计分析出医院信息系统需分区的表，对需分区的表选择分区键，即找出包括在你的分区键中的列(表的属性)，对大型数据的管理比较有意义， 本文的工作在Oracle8.1.6下实现。Oracle虽然是一个大型的DBMS，但假如不对记录比较多的表进行处理，仍旧发挥不了Oracle管理大型数据的强大功能，因此对某些表进行分区，具有如下优点：分区表中每个分区可以在逻辑上认为是一个独立的对象；可以在一个表中的一个或多个分区上进行如删除、移动、析分等维护操作，而不会影响其它分区，具有分区独立性；假如选择合适的分区策略，会大大的加快数据的查询速度。

2、一 找出需分区的表本节描述通过统计分析出医院His系统需分区的表，对需分区的表找出包括在你的分区键中的列(表的属性)，即选择分区键。1、基于访问频度找出需分区的表Oracle8i允许访问数据库中的审核信息，借助于收集的审核信息，设计者能够确定哪些表的数据是真正最频繁访问的，即找出那些表，须要进行分区。打开审核：在作为SYS或SYSSTEM登录到数据库后，动行如下脚本，打开对象的审核功能。set echo off feed off ver off pages 0spool audon.sqlselect audit select on |owner|.|object_name| byaccess

3、;from dba_objectswhere object_type in (VIEW，TABLE) and owner in(ORDADM);spool offset echo on feed on ver on这些代码的运行将产生“audon.sql”的输出文件，它包含下面清单中所示格式的语句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;a

4、udit 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;运用吩咐audon.sql激活上述代码以打开审核功能收集收集审核信息，建立一个表以保存概要信息：create table aud_summary (obj_name varchar2(30)，owner varchar2(30)

5、，hits number);将审核信息从dba_audit_object表中取出并装入概要表中：insert into aud_summaryselect obj_name，owner，count(*)from dba_audit_objectgroup by obj_name，owner;关闭审核：set echo off feed off ver off pages 0spool audoff.sqlselect noaudit select on |owner|.|object_name| by access;from dba_objectswhere object_type in (V

6、IEW，TABLE) and owner in(ORDADM);spool offset echo on feed on ver on这些代码的运行将产生“audoff.sql”的输出文件。运用吩咐 audoff.sql激活上述代码以关闭上述对象的审核功能。清除审核信息：delete sys.aud$分析审核信息col obj_name form a30col owner form a20col hits form 99，990selec obj_name，owner，hits from aud_summary;OBJ_NAME OWNER COUNT(*)- - -DOCTOR_OR

7、DERS ORDADM 30309DRUG_STOCK PHARMACY 11094GROUP_ORDER_ITEMS ORDADM 1030GROUP_ORDER_MASTER ORDADM 1196ORDERS ORDADM 40421ORDERS_COSTS ORDADM 10109以上是HIS系统临床医嘱部分24小时内对表的访问状况，从上面的查询得表1-1。表1-1表行数命中率doctor_orders205270930309 drug_stock251111094 group_order_item38001030 group_order_master1861196 orders163

8、301040421 orders_costs2403214 10109group_order_master( 医嘱套攴主记录) 、group_order_item(医嘱套攴明细)， 表的行数比较少，不适合分区；drug_stock（药品库存）虽然存取频率比较高，但表的行数比较少，因此也不适合分区。我们选择表的行数比较多、存取频率比较高的表作分区处理，如 doctor_orders、orders、orders_costs，考虑到doctor_orders是医生工作站上医生开的医嘱，orders是由doctor_orders生成、护士工作站上执行的医嘱，两个表结构类似，而医嘱与药品、卫生材料、计费

9、联系比较亲密的是Orders，因此重点介绍对表orders的处理。2、基于列值选择分区键运用Sql*plus下用吩咐Analyze收集末分区表的统计信息，根据Oracle举荐的取样20%进行分析，并将统计结果保存在数据字典中。Analyze table ORDADM.ORDERS estimate statisticssample 20 percent;对DBA_TAB_COLUMNS数据字典视图进行查询产生表1-2select table_name，column_name，num_distinctfrom DBA_TAB_COLUMNSwhere owner like ORDADM;表1-2

10、列不同的值PATIENT_ID 28720 VISIT_ID2 ORDER_NO395 ORDER_SUB_NO 10 ORDER_CLASS 9 ORDER_CODE 825 ORDER_TEXT1551 ORDERING_DEPT 15 START_DATE_TIME 1194176 STOP_DATE_TIME 636798从表1-2中，我们可以看到欲分区表的各个候选分区键的分布频谱，ORDER_CODE（医嘱代码）键值没有出现一种匀称分布，用它作分区键，明显不合适；ORDER_CLASS（医嘱类别代码）、ORDERING_DEPT（开医嘱科室代码），键值出现匀称分布，如用它的各个键值作

11、基于范围的分区，每个分区具有的记录数比较匀称，但这种方法对于每天增加上万条记录的表来看，明显不是最优的。假如选用START_DATE_TIME（医嘱起先时间）建立范围分区，每月的数据建立一个分区，在每个分区内基于ORDERING_DEPT建立散列子分区，每月的数据形成一个组合分区，会使每个分区的记录数分布匀称、查询速度提高、易于备份和删除。因为大多数的统计和查询是在一个月的范围内，而且从实际的查询效果看，跨月和跨年数据的统计和查询速度，也比未分区的时候大大缩短，效果特别明显。12下一页 二 分区表的定义Create Table orders (PATIENT_ID varchar2(10) n

12、ot 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 dateSTOP_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

13、STORE IN (ordersub1， ordersub 2， ordersub 3)(PARTITION orders202212 VALUES LESS THAN (to_date(2022-01-01 00:00: 00，yyyy-mm-dd hh24:mi:ss)tablespace Tsp_Orders，PARTITION orders202201VALUES LESS THAN (to_date(2022-02-01 00:00: 00，yyyy-mm-dd hh24:mi:ss)tablespace Tsp_Orders，PARTITION orders 202202VALUE

14、S LESS THAN (to_date(2022-03-01 00:00: 00，yyyy-mm-dd hh24:mi:ss)tablespace Tsp_Orders，.PARTITION orders202202VALUES LESS THAN (to_date(2022-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，SUBPARTI

15、TION ordersub3 TABLESPACE Tsp_Orders);在建立的Orders表中，按每月一个分区，从2022年12月起先，2022年02月止，共15个分区， PARTITION BY RANGE(START_DATE_TIME) 子句，表示用表的START_DATE_TIME属性建立范围分区，详细的每个分区名、时间范围，在PARTITION子句定义；SUBPARTITION BY HASH(ORDER_CODE)子句，表示用表的ORDER_CODE 属性，在每个分区下建立散列子分区，详细的每个子分区名、所在表空间，在UBPARTITION子句定义。用下面ALTER TABL

16、E数据定义语句为Orders建立约束和索引。ALTER TABLE Orders ADD CONSTRAINT Pk_Orders KEY (Patient_id，Visit_id，Order_no，Order_sub_no) USING INDEX PCTFREE 5 TABLESPACE Tsp_Orders;表1-3是用select count(*) from orders partition (分区名)语句，查询出的名分区的记录数。表1-3分区名记录数orders20220187311 orders220220287553 orders2202203107533 . orders202

17、201124812三 分区表的维护操作增加分区：Alter Table orders Add partition orders202203Aalues less (to_date(2022-04-01 00:00:00，yyyy-mm-dd hh24: mi:ss)Tablespace Tsp_Orders;删除分区：Alter Table table_name Drop Partition partition_name;裁断分区:Alter Table table_name Truncate Partition partition_name Storage;Oracle数据表分区小学问：为了简化数据库大表的管理，O r a c l e 8 及以后版本推出了分区选项。分区将表分别在若干不同的表空间上，用分而治之的方