-- Oracle 12c: In Memory tables 
-- Create table with im memory (INMEMORY) option

CREATE TABLE o12c_im_tab1 
(idx         NUMBER GENERATED BY DEFAULT ON NULL AS IDENTITY,  
 ref_desc    VARCHAR2(100),
 tst_date    TIMESTAMP(3) DEFAULT SYSTIMESTAMP(3) NOT NULL
)
IN MEMORY
;


-- Creating table (CTAS) and enabling INMEMORY 

CREATE TABLE o12c_im_ctas1 
AS
SELECT
 level  idx,
 level||'0'||MOD(level,2)  ref_desc,
 SYSTIMESTAMP(3)  created_date
FROM DUAL
CONNECT BY level < 100001;

-- To enable in memory table option 

ALTER TABLE o12c_ctas1  INMEMORY;


-- Creating in memory table (CTAS) 

CREATE TABLE o12c_IM_ctas2
 INMEMORY
AS
SELECT
 level  idx,
 level||'0'||MOD(level,5)  ref_desc, 
 SYSTIMESTAMP(3)  created_date
FROM DUAL
CONNECT BY level < 100001;


-- To disable in memory table option 

ALTER TABLE o12c_im_ctas  NO INMEMORY;


-- Check in memory table options for created tables

SELECT
  table_name,
  inmemory,
  inmemory_priority,
  inmemory_distribute,
  inmemory_compression,
  inmemory_duplicate  
FROM  all_tables
WHERE INSTR(table_name,'O12C_IM_')>0
;

-- Create a table for in memory column (revised Oracle version)

CREATE TABLE emp_comp_detail (
 emp_id        NUMBER(15) PRIMARY KEY
,emp_name      VARCHAR2(25) NOT NULL
,job_title     VARCHAR2(50)
,salary_amt    NUMBER(15,2)
,hourly_rate   NUMBER(10,2)
                  GENERATED ALWAYS AS (salary_amt/40/52)
,dept_num      VARCHAR2(50) NOT NULL
)
 INMEMORY;

ALTER TABLE emp_comp_detail INMEMORY(hourly_rate);

SELECT table_name, column_name, inmemory_compression
FROM  v$im_column_level
;