ORA-14038: GLOBAL partitioned index must be prefixed
来源:互联网 发布:淘宝延长收货是几天啊 编辑:程序博客网 时间:2024/05/22 03:28
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
- ORA-14038: GLOBAL partitioned index must be prefixed
- ORA-01502& ORA-14086: a partitioned index may not be rebuilt as a whole
- Partitioned index & Partitioning index
- Hive Partitioned & Index
- 【分区管理】如何确定分区索引是Global还是Local,PREFIXED 还是NON-PREFIXED
- 【翻译自mos文章】关于分区索引:Global, Local, Prefixed and Non-Prefixed
- 解决:Page index must not be less than zero!
- ORA-01453: SET TRANSACTION must be first statement of transaction
- ORA-01453: SET TRANSACTION must be first statement of transaction
- ORA-39700: database must be opened with UPGRADE option
- ORA-01849: hour must be between 1 and 12
- ORA-39700:DATABASE MUST BE OPENED WITH UPGRADE OPTION
- Ora-26678 Streams capture process must be created first
- ORA-39700: database must be opened with UPGRADE option
- ORA-39700:DATABASE MUST BE OPENED WITH UPGRADE OPTION
- ORA-39700: database must be opened with UPGRADE option
- ORA-39700:database must be opened with UPGRADE option
- ORA-19527: physical standby redo log must be renamed
- 1017. Queueing at Bank (25)
- Struts2与Spring集成中的自动装配策略
- V4L2驱动的移植与应用(三)
- word-spacing
- java中判断字符串是否为数字的三种方法
- ORA-14038: GLOBAL partitioned index must be prefixed
- android 通过电话号码来获取联系人姓名
- PYTHON解析BAIDU的MP3列表
- STL stable_sort 稳定排序
- 内存泄露分析方法(android内存溢出)
- Android 跳转到通话记录详情页
- 由电闸跳闸 厕所停水 想到的。
- 三次握手
- OCP-1Z0-053-V13.02-45题