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: March 05, 2026 - 5:09 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

  (2 ratings)

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

Sokrates, February 27, 2026 - 3:34 pm UTC

thanks
Connor McDonald
March 05, 2026 - 5:09 am UTC

Sorry it wasn't the news you wanted
more

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's channel and Chris's channel from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.

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