一、建表
-- Create tablecreate table DIM_LICENSE_PLATE_REAL
(
LP_CAMERA_ID VARCHAR2(15) not null,
DATE_KEY NUMBER(8) not null,
TIME_KEY NUMBER(9) not null,
LICENSE_PLATE VARCHAR2(20) not null,
VEHICLE_TYPE CHAR(1) not null,
LICENSE_PLATE_COLOR VARCHAR2(2) not null,
DIRECTION CHAR(2) not null,
LANE VARCHAR2(2),
VEHICLE_SPEED NUMBER(4) not null,
VEHICLE_LENGTH NUMBER(4),
ALARM_TYPE VARCHAR2(4),
TSTRANS CHAR(1),
LICENSE_REGION CHAR(4) not null,
LICENSE_PLATE_LINK1 VARCHAR2(150) not null,
LICENSE_PLATE_LINK2 VARCHAR2(150),
LICENSE_PLATE_LINK3 VARCHAR2(150),
DEVICE_TYPE CHAR(2) not null
)
tablespace USERS
pctfree 10
initrans 1
maxtrans 255
storage
(
initial 64K
next 1M
minextents 1
maxextents unlimited
);
-- Create/Recreate primary, unique and foreign key constraints
alter table DIM_LICENSE_PLATE_REAL
add constraint DIM_LICENSE_PLATE_REAL_PK primary key (LP_CAMERA_ID, DATE_KEY, TIME_KEY, LICENSE_PLATE)
using index
tablespace USERS
pctfree 10
initrans 2
maxtrans 255
storage
(
initial 64K
next 1M
minextents 1
maxextents unlimited
);
二、插入数据
select count(*) from Dim_License_plate_real;
1 5390000条数据
三、分页查询
select *
from (select rownum test,LP_camera_id,
date_key,
time_key,
license_plate,
vehicle_type,
license_plate_color,
direction,
vehicle_speed,
vehicle_length,
alarm_type,
license_plate_link1,
license_plate_link3,
device_type
from Dim_License_plate_real
where date_key between 20131101 and 20131206
and time_key between 0 and 235959999
and vehicle_type = '1'
and rownum <= 10000
order by date_key, time_key)
where test >= 9800