分区表一些特性

(一)创建分区表

 create table tmp1
(
  DEALER_ID            NUMBER(8) not null,
  CO_NO                CHAR(12) not null,
  SO_NO                CHAR(12) not null,
  COMPANY_ID           NUMBER(8) not null,
  CO_STATUS            NUMBER(8),
  CLAIMING_TYPE        NUMBER(8),
  CLAIM_CATEGORY       NUMBER(8),
  CLAIMING_MONTH       NUMBER(6),
  CI_APANAGE_DLR       NUMBER(8),
  CI_REQUISITION_NO    CHAR(12),
  PART_SO_NO           CHAR(12),
  RESCUE_REPORT_NO     CHAR(12),
  KIT_FACTORY_CODE     VARCHAR2(16),
  FAULTY_TYPE          NUMBER(8),
  FP_ID                NUMBER(12),
  FP_CODE              VARCHAR2(16),
  FAULTY_CAUSE         NUMBER(8),
  FAULTY_MODE          NUMBER(8),
  FAULTY_DESC          VARCHAR2(512 CHAR),
  CP_ID                NUMBER(12),
  CP_NO                VARCHAR2(32),
  CO_AUDITOR           NUMBER(8),
  CO_AUDITED_DATE      TIMESTAMP(6),
  CO_AUDITED_DESC      VARCHAR2(256 CHAR),
  CO_AUDITED_RESULT    NUMBER(8),
  SP_AUDITOR           NUMBER(8),
  SP_AUDITED_DATE      TIMESTAMP(6),
  SP_AUDITED_DESC      VARCHAR2(256 CHAR),
  SCRAP_AUDITED_RESULT NUMBER(8),
  REJECTED_TIMES       NUMBER(4),
  CO_MAN_HOURS_COUNT   NUMBER(8,2),
  CO_MAN_HOURS_FEE     NUMBER(10,2),
  CO_PARTS_COUNT       NUMBER(8,2),
  CO_PARTS_FEE         NUMBER(10,2),
  CO_MANAGEMENT_FEE    NUMBER(10,2),
  CO_RESCUE_FEE        NUMBER(10,2),
  CO_COMPENSATED_FEE   NUMBER(10,2),
  CO_ADDED_FEE         NUMBER(10,2),
  CO_AMOUNT_SUM        NUMBER(10,2),
  AU_MAN_HOURS_COUNT   NUMBER(8,2),
  AU_MAN_HOURS_FEE     NUMBER(10,2),
  AU_PARTS_COUNT       NUMBER(10,2),
  AU_PARTS_FEE         NUMBER(10,2),
  AU_MANAGEMENT_FEE    NUMBER(10,2),
  AU_RESCUE_FEE        NUMBER(10,2),
  AU_COMPENSATED_FEE   NUMBER(10,2),
  AU_ADDED_FEE         NUMBER(10,2),
  AU_AMOUNT_SUM        NUMBER(10,2),
  IS_BALANCED_SRC      NUMBER(1) default 0,
  SETTLEMENT_NO_SRC    CHAR(14),
  IS_BALANCED_KIT      NUMBER(1) default 0,
  IS_AUDITED_KIT       NUMBER(1) default 0,
  SETTLEMENT_NO_KIT    CHAR(14),
  BEST_CALL_TIME       VARCHAR2(128 CHAR),
  INQUIRY_RESULT       NUMBER(8),
  CI_HANDLE_RESULT     VARCHAR2(256 CHAR),
  CLAIMER              VARCHAR2(32 CHAR),
  RU_UPLOADED_DATE     TIMESTAMP(6),
  ALLOW_UNDOING        NUMBER(1) default 0,
  UNDONE_CAUSE         VARCHAR2(128 CHAR),
  UNDONE_BY            NUMBER(8),
  UNDONE_DATE          TIMESTAMP(6),
  DEDUCTED             NUMBER(1) default 0,
  DEDUCTED_AMT         NUMBER(10,2),
  DEDUCTED_DESC        VARCHAR2(128 CHAR),
  IS_INACTIVE          NUMBER(1) default 0,
  CREATED_BY           NUMBER(8),
  CREATED_TIME         TIMESTAMP(6),
  UPDATED_BY           NUMBER(8),
  UPDATED_TIME         TIMESTAMP(6),
  VERSION              NUMBER(8) default 0,
  IS_INTACT            NUMBER(1) default 1,
  HANDLER_REP          NUMBER(8),
  INQUIRY_CONDITION    VARCHAR2(512 CHAR),
  APPEAL_INQUIRY_DATE  TIMESTAMP(6),
  SP_AUDITED_REMARK    VARCHAR2(128 CHAR),
  CI_APANAGE_RN        NUMBER(8),
  KIT_FACTORY_CODE_X   VARCHAR2(16),
  OUT_WARRANTY         NUMBER(1) default 0,
  SP_AMOUNT_KIT        NUMBER(10,2),
  MG_AMOUNT_KIT        NUMBER(10,2),
  AUDITED_REMARK       VARCHAR2(128 CHAR),
  CC_TYPE              NUMBER(8),
  VEHICLE_UPDATED      NUMBER(1) default 0,
  IS_GUARANTEES        NUMBER(1),
  IS_FOREWARN          NUMBER(1),
  PART_DEALER_ID       NUMBER(8)
)
PARTITION BY RANGE (RU_UPLOADED_DATE) 
   (
    PARTITION  PART_2011  VALUES LESS THAN (to_date('2012-01-01','yyyy-mm-dd')),
    PARTITION  PART_2012_1  VALUES LESS THAN (to_date('2012-04-01','yyyy-mm-dd')),
    PARTITION  PART_2012_2  VALUES LESS THAN (to_date('2012-07-01','yyyy-mm-dd')),
    PARTITION  PART_2012_3  VALUES LESS THAN (to_date('2012-10-01','yyyy-mm-dd')),
    PARTITION  PART_2012_4  VALUES LESS THAN (to_date('2013-01-01','yyyy-mm-dd')),
    PARTITION  PART_2013_1  VALUES LESS THAN (to_date('2013-04-01','yyyy-mm-dd')),
    PARTITION  PART_2013_2  VALUES LESS THAN (to_date('2013-07-01','yyyy-mm-dd')),
    PARTITION  PART_2013_3  VALUES LESS THAN (to_date('2013-10-01','yyyy-mm-dd')),
    PARTITION  PART_2013_4  VALUES LESS THAN (to_date('2014-01-01','yyyy-mm-dd')),
    PARTITION  PART_2014_1  VALUES LESS THAN (to_date('2014-04-01','yyyy-mm-dd'))
  )

(二)插入数据

                      insert into tmp1 select  *  from NT_CI_CLAIMING_ORDERS;

         commit;

(三)创建索引

1 创建主键

  SQL> alter table TMP1
  2    add constraint PK_TMP1 primary key (CO_NO, DEALER_ID)
  3    using index
  4    local;

Table altered

2  创建其他索引

SQL>   create index IDX_tmp1 on tmp1 (RU_UPLOADED_DATE) local;
 
Index created
 
SQL> create index IF_tmp1 on tmp1 (SO_NO, DEALER_ID) local;
 
Index created
 
SQL> create index IND_tmp1 on tmp1 (KIT_FACTORY_CODE) local compress;
 
Index created
 
SQL> create index INX_tmp1 on tmp1 (SETTLEMENT_NO_SRC, SCRAP_AUDITED_RESULT)local;
 
Index created

SQL> create index tmp1_IDX on tmp1 (SETTLEMENT_NO_SRC, CO_AUDITED_RESULT)
  2  local;

SQL> create index tmp1_INX on tmp1 (DEALER_ID, CO_NO) local compress;  
 
Index created