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 reading 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, shut down, and start 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.