Oracle Database 21c Enhanced In-Memory External Table Support

In Oracle Database 21c In-Memory External Table support has been enhanced to support partitioned external tables. External table support with Database In-Memory was added in Oracle Database 18c but did not support partitioning. Now in Oracle Database 21c both external partitioned tables and hybrid partitioned tables are supported. In case you aren't sure what a hybrid partitioned table is it's a partitioned table with both internal partitions, partitions that reside in the database, and external partitions, partitions that reside in external files outside the database.

External tables are populated with the DBMS_INMEMORY.(RE)POPULATE procedure. You also have to set the parameter QUERY_REWRITE_INTEGRITY to STALE_TOLERATED. This is described in detail in the above 18c blog post, and the basic issue is that Database In-Memory is "unaware of any changes to the underlying external files pointed to by the Location clause." This means that Database In-Memory cannot automatically re-populate external tables.

Not all Database In-Memory features are supported with external tables. Subpartitions are not supported, and some subclauses including column, distribute and priority are not supported. The Database In-Memory features Join Groups, In-Memory Optimized Arithmetic and IM Expressions are also not supported with external tables.

Any combination of individual external partition or hybrid partition population is supported so  "rolling window" type populations can be used. For example, I created two external partitions and two internal partitions from the SSB schema's CUSTOMER table and populated one partition of each in the IM column store. I called the table EXT_CUST_HYBRID_PART and named the partitions N1 through N4. I also included the "IS_EXTERNAL" column in the V$IM_SEGMENTS query below so that you could see that one of the external and one of the internal partitions has been populated:

SEGMENT_NAME                   PARTITION_NAME       EXTERNAL      BYTES POP STATUS    INMEMORY_SIZE BYTES_NOT_POPULATED
------------------------------ -------------------- -------- ---------- ------------- ------------- -------------------
EXT_CUST_HYBRID_PART           N1                   TRUE              0 COMPLETED           1179648                   0
EXT_CUST_HYBRID_PART           N3                   FALSE        188416 COMPLETED           1179648                   0

Note that the N1 partition has an external value of true and no on-disk bytes, just the size in the IM columns store since Oracle does not manage the external space. Again, this is why Database In-Memory does not automatically re-populate data in external tables since it is not aware of external data changes.

It is a little trickier to figure out whether specific individual partitions are enabled for inmemory since external partitions have their own set of views, for example USER_XTERNAL_TAB_PARTITIONS. With a hybrid partitioned table you will have to query both the USER_TAB_PARTITIONS view and the USER_XTERNAL_TAB_PARTITIONS view to determine the inmemory status. The following SQL combines the info:

SQL> select
  tp.TABLE_NAME,
  tp.PARTITION_NAME,
  tp.INMEMORY as "USER_TAB_PARTITIONS",
  xtp.INMEMORY as "XTERNAL_TAB_PARTITIONS"
from
  user_tab_partitions tp,
  user_xternal_tab_partitions xtp
where
  tp.table_name = xtp.table_name(+)
  and tp.partition_name = xtp.partition_name(+)
  and tp.table_name = 'EXT_CUST_HYBRID_PART';

  2    3    4    5    6    7    8    9   10   11   12
TABLE_NAME                     PARTITION_NAME       USER_TAB_PARTITIONS XTERNAL_TAB_PARTITIONS
------------------------------ -------------------- ------------------- ----------------------
EXT_CUST_HYBRID_PART           N1                                       ENABLED
EXT_CUST_HYBRID_PART           N2                                       DISABLED
EXT_CUST_HYBRID_PART           N3                   ENABLED
EXT_CUST_HYBRID_PART           N4                   DISABLED

SQL>

What does an execution plan look like? If none of the partitions are enabled for inmemory then we would see TABLE ACCESS HYBRID PART FULL as part of the execution plan. If all of the partitions are enabled for inmemory we would see an execution plan like:

---------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                                | Name                 | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
---------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                         |                      |       |       |    72 (100)|          |       |       |
|   1 |  PARTITION LIST ALL                      |                      | 20914 |   224K|    72  (20)| 00:00:01 |     1 |     4 |
|   2 |   HASH GROUP BY                          |                      | 20914 |   224K|    72  (20)| 00:00:01 |       |       |
|   3 |    TABLE ACCESS HYBRID PART INMEMORY FULL| EXT_CUST_HYBRID_PART | 20914 |   224K|    63   (8)| 00:00:01 |     1 |     4 |
|   4 |     TABLE ACCESS INMEMORY FULL           | EXT_CUST_HYBRID_PART |       |       |            |          |     1 |     4 |
---------------------------------------------------------------------------------------------------------------------------------

In our example with two partitions enabled for inmemory and populated and with two not enabled for inmemory and the partitions split between internal and external we get a rather complex execution plan:

-----------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                                  | Name                 | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
-----------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                           |                      |       |       |    81 (100)|          |       |       |
|   1 |  HASH GROUP BY                             |                      | 20914 |   224K|    81  (10)| 00:00:01 |       |       |
|   2 |   VIEW                                     | VW_TE_2              |   232 |  2552 |    80   (9)| 00:00:01 |       |       |
|   3 |    UNION-ALL                               |                      |       |       |            |          |       |       |
|   4 |     PARTITION LIST INLIST                  |                      |   209 |  2299 |    70   (9)| 00:00:01 |KEY(I) |KEY(I) |
|   5 |      TABLE ACCESS HYBRID PART INMEMORY FULL| EXT_CUST_HYBRID_PART |   209 |  2299 |    70   (9)| 00:00:01 |KEY(I) |KEY(I) |
|   6 |       TABLE ACCESS INMEMORY FULL           | EXT_CUST_HYBRID_PART |       |       |            |          |KEY(I) |KEY(I) |
|   7 |     PARTITION LIST SINGLE                  |                      |    23 |   253 |    10  (10)| 00:00:01 |   KEY |   KEY |
|   8 |      TABLE ACCESS FULL                     | EXT_CUST_HYBRID_PART |    23 |   253 |    10  (10)| 00:00:01 |     4 |     4 |
-----------------------------------------------------------------------------------------------------------------------------------

The bottom line is that when an internal partition is accessed you will see TABLE ACCESS INMEMORY FULL, when an external partition is accessed you will see EXTERNAL TABLE ACCESS INMEMORY FULL and when a combination of internal and external partitions are accessed you will see TABLE ACCESS HYBRID PART INMEMORY FULL. And to be complete, if the partitions are not enabled for inmemory then you will not see the INMEMORY key word as part of the access.

To summarize, inmemory external tables allow an application to populate data in the IM column store that exists outside Oracle Database without having to first materialize the data within the database. It also allows an application to take advantage of Oracle Database's rich feature set to query and analyze that data and perhaps combine it with other application data that does reside in Oracle Database. In Oracle Database 21c, Database In-Memory provides partition support for that external data to allow even more flexibility in managing fast analytics on many different types of data.