Drop table EVENTS - we rebuild it later on and populate it.

drop table events  purge;

Drop table UNWANTED_EVENTS - we rebuild it later on and populate it.

drop table unwanted_events  purge;

Drop table SENSORS - we rebuild it later on and populate it.

drop table sensors purge;

Create non-partitined table SENSORS.

create table sensors 
( sensor_id integer GENERATED BY DEFAULT AS IDENTITY (START WITH 1) NOT NULL PRIMARY KEY, 
  install_date   date not null, 
  uninstall_date date, 
  sensor_type varchar2(32) not null,  
  reading_uom varchar2(32) not null );

Create partitioned table EVENTS. We apply RANGE partitioning by calendar year. It references table SENSORS.

create table events  
( event_id integer GENERATED BY DEFAULT AS IDENTITY (START WITH 1) NOT NULL PRIMARY KEY, 
  event_date date not null,   
  sensor_id  integer not null,  
  reading_val varchar2(32) not null, 
  CONSTRAINT fk_sensor
  FOREIGN KEY ( sensor_id )
  REFERENCES sensors ( sensor_id )
)
partition by range ( event_date )
(PARTITION observations_PAST    VALUES LESS THAN (TO_DATE('20000101','YYYYMMDD')),
 PARTITION observations_CY_2000 VALUES LESS THAN (TO_DATE('20010101','YYYYMMDD')),
 PARTITION observations_CY_2001 VALUES LESS THAN (TO_DATE('20020101','YYYYMMDD')),
 PARTITION observations_CY_2002 VALUES LESS THAN (TO_DATE('20030101','YYYYMMDD')),
 PARTITION observations_CY_2003 VALUES LESS THAN (TO_DATE('20040101','YYYYMMDD')),
 PARTITION observations_CY_2004 VALUES LESS THAN (TO_DATE('20050101','YYYYMMDD')),
 PARTITION observations_CY_2005 VALUES LESS THAN (TO_DATE('20060101','YYYYMMDD')),
 PARTITION observations_CY_2006 VALUES LESS THAN (TO_DATE('20070101','YYYYMMDD')),
 PARTITION observations_CY_2007 VALUES LESS THAN (TO_DATE('20080101','YYYYMMDD')),
 PARTITION observations_CY_2008 VALUES LESS THAN (TO_DATE('20090101','YYYYMMDD')),
 PARTITION observations_CY_2009 VALUES LESS THAN (TO_DATE('20100101','YYYYMMDD')),
 PARTITION observations_CY_2010 VALUES LESS THAN (TO_DATE('20110101','YYYYMMDD')),
 PARTITION observations_FUTURE  VALUES LESS THAN ( MAXVALUE ) );

Add virtual columns CALENDAR_YEAR and THE_HOUR - make it look nicer with virtual columns

alter table events add cal_year  as ( to_number( to_char( event_date, 'YYYY' )));
alter table events add cal_month as ( to_number( to_char( event_date, 'MM'   )));
alter table events add cal_day   as ( to_number( to_char( event_date, 'DD'   )));
alter table events add cal_hour  as ( to_number( to_char( event_date, 'HH24' )));

Create first of four sets of SENSORS.

insert into sensors ( sensor_id, install_date, uninstall_date, sensor_type, reading_uom  ) 
SELECT 1000000 + rownum,  
       to_date ('20050110','YYYYMMDD'),  
       null,
       'ammeter',  
       'Ampere'  
FROM dual CONNECT BY LEVEL <= 12; 

Create second of four sets of SENSORS.

insert into sensors ( sensor_id, install_date, uninstall_date, sensor_type, reading_uom  ) 
SELECT 2000000 + rownum,  
       to_date ('20050108','YYYYMMDD'),  
       null,
       'voltmeter',  
       'Volt'  
FROM dual CONNECT BY LEVEL <= 15; 

Create third of four sets of SENSORS.

insert into sensors ( sensor_id, install_date, uninstall_date, sensor_type, reading_uom  ) 
SELECT 3000000 + rownum,  
       to_date ('20050115','YYYYMMDD'),  
       null,
       'thermometer',  
       'Kelvin'  
FROM dual CONNECT BY LEVEL <= 47; 

Create forth of four sets of SENSORS.

insert into sensors ( sensor_id, install_date, uninstall_date, sensor_type, reading_uom  ) 
SELECT 4000000 + rownum,  
       to_date ('20050103','YYYYMMDD'),  
       null,
       'accelerometer',  
       'g'  
FROM dual CONNECT BY LEVEL <= 23; 

1st run for table SENSORS - statistics gathering

execute DBMS_STATS.GATHER_TABLE_STATS( null, tabname => 'sensors');

Create history of SENSOR driven EVENTS - for first set of SENSORS.

insert into events ( event_date, sensor_id, reading_val ) 
SELECT install_date + round( date_incr / 24 / 8, 6 ), 
       sensor_id, 
       round( dbms_random.value(1,10), 6 ) 
FROM   sensors,  
       ( select rownum date_incr FROM dual CONNECT BY LEVEL <= 250 )
WHERE  sensor_type = 'voltmeter';

Create history of SENSOR driven EVENTS - for second set of SENSORS.

insert into events ( event_date, sensor_id, reading_val ) 
SELECT install_date + round( date_incr / 24 / 6, 6 ), 
       sensor_id, 
       round( dbms_random.value(1,10) / 100, 4 ) 
FROM   sensors,  
       ( select rownum date_incr FROM dual CONNECT BY LEVEL <= 540 )
WHERE  sensor_type = 'ammeter';

Create history of SENSOR driven EVENTS - for third set of SENSORS - part 1 of 2

insert into events ( event_date, sensor_id, reading_val ) 
SELECT install_date + round( date_incr / 24 / 4, 6 ),  
       sensor_id, 
       round( 4 + dbms_random.value(-500,50) / 1000, 4 ) 
FROM   sensors,  
       ( select rownum date_incr FROM dual CONNECT BY LEVEL <= 160 )
WHERE  sensor_type = 'thermometer';

Create history of SENSOR driven EVENTS - for third set of SENSORS - part 2 of 2

insert into events ( event_date, sensor_id, reading_val ) 
SELECT install_date + 5 + round( date_incr / 24 / 4, 6 ),  
       sensor_id, 
       round( 4 + dbms_random.value(-1000,750) / 1000, 4 ) 
FROM   sensors,  
       ( select rownum date_incr FROM dual CONNECT BY LEVEL <= 160 )
WHERE  sensor_type = 'thermometer'
AND    mod(sensor_id, 7 ) in ( 1,2,4,5,6 );

Create history of SENSOR driven EVENTS - for forth set of SENSORS.

insert into events ( event_date, sensor_id, reading_val ) 
SELECT install_date + round( date_incr / 24 / 20, 6 ),  
       sensor_id, 
       round( 4 + dbms_random.value(1,2) / 1000, 4 ) 
FROM   sensors,  
       ( select rownum date_incr FROM dual CONNECT BY LEVEL <= 240 )
WHERE  sensor_type = 'accelerometer';

1st time statistics gathering

execute DBMS_STATS.GATHER_TABLE_STATS( null, tabname => 'events');

Provide basic information about table EVENTS - the POPULATED partitions

select partition_name populated_partition, sum( subpartition_count) num_sub_partitions, sum( num_rows  ) num_rows
from   user_tab_partitions 
where  num_rows > 0
group by cube( partition_name );

Provide basic information about table EVENTS - the EMPTY partitions

select partition_name empty_partition, sum( subpartition_count) num_sub_partitions
from   user_tab_partitions 
where  num_rows = 0
group by cube( partition_name );

Split CY 2005 into monthly partitions ONLINE in one go and SPLIT January in various ways

ALTER TABLE events SPLIT PARTITION observations_CY_2005 INTO 
  ( PARTITION observations_01d1_2005 VALUES LESS THAN ( TO_DATE('20050104','YYYYMMDD')),
    PARTITION observations_01d2_2005 VALUES LESS THAN ( TO_DATE('20050108','YYYYMMDD')),
    PARTITION observations_01d3_2005 VALUES LESS THAN ( TO_DATE('20050112','YYYYMMDD')),
    PARTITION observations_01d4a_2005 VALUES LESS THAN ( TO_DATE('20050113','YYYYMMDD')),
    PARTITION observations_01d4b_2005 VALUES LESS THAN ( TO_DATE('20050114','YYYYMMDD')),
    PARTITION observations_01d4c_2005 VALUES LESS THAN ( TO_DATE('20050115','YYYYMMDD')),
    PARTITION observations_01d4d_2005 VALUES LESS THAN ( TO_DATE('20050116','YYYYMMDD')),
    PARTITION observations_01d5_2005 VALUES LESS THAN ( TO_DATE('20050120','YYYYMMDD')),
    PARTITION observations_01d6_2005 VALUES LESS THAN ( TO_DATE('20050124','YYYYMMDD')),
    PARTITION observations_01d7_2005 VALUES LESS THAN ( TO_DATE('20050128','YYYYMMDD')),
    PARTITION observations_01x_2005 VALUES LESS THAN ( TO_DATE('20050201','YYYYMMDD')),
    PARTITION observations_02_2005 VALUES LESS THAN ( TO_DATE('20050301','YYYYMMDD')),
    PARTITION observations_03_2005 VALUES LESS THAN ( TO_DATE('20050401','YYYYMMDD')),
    PARTITION observations_04_2005 VALUES LESS THAN ( TO_DATE('20050501','YYYYMMDD')),
    PARTITION observations_05_2005 VALUES LESS THAN ( TO_DATE('20050601','YYYYMMDD')),
    PARTITION observations_06_2005 VALUES LESS THAN ( TO_DATE('20050701','YYYYMMDD')),
    PARTITION observations_07_2005 VALUES LESS THAN ( TO_DATE('20050801','YYYYMMDD')),
    PARTITION observations_08_2005 VALUES LESS THAN ( TO_DATE('20050901','YYYYMMDD')),
    PARTITION observations_09_2005 VALUES LESS THAN ( TO_DATE('20051001','YYYYMMDD')),
    PARTITION observations_10_2005 VALUES LESS THAN ( TO_DATE('20051101','YYYYMMDD')),
    PARTITION observations_11_2005 VALUES LESS THAN ( TO_DATE('20051201','YYYYMMDD')),
    PARTITION observations_12_2005
   )
online;

Demonstrate that you can split to any precison allowed with the DATE type

ALTER TABLE events SPLIT PARTITION observations_01d3_2005 INTO 
  ( PARTITION observations_01d3a_2005 VALUES LESS THAN 
                                     ( TO_DATE('20050108 12:00','YYYYMMDD HH24:MI')),
    PARTITION observations_01d3b_2005 VALUES LESS THAN 
                                     ( TO_DATE('20050108 18:00','YYYYMMDD HH24:MI')),
    PARTITION observations_01d3c_2005 VALUES LESS THAN 
                                     ( TO_DATE('20050109 00:00','YYYYMMDD HH24:MI')),
    PARTITION observations_01d3d_2005 VALUES LESS THAN 
                                     ( TO_DATE('20050109 06:00:15','YYYYMMDD HH24:MI:SS')),
    PARTITION observations_01d3e_2005 VALUES LESS THAN 
                                     ( TO_DATE('20050109 12:00:47','YYYYMMDD HH24:MI:SS')),
    PARTITION observations_01d3f_2005 VALUES LESS THAN 
                                     ( TO_DATE('20050109 18:00:23','YYYYMMDD HH24:MI:SS')),
    PARTITION observations_01d3x_2005
   )
online;

add a column that we use to define a status for a measurement

alter table events add  ( event_status varchar2(32) default 'trusted' );

update status of EVENT records (just to obtain a significant set of records having different status)

update events set event_status = 'not validated' 
where  mod ( event_id, 71 ) in ( 5, 13, 27, 43, 51, 54, 61 );

update status of EVENT records (just to obtain a significant set of records having different status)

update events set event_status = 'failed validation' 
where  mod ( event_id, 71 ) in ( 7, 9, 21, 35, 63, 68 );

BAD EXAMPLE 4 Filtered Partition Maintenance Operation - DANGEROUS stuff - "clean up a named partition"

select event_status, count(*)
from   events partition ( OBSERVATIONS_01D4D_2005 )
group by event_status;

create table unwanted_events as
   select *
   from   events partition ( OBSERVATIONS_01D4D_2005 )
   where  not( event_status = 'trusted' );

ALTER TABLE events
MOVE PARTITION OBSERVATIONS_01D4D_2005
INCLUDING ROWS WHERE event_status = 'trusted';

select event_status, count(*)
from   events partition ( OBSERVATIONS_01D4D_2005 )
group by event_status;

2nd time statistics gathering

execute DBMS_STATS.GATHER_TABLE_STATS( null, tabname => 'events');
execute DBMS_STATS.GATHER_TABLE_STATS( null, tabname => 'unwanted_events');

Provide basic information about table EVENTS - the POPULATED partitions

select partition_name populated_partition, sum( subpartition_count) num_sub_partitions, sum( num_rows  ) num_rows
from   user_tab_partitions 
where  num_rows > 0
group by cube( partition_name );

Provide basic information about table EVENTS - the EMPTY partitions

select partition_name empty_partition, sum( subpartition_count) num_sub_partitions
from   user_tab_partitions 
where  num_rows = 0
group by cube( partition_name );

A simple query (without any statistical or windowing functions)

select s.sensor_type,   
       to_char( min(e.event_date),    'YYYYMMDD')  earliest_day,   
       to_char( max(e.event_date),    'YYYYMMDD')  latest_day,  
       count(*) readings_during_day,  
       count( distinct e.sensor_id ) sensors_in_operation,
       count( distinct to_char( e.event_date, 'YYYYMMDD' ) ) days_having_readings, 
       count( distinct to_char( e.event_date, 'HH24'     ) ) hours_having_readings  
from   events e,
       sensors s
where  e.sensor_id = s.sensor_id
and    event_date between to_date( '20050102', 'YYYYMMDD' ) and to_date( '20050130', 'YYYYMMDD' )
and    event_status = 'trusted'
group by cube( sensor_type )  
order by sensor_type;

How is my data distributed by SENSOR_TYPE and CAL_DAY of events ? ... just the first TEN arbitrary records

Select * 
from   
(
select s.sensor_type,   
       e.cal_year,
       e.cal_month,
       e.cal_day,
       e.event_status,
       count(*) readings_during_day,  
       min( cal_hour ) earliest_hour, 
       max( cal_hour ) latest_hour, 
       count( distinct cal_hour ) hours_having_readings  
from   events  e,
       sensors s
where  event_date between to_date( '20050110', 'YYYYMMDD' ) and to_date( '20050114', 'YYYYMMDD' )
group by s.sensor_type, e.cal_year, e.cal_month, e.cal_day, e.event_status
)
where rownum < 11;

Query TEMPERATURES and check the MIN/MEDIAN/MAX readings on hours intervals

select s.sensor_id,   
       e.cal_year,
       e.cal_month,
       e.cal_day,
       count(*) readings_during_day,  
       min( cal_hour ) earliest_hour, 
       max( cal_hour ) latest_hour, 
       count( distinct cal_hour ) hours_having_readings,
       min( to_number( reading_val ))    lowest_temperature,
       median( to_number( reading_val )) median_temperature,
       max( to_number( reading_val ))    highest_temperature
from   events  e,
       sensors s
where  e.sensor_id= s.sensor_id
and    event_date between to_date( '20050110', 'YYYYMMDD' ) and to_date( '20050114', 'YYYYMMDD' )
and    mod( s.sensor_id, 10) in ( 1,2 )
and    s.sensor_type = 'thermometer'
and    e.event_status = 'trusted'
group by s.sensor_id, e.cal_year, e.cal_month, e.cal_day;

Create a view to determine a primitive TEMPERATURE GRADIENT per sensor.

create or replace view temperature_gradients as
select sensor_id,
       to_char( prv_2_date , 'YYYYMMDD HH24:MI:SS') interval_start,
       to_char( nxt_2_date , 'YYYYMMDD HH24:MI:SS') interval_stop,
       round( ( nxt_2_date - prv_2_date ) * 24 * 60 ) interval_in_minutes,
       curr_reading,
       ( nxt_2_reading - prv_2_reading ) temperature_change,
       round( ( abs(  nxt_2_reading - prv_2_reading ) / curr_reading ) / round( ( nxt_2_date - prv_2_date ) * 24 * 60 ), 8 ) temperature_gradient
from
(
select s.sensor_id,   
       LAG  ( e.event_date , 2, null ) OVER (PARTITION BY e.sensor_id ORDER BY e.event_date ) prv_2_date,
       LAG  ( e.event_date , 1, null ) OVER (PARTITION BY e.sensor_id ORDER BY e.event_date ) prv_1_date,
       e.event_date curr_date,
       LEAD ( e.event_date , 1, null ) OVER (PARTITION BY e.sensor_id ORDER BY e.event_date ) nxt_1_date,
       LEAD ( e.event_date , 2, null ) OVER (PARTITION BY e.sensor_id ORDER BY e.event_date ) nxt_2_date,
       LAG  ( to_number( e.reading_val ), 2, null ) OVER (PARTITION BY e.sensor_id ORDER BY e.event_date ) prv_2_reading,
       LAG  ( to_number( e.reading_val ), 1, null ) OVER (PARTITION BY e.sensor_id ORDER BY e.event_date ) prv_1_reading,
       to_number( e.reading_val ) curr_reading,
       LEAD ( to_number( e.reading_val ), 1, null ) OVER (PARTITION BY e.sensor_id ORDER BY e.event_date ) nxt_1_reading,
       LEAD ( to_number( e.reading_val ), 2, null ) OVER (PARTITION BY e.sensor_id ORDER BY e.event_date ) nxt_2_reading
from   events  e,
       sensors s
where  e.sensor_id= s.sensor_id
and    s.sensor_type = 'thermometer'
and    e.event_status = 'trusted'
)
where  ( ( ( prv_2_reading  <= prv_1_reading ) AND ( prv_1_reading  <= curr_reading  ) AND
           ( curr_reading   <= nxt_1_reading ) AND ( nxt_1_reading  <= nxt_2_reading ) )         OR
         ( ( prv_2_reading  >= prv_1_reading ) AND ( prv_1_reading  >= curr_reading  ) AND
           ( curr_reading   >= nxt_1_reading ) AND ( nxt_1_reading  >= nxt_2_reading ) )       );


       
       

A simple query on the UNWANTED event data(without any statistical or windowing functions)

select s.sensor_type,
       e.event_status,   
       to_char( min(e.event_date),    'YYYYMMDD')  earliest_day,   
       to_char( max(e.event_date),    'YYYYMMDD')  latest_day,  
       count(*) readings_during_day,  
       count( distinct e.sensor_id ) sensors_in_operation,
       count( distinct to_char( e.event_date, 'YYYYMMDD' ) ) days_having_readings, 
       count( distinct to_char( e.event_date, 'HH24'     ) ) hours_having_readings  
from   unwanted_events e,
       sensors s
where  e.sensor_id = s.sensor_id
group by cube( sensor_type ) , event_status 
order by sensor_type;

Check for TRENDING TEMPERATURE having a relatively high gradient - just 10 arbitrary records

select *
from   temperature_gradients
where  abs ( temperature_gradient ) > 1 / 1000 / 1000
and    rownum < 11;

Merge a range of partitions into one for January 2005

alter table events
merge partitions observations_01d1_2005 TO observations_01x_2005
into  partition observations_01_2005
online;

execute DBMS_STATS.GATHER_TABLE_STATS( null, tabname => 'events');

Provide basic information about table EVENTS - the POPULATED partitions

select partition_name populated_partition, sum( subpartition_count) num_sub_partitions, sum( num_rows  ) num_rows
from   user_tab_partitions 
where  num_rows > 0
group by cube( partition_name );

Provide basic information about table EVENTS - the EMPTY partitions

select partition_name empty_partition, sum( subpartition_count) num_sub_partitions
from   user_tab_partitions 
where  num_rows = 0
group by cube( partition_name );

Thank you

select 'That is all folks' from dual;