oracle数据库优化——表分区技术.pptx

我们为什么要使用分区表?;我们为什么要使用分区表;分区技术原理;分区技术原理;什么是分区;分区技术的广泛应用;什么情况下推荐使用分区;分区的一些限制;分区技术的好处;分区表与非分区表的区别;分区表种类(10g);分区类型(11g新增);什么时候使用Range分区;什么时候使用hash分区;什么时候使用list分区;什么时候使用组合分区;Range 分区示例;Range分区的特点;create table big_table2 ( ID, OWNER, OBJECT_NAME, SUBOBJECT_NAME, OBJECT_ID, DATA_OBJECT_ID, OBJECT_TYPE, CREATED, LAST_DDL_TIME, TIMESTAMP, STATUS, TEMPORARY, GENERATED, SECONDARY ) partition by hash(id) (partition part_1 tablespace big2, partition part_2 tablespace big2, partition part_3 tablespace big2, partition part_4 tablespace big2, ) as select rownum,* from dba_objects;;Hash分区的特点;CREATE TABLE sales_list (salesman_id NUMBER(5), salesman_name VARCHAR2(30), sales_state VARCHAR2(20), sales_amount NUMBER(10), sales_date DATE) PARTITION BY LIST(sales_state)( PARTITION sales_west VALUES(California, Hawaii), PARTITION sales_east VALUES (New York, Virginia, Florida), PARTITION sales_central VALUES(Texas, Illinois), PARTITION sales_other VALUES(DEFAULT) ); ;List 分区的特点;CREATE TABLE quarterly_regional_sales (deptno NUMBER, item_no VARCHAR2(20), txn_date DATE, txn_amount NUMBER, state VARCHAR2(2)) PARTITION BY RANGE (txn_date) SUBPARTITION BY LIST (state) ( PARTITION q1_1999 VALUES LESS THAN(TO_DATE(1-APR-1999,DD-MON-YYYY)) (SUBPARTITION q1_1999_northwest VALUES (OR, WA), SUBPARTITION q1_1999_southwest VALUES (AZ, UT, NM), SUBPARTITION q1_1999_northeast VALUES (NY, VM, NJ), SUBPARTITION q1_1999_southeast VALUES (FL, GA), SUBPARTITION q1_1999_northcentral VALUES (SD, WI), SUBPARTITION q1_1999_southcentral VALUES (NM, TX)), PARTITION q2_1999 VALUES LESS THAN(TO_DATE(1-JUL-1999,DD-MON-YYYY)) (SUBPARTITION q2_1999_northwest VALUES (OR, WA), SUBPARTITION q2_1999_southwest VALUES (AZ, UT, NM), SUBPARTITION q2_1999_northeast VALUES (NY, VM, NJ), SUBPARTITION q2_1999_southeast VALUES (FL, GA), SUBPARTITION q2_1999_northcentral VALUES (SD, WI), SUBPARTITION q2_1999_southcentral VALUES (NM, TX)), PARTITION q3_1999 VALU