Using INMEMORY with External Tables

Posted on by Posted in 12c, External tables, inmemory, oracle, SmartScan 1,589 Page views

Several people have asked if there is any way to use INMEMORY on External Tables because the INMEMORY syntax is not supported in Create Table and Alter Table with Organization External (actually Create Table parses it but then ignores it).

While there is no way out of having to load the data into Oracle 12.1.0.2, there is a short cut to simplify life when you have an external process recreating flat file data on a regular basis and want to query the latest incarnation against corporate data in Oracle Tablespaces. One example we’ve seen of this is where you have a map-reduce job on Hadoop that highly summarizes large amounts of ephemeral data and it is the summary data that needs to be joined against Oracle data.

Instead of truncating the Oracle table and doing a flat file load each time, we can use a Materialized View on the external table and simply issue an on-demand refresh and let the materialized view take care of the truncate and load under the covers for us.

Since INMEMORY does support Materialized Views, we can now automatically get INMEMORY query speeds on the data from an external source.

Let’s see how this works. For example here’s a loader table I use with TPC-H Scale 1 for local testing:

create table p_et(
    p_partkey number, p_name varchar2(55),
    p_mfgr char(25),
    p_brand char(10), p_type varchar2(25), p_size number,
    p_container char(10), p_retailprice number,
    p_comment varchar2(23)
) 
organization external( 
    type ORACLE_LOADER 
    default directory T_WORK 
    access parameters( 
    records delimited by newline 
    nobadfile 
    nologfile 
    fields terminated by '|' 
    missing field values are null) 
    location (T_WORK:'part.tbl')) 
    reject limit unlimited;

Instead of just getting the external data into an Oracle MVIEW segment to query, I can get it into an INMEMORY segment and get much better query performance:

SQL> create materialized view im_p_et
     inmemory memcompress for capacity low
     as select * from p_et;

SQL> alter materialized view im_p_et ENABLE QUERY REWRITE ;

That succeeds, so first let’s make sure our queries are actually going to use this Materialized View:

SQL> begin
  2    DBMS_MVIEW.EXPLAIN_REWRITE(
  3       query=>'select count(p_comment) from p_et',
  4       mv=>'im_p_et');
  5  end; 
  6 / 

PL/SQL procedure successfully completed.


SQL> select message from rewrite_table;

QSM-01151: query was rewritten
QSM-01209: query rewritten with materialized view, IM_P_ET, using text match algorithm

OK so we are sure this simple query can use our Materialized View, let’s verify that it actually did:

Note: my table was too small so I had to force the XT MVIEW to be loaded:

SQL> begin 
  2    dbms_inmemory.repopulate(schema_name=>'TPCH',
  3       table_name=>'IM_P_ET',
  4       force=>TRUE);
  5  end;
  6 /

PL/SQL procedure successfully completed.

SQL> select INMEMORY_SIZE,POPULATE_STATUS from v$im_segments where SEGMENT_NAME='IM_P_ET';

INMEMORY_SIZE POPULATE_STAT
------------- -------------
      7602176 COMPLETED

SQL> set autotrace on 
SQL> select count(p_comment) from p_et;
COUNT(P_COMMENT)
----------------
 200000

Execution Plan
----------------------------------------------------------
Plan hash value: 703290957
---------------------------------------------------------------------------------------------------
| Id  | Operation                              | Name    | Rows  | Bytes | Cost (%CPU)|    Time   |
---------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                       |         |     1 |    13 |    50  (62)|  00:00:01 |
|   1 |  SORT AGGREGATE                        |         |     1 |    13 |            |           |
|   2 |   MAT_VIEW REWRITE ACCESS INMEMORY FULL| IM_P_ET |   152K|  1941K|    50  (62)|  00:00:01 |
---------------------------------------------------------------------------------------------------

Statistics
----------------------------------------------------------
 1  recursive calls
 3  db block gets
1881  consistent gets
 0  physical reads
 0  redo size
550  bytes sent via SQL*Net to client
607  bytes received via SQL*Net from client
  2  SQL*Net roundtrips to/from client
  0  sorts (memory)
  0  sorts (disk)
  1  rows processed

and in the query plan we see ‘ MAT_VIEW REWRITE ACCESS INMEMORY FULL‘ and in the stats section we see physical reads is now zero. We are now getting very fast INMEMORY queries against a Materialized View built using data from the flat file on disk.

Here are some things to remember when using Materialized Views with External Tables:

  • The Materialized View must have rewrite enabled
  • The optimizer_mode must be set to cost based e.g. ‘first_rows_100’
  • The query_rewrite_parameter must be set to ‘enabled’ or ‘force’
  • The query_rewrite_integrity parameter for an XT must be set to ‘trusted’ or ‘stale_tolerated’
Page views: 1,589