In SQL*Plus, log in to the database as the user who will own the table, and then execute the following SQL statement:

CREATE TABLE test_inmem (
     id        NUMBER(5) PRIMARY KEY,
     test_col  VARCHAR2(15))
  INMEMORY;

The preceding statement uses the defaults for the INMEMORY clause: MEMCOMPRESS FOR QUERY and PRIORITY NONE. Because PRIORITY is NONE, the database will not automatically populate the table.

Log in to SQL*Plus as the user that will own the new table, and then execute the following DDL statement:

CREATE TABLE range_sales
    ( prod_id        NUMBER(6)
    , cust_id        NUMBER
    , time_id        DATE
    , channel_id     CHAR(1)
    , promo_id       NUMBER(6)
    , quantity_sold  NUMBER(3)
    , amount_sold    NUMBER(10,2)
    ) 
PARTITION BY RANGE (time_id)
  (PARTITION SALES_Q4_1999 
      VALUES LESS THAN (TO_DATE('01-JAN-2015','DD-MON-YYYY'))
      INMEMORY MEMCOMPRESS FOR DML,
   PARTITION SALES_Q1_2000 
      VALUES LESS THAN (TO_DATE('01-APR-2015','DD-MON-YYYY'))
      INMEMORY MEMCOMPRESS FOR QUERY,
   PARTITION SALES_Q2_2000 
      VALUES LESS THAN (TO_DATE('01-JUL-2015','DD-MON-YYYY'))
      INMEMORY MEMCOMPRESS FOR CAPACITY,
   PARTITION SALES_Q3_2000 
      VALUES LESS THAN (TO_DATE('01-OCT-2015','DD-MON-YYYY'))
      NO INMEMORY,
   PARTITION SALES_Q4_2000 
      VALUES LESS THAN (MAXVALUE));

The preceding SQL specifies a different compression level for the first three partitions in the IM column store. The last two partitions are not eligible for population in the IM column store.

The following SQL script creates the comma-delimited flat file /tmp/data/sh_sales.csv from the sh.sales table. Execute the script as user sh.

SET HEAD OFF
SET PAGES 0
SET FEEDBACK OFF
SET TERMOUT OFF
SPOOL /tmp/data/sh_sales.csv
SELECT prod_id       || ',' || cust_id  || ',' || time_id || ',' || 
       channel_id    || ',' || promo_id || ',' || 
       quantity_sold || ',' || amount_sold 
FROM   sales;
SPOOL OFF

Using the sh_sales.csv file, the following SQL script creates the external table sh.admin_ext_sales with the INMEMORY option:

CONNECT  /  AS SYSDBA;
-- Set up directories and grant access to sh 
CREATE OR REPLACE DIRECTORY admin_dat_dir
    AS '/tmp/data'; 
CREATE OR REPLACE DIRECTORY admin_log_dir 
    AS '/tmp/log'; 
CREATE OR REPLACE DIRECTORY admin_bad_dir 
    AS '/tmp/bad'; 
GRANT READ ON DIRECTORY admin_dat_dir TO sh; 
GRANT WRITE ON DIRECTORY admin_log_dir TO sh; 
GRANT WRITE ON DIRECTORY admin_bad_dir TO sh;
-- sh connects. Provide the user password (sh) when prompted.
CONNECT sh
-- create the external table
DROP TABLE admin_ext_sales;
CREATE TABLE admin_ext_sales
                   (prod_id           NUMBER, 
                    cust_id           NUMBER,
                    time_id           DATE, 
                    channel_id        NUMBER,
                    promo_id          NUMBER,
                    quantity_sold     NUMBER(10,2),
                    amount_sold       NUMBER(10,2)
                   ) 
     ORGANIZATION EXTERNAL 
     ( 
       TYPE ORACLE_LOADER 
       DEFAULT DIRECTORY admin_dat_dir 
       ACCESS PARAMETERS 
       ( 
         records delimited by newline 
         badfile admin_bad_dir:'empxt%a_%p.bad' 
         logfile admin_log_dir:'empxt%a_%p.log' 
         fields terminated by ',' 
         missing field values are null 
         ( prod_id, cust_id,
           time_id char date_format date mask "dd-mon-yy", 
           channel_id, promo_id, quantity_sold, amount_sold 
         ) 
       ) 
       LOCATION ('sh_sales.csv') 
     ) 
     REJECT LIMIT UNLIMITED
     INMEMORY; 

The following query of ALL_EXTERNAL_TABLES shows that the admin_ext_sales table is enabled for INMEMORY:

COL OWNER FORMAT A10
COL TABLE_NAME FORMAT A15

SELECT OWNER, TABLE_NAME, 
       INMEMORY, INMEMORY_COMPRESSION 
FROM   ALL_EXTERNAL_TABLES 
WHERE  TABLE_NAME = 'ADMIN_EXT_SALES';

OWNER      TABLE_NAME      INMEMORY INMEMORY_COMPRESS
---------- --------------- -------- -----------------
SH         ADMIN_EXT_SALES ENABLED  FOR QUERY LOW

Related views include ALL_XTERNAL_PART_TABLES, ALL_XTERNAL_TAB_PARTITIONS, and ALL_XTERNAL_TAB_SUBPARTITIONS.

1. In Linux, create a temporary directory, and then create a text file with one row of sales data.

   mkdir /tmp/sales_data
   echo "1002,110,19-02-2015,12,18,150,4800" > /tmp/sales_data/sales2015_data.txt

2. In SQL*Plus, log is with administrator privileges, and then create a directory object for the sales data:

   CONNECT / AS SYSDBA
   
   CREATE DIRECTORY sales_data AS '/tmp/sales_data';
   GRANT READ,WRITE ON DIRECTORY sales_data TO sh;
   

3. Log in as user sh, and then create the sales_hpt table:

   CONNECT sh
   
   DROP TABLE sales_hpt;
   CREATE TABLE sales_hpt
     ( prod_id       NUMBER        NOT NULL,
       cust_id       NUMBER        NOT NULL,
       time_id       DATE          NOT NULL,
       channel_id    NUMBER        NOT NULL,
       promo_id      NUMBER        NOT NULL,
       quantity_sold NUMBER(10,2)  NOT NULL,
       amount_sold   NUMBER(10,2)  NOT NULL
     )
       EXTERNAL PARTITION ATTRIBUTES (
         TYPE ORACLE_LOADER 
         DEFAULT DIRECTORY sales_data
          ACCESS PARAMETERS(
            FIELDS TERMINATED BY ','
            (prod_id,cust_id,time_id DATE 'dd-mm-yyyy',channel_id,promo_id,quantity_sold,amount_sold)
          ) 
         REJECT LIMIT UNLIMITED
        ) 
       PARTITION BY RANGE (time_id)
       (
        PARTITION sales_2014 VALUES LESS THAN (TO_DATE('01-01-2015','dd-mm-yyyy')),
        PARTITION sales_2015 VALUES LESS THAN (TO_DATE('01-01-2016','dd-mm-yyyy')),
        PARTITION sales_2016 VALUES LESS THAN (TO_DATE('01-01-2017','dd-mm-yyyy')) EXTERNAL 
             LOCATION ('sales2016_data.txt')
   );

   The preceding statement shows that the table has three partitions: sales_2014, sales_2015, and sales_2016. Only sales_2016 is designed as external.

4. Query the data dictionary to confirm that the table is a hybrid:

   SQL> COL TABLE_NAME FORMAT a25
   SQL> SELECT TABLE_NAME, HYBRID FROM USER_TABLES WHERE HYBRID = 'YES';
   
   TABLE_NAME                HYB
   ------------------------- ---
   SALES_HPT                 YES

5. Insert rows into the two internal partitions:

   INSERT /*+APPEND*/ INTO sh.sales_hpt (SELECT * FROM sales);
   INSERT INTO sh.sales_hpt VALUES (30, 21086, '30-12-2015', 2, 999, 1, 10.19);
   COMMIT;

   The first of the preceding statements inserts all rows from the sales table. All dates in sales are before 2002, so all rows from sales are inserted into the sales_2014 partition. The second statement inserts a single row into the sales_2015 partition.

6. Query the partitions to confirm that the correct data exists:

   SQL> SELECT COUNT(*) FROM sales_hpt PARTITION(sales_2014);
   
     COUNT(*)
   ----------
       918843
   
   SQL> SELECT COUNT(*) FROM sales_hpt PARTITION(sales_2015);
   
     COUNT(*)
   ----------
            1
   
   SQL> SELECT COUNT(*) FROM sales_hpt PARTITION(sales_2016);
   
     COUNT(*)
   ----------
            1

7. Apply the INMEMORY attribute at the table level, and then force the database to populate the table into the IM column store:

   ALTER TABLE sales_hpt INMEMORY;
   SELECT /*+ FULL(sales_hpt) NO_PARALLEL(sales_hpt) */ COUNT(*) FROM sales_hpt;

8. Query the population status of the sales_hpt partitions:

   COL OWNER FORMAT a3
   COL NAME FORMAT a10
   COL PARTITION FORMAT a13
   COL STATUS FORMAT a20
   
   SELECT OWNER, SEGMENT_NAME NAME, PARTITION_NAME PARTITION,
          POPULATE_STATUS STATUS, BYTES_NOT_POPULATED
   FROM   V$IM_SEGMENTS;
   
   OWN NAME       PARTITION     STATUS               BYTES_NOT_POPULATED
   --- ---------- ------------- -------------------- -------------------
   SH  SALES_HPT  SALES_2015    COMPLETED                              0
   SH  SALES_HPT  SALES_2014    COMPLETED                              0
   

   The query shows that only the two internal partitions were populated, which is expected. The INMEMORY attribute does not apply to external partitions.

In SQL*Plus, log in to the database as the sh user, and then execute the following DDL statement:

ALTER TABLE sales INMEMORY;

The preceding statement uses the defaults for the INMEMORY clause: MEMCOMPRESS FOR QUERY and PRIORITY NONE.

In SQL*Plus, log in to the database as the oe user, and then execute the following DDL statement:

ALTER TABLE product_information 
  INMEMORY 
  MEMCOMPRESS FOR CAPACITY LOW;

The preceding statement uses the default for the PRIORITY clause of NONE. Populate the table by forcing a full table scan as follows (sample output included):

SELECT /*+ FULL(p) NO_PARALLEL(p) */ COUNT(*) 
FROM   product_information p;

  COUNT(*)
----------
       288

In a separate session, log in as a user with administrative privileges, and then calculate the compression ratio by executing the following query (sample output included):

COL OWNER FORMAT a5
COL SEGMENT_NAME FORMAT a19
SET PAGESIZE 50000

SELECT OWNER, SEGMENT_NAME, BYTES ORIG_SIZE,
       INMEMORY_SIZE IN_MEM_SIZE,
       ROUND (BYTES / INMEMORY_SIZE, 2) COMP_RATIO
FROM   V$IM_SEGMENTS
WHERE  SEGMENT_NAME LIKE 'P%'
ORDER BY 4;

OWNER SEGMENT_NAME         ORIG_SIZE IN_MEM_SIZE COMP_RATIO
----- ------------------- ---------- ----------- ----------
OE    PRODUCT_INFORMATION      98304     1310720        .08

In SQL*Plus, log in to the database as the oe user, and then execute the following DDL statement:

ALTER TABLE 
  product_information 
  INMEMORY 
  PRIORITY HIGH;

In SQL*Plus, log in to the database as an administrative user, and then execute the following query to show the current priority and compression setting for the oe.product_information table:

COL OWNER FORMAT a5
COL SEGMENT_NAME FORMAT a19
SET PAGESIZE 50000

SELECT v.OWNER, v.SEGMENT_NAME, v.INMEMORY_PRIORITY,
       v.INMEMORY_COMPRESSION
FROM   V$IM_SEGMENTS v
WHERE  SEGMENT_NAME LIKE 'P%';

OWNER SEGMENT_NAME        INMEMORY INMEMORY_COMPRESS
----- ------------------- -------- -----------------
OE    PRODUCT_INFORMATION HIGH     FOR CAPACITY LOW

The following DDL statement alters oe.product_information to use FOR CAPACITY HIGH table compression and PRIORITY LOW:

ALTER TABLE oe.product_information 
  INMEMORY 
  MEMCOMPRESS FOR CAPACITY HIGH 
  PRIORITY LOW;

Log in to the database as the user oe, and then execute the following statement to disable the product_information table for the IM column store:

ALTER TABLE oe.product_information NO INMEMORY;

The V$IM_SEGMENTS view lists the database objects that are populated in the IM column store.

By default, Exadata Smart Flash Cache compresses data using the level MEMCOMPRESS FOR CAPACITY LOW. To change the compression level or disable the columnar format altogether, use the ALTER TABLE ... NO CELLMEMORY statement.

Log in to the database as user oe, and execute the following DDL statement:

ALTER TABLE product_information NO CELLMEMORY;