Define view: Storage allocation of SEGMENTs by usage i.e. INT STR IOT approach

create or replace view my_storage_allocation as
select what_is_it, count(*) number_segments, sum(blocks) blocks
from
(
select segment_type, 
       decode ( INSTR ( segment_name, 'IOT' ), 0, NULL, 'IOT one segment only' )  ||
       decode ( INSTR ( segment_name, 'STR' ), 0, NUll, 'STR table and index using STRING' )  ||
       decode ( INSTR ( segment_name, 'INT' ), 0, NULL, 'INT table and index using INTEGER' )  what_is_it,
       segment_name,
       blocks
from user_segments  
)
group by what_is_it;

Clean up the data model - drop and purge table %INT

drop table T_events_int purge;

Clean up the data model - drop and purge table %STR

drop table T_events_str purge;

Clean up the data model - drop and purge table %IOT

drop table T_events_iot purge;

Create table - best practise for Primary Key - generate the values & use INTEGER

create table T_events_int  
( event_id integer GENERATED BY DEFAULT AS IDENTITY (START WITH 1) NOT NULL,  
  event_date date not null,    
  sensor_type varchar2(32) not null,  
  sensor_id  integer not null,   
  reading_val varchar2(32) not null,
  CONSTRAINT PK_events_int PRIMARY KEY ( event_id )
);

Insert 10k records into T_EVENTS_INT table

insert into T_events_int ( event_date, sensor_type, sensor_id, reading_val ) 
SELECT sysdate + round( date_incr / 24 / 8, 6 ), 
       'voltmeter',
       round( dbms_random.value(1,100), 0 ),
       round( dbms_random.value(1,10), 6 ) 
FROM   ( select rownum date_incr FROM dual CONNECT BY LEVEL <= 10000 );

Gather table statistics for table T_EVENTS_INT

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

Create table using a lengthy string as PK - is that best practise? Depends ...

create table T_events_str 
( event_id varchar2(36) NOT NULL,  
  event_date date not null,    
  sensor_type varchar2(32) not null,  
  sensor_id  integer not null,   
  reading_val varchar2(32) not null,
  CONSTRAINT PK_events_str PRIMARY KEY ( event_id )
);

Insert 10k records into T_EVENTS_STR table

insert into T_events_str ( event_id, event_date, sensor_type, sensor_id, reading_val ) 
SELECT DBMS_RANDOM.STRING ( 'p', 36 ),
       sysdate + round( date_incr / 24 / 8, 6 ), 
       'voltmeter',
       round( dbms_random.value(1,100), 0 ),
       round( dbms_random.value(1,10), 6 ) 
FROM   ( select rownum date_incr FROM dual CONNECT BY LEVEL <= 10000 );

Gather table statistics for table T_EVENTS_STR

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

create table T_events_iot  
( event_id integer GENERATED BY DEFAULT AS IDENTITY (START WITH 1) NOT NULL,  
  event_date date not null,    
  sensor_type varchar2(32) not null,  
  sensor_id  integer not null,   
  reading_val varchar2(32) not null,
  CONSTRAINT PK_events_iot PRIMARY KEY ( event_id )
)
ORGANIZATION INDEX;

Insert 10k records into T_EVENTS_IOT table

insert into T_events_iot ( event_date, sensor_type, sensor_id, reading_val ) 
SELECT sysdate + round( date_incr / 24 / 8, 6 ), 
       'voltmeter',
       round( dbms_random.value(1,100), 0 ),
       round( dbms_random.value(1,10), 6 ) 
FROM   ( select rownum date_incr FROM dual CONNECT BY LEVEL <= 10000 );

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

select * from my_storage_allocation;

Let us try to COMPRESS the table %INT

alter table T_EVENTS_INT compress;

Let us try to COMPRESS the table %STR

alter table T_EVENTS_STR compress;

Let us try to COMPRESS the table %IOT

alter table T_EVENTS_IOT compress;

select * from my_storage_allocation;

Thank you

select 'That is all folks' from dual;