Oracle SQL
  • LICENSE

Using INMEMORY with External Tables

Posted on May 4, 2017 by Roger MacNicol Posted in 12c, External tables, inmemory, oracle, SmartScan

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’
external tables inmemory oracle Roger MacNicol SmartScan
« More on tracing the offload server
Why are HCC stats being bumped when Smart Scanning row major data in 12.2 »
photo Sayan Malakshinov

Oracle ACE Pro Oracle ACE Pro

DEVVYOracle Database Developer Choice Award winner

Oracle performance tuning expert

UK / Cambridge

LinkedIn   Twitter
sayan@orasql.org

Recent Posts

  • CBO and Partial indexing
  • Slow index access “COL=:N” where :N is NULL
  • Where does the commit or rollback happen in PL/SQL code?
  • :1 and SP2-0553: Illegal variable name “1”.
  • ORA exceptions that can’t be caught by exception handler

Recent Comments

  • Oracle SGA 값을 증가 시킬 때 발생 장애 원인 – DBA의 정석 on Example of controlling “direct path reads” decision through SQL profile hints (index_stats/table_stats)
  • Oracle SQL | Oracle diagnostic events — Cheat sheet on Where does the commit or rollback happen in PL/SQL code?
  • Functions & Subqueries | Oracle Scratchpad on Deterministic function vs scalar subquery caching. Part 3
  • Materialized views state turns into compilation_error after refresh - kranar.top - Answering users questions... on Friday prank: select from join join join
  • Exadata Catalogue | Oracle Scratchpad on When bloggers get it wrong – part 1
  • Exadata Catalogue | Oracle Scratchpad on Serial Scans failing to offload
  • lateral join – decorrelation gone wrong – svenweller on Lateral view decorrelation(VW_DCL) causes wrong results with rownum
  • 255 column catalogue | Oracle Scratchpad on Intra-block row chaining optimization in 12.2
  • 255 column catalogue | Oracle Scratchpad on row pieces, 255 columns, intra-block row chaining in details
  • opt_estimate catalogue | Oracle Scratchpad on Correct syntax for the table_stats hint

Blogroll

  • Alex Fatkulin
  • Alexander Anokhin
  • Andrey Nikolaev
  • Charles Hooper
  • Christian Antognini
  • Coskan Gundogar
  • David Fitzjarrell
  • Igor Usoltsev
  • Jonathan Lewis
  • Karl Arao
  • Mark Bobak
  • Martin Bach
  • Martin Berger
  • Neil Chandler
  • Randolf Geist
  • Richard Foote
  • Riyaj Shamsudeen
  • Tanel Poder
  • Timur Akhmadeev
  • Valentin Nikotin

Meta

  • Log in
  • Entries feed
  • Comments feed
  • WordPress.org
©Sayan Malakshinov. Oracle SQL