1 创建分区表
create table tmp2
(
DEALER_ID NUMBER(8) not null,
SO_NO CHAR(12) not null,
SO_STATUS NUMBER(8) not null,
COMPANY_ID NUMBER(8) not null,
DEALER_CODE VARCHAR2(16) not null,
CUSTOMER_ID NUMBER(12) not null,
VEHICLE_ID NUMBER(12) not null,
VIN VARCHAR2(17) not null,
SERVICE_TYPE NUMBER(8) not null,
FREE_AS_TYPE NUMBER(8),
INSURER_NAME VARCHAR2(128 CHAR),
ADVISOR_NAME VARCHAR2(32 CHAR),
ARTIFICER_NAME VARCHAR2(32 CHAR),
COMING_TIME TIMESTAMP(6),
FINISHING_TIME TIMESTAMP(6),
CI_CHECKED NUMBER(1) default 0,
PLATE_NO VARCHAR2(32 CHAR),
BRAND_CODE VARCHAR2(32),
SERIES_CODE VARCHAR2(32),
MODEL_CODE VARCHAR2(32),
COLOR_CODE VARCHAR2(32),
VEHICLE_USES NUMBER(8),
ENGINE_NO VARCHAR2(32),
GEARBOX_NO VARCHAR2(64),
WARRANTY_START_DATE TIMESTAMP(6),
PRODUCTION_DATE TIMESTAMP(6),
SOLD_DATE TIMESTAMP(6),
NO_WARRANTY NUMBER(1) default 0,
NEW_ODOMETER NUMBER(1) default 0,
ODO_MILEAGE NUMBER(10,2),
MILEAGE_SUM NUMBER(10,2),
VC_REFRESHED NUMBER(1) default 0,
OWNER_NAME VARCHAR2(128 CHAR),
OWNER_GENDER NUMBER(8),
OWNER_MOBILE VARCHAR2(32),
OWNER_PHONE VARCHAR2(32),
COUNTRY NUMBER(8),
PROVINCE NUMBER(8),
CITY NUMBER(8),
COUNTY NUMBER(8),
OWNER_ADDRESS VARCHAR2(128 CHAR),
ZIP_CODE VARCHAR2(8),
REPAIR_MAN VARCHAR2(32 CHAR),
REPAIR_MAN_GANDER NUMBER(8),
REPAIR_MAN_MOBILE VARCHAR2(32),
REPAIR_MAN_PHONE VARCHAR2(32),
REMARK_128 VARCHAR2(128 CHAR),
SO_SUM_AMT NUMBER(10,2),
DISCOUNT_MODE_N VARCHAR2(64 CHAR),
DISCOUNT_RATE NUMBER(8,4),
DISCOUNT_AMT NUMBER(10,2),
ODD_AMT NUMBER(10,2),
SO_RC_AMT NUMBER(10,2),
SO_MH_AMT NUMBER(10,2),
SO_SP_AMT NUMBER(10,2),
SO_AD_AMT NUMBER(10,2),
SO_AX_AMT NUMBER(10,2),
SO_MG_AMT NUMBER(10,2),
PAY_MODE NUMBER(8),
COMPLETED_DATE TIMESTAMP(6),
COMPLETED_BY_N VARCHAR2(32 CHAR),
SETTLED_DATE TIMESTAMP(6),
SO_ST_NO CHAR(12),
SETTLED_BY_N VARCHAR2(32 CHAR),
APPRAISED_DATE TIMESTAMP(6),
APPRAISED_BY_N VARCHAR2(32 CHAR),
BEST_INQ_DATE TIMESTAMP(6),
NEXT_DATE_MT TIMESTAMP(6),
NEXT_MILEAGE_MT NUMBER(10,2),
SO_INQUIRING NUMBER(1) default 0,
NO_INQ_CAUSE VARCHAR2(64 CHAR),
DELAYED_CAUSE VARCHAR2(128 CHAR),
INSPECTED_DESC VARCHAR2(128 CHAR),
CUST_OPINION VARCHAR2(128 CHAR),
COMPLETED_DESC VARCHAR2(128 CHAR),
AT_STATION NUMBER(1) default 1,
OIL_SCALE NUMBER(8),
TAKING_SCRAP NUMBER(1) default 0,
SO_FAULT_DESC VARCHAR2(128 CHAR),
SO_DIAGNOSES VARCHAR2(128 CHAR),
SO_FAULT_CAUSE VARCHAR2(128 CHAR),
SO_CHECK_DESC VARCHAR2(128 CHAR),
UPLOAN_TIMES NUMBER(8) default 0,
CREATED_BY NUMBER(8),
CREATED_TIME TIMESTAMP(6),
UPDATED_BY NUMBER(8),
UPDATED_TIME TIMESTAMP(6),
VERSION NUMBER(8) default 0,
SS_COMPANY_ID NUMBER(8) default -1
)
PARTITION BY RANGE (COMING_TIME)
( 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')),
PARTITION PART_2014_2 VALUES LESS THAN (to_date('2014-07-01','yyyy-mm-dd')),
PARTITION PART_2014_3 VALUES LESS THAN (to_date('2014-10-01','yyyy-mm-dd')),
PARTITION PART_2014_4 VALUES LESS THAN (to_date('2015-01-01','yyyy-mm-dd'))
)
tablespace dcs01;
2 创建全局索引
SQL> alter table tmp2
2 add constraint PK_tmp2 primary key (SO_NO, DEALER_ID)
3 using index global partition by range(DEALER_ID)
4 (PARTITION PART_1 VALUES LESS THAN (2001),
5 PARTITION PART_2 VALUES LESS THAN (3001),
6 PARTITION PART_maxvalue VALUES LESS THAN (maxvalue)
7 )
8 ;
alter table tmp2
add constraint PK_tmp2 primary key (SO_NO, DEALER_ID)
using index global partition by range(DEALER_ID)
(PARTITION PART_1 VALUES LESS THAN (2001),
PARTITION PART_2 VALUES LESS THAN (3001),
PARTITION PART_maxvalue VALUES LESS THAN (maxvalue)
)
ORA-14038: GLOBAL partitioned index must be prefixed
或
SQL> alter table tmp2
2 add constraint PK_tmp2 primary key (SO_NO, DEALER_ID)
3 using index global partition by hash(DEALER_ID)
4 (PARTITION PART_1 ,
5 PARTITION PART_2
6 );
alter table tmp2
add constraint PK_tmp2 primary key (SO_NO, DEALER_ID)
using index global partition by hash(DEALER_ID)
(PARTITION PART_1 ,
PARTITION PART_2
)
ORA-14038: GLOBAL partitioned index must be prefixed
或
SQL> alter table tmp2
2 add constraint PK_tmp2 primary key (SO_NO, DEALER_ID)
3 using index global partition by hash(DEALER_ID,SO_NO)
4 (PARTITION PART_1 ,
5 PARTITION PART_2
6 );
alter table tmp2
add constraint PK_tmp2 primary key (SO_NO, DEALER_ID)
using index global partition by hash(DEALER_ID,SO_NO)
(PARTITION PART_1 ,
PARTITION PART_2
)
ORA-14038: GLOBAL partitioned index must be prefixed
注:全局索引必须要有SO_NO第一个字段为分区键,如下:
SQL> alter table tmp2
2 add constraint PK_tmp2 primary key (SO_NO, DEALER_ID)
3 using index global partition by hash(SO_NO)
4 (PARTITION PART_1 ,
5 PARTITION PART_2
6 );
Table altered