Enhancing Query Performance: Leveraging In-Memory Optimized Dates in Oracle 23ai

In-Memory Column Store

Unlike traditional row-based storage, The In-Memory Column Store (IM column store) stores tables and partitions in memory using a columnar format optimized for rapid scans. This columnar format is optimized for analytical workloads, allowing for efficient scanning of specific columns without needing to read entire rows. 

In-Memory Optimized Dates

To enhance the performance of DATE-based queries DATE components (i.e. DAY, MONTH, YEAR) can be extracted and populated in the IM column store leveraging the In-Memory Expressions framework. This approach enables faster query processing on DATE columns, significantly improving the performance of date-based analytic queries.

Scenarios:

To explain the difference between using In-Memory Optimized Dates, I have prepared two scenarios:

• Scenario 1: Running a query without using In-Memory Optimized Dates
• Scenario 2: Running a query using In-Memory Optimized Dates

Prerequisites for these scenarios

Because the JOB_HISTORY table has a foreign key with another table, I cannot add several rows to it without the parent key, therefore, I created the JOB_HISTORY_INMEMORY table and added 10000 rows to it.

SQL> CREATE TABLE hr.job_history_inmemory AS SELECT * FROM hr.job_history;
SQL> BEGIN
  FOR i IN 1..10000 LOOP
    INSERT INTO hr.job_history_inmemory (employee_id, start_date, end_date, job_id, department_id)
    VALUES (MOD(i, 200), SYSDATE - DBMS_RANDOM.VALUE(1, 1000), SYSDATE, 'IT_PROG', MOD(i, 10));
  END LOOP;
  COMMIT;
END;
/

Scenario 1: Running a query without using In-Memory Optimized Dates

SQL>SELECT employee_id, EXTRACT(MONTH FROM start_date) AS start_month, 
       EXTRACT(YEAR FROM start_date) AS start_year
FROM hr.job_history_inmemory 
WHERE EXTRACT(MONTH FROM start_date) = 5;

Evaluating the plan shows that Oracle didn't use In-Memory Optimized Date.

Scenario 2: Running a query using In-Memory Optimized Dates

The following conditions should be met to leverage the In-Memory Optimized Dates:

1.    Enabling the IM Column Store

To enable the IM Column Store, we should determine the size of the INMEMORY_SIZE parameter at the CDB level, shutdown, and startup the database.

SQL> ALTER SYSTEM SET INMEMORY_SIZE = 1G SCOPE=SPFILE; 

Use the following query to find out if the In-Memory Column Store is enabled and its size.

2.    Ensure the table is populated into the In-Memory Column Store:

Use the following command and check it using the query.

SQL>  ALTER TABLE hr.job_history_inmemory INMEMORY;
SQL> SELECT 
    table_name,
    inmemory_size,
    inmemory_priority,
    inmemory_column_format
FROM 
    dba_inmemory_tables
WHERE 
    table_name = 'JOB_HISTORY_INMEMORY'
    AND owner = 'HR';    

3.    Enabling INMEMORY_OPTIMIZED_DATE  parameter 

SQL>ALTER SYSTEM SET INMEMORY_OPTIMIZED_DATE = ENABLE SCOPE=MEMORY;

Running the previous query and evaluating the plan:

SQL>SELECT employee_id, EXTRACT(MONTH FROM start_date) AS start_month, 
       EXTRACT(YEAR FROM start_date) AS start_year
FROM hr.job_history_inmemory 
WHERE EXTRACT(MONTH FROM start_date) = 5;

The above plan shows that Oracle used In-Memory Optimized Date.