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.
Andy Rivenes is a Senior Principal Product Manager at Oracle Corporation and is a Product Manager for Database In-Memory. Andy has been working with Oracle products since 1992 and spent a large part of his career managing Oracle databases for both custom and E-Business Suite applications. Andy now spends his time helping customers implement Database In-Memory and providing information about Database In-Memory through blogs, conferences, and customer visits.