场景:
基础知识查询
动机:
纯粹基础知识积累,便于后续查询.在很多场合,负责人们都在谈论去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);
以上,感谢.