Assume that you are logged in to the database as an administrator, and that you have issued the following DDL statement to add the INMEMORY clause to the sh.sales table:

ALTER TABLE sh.sales 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 into the IM column store. The following query confirms that the sh.sales table is not currently populated:

COL OWNER FORMAT a10;
COL NAME FORMAT a25;
COL STATUS FORMAT a10;

SELECT OWNER, SEGMENT_NAME NAME,
       POPULATE_STATUS STATUS
FROM   V$IM_SEGMENTS
WHERE  SEGMENT_NAME = 'SALES';

no rows selected

The following query uses the FULL hint to force a full table scan of sales, thereby initiating population (sample output included):

SELECT /*+ FULL(sales) NO_PARALLEL(sales) */ COUNT(*) FROM sh.sales;

  COUNT(*)
----------
    918843

The following query shows the population status of sales (sample output included):

SET PAGESIZE 50000
COL OWNER FORMAT a3
COL NAME FORMAT a10
COL STATUS FORMAT a20

SELECT OWNER, SEGMENT_NAME NAME, 
       POPULATE_STATUS STATUS
FROM   V$IM_SEGMENTS
WHERE  SEGMENT_NAME = 'SALES';

OWN NAME       STATUS
--- ---------- --------------------
SH  SALES      COMPLETED
SH  SALES      COMPLETED
SH  SALES      COMPLETED
SH  SALES      COMPLETED
SH  SALES      COMPLETED
SH  SALES      COMPLETED
SH  SALES      COMPLETED
SH  SALES      COMPLETED
SH  SALES      COMPLETED
SH  SALES      COMPLETED
SH  SALES      COMPLETED
SH  SALES      COMPLETED
SH  SALES      COMPLETED
SH  SALES      COMPLETED
SH  SALES      COMPLETED
SH  SALES      COMPLETED

16 rows selected.

The following query calculates the compression ratio of the table. The query assumes that the tables are not further compressed on disk.

COL OWNER FORMAT a5
COL SEGMENT_NAME FORMAT a5
SET PAGESIZE 50000

SELECT v.OWNER, v.SEGMENT_NAME, v.BYTES ORIG_SIZE,
       v.INMEMORY_SIZE IN_MEM_SIZE,
       ROUND(v.BYTES / v.INMEMORY_SIZE, 2) COMP_RATIO
FROM   V$IM_SEGMENTS v
ORDER BY 4;

OWNER SEGME  ORIG_SIZE IN_MEM_SIZE COMP_RATIO
----- ----- ---------- ----------- ----------
SH    SALES     851968     1310720        .65
SH    SALES     835584     1310720        .64
SH    SALES     925696     1310720        .71
SH    SALES     958464     1310720        .73
SH    SALES     950272     1310720        .73
SH    SALES     786432     1310720         .6
SH    SALES     876544     1310720        .67
SH    SALES     753664     1310720        .58
SH    SALES    1081344     1310720        .83
SH    SALES     901120     1310720        .69
SH    SALES     925696     1310720        .71
SH    SALES     933888     1310720        .71
SH    SALES     843776     1310720        .64
SH    SALES     999424     1310720        .76
SH    SALES     581632     1507328        .39
SH    SALES     696320     1507328        .46

16 rows selected.

Assume that you are logged in to the database as an administrator, and that you have issued the following DDL statement to add the INMEMORY clause to the sh.customers table:

ALTER TABLE sh.customers 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 into the IM column store. The following query confirms that the sh.customers table is not currently populated:

COL OWNER FORMAT a10;
COL NAME FORMAT a25;
COL STATUS FORMAT a10;

SELECT OWNER, SEGMENT_NAME NAME,
       POPULATE_STATUS STATUS
FROM   V$IM_SEGMENTS
WHERE  SEGMENT_NAME = 'CUSTOMERS';

no rows selected

The following PL/SQL code uses the POPULATE procedure to initiative population:

EXEC DBMS_INMEMORY.POPULATE('SH', 'CUSTOMERS');

The following query shows the population status of customers (sample output included):

SELECT OWNER, SEGMENT_NAME NAME,
       POPULATE_STATUS STATUS
FROM   V$IM_SEGMENTS
WHERE  SEGMENT_NAME = 'CUSTOMERS';

OWN NAME       STATUS
--- ---------- --------------------
SH  CUSTOMERS  COMPLETED

Example 5-1 Specifying a Timeout Interval for In-Memory Population

In this example, the database contains a number of In-Memory tables with a variety of priority settings. Your goal is to populate every In-Memory table to 100% completion in a restricted database session, and then disable the restricted session so that the application can be guaranteed of querying only the In-Memory representations.

Assume that the database is shut down. In SQL*Plus, you connect to an idle instance as SYSDBA, and then execute the following command (sample output included):

SQL> STARTUP RESTRICT
ORACLE instance started.

Total System Global Area 1157624280 bytes
Fixed Size                  8839640 bytes
Variable Size             754974720 bytes
Database Buffers           16777216 bytes
Redo Buffers                7933952 bytes
In-Memory Area            369098752 bytes
Database mounted.
Database opened.

The database is open, but is accessible only to administrative users. You execute the following statements in SQL*Plus (sample output shown in bold):

VARIABLE b_pop_status NUMBER

SELECT DBMS_INMEMORY_ADMIN.POPULATE_WAIT(
         priority   => 'NONE' ,
         percentage => 100    ,
         timeout    => 300    )
  INTO b_pop_status
FROM   DUAL;

PRINT b_pop_status
-1

After 5 minutes, the function returns the number –1. This code indicates that the function timed out while waiting for population to complete. 5 minutes is not long enough to populate all INMEMORY tables. You re-execute the SELECT statement, specifying a 30-minute timeout:

SELECT DBMS_INMEMORY_ADMIN.POPULATE_WAIT(
         priority   => 'NONE' ,
         percentage => 100    ,
         timeout    => 1800   )
  INTO b_pop_status
FROM   DUAL;

PRINT b_pop_status
0

After 8 minutes, the function returns the number 0. This code indicates that all tables are completely populated. You now disable the restricted session so that the application can start query In-Memory objects with full confidence that only In-Memory representations will be accessed:

ALTER SYSTEM DISABLE RESTRICTED SESSION;

The admin_ext_sales table is not yet populated. Starting in Oracle Database 19c, a full table scan populates an external table just as it populates a standard table. However, in this scenario, you choose to initiate population by using the DBMS_INMEMORY package.

To populate the table, you perform the following steps:

1. Log in as user sh.

2. Set QUERY_REWRITE_INTEGRITY in the database session:

   ALTER SESSION SET QUERY_REWRITE_INTEGRITY=stale_tolerated;
   

3. Execute the following PL/SQL program:

   EXEC DBMS_INMEMORY.POPULATE('SH', 'ADMIN_EXT_SALES');

4. Check the population status by querying V$IM_SEGMENTS:

   COL OWNER FORMAT a3
   COL NAME FORMAT a15
   COL STATUS FORMAT a9
   
   SELECT OWNER, SEGMENT_NAME NAME, 
          POPULATE_STATUS STATUS
   FROM   V$IM_SEGMENTS;
   
   OWN NAME            STATUS
   --- --------------- ---------
   SH  ADMIN_EXT_SALES COMPLETED

   The preceding output shows that the admin_ext_sales table has been populated

Assume you add a record to /tmp/data/sh_sales.csv as follows:

echo "148,8787,23-NOV-01,2,999,1,23.43" >> /tmp/data/sh_sales.csv

The following example uses the REPOPULATE procedure to force the IM column store to refresh admin_ext_sales:

EXEC DBMS_INMEMORY.REPOPULATE('SH', 'ADMIN_EXT_SALES');