Skip to Main Content

Breadcrumb

Question and Answer

Connor McDonald

Thanks for the question, Sokrates.

Asked: February 09, 2026 - 3:21 pm UTC

Last updated: February 24, 2026 - 2:29 am UTC

Version: 19

Viewed 100+ times

You Asked

We don't seem to be able memoptimize a table for read when there are triggers on it (this seems to be undocumented however ?).
But: we can add the triggers later on without problem and the table continues to be memoptimized for read.

Question: WHY ? Is it a bug ?

It is a bit annoying because when trying to take advantage of this feature we first have to drop triggers on the table and then re-create them.

SQL> select banner from v$version where rownum=1;

BANNER
--------------------------------------------------------------------------------
Oracle Database 19c EE Extreme Perf Release 19.0.0.0.0 - Production

SQL> drop table t;

Table dropped.

SQL> create table t( i int primary key, v varchar2(1000)) segment creation immediate;

Table created.

SQL> create trigger tr_t before insert on t for each row begin null; end tr_t;
  2  /

Trigger created.

SQL> alter table t MEMOPTIMIZE for read;
alter table t MEMOPTIMIZE for read
*
ERROR at line 1:
ORA-62181: The MEMOPTIMIZE FOR READ feature is not supported on this table.


SQL> drop trigger tr_t;

Trigger dropped.

SQL> alter table t MEMOPTIMIZE for read;

Table altered.

SQL> create trigger tr_t before insert on t for each row begin null; end tr_t;
  2  /

Trigger created.

SQL> select MEMOPTIMIZE_READ  from user_tables where 'T'=table_name
  2  /

MEMOPTIM
--------
ENABLED

SQL>

and Connor said...

I suspect that this is a bug ... but not in the way you would like it to be :-)

My hypothesis is that we block triggers because the trigger might do something like alter the value of the primary key, which (possiblly, I don't know) might cause issues with the memory row store because that is conceptually a hash index keyed on the primary key value.

So if I had to guess what the bug is - it would be that we allow you to create a trigger after the fact :-)

But I will ask around internally and see what I can find out.

Rating

  (1 rating)

Comments

Addenda:

A reader, February 25, 2026 - 5:11 am UTC

Internal response from team is that you should *not* be able to set memoptimize for a table containing a trigger.

(Hopefully that is a restriction we can lift in future)

Connor

More to Explore

PL/SQL demos

Check out more PL/SQL tutorials on our LiveSQL tool.

PL/SQL docs

PL/SQL reference manual from the Oracle documentation library