DEV Community

Vahid Yousefzadeh
Vahid Yousefzadeh

Posted on

Oracle AI Database 26ai: Bind Variable Support in Materialized View Query Rewrite

#oracleaidatabase26ai #26ai #oracle #database

Using bind variables is a well-known best practice in Oracle database to enhance performance and reduce shared pool contention. Bind variables enable the database to reuse execution plans instead of hard-parsing SQL statements repeatedly, which significantly improves scalability in systems with high concurrency.

However, before Oracle 26ai, a major limitation existed: queries that contained bind variables could not take advantage of materialized view (MV) query rewrite. Since the optimizer could not determine the actual value of a bind variable during parsing, it was unable to confirm whether the query’s filter conditions were covered by the MV’s definition. As a result, query rewrite was disabled, forcing the database to access base tables directly and losing the performance benefits of pre-aggregated data.

Starting with Oracle AI Database 26ai, this limitation has been removed. Oracle now supports bind-variable-aware query rewrite through bind peeking during containment checks. This enhancement allows the optimizer to evaluate the actual value of a bind variable at runtime and decide whether the query can safely be rewritten to use the materialized view.

To illustrate this improvement, let’s compare the behavior in Oracle 19c and Oracle 26ai using a simple example.

Creating the Materialized View

SQL> CREATE MATERIALIZED VIEW vahid.mv_emp_dep
ENABLE QUERY REWRITE AS
SELECT department_id,
       COUNT(*) AS emp_count,
       SUM(salary) AS total_salary
FROM vahid.employees where department_id<100
GROUP BY department_id;

Materialized view created
Enter fullscreen mode Exit fullscreen mode

This MV stores aggregated employee information for departments with IDs less than 100 and is eligible for automatic query rewrite.

Behavior in Oracle 19c
When we execute a query that falls entirely within the MV’s filter range, such as department_id < 90, Oracle 19c successfully rewrites it to use the MV:

SQL> SELECT department_id,
       COUNT(*) AS emp_count,
       SUM(salary) AS total_salary
FROM vahid.employees where department_id<90
GROUP BY department_id;
Enter fullscreen mode Exit fullscreen mode 
Execution Plan
----------------------------------------------------------
Plan hash value: 3470845597
-------------------------------------------------------------------------------------------
| Id  | Operation                    | Name       | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |            |     8 |    80 |     2   (0)| 00:00:01 |
|*  1 |  MAT_VIEW REWRITE ACCESS FULL| MV_EMP_DEP |     8 |    80 |     2   (0)| 00:00:01 |
-------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   1 - filter("MV_EMP_DEP"."DEPARTMENT_ID"<90)
Enter fullscreen mode Exit fullscreen mode

However, if we query with a condition that exceeds the MV’s range, such as department_id > 90, Oracle must read directly from the base table:

SQL> SELECT department_id,
       COUNT(*) AS emp_count,
       SUM(salary) AS total_salary
FROM vahid.employees where department_id>90
GROUP BY department_id;
Enter fullscreen mode Exit fullscreen mode 
Execution Plan
----------------------------------------------------------
Plan hash value: 1192169904
--------------------------------------------------------------------------------
| Id  | Operation          | Name      | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |           |     8 |   208 |     3  (34)| 00:00:01 |
|   1 |  HASH GROUP BY     |           |     8 |   208 |     3  (34)| 00:00:01 |
|*  2 |   TABLE ACCESS FULL| EMPLOYEES |     8 |   208 |     2   (0)| 00:00:01 |
--------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   2 - filter("DEPARTMENT_ID">90)
Enter fullscreen mode Exit fullscreen mode

The key limitation appears when using a bind variable.

SQL> variable depid number
SQL> exec :depid:=90
PL/SQL procedure successfully completed.

SQL> SELECT department_id,
       COUNT(*) AS emp_count,
       SUM(salary) AS total_salary
FROM vahid.employees where department_id<:depid
GROUP BY department_id;
Enter fullscreen mode Exit fullscreen mode 
Execution Plan
----------------------------------------------------------
Plan hash value: 1192169904

--------------------------------------------------------------------------------
| Id  | Operation          | Name      | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |           |    22 |   572 |     3  (34)| 00:00:01 |
|   1 |  HASH GROUP BY     |           |    22 |   572 |     3  (34)| 00:00:01 |
|*  2 |   TABLE ACCESS FULL| EMPLOYEES |    22 |   572 |     2   (0)| 00:00:01 |
--------------------------------------------------------------------------------
Enter fullscreen mode Exit fullscreen mode

Even though the bind value (90) is within the MV range, Oracle 19c cannot rewrite the query because the optimizer does not know the value of :depid during parsing. The execution plan therefore shows a full table scan instead of an MV access.

Behavior in Oracle AI Database 26ai
In 26ai, the optimizer introduces bind peeking during query rewrite. This allows Oracle to check the actual runtime value of the bind variable and determine if the MV can be used.

If we execute the same queries as before, the behavior changes significantly:

For department_id < 90, the optimizer still rewrites the query using the MV, just like in 19c.

SQL> SELECT department_id,
       COUNT(*) AS emp_count,
       SUM(salary) AS total_salary
FROM vahid.employees where department_id<90
GROUP BY department_id;
Enter fullscreen mode Exit fullscreen mode 
Execution Plan
----------------------------------------------------------
Plan hash value: 3470845597
-------------------------------------------------------------------------------------------
| Id  | Operation                    | Name       | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |            |     8 |    80 |     3   (0)| 00:00:01 |
|*  1 |  MAT_VIEW REWRITE ACCESS FULL| MV_EMP_DEP |     8 |    80 |     3   (0)| 00:00:01 |
-------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   1 - filter("MV_EMP_DEP"."DEPARTMENT_ID"<90)
Enter fullscreen mode Exit fullscreen mode

For department_id > 90, it reads from the base table, since the MV does not contain those rows.

SQL> SELECT department_id,
       COUNT(*) AS emp_count,
       SUM(salary) AS total_salary
FROM vahid.employees where department_id>90
GROUP BY department_id;
Enter fullscreen mode Exit fullscreen mode 
Execution Plan
----------------------------------------------------------
Plan hash value: 1192169904
--------------------------------------------------------------------------------
| Id  | Operation          | Name      | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |           |     8 |   208 |     4  (25)| 00:00:01 |
|   1 |  HASH GROUP BY     |           |     8 |   208 |     4  (25)| 00:00:01 |
|*  2 |   TABLE ACCESS FULL| EMPLOYEES |     8 |   208 |     3   (0)| 00:00:01 |
--------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   2 - filter("DEPARTMENT_ID">90)
Enter fullscreen mode Exit fullscreen mode

But most importantly, for the query that uses a bind variable (:depid = 90), Oracle AI Database 26ai now peeks at the bind value, verifies that it falls within the MV’s range (< 100), and successfully rewrites the query:

SQL> variable depid number
SQL> exec :depid:=90
PL/SQL procedure successfully completed.

SQL> SELECT department_id,
       COUNT(*) AS emp_count,
       SUM(salary) AS total_salary
FROM vahid.employees where department_id<90
GROUP BY department_id;
Enter fullscreen mode Exit fullscreen mode 
Execution Plan
----------------------------------------------------------
Plan hash value: 3470845597
-------------------------------------------------------------------------------------------
| Id  | Operation                    | Name       | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |            |     8 |    80 |     3   (0)| 00:00:01 |
|*  1 |  MAT_VIEW REWRITE ACCESS FULL| MV_EMP_DEP |     8 |    80 |     3   (0)| 00:00:01 |
-------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   1 - filter("MV_EMP_DEP"."DEPARTMENT_ID"<90)
Enter fullscreen mode Exit fullscreen mode

This marks a fundamental change. The optimizer can now use materialized views even for queries that rely on bind variables, achieving both query rewrite and cursor sharing simultaneously.

Top comments (0)