oracle基础知识积累-创建表

场景:
    基础知识查询
动机:
    纯粹基础知识积累,便于后续查询.在很多场合,负责人们都在谈论去IOE话题,
所以,难免使用场合会或多或少会减少,于是按照习惯的思路记录一下.
环境: 
    Oracle Database 11g; PL/SQL Developer
1.创建表-常规建表sql

create table D_LOG_INFO
(
  task_id      NUMBER(16) not null,
  begin_time      DATE,
  end_time        DATE,
  flag       VARCHAR2(8),
  fail_info       VARCHAR2(512),
  data_count NUMBER(16),
  table_name      VARCHAR2(256)
);
-- Add comments to the table 
comment on table D_LOG_INFO
  is '任务日志信息';
-- Add comments to the columns 
comment on column D_LOG_INFO.task_id
  is '任务标识';
comment on column D_LOG_INFO.begin_time
  is '记录任务开始时间';
comment on column D_LOG_INFO.end_time
  is '记录任务结束时间';
comment on column D_LOG_INFO.flag
  is '执行标志 0：失败、1：成功';
comment on column D_LOG_INFO.fail_info
  is '记录任务失败信息';
comment on column D_LOG_INFO.data_count
  is '记录数据条数';
comment on column D_LOG_INFO.table_name
  is '本次任务操作表名';
-- Create/Recreate primary, unique and foreign key constraints 
alter table D_LOG_INFO
  add constraint PK_D_LOG_INFO primary key (task_id);

2.创建表-联合主键与索引

create table SENSOR_COLLECT_DATA
(
  sensor_id         NUMBER(16) not null,
  data_date       DATE not null,
  data_type       NUMBER(2) not null,
  region          VARCHAR2(16) not null,
  s1       NUMBER(6,3),        
  s2       NUMBER(6,3),       
  s3       NUMBER(6,3),       
  s4       NUMBER(6,3),       
  s5       NUMBER(6,3),       
  s6       NUMBER(6,3),       
  s7       NUMBER(6,3),       
  s8       NUMBER(6,3),       
  s9       NUMBER(6,3),       
  s10      NUMBER(6,3),       
  s11      NUMBER(6,3),       
  s12      NUMBER(6,3)
);
comment on table SENSOR_COLLECT_DATA
  is '传感器采集数据';
comment on column SENSOR_COLLECT_DATA.sensor_id
  is '数据id实体唯一标识';
comment on column SENSOR_COLLECT_DATA.data_date
  is '数据日期';
comment on column SENSOR_COLLECT_DATA.data_type
  is '数据类型(1:温度、2:湿度)';
comment on column SENSOR_COLLECT_DATA.region
  is '传感器安装区域';
comment on column SENSOR_COLLECT_DATA.s1
  is '传感器采集的值1';
comment on column SENSOR_COLLECT_DATA.s2
  is '传感器采集的值2';
comment on column SENSOR_COLLECT_DATA.s3
  is '传感器采集的值3';
comment on column SENSOR_COLLECT_DATA.s4
  is '传感器采集的值4';
comment on column SENSOR_COLLECT_DATA.s5
  is '传感器采集的值5';
comment on column SENSOR_COLLECT_DATA.s6
  is '传感器采集的值6';
comment on column SENSOR_COLLECT_DATA.s7
  is '传感器采集的值7';
comment on column SENSOR_COLLECT_DATA.s8
  is '传感器采集的值8';
comment on column SENSOR_COLLECT_DATA.s9
  is '传感器采集的值9';
comment on column SENSOR_COLLECT_DATA.s10
  is '传感器采集的值10';
comment on column SENSOR_COLLECT_DATA.s11
  is '传感器采集的值11';
comment on column SENSOR_COLLECT_DATA.s12
  is '传感器采集的值12';
create index INDEX_SENSOR_COLLECT_DATA_01 on SENSOR_COLLECT_DATA (sensor_id);
create index INDEX_SENSOR_COLLECT_DATA_02 on SENSOR_COLLECT_DATA (sensor_id, data_date);
alter table SENSOR_COLLECT_DATA
  add constraint PK_SENSOR_COLLECT_DATA_03 primary key (sensor_id, data_date, data_type, region);

3.创建表-分区表

create table SENSOR_COLLECT_DATA_DAY
(
  sensor_id         NUMBER(16) not null,
  data_date       DATE not null,
  data_type       NUMBER(2) not null,
  region          VARCHAR2(16) not null,
  s1       NUMBER(6,3),        
  s2       NUMBER(6,3),       
  s3       NUMBER(6,3),       
  s4       NUMBER(6,3),       
  s5       NUMBER(6,3),       
  s6       NUMBER(6,3),       
  s7       NUMBER(6,3),       
  s8       NUMBER(6,3),       
  s9       NUMBER(6,3),       
  s10      NUMBER(6,3),       
  s11      NUMBER(6,3),       
  s12      NUMBER(6,3)
)
partition by range (DATA_DATE)
(
  partition P20190826 values less than (TO_DATE(' 2019-08-27 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
    tablespace USERS
    pctfree 10
    initrans 1
    maxtrans 255
    storage
    (
      initial 1M
      next 1M
      minextents 1
      maxextents unlimited
    ),
 partition P20190827 values less than (TO_DATE(' 2019-08-28 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
    tablespace USERS
    pctfree 10
    initrans 1
    maxtrans 255
    storage
    (
      initial 1M
      next 1M
      minextents 1
      maxextents unlimited
    )
);
comment on table SENSOR_COLLECT_DATA_DAY
  is '传感器采集数据';
comment on column SENSOR_COLLECT_DATA_DAY.sensor_id
  is '数据id实体唯一标识';
comment on column SENSOR_COLLECT_DATA_DAY.data_date
  is '数据日期';
comment on column SENSOR_COLLECT_DATA_DAY.data_type
  is '数据类型(1:温度、2:湿度)';
comment on column SENSOR_COLLECT_DATA_DAY.region
  is '传感器安装区域';
comment on column SENSOR_COLLECT_DATA_DAY.s1
  is '传感器采集的值1';
comment on column SENSOR_COLLECT_DATA_DAY.s2
  is '传感器采集的值2';
comment on column SENSOR_COLLECT_DATA_DAY.s3
  is '传感器采集的值3';
comment on column SENSOR_COLLECT_DATA_DAY.s4
  is '传感器采集的值4';
comment on column SENSOR_COLLECT_DATA_DAY.s5
  is '传感器采集的值5';
comment on column SENSOR_COLLECT_DATA_DAY.s6
  is '传感器采集的值6';
comment on column SENSOR_COLLECT_DATA_DAY.s7
  is '传感器采集的值7';
comment on column SENSOR_COLLECT_DATA_DAY.s8
  is '传感器采集的值8';
comment on column SENSOR_COLLECT_DATA_DAY.s9
  is '传感器采集的值9';
comment on column SENSOR_COLLECT_DATA_DAY.s10
  is '传感器采集的值10';
comment on column SENSOR_COLLECT_DATA_DAY.s11
  is '传感器采集的值11';
comment on column SENSOR_COLLECT_DATA_DAY.s12
  is '传感器采集的值12';

create index INDEX_SENSOR_COLLECT_DATA_DAY1 on SENSOR_COLLECT_DATA_DAY (sensor_id);
create index INDEX_SENSOR_COLLECT_DATA_DAY2 on SENSOR_COLLECT_DATA_DAY (sensor_id, data_date);
alter table SENSOR_COLLECT_DATA_DAY
  add constraint PK_SENSOR_COLLECT_DATA_DAY3 primary key (sensor_id, data_date, data_type, region);

以上,感谢.