Querying In-Memory External Tables

Before You Begin

This 15-minute tutorial shows you how to create, populate and then query in-memory external tables.

Background

Before Oracle Database 18c, querying data from an external table was only possible from the buffer cache.

What Do You Need?

• Familiarity with the In-Memory column store (IM column store), in-memory segments creation, and populating the IM column store
• Oracle Database 18c
• A container database (CDB) with one pluggable database (PDB)
• The data file empext1.dat with employees data to query directly from an external table. Download empext1.dat to the labs directory created on your server /home/oracle/labs. The file contains a lot of records such as: 114,Den,Raphaely,DRAPHEAL,515.127.4561,07-DEC-02,PU_MAN,11000,,100,30 115,Alexander,Khoo,AKHOO,515.127.4562,18-MAY-03,PU_CLERK,3100,,114,30 116,Shelli,Baida,SBAIDA,515.127.4563,24-DEC-05,PU_CLERK,2900,,114,30 117,Sigal,Tobias,STOBIAS,515.127.4564,24-JUL-05,PU_CLERK,2800,,114. The fields contain the EMPLOYEE_ID, FIRST_NAME, LAST_NAME, EMAIL, PHONE_NUMBER, HIRE_DATE, JOB_ID, SALARY, COMMISSION_PCT, MANAGER_ID, DEPARTMENT_ID.

Create In-Memory External Tables

In this section, you create an in-memory external table from the /home/oracle/labs/empext1.dat external file. The data from the external table needs some space in the IM column store to be populated into the IM column store.

1. Log in to ORCL.

   sqlplus / AS SYSDBA 

2. Set the IM column store size to 160M.

   ALTER SYSTEM SET inmemory_size = 160M SCOPE=SPFILE;

3. Restart the instance, and open PDB_ORCL.

   SHUTDOWN IMMEDIATE
   STARTUP
   ALTER PLUGGABLE DATABASE pdb_orcl OPEN;

4. Log in to PDB_ORCL as SYSTEM.

   CONNECT system@PDB_ORCL
   Enter password: password

5. Create the HR user.

   CREATE USER hr IDENTIFIED BY password;

6. Grant HR the appropriate system privileges.

   GRANT create session, create table, unlimited tablespace, 
         select any dictionary TO hr;

7. Create the ext_dir directory AS '/home/oracle/labs', and grant read and write privileges on the directory to HR user.

   CREATE DIRECTORY ext_dir AS '/home/oracle/labs';
   GRANT read, write ON DIRECTORY ext_dir TO hr;

8. Reconnect to PDB_ORCL.

   CONNECT hr@PDB_ORCL
   Enter password: password

9. Create the in-memory external table. Use the CREATE TABLE command from code1.
10. Verify that the external table is an in-memory object.

    SELECT table_name, inmemory, inmemory_compression FROM user_external_tables;
    
    TABLE_NAME   INMEMORY INMEMORY_COMPRESS
    ------------ -------- -----------------
    EXT_EMP      ENABLED  FOR QUERY LOW

Populate In-Memory External Tables

In this section, you populate the external table into the IM column store.

1. Count the number of rows in the table.

   SELECT count(*) FROM hr.ext_emp;
   
     COUNT(*)
   ----------
         2078
   

2. Connect to PDB_ORCL as SYS to verify that the table is populated into the IM column store after the query execution.

   CONNECT sys@PDB_ORCL AS SYSDBA
   Enter password: password

3. Query the V$IM_SEGMENTS view to verify that the table is populated into the IM column store.

   SELECT owner, segment_name, populate_status, con_id FROM v$im_segments;
   
   no rows selected

4. Populate the external table into the IM column store.

   EXEC dbms_inmemory.populate ('HR','EXT_EMP')

5. Verify that the segment is populated into the IM column store.

   SELECT owner, segment_name, populate_status, con_id FROM v$im_segments;
   
   OWNER        SEGMENT_NAME POPULATE_STAT     CON_ID
   ------------ ------------ ------------- ----------
   HR           EXT_EMP      COMPLETED              4
   

Query In-Memory External Tables

In this section, after the in-memory external table is populated into  the IM column store, you can take full advantage of the query performance by using the in-memory columnar format.

1. Log into PDB_ORCL as user HR.

   CONNECT hr@PDB_ORCL
   Enter password: password

2. Query the in-memory external table.

   SELECT count(*) FROM hr.ext_emp;
   
     COUNT(*)
   ----------
         2078

3. Verify that the in-memory external table data is queried from the IM column store or buffer cache.

   SELECT * FROM table(dbms_xplan.display_cursor());
   ...
   -------------------------------------------------------------------------------
   | Id  | Operation                   | Name    | Rows  | Cost (%CPU)| Time     |
   -------------------------------------------------------------------------------
   |   0 | SELECT STATEMENT            |         |       |   341 (100)|          |
   |   1 |  SORT AGGREGATE             |         |     1 |            |          |
   |   2 |   EXTERNAL TABLE ACCESS FULL| EXT_EMP |   102K|   341   (1)| 00:00:01 | 

   The execution plan shows that the table was accessed from the buffer cache.

4. Set the QUERY_REWRITE_INTEGRITY parameter to STALE_TOLERATED in your session.

   ALTER SESSION SET QUERY_REWRITE_INTEGRITY = STALE_TOLERATED;
   

5. Re-query the table.

   SELECT count(*) FROM hr.ext_emp;
   
     COUNT(*)
   ----------
         2078

6. Regenerate the execution plan.

   SELECT * FROM table(dbms_xplan.display_cursor());
   ...
   ----------------------------------------------------------------------------------------
   | Id  | Operation                            | Name    | Rows  | Cost (%CPU)| Time     |
   ----------------------------------------------------------------------------------------
   |   0 | SELECT STATEMENT                     |         |       |   341 (100)|          |
   |   1 |  SORT AGGREGATE                      |         |     1 |            |          |
   |   2 |   EXTERNAL TABLE ACCESS INMEMORY FULL| EXT_EMP |   102K|   341   (1)| 00:00:01 |

Update the External File

In this section, you add records to the external file. You observe how queries on the in-memory external tables behave.

1. Add two records to the /home/oracle/labs/empext1.dat external file.

   host echo "202,Pat,Fay,PFAY,603.123.6666,17-AUG-05,MK_REP,6000,,201,20" >> /home/oracle/labs/empext1.dat
   host echo "203,Susan,Mavris,SMAVRIS,515.123.7777,07-JUN-02,HR_REP,6500,,101,40" >> /home/oracle/labs/empext1.dat

2. Is the in-memory external table automatically repopulated into the IM column store?

   SELECT count(*) FROM hr.ext_emp;
   
     COUNT(*)
   ----------
         2078

3. Repopulate the in-memory external table.

   EXEC dbms_inmemory.repopulate ('HR','EXT_EMP')
   

4. Verify that the in-memory external table is repopulated into the IM column store.

   SELECT count(*) FROM hr.ext_emp;
   
     COUNT(*)
   ----------
         2080                

Clean Up the Tutorial Environment

1. Connect as SYSTEM to the PDB.

   CONNECT system@PDB_ORCL
   Enter password: password

2. Drop the user HR.

   DROP USER hr CASCADE;
   

3. Quit the session.

   EXIT

4. Remove the data files.

   rm /home/oracle/labs/empext1.dat