With the introduction of Oracle Database In-Memory, a single database can now efficiently support mixed workloads, delivering optimal performance for transactions while simultaneously supporting real-time analytics and reporting. This is possible due to a unique “dual-format” architecture that enables data to be maintained in both the existing Oracle row format, for OLTP operations, and a new purely in-memory column format, optimized for analytical processing. It is not a free feature though, see at the bottom for prices.
Database In-Memory uses an In-Memory column store (IM column store), which is a new component of the Oracle Database System Global Area (SGA), called the In-Memory Area. The In-Memory area is a static pool within the SGA, whose size is controlled by the initialization parameter INMEMORY_SIZE (default 0) and must have a minimum size of 100MB.
The following objects can be stored in Oracle In-Memory database:
If it is enabled at the tablespace level, then all new tables and materialized views in the tablespace will be enabled for the In-Memory column store by default.
Oracle Database In-Memory Area specified at the Container Database level (CDB) and by default each Pluggable Database (PDB) inherits the INMEMORY_SIZE from Container Database level (CDB).
Login as “sys” user to CDB (container database), change the parameter, restart database and start pluggable database PDB01;
SQL> ALTER SYSTEM SET inmemory_size = 512M scope=spfile; SQL> shutdown immediate; SQL> startup; SQL> alter pluggable database "PDB01" open read write;
Login as “sh” user to pluggable database PDB01 and alter table;
SQL> ALTER TABLE customers INMEMORY; table CUSTOMERS altered. SQL> select count(*) from customers; COUNT(*) ---------- 55500
Checking in-memory option:
SELECT table_name, inmemory, inmemory_priority, inmemory_compression FROM user_tables WHERE table_name='CUSTOMERS'; TABLE_NAME INMEMORY INMEMORY_PRIORITY INMEMORY_COMPRESSION ---------- -------- ----------------- -------------------- CUSTOMERS ENABLED NONE FOR QUERY LOW
Enabling the In-Memory attribute on the EXAMPLE tablespace by specifying the INMEMORY attribute
SQL> ALTER TABLESPACE example INMEMORY;
Enabling the In-Memory attribute on the sales table but excluding the “prod_id” column
SQL> ALTER TABLE sales INMEMORY NO INMEMORY(prod_id);
Disabling the In-Memory attribute on one partition of the sales table by specifying the NO INMEMORY clause
SQL> ALTER TABLE sales MODIFY PARTITION SALES_Q1_1998 NO INMEMORY;
Enabling the In-Memory attribute on the customers table with a priority level of critical
SQL> ALTER TABLE customers INMEMORY PRIORITY CRITICAL;
Option CRITICAL: object is populated immediately after the database is opened.
I encourage you to read the following white paper “Oracle Database In-Memory” where you will find detailed information about In-Memory features such as In-Memory compression, scans, joins, aggregation, column store on RAC and much more.
The cookie settings on this website are set to "allow cookies" to give you the best browsing experience possible. If you continue to use this website without changing your cookie settings or you click "Accept" below then you are consenting to this.