In-Memory Column Store in Oracle Database 12c

 

https://oracle-base.com/articles/12c/in-memory-column-store-12cr1

The In-Memory Column Store (IM column store) was the headline feature of the 12.1.0.2 patchset. This features allows you to store columns, tables, partitions and materialized views in memory in a columnar format, rather than the typical row format. The advantage of having data in memory is obvious, but the columnar storage lends itself extremely well to analytic queries found in business intelligence products.

ALTER SYSTEM SET INMEMORY_SIZE=2G SCOPE=SPFILE;

ALTER SYSTEM SET INMEMORY_QUERY=ENABLE;

CREATE TABLE im_tab (
  id  NUMBER
) INMEMORY;

CREATE TABLE noim_tab (
  id  NUMBER
) NO INMEMORY;
SELECT table_name,
       inmemory,
       inmemory_priority,
       inmemory_distribute,
       inmemory_compression,
       inmemory_duplicate  
FROM   user_tables
ORDER BY table_name;

OLAP - Surrogate key

Surrogate keys can be used to join a dimension table to a fact table in a star schema database.

A surrogate key is a unique identifier for an entity or object in a database that's used when natural keys are not available.

Surrogate keys are  system generated, so it's not possible to create or store a duplicate value.

Dimension Tables 

Fact Tables 

Dimension Tables and Fact Tables are inter-related in 2 ways--

1. Star Schema.

2. Snowflake Schema.

OLAP Cube is a multi-dimensional data..

Four types of analytical operations in OLAP are:-

1. Roll-up  --             Summarize the data / Summation
2. Drill-down -         Reverse of roll-up / Detail view
3. Slice and dice -     Project and Select
4. Pivot (rotate) -       Re-orient the cube./ Transpose