Oracle 分区索引

-- Create table
create 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 table
create 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		   NO


SQL> 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		   YES

SQL> 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						  110263		INDEX
IDX_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 a30
SQL> 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						  110263		INDEX
IDX_ESB2_TRANS_LOG	       ESB2_TRANS_LOG_170801		  110264	 110264 INDEX PARTITION
IDX_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.