Oracle建表最正确的姿势

案例1：

create table CM.F_5_C_N_RIPORT_NRM
(
  DN                   VARCHAR2(512) not null,
  SubNetwork_UK        VARCHAR2(128),
  ManagedElement_uk    VARCHAR2(256),
  InventoryUnitRack_UK VARCHAR2(256),
  InventoryUnitShelf_UK VARCHAR2(256),
  InventoryUnitPack_UK VARCHAR2(256),
  RiPort_UK    		   VARCHAR2(128),
  OMC_UK               VARCHAR2(128),
  OMC_NAME             VARCHAR2(128),
  VENDOR_UK            VARCHAR2(128),
  VENDOR_NAME          VARCHAR2(128),
  START_TIME           DATE not null,
  END_TIME             DATE,
  TIME_STAMP           DATE default sysdate,
  RMUID                VARCHAR2(512),
  ID                   VARCHAR2(256),
  IrPortRate           VARCHAR2(256)
)
partition by range (START_TIME)
(
  partition P_1D_20190506 values less than (TO_DATE(' 2019-05-07 00:00:00', 'SYYYY-MM-DD HH24:MI:SS'))
);
comment on table CM.F_5_C_N_RIPORT_NRM
  is '无线接口端口';
comment on column CM.F_5_C_N_RIPORT_NRM.ID
  is '标识符';
comment on column CM.F_5_C_N_RIPORT_NRM.IrPortRate
  is '端口速率';
create unique index CM.F_5_C_N_RIPORT_NRM_0 on CM.F_5_C_N_RIPORT_NRM (START_TIME, RiPort_UK) local;

解析：

创建分区表
partition by range (START_TIME)
(
  partition P_1D_20190506 values less than (TO_DATE(' 2019-05-07 00:00:00', 'SYYYY-MM-DD HH24:MI:SS'))
);

创建本地索引，可以加快查询速度
create unique index CM.F_5_C_N_RIPORT_NRM_0 on CM.F_5_C_N_RIPORT_NRM (START_TIME, RiPort_UK) local;

案例2：

create table pm.INTONE_RTP_SUB_paidan (
DATA_TIME	DATE,
CGI	VARCHAR2(64),
MAX_SLICE_SINGLE_RATE	NUMBER,
MIN_SLICE_SINGLE_RATE	NUMBER,
AVG_SLICE_SINGLE_TIME	NUMBER,
MAX_SLICE_SUM	NUMBER
)
partition by range (DATA_TIME)
(
  partition P_1D_20220426 values less than (TO_DATE(' 2022-04-27 00:00:00', 'SYYYY-MM-DD HH24:MI:SS'))
) tablespace PM_LTE_TBS ;

comment on table PM.INTONE_RTP_SUB_paidan
  is 'VOLTE差小区派单';
comment on column PM.INTONE_RTP_SUB_paidan.MAX_SLICE_SINGLE_RATE
  is '切片单通率最大值';
comment on column PM.INTONE_RTP_SUB_paidan.MIN_SLICE_SINGLE_RATE
  is '切片单通率最小值';
  comment on column PM.INTONE_RTP_SUB_paidan.AVG_SLICE_SINGLE_TIME
  is '日均切片单通次数';
  comment on column PM.INTONE_RTP_SUB_paidan.MAX_SLICE_SUM
  is '切片总次数最大值';
create unique index PM.INTONE_RTP_SUB_paidan_idx on pm.INTONE_RTP_SUB_paidan (DATA_TIME, cgi) local ;

解析：

partition by range (DATA_TIME)
(
  partition P_1D_20220426 values less than (TO_DATE(' 2022-02-27 00:00:00', 'SYYYY-MM-DD HH24:MI:SS'))
) tablespace PM_LTE_TBS 

指定表存储的表空间：PM_LTE_TBS ，不指定为默认表空间

PS：
由于创建表分区时已经指定了表空间，创建索引时指定为local本地索引，所以索引的表空间与分区的表空间一致。

若在创建索引时未加local，就需要指定表空间。如：

CREATE UNIQUE INDEX  NB_MRS_RIPSC_UIDX  ON NB_MRS_RIPSC (ENBID,CELLID,EARFCN,SUBCARRIERNBR,START_TIME) tablespace  LTE_MR;