aisDbOracle.sql

CREATE TABLE AIS
(
MessageType NUMBER(11), 
MMSI NUMBER(11), 
NavStatus NUMBER(11), 
ROT FLOAT, 
SOG FLOAT, 
lon FLOAT, 
lat FLOAT, 
COG FLOAT, 
true_heading NUMBER(11), 
DateTime NUMBER(20), 
IMO NUMBER(11), 
VesselName VARCHAR2(50), 
VesselTypeInt NUMBER(11), 
shipLength NUMBER(11), 
shipWidth NUMBER(11), 
Bow NUMBER(11), 
Stern NUMBER(11), 
Port NUMBER(11), 
Starboard NUMBER(11), 
Draught NUMBER(11), 
Destination VARCHAR2(50), 
Callsign VARCHAR2(50), 
PosAccuracy NUMBER(11), 
ETA NUMBER(11), 
PosFixType NUMBER(11),
AISGUID VARCHAR2(36) 
)
PARTITION BY RANGE (DateTime)
INTERVAL(7200)
 (
  PARTITION ais_p1 values less than (1330480800),
PARTITION ais_p2 values less than (1330488000)
) NOLOGGING;
CREATE INDEX LATAIS_IX ON AIS (lat) NOLOGGING;
CREATE INDEX LONAIS_IX ON AIS (lon) NOLOGGING;
CREATE INDEX DATEAIS_IX ON AIS (DateTime) NOLOGGING;
CREATE INDEX MMSIAIS_IX ON AIS (MMSI) NOLOGGING;
CREATE BITMAP INDEX MTAIS_IX ON (MESSAGETYPE) LOCAL NOLOGGING;