SET ECHO OFF
REM ***************************************************************************
REM ***************************** T R I V A D I S *****************************
REM ******************** Oracle Database 12c New Features *********************
REM ************************ http://www.trivadis.com **************************
REM ***************************************************************************
REM
REM Privileges..: DBA
REM File name...: imcs_population.sql
REM Author......: Christian Antognini
REM Date........: September 2015
REM Description.: This script shows how to define that the data stored in some
REM               tables is made eligible to be loaded in the IMCS and how to 
REM               trigger the population.
REM Notes.......: -
REM Parameters..: -
REM Requirements: refer to imcs_prepare_schema.sql
REM
REM Changes:
REM DD.MM.YYYY Author Description
REM ---------------------------------------------------------------------------
REM 
REM ***************************************************************************

COLUMN segment_name FORMAT A12
COLUMN gb FORMAT 990.999
COLUMN mb FORMAT 999,990.999
COLUMN im_mb FORMAT 999,990.999
COLUMN non_pop_mb FORMAT 999,990.999

@connect.sql

SET ECHO ON TIMING OFF TERMOUT ON SERVEROUTPUT OFF LINESIZE 128 PAGESIZE 128

ALTER TABLE sales NO INMEMORY;
ALTER TABLE customers NO INMEMORY;
ALTER TABLE times NO INMEMORY;
ALTER TABLE products NO INMEMORY;
ALTER TABLE channels NO INMEMORY;
ALTER TABLE promotions NO INMEMORY;

PAUSE

SELECT sum(bytes)/1E9 AS gb
FROM user_segments 
WHERE segment_name IN ('SALES', 'CUSTOMERS', 'TIMES', 'PRODUCTS', 'CHANNELS', 'PROMOTIONS');

PAUSE

ALTER TABLE sales INMEMORY 
                  PRIORITY CRITICAL 
                  MEMCOMPRESS FOR QUERY HIGH;

PAUSE

ALTER TABLE customers INMEMORY 
                      PRIORITY HIGH 
                      MEMCOMPRESS FOR QUERY HIGH;

PAUSE

ALTER TABLE times INMEMORY 
                  PRIORITY MEDIUM 
                  MEMCOMPRESS FOR CAPACITY HIGH;

PAUSE

ALTER TABLE products INMEMORY 
                     PRIORITY LOW 
                     MEMCOMPRESS FOR CAPACITY HIGH;

PAUSE

ALTER TABLE channels INMEMORY 
                     PRIORITY NONE 
                     MEMCOMPRESS FOR CAPACITY HIGH;

PAUSE

ALTER TABLE promotions INMEMORY 
                       PRIORITY NONE 
                       MEMCOMPRESS FOR CAPACITY HIGH;

PAUSE

BEGIN
  dbms_inmemory.populate(schema_name => user, table_name => 'SALES');
  dbms_inmemory.populate(schema_name => user, table_name => 'CUSTOMERS');
  dbms_inmemory.populate(schema_name => user, table_name => 'PRODUCTS');
  dbms_inmemory.populate(schema_name => user, table_name => 'TIMES');
END;
/

PAUSE

SELECT inst_id, segment_name, populate_status, 
       sum(bytes)/1E6 AS mb, 
       sum(inmemory_size)/1E6 AS im_mb, 
       sum(bytes_not_populated)/1E6 AS not_pop_mb
FROM gv$im_user_segments
WHERE segment_name IN ('SALES', 'CUSTOMERS', 'PRODUCTS', 'TIMES')
GROUP BY inst_id, segment_name, populate_status
ORDER BY inst_id, segment_name;

