Thanks for the question, Girish.
Asked: April 16, 2025 - 4:10 am UTC
Last updated: April 19, 2025 - 1:53 am UTC
Version: 19
Viewed 100+ times
You Asked
Hi,
I see Oracle document shows an example of insert statement for fast ingest as
INSERT /*+ MEMOPTIMIZE_WRITE */ INTO test_fast_ingest VALUES (1,'test');
Can fast ingest using memoptimize be used for insert statement, that is INSERT as select...
INSERT /*+ MEMOPTIMIZE_WRITE */ INTO test_fast_ingest select col1,col2,col2 from another_table;
Thanks,
Girish
and Connor said...
Yes it does
SQL> create table t
2 (
3 c1 int,
4 c2 int,
5 c3 int
6 ) segment creation immediate;
Table created.
SQL>
SQL> alter table t memoptimize for write;
Table altered.
SQL>
SQL> select pool, sum(bytes)
2 from v$sgastat
3 group by pool;
POOL SUM(BYTES)
-------------- ----------
5771359184
shared pool 854805032
large pool 40108008
in-memory pool 536870912
streams pool 33554432
SQL>
SQL> insert /*+ memoptimize_write */ into t
2 select rownum, rownum, rownum
3 from dual connect by level <= 100;
100 rows created.
SQL>
SQL> select pool, sum(bytes)
2 from v$sgastat
3 group by pool;
POOL SUM(BYTES)
-------------- ----------
3489657808
shared pool 854997560
large pool 2321808760
in-memory pool 536870912
streams pool 33554432
SQL> select * from V$MEMOPTIMIZE_WRITE_AREA;
TOTAL_SIZE USED_SPACE FREE_SPACE NUM_WRITES NUM_WRITERS CON_ID
---------- ---------- ---------- ---------- ----------- ----------
2154823680 1212896 2153610784 0 1 3
but I'd be surprised if you see a lot of benefit in using it in this sense. The concept of fast ingest is to let the database batch individual writes together for efficiency.
An insert-select has *already* in effect done that batching
Rating
(1 rating)