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;
至此，完成优化，