“The In-Memory Column Store (IM column store) is an optional portion of the system global area (SGA) that stores copies of tables, table partitions, and other database objects. In the IM column store, data is populated by column rather than row as it is in other parts of the SGA, and data is optimized for rapid scans. The IM column store is included with the Oracle Database In-Memory option.”
- Configure the database to use IM Column Store; (https://www.techdatabasket.com/enabling-oracle-database-in-memory-database-in-memory-option-in-oracle-database-12c-release-1/)
- Setup some tables to use the IM Column Store; (https://www.techdatabasket.com/enabling-and-disabling-table-for-the-im-column-store-in-oracle-12c/)
- Datapump from one database to another in order to use IM Column Store. (https://www.techdatabasket.com/using-datapump-utility-to-import-tables-with-in-memory-column-store-in-oracle-12c/)
VIEWS and SQL key statements:
- V$IM_SEGMENTS
- USER_TABLES
- DBA_DIRECTORIES
- SELECT OWNER, SEGMENT_NAME, INMEMORY_PRIORITY, INMEMORY_COMPRESSION
FROM V$IM_SEGMENTS; - “In-Memory FastStart (IM FastStart) reduces the time to populate data into the IM column store when a database instance restarts.”
- “You can import database objects that are enabled for the IM column store using the TRANSFORM=INMEMORY:y option of the impdp command. “
Automatic Big Table Caching
“In Oracle Real Application Clusters (Oracle RAC) environments, this feature is supported only with parallel queries. In single instance environments, this feature is supported with both parallel and serial queries.”
“The DB_BIG_TABLE_CACHE_PERCENT_TARGET parameter determines the percentage of the buffer cache size used for scans. “
“The DB_BIG_TABLE_CACHE_PERCENT_TARGET parameter is only enabled in an Oracle RAC environment if PARALLEL_DEGREE_POLICY is set to AUTO or ADAPTIVE. “
Views:
- V$BT_SCAN_CACHE
- V$BT_SCAN_OBJ_TEMPS
Data Warehousing and Business Intelligence -> Database VLDB and Partitioning Guide -> Automatic Big Table Caching
Oracle Database Administrator’s Guide -> Managing Memory -> 6.7 Using the In-Memory Column Store
Database SQL Language Reference -> CREATE DIRECTORY
Hi! I am Bruno, a Brazilian born and bred, and I am also a naturalized Swedish citizen. I am a former Oracle ACE and, to keep up with academic research, I am a Computer Scientist with an MSc in Data Science and another MSc in Software Engineering. I have over ten years of experience working with companies such as IBM, Epico Tech, and Playtech across three different countries (Brazil, Hungary, and Sweden), and I have joined projects remotely in many others. I am super excited to share my interests in Databases, Cybersecurity, Cloud, Data Science, Data Engineering, Big Data, AI, Programming, Software Engineering, and data in general.
(Continue reading)