Oracle10g大表查询优化

Oracle10g大表查询优化
对于Oracle中的大表，我们可以采用分区表的方式进行优化，以提高访问表的性能。
以下是对长庆物资系统的BILL表的优化过程：
分析：
BILL表有129个字段，24万多条数据。
虽然数据量不是很大，但是字段过多，造成了读取表的效率不高，经常出现资源竞争频繁，I/O阻塞。
因此有必要对BILL表进行优化，提高效率。
对大表一般采用分区表的方式进行优化，由于Oracle没有提供直接将普通表转变为分区表的方式，必须通过重建表的方式进行优化，一般有三种方式实现，根据不同情况使用，
第一种：利用原表重建分区表，方法简单易用，由于采用DDL语句，不会产生UNDO，且只产生少量REDO，效率相对较高，而且建表完成后数据已经在分布到各个分区中了；缺点是对于数据的一致性方面还需要额外的考虑。由于几乎没有办法通过手工锁定T表的方式保证一致性，在执行CREATE TABLE语句和RENAME T_NEW TO T语句直接的修改可能会丢失，如果要保证一致性，需要在执行完语句后对数据进行检查，而这个代价是比较大的。另外在执行两个RENAME语句之间执行的对T的访问会失败。适用于修改不频繁的表，在闲时进行操作，表的数据量不宜太大。
第二种：使用交换分区的方法，只是对数据字典中分区和表的定义进行了修改，没有数据的修改或复制，效率最高。如果对数据在分区中的分布没有进一步要求的话，实现比较简单。在执行完RENAME操作后，可以检查T_OLD中是否存在数据，如果存在的话，直接将这些数据插入到T中，可以保证对T插入的操作不会丢失；缺点是仍然存在一致性问题，交换分区之后RENAME T_NEW TO T之前，查询、更新和删除会出现错误或访问不到数据。如果要求数据分布到多个分区中，则需要进行分区的SPLIT操作，会增加操作的复杂度，效率也会降低；适用于包含大数据量的表转到分区表中的一个分区的操作。应尽量在闲时进行操作。
第三种：使用在线重定义的方法，保证数据的一致性，在大部分时间内，表T都可以正常进行DML操作。只在切换的瞬间锁表，具有很高的可用性。这种方法具有很强的灵活性，对各种不同的需要都能满足。而且，可以在切换前进行相应的授权并建立各种约束，可以做到切换完成后不再需要任何额外的管理操作；缺点是实现上比上面两种略显复杂。
由于我们是在生产系统上进行优化，必须保证数据的完整性，所以选择第三种方式进行优化，优化过程：
1、创建一个中间表，这个表要和BILL表的结构一致。
create table BILL_TEST
(
BILL_ID CHAR(8) not null,
PLAN_MAKE_TIME DATE,
UP_TIME DATE not null,
UP_NAME VARCHAR2(80),
SHENPI_PERSON VARCHAR2(20),
UP_MODE VARCHAR2(10) not null,
LOW_NAME VARCHAR2(80),
LOW_UP_TIME DATE,
UP_PERSON VARCHAR2(20) not null,
MAX_TYPE VARCHAR2(50) not null,
WARE_TYPE_SIZE VARCHAR2(1000) not null,
ERJI_WARE_TYPE VARCHAR2(8) not null,
ERJI_WARE_NUM NUMBER(15,4) not null,
ERJI_NEED_TIME DATE not null,
TUIJIAN_CORP VARCHAR2(20) default 1 not null,
SUPPLY_PINGKU CHAR(10) default 0,
JIHUA_ORDER_TIME VARCHAR2(20),
ORDER_TYPE VARCHAR2(50),
ORDER_MODE VARCHAR2(50),
ORDER_TIME DATE,
GET_WARE_TIME DATE,
SUPPLY_CORP VARCHAR2(500),
CAIGOU_WARE_NUM NUMBER(10,4),
CAIGOU_WARE_PRICE NUMBER(12,3),
CAIGOU_NEED_TIME DATE,
CAIGOU_NEED_ADDR VARCHAR2(200),
HETONG_ID VARCHAR2(100) default 0,
ZHILIANG_NOTE VARCHAR2(200),
ZHILIANG_MONEY NUMBER(12,2),
CONTENT VARCHAR2(200),
ERJI_WARE VARCHAR2(100),
WT_DATE DATE,
END_NUM VARCHAR2(20),
QICAI_WARE VARCHAR2(100),
SCCJ VARCHAR2(200),
JHDATE VARCHAR2(100),
JHNUM VARCHAR2(20),
SHOW NUMBER(1) default 0,
JH_ADDR VARCHAR2(200),
LAST_PRICE NUMBER(12,2),
PRE_PRICE NUMBER(12,2),
CLASS_ID VARCHAR2(20),
LD_MARK VARCHAR2(10) default 0,
ASK_PRICE NUMBER(12,2),
PLAN_CODE VARCHAR2(50),
ARRIVE_TIME DATE,
ARRIVE_WEIGHT VARCHAR2(50),
ARRIVE_QUALITY VARCHAR2(50),
BILL_STEP NUMBER(10,2) default 0,
XJD_CODE NUMBER(10),
XJD_DATE DATE,
XJD_SUPPLY VARCHAR2(1000),
OUT_FLAG VARCHAR2(2) default 0,
YY_PRICE NUMBER(12,2) default 0,
BASE_BILL VARCHAR2(8),
BASE_ID VARCHAR2(4),
TECK_ASK VARCHAR2(4000),
NEW_WARE_TYPE_SIZE VARCHAR2(1000),
NEW_ERJI_TYPE_SIZE VARCHAR2(100),
BIDE_YEAR VARCHAR2(4),
BIDE_ID VARCHAR2(5) default 0,
BIDE_FINISH NUMBER(1) default 0,
YSD_ID VARCHAR2(1000) default 0,
BG_TYPE NUMBER(1) default 0,
PLAN_CHECK VARCHAR2(20),
IF_ENERGY VARCHAR2(1) default 0,
LAST_SUPPLY VARCHAR2(200),
ASK_STEP NUMBER(4,1) default 0,
ASK_END NUMBER(1) default 1,
ASK_TIMES NUMBER(1) default 0,
CON_END_STEP NUMBER(1),
PZ_CODE VARCHAR2(50),
FP_CODE VARCHAR2(100),
QC_STEP NUMBER(2) default 0,
QC_PRICE NUMBER(12,2),
QC_SUPPLY VARCHAR2(200),
QC_SCCJ VARCHAR2(100),
QC_CON_ID VARCHAR2(20),
QC_JHDATE VARCHAR2(100),
QC_JHADDR VARCHAR2(100),
BJ_TYPE NUMBER(1),
BJ_INFO VARCHAR2(100),
FP_FILE VARCHAR2(20),
BJ_FILE VARCHAR2(100),
CC_STYLE NUMBER(1) default 0,
KROOM_ID VARCHAR2(40),
YSOVER VARCHAR2(10) default 0,
YSD_SJDHRQ VARCHAR2(11) default 0,
YSD_CPH VARCHAR2(10) default 0,
YSD_YDH VARCHAR2(10) default 0,
YSD_DUN NUMBER(10,2) default 0,
YSD_GEN NUMBER(10,2) default 0,
YSD_MI NUMBER(10,2) default 0,
YSD_SJBGH VARCHAR2(100) default 0,
YSD_KS VARCHAR2(1) default 0,
FLD_BH VARCHAR2(2000) default 0,
WZCD VARCHAR2(100),
QC_CODE NUMBER(10),
JH_STIME DATE,
CG_STIME DATE,
LD_STIME DATE,
CG_PTIME DATE,
JG_PTIME DATE,
IF_PRINT NUMBER(1) default 0,
KC_PRICE NUMBER(12,2),
KC_RATE NUMBER(8,3),
KC_ID NUMBER(8),
YSD1_FLAG NUMBER(1) default 0,
FLD_FLAG NUMBER(1) default 0,
YSD2_FLAG NUMBER(1) default 0,
OLD_PRICE NUMBER(12,2),
TECK_ASK_FILE VARCHAR2(40),
ZL_STIME DATE,
IF_JS NUMBER(1),
SITE_NAME VARCHAR2(200),
CLASS_ID_OLD VARCHAR2(20),
SD_FLAG NUMBER(1) default 0,
DJSD_FLAG NUMBER(1) default 0,
CON_JHDATE DATE,
CON_CONFIRM_DATE DATE,
RETURN_FLAG NUMBER(1),
PLAN_TYPE VARCHAR2(20),
WW_FLAG NUMBER(1) default 0,
YS_FLAG NUMBER(1) default 0,
RUN_TIME DATE,
OLD_WARE_NUM NUMBER(15,4),
JH_CTIME DATE default sysdate
)
tablespace DEMO_USER
partition by range(up_time)
(
partition p1 values less than (to_date('2002-1-1', 'yyyy-mm-dd')),
partition p2 values less than (to_date('2003-1-1', 'yyyy-mm-dd')),
partition p3 values less than (to_date('2004-1-1', 'yyyy-mm-dd')),
partition p4 values less than (to_date('2005-1-1', 'yyyy-mm-dd')),
partition p5 values less than (to_date('2006-1-1', 'yyyy-mm-dd')),
partition p6 values less than (to_date('2007-1-1', 'yyyy-mm-dd')),
partition p7 values less than (to_date('2008-1-1', 'yyyy-mm-dd')),
partition p8 values less than (maxvalue));
在新建的表中创建8个分区，将每一年的数据放在一个分区中，用up_time字段区分时间段。
2、开始重定向表
begin
dbms_redefinition.can_redef_table('demo_user','bill',dbms_redefinition.cons_use_pk);
dbms_redefinition.start_redef_table('demo_user','bill','bill_test',null,dbms_redefinition.cons_use_pk);
end;
3、创建与BILL_TEST相关联的对象，保持和BILL表的一致
alter table BILL_TEST
add constraint TEST_BILL_UK21144125351128 unique (PLAN_CHECK)
using index
tablespace DEMO_USER;

create index TEST_SY_BILL_BASE_ID on BILL_TEST (BASE_ID) tablespace DEMO_USER;

create index TEST_SY_BILL_HETONG_ID on BILL_TEST (HETONG_ID) tablespace DEMO_USER;

create index TEST_SY_BILL_MAX_TYPE on BILL_TEST (MAX_TYPE) tablespace DEMO_USER;

create index TEST_SY_BILL_STEP on BILL_TEST (BILL_STEP) tablespace DEMO_USER;

create index TEST_SY_BILL_UP_TIME on BILL_TEST (UP_TIME) tablespace DEMO_USER;
4、同步分区表
begin
dbms_redefinition.sync_interim_table('demo_user','bill','bill_test');
end;
5、完成重定向表
begin
dbms_redefinition.finish_redef_table('demo_user','bill','bill_test');
end;
6、删除中间表
drop table bill_test;
至此，完成优化，