Oracle 分区索引

-- Create tablecreate table TLCB_MON_LINUX(  sdate        DATE,  ip           VARCHAR2(20),  processcpu   CLOB,  processmem   CLOB,  port         CLOB,  countprocess VARCHAR2(100),  countport    VARCHAR2(100))partition by range (SDATE)(  partition P20170524 values less than (TO_DATE(' 2017-05-25 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))    tablespace USERS    pctfree 10    initrans 1    maxtrans 255,发我和小小 ,-- Create tablecreate table ESB2_TRANS_LOG(  esbflowno        VARCHAR2(100) not null,  flowstepid       VARCHAR2(3) not null,  locationid       VARCHAR2(20) not null,  channelid        VARCHAR2(40),  serviceid        VARCHAR2(40),  respstatus       CHAR(1),  respcode         VARCHAR2(30),  respmsg          VARCHAR2(512),  operstamp        TIMESTAMP(3) default systimestamp not null,  logicsystem      VARCHAR2(40),  realsystem       VARCHAR2(40),  trans_date       DATE default sysdate not null,  loop             VARCHAR2(20))partition by range (TRANS_DATE)(  partition ESB2_TRANS_LOG_170801 values less than (TO_DATE(' 2017-08-02 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))  )create index IDX_ESB2_TRANS_LOG on ESB2_TRANS_LOG (ESBFLOWNO, FLOWSTEPID);select owner,index_name,table_name,PARTITIONED from dba_indexes where table_name=upper('esb2_trans_log');SQL> OWNER       INDEX_NAME    TABLE_NAME   PAR------------------------------ -------------------- ------------------------------ ---TLCBUSER       IDX_ESB2_TRANS_LOG   ESB2_TRANS_LOG   NOSQL> create index IDX_ESB2_TRANS_LOG on ESB2_TRANS_LOG (ESBFLOWNO, FLOWSTEPID) local;Index created.SQL> select owner,index_name,table_name,PARTITIONED from dba_indexes where table_name=upper('esb2_trans_log');OWNER       INDEX_NAME    TABLE_NAME   PAR------------------------------ -------------------- ------------------------------ ---TLCBUSER       IDX_ESB2_TRANS_LOG   ESB2_TRANS_LOG   YESSQL> select object_name,subobject_name,object_id,data_object_id,object_type from user_objects where object_name='IDX_ESB2_TRANS_LOG';OBJECT_NAME       SUBOBJECT_NAME       OBJECT_ID DATA_OBJECT_ID OBJECT_TYPE------------------------------ ------------------------------ ---------- -------------- -------------------IDX_ESB2_TRANS_LOG  110263INDEXIDX_ESB2_TRANS_LOG       ESB2_TRANS_LOG_170801  110264 110264 INDEX PARTITION添加新的分区:alter table ESB2_TRANS_LOG add partition  ESB2_TRANS_LOG_170802 values less than (TO_DATE(' 2017-08-03 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))SQL> col object_name format a30SQL> select object_name,subobject_name,object_id,data_object_id,object_type from user_objects where object_name='IDX_ESB2_TRANS_LOG';OBJECT_NAME       SUBOBJECT_NAME       OBJECT_ID DATA_OBJECT_ID OBJECT_TYPE------------------------------ ------------------------------ ---------- -------------- -------------------IDX_ESB2_TRANS_LOG  110263INDEXIDX_ESB2_TRANS_LOG       ESB2_TRANS_LOG_170801  110264 110264 INDEX PARTITIONIDX_ESB2_TRANS_LOG       ESB2_TRANS_LOG_170802  110266 110266 INDEX PARTITION添加主键:SQL> alter table ESB2_TRANS_LOG  add primary key (esbflowno) using index local;alter table ESB2_TRANS_LOG  add primary key (esbflowno) using index local*ERROR at line 1:ORA-14039: 分区列必须构成 UNIQUE 索引的关键字列子集Oracle 不支持在分区表上创建主键时不包含分区列不分区就可以:SQL> alter table ESB2_TRANS_LOG  add primary key (esbflowno);Table altered.