You Asked

Are the Memoptimized Rowstore tables newly available in Oracle 18, a complete substitute for single-table hash clusters?

Judging by the docs. about the direct memory access available with these types of table, I guess they're actually superior in performance to single-table hash clusters.

Could you comment please? Many thanks, Martin.

and we said...

Hi Martin,

The primary goal of a single-table hash cluster and the new Memoptimized RowStore (rowstore) are the same, to improve the performance and scalability of queries based on a key value look up.

However, the implementations are quite different, which means the rowstore can be approximately 25% faster than a single-table hash cluster.

This performance gain comes mainly from the fact that queries utilizing the rowstore by-passes the SQL execution layer and are processed directly in the data layer.

Please note there are some restrictions in 18c on when you can use the Memoptimized RowStore. For example, the table and it's automatically created hash index must fit completely within the memoptimize pool (new pool in the SGA). The underlying heap table on disk cannot be compressed or partitioned and the where clause predicate has to be an equality predicate on the primary key. All other types of predicates will not use the rowstore.

More information on using the Memoptimized RowStore can be found in the Oracle Database 18c documentations, https://docs.oracle.com/en/database/oracle/oracle-database/18/tgdba/tuning-system-global-area.html#GUID-9752E93D-55A7-4584-B09B-9623B33B5CCF

Rating

(3 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

A reader, March 13, 2018 - 12:25 am UTC

Martin Rose, March 14, 2018 - 11:10 am UTC

Many thanks for your reply.

It'll be a relief to no longer have to try & judge the SIZE, only to find I've got it wrong. ;-)

March 16, 2018 - 3:55 am UTC

glad we could help

Seems like Fast Ingest is not working on tables partitioned by time interval

A reader, June 05, 2020 - 11:08 pm UTC

I'm trying to do Fast Ingest on a table partitioned on timestamp interval -

drop table eventlog;
CREATE TABLE eventlog
(
EVENT_TIMESTAMP TIMESTAMP WITH TIME ZONE,
EVENT_TIMESTAMP_UTC TIMESTAMP GENERATED ALWAYS AS (SYS_EXTRACT_UTC(EVENT_TIMESTAMP)),
...
)
SEGMENT CREATION IMMEDIATE
PARTITION BY RANGE (EVENT_TIMESTAMP_UTC) INTERVAL (NUMTODSINTERVAL (2, 'MINUTE'))(PARTITION OLD_DATA VALUES LESS THAN (TIMESTAMP '2013-01-01 00:00:00 UTC'));

SQL> alter table eventlog memoptimize for write
*
ERROR at line 1:
ORA-62165: MEMOPTIMIZE FOR WRITE cannot be enabled on table with specified partitioning type.

I'm not able to find documents specifying why this type of partition are not supported (and what are supported partitioned types).. Any comments or work-around would be greatly appreciated..

June 08, 2020 - 10:39 am UTC

The limitations for this are listed at

https://docs.oracle.com/en/database/oracle/oracle-database/20/tgdba/tuning-system-global-area.html#GUID-CFADC9EA-2E2F-4EBB-BA2C-3663291DCC25

Which includes:

The following partitioning types are not supported.

REFERENCE
SYSTEM
INTERVAL
AUTOLIST

You have to use standard range partitioning for now.

More to Explore

Design

New to good database design? Check out Chris Saxon's full fundamentals class.

Breadcrumb

Question and Answer