How to optimize assertion performance

Assertions enable you to define cross-row, cross-table constraints. Oracle AI Database has many optimizations to ensure these are efficient. But in high-performance environments, you may still need to take action to ensure they are as fast as possible.

The question is: how?

We’ll review how to optimize creating assertions and their ongoing validation. Then we’ll cover a worked example to show this in practice. We’ll finish with general recommendations for writing efficient assertions.

How to optimize CREATE ASSERTION

When you first create an assertion, the database runs the whole query to check that existing data conforms to it. To make this faster, run the query in the assertion and tune it as you would any other SQL.

Note: if you’ve written the assertion using ALL … SATISFY syntax, this is only available within assertions. You’ll need to convert it to the NOT EXISTS equivalent first to do this.

But tuning the assertion’s query can only get you so far. When you create assertions on huge tables, it will take time to check existing rows.

To cut deployment times, you can either:

• Force your session to use parallel query before creating an assertion.
• Create assertions in the NOVALIDATE state.

Which of these you use depends on whether verifying existing data or having fast deployments is the priority for you. Using parallel query will help check the data faster. Whereas NOVALIDATE skips the verification step, so creation is instant.

If you do use NOVALIDATE, you can check existing data later:

/* Check all data conforms to an assertion */
alter assertion … validate;

As with creating an assertion, you can parallelize this operation. You can skip this step if you don’t want or need to verify values that were present before you created the assertion.

Once you’ve decided how to deploy an assertion, the next step is to optimize its ongoing validation. To help you see what tuning is needed, we’ll run through the validation process.

How assertion validation works

The process for checking an assertion after DML on the tables it uses is:

1. Capture the rows changed by DML statements.
2. Determine whether validation is needed.
3. If yes, validate that the changes meet the assertion.

The data captured at step 1 is written to global temporary tables (GTTs) defined when you create an assertion. The database will create a GTT for each table used in the assertion. If many assertions reference the same table, the database only creates one GTT to support all the table’s assertions.

These GTTs store the values of all the columns used in each assertion. Whenever you write to these columns, the database stores the changes in the assertion’s GTT.

So, if an assertion queries irrelevant columns, any DML on these columns does unnecessary work saving their values to the GTT. The standard advice to select only the columns you need applies to assertions too.

After capturing the changes, Oracle AI Database then uses an internal algorithm to determine whether validation is needed. This uses guard queries to check whether the new values might violate the assertion.

These guard queries run against the GTTs. These tables are system-controlled. So you can’t add any indexes or make any other changes to them, leaving you with few or no optimization opportunities.

If the guard queries find validation is needed, the database merges the changed values with the assertion to form the validation queries. These queries combine the changes in the GTTs with regular tables in the assertion. So inefficiencies in them slow the validation process. Focus your tuning efforts here.

How to optimize assertion validation queries

To verify an assertion, the database injects the changed rows stored in the GTT into the assertion. So if you have any assertion like:

create assertion ... check (
  all ( select ... from some_tab ) st
  satisfy (
    exists (
      select 'a matching row' from other_tab
      where  ...
    )
  )
)

The database needs to check new rows you add to some_tab, leading to a validation query like:

create assertion ... check (
  all ( select ... from inserted_rows ) st
  satisfy (
    exists (
      select 'a matching row' from other_tab
      where  ...
    )
  )
)

To ensure optimal performance, review the plans for these queries. A good way to do this is to run a SQL trace for the DML statement. This shows you the validation query and detailed information about its plan, helping you identify indexes that could make it faster.

But finding the validation queries in a trace can still be tricky!

The database swaps in the GTT and may have applied other transformations so they no longer look exactly like the assertion queries. Throw in the guard queries, other internal SQL, and whatever else you capture in your trace and they can be hard to find.

To help you narrow it down, you can query v$sql for statements with sql_text like:

'%SQL_ASSERTION obj#=<object_id>%'

Here, <object_id> is the object_id for the assertion. This filter includes the guard queries. Match the WHERE clauses for your assertions to these statements to find the validation queries. This text appears as a comment before the SQL, so you can only see this in v$sql and related views.  

Once you’ve identified the validation queries and their plans, you’ll need to draw on your regular SQL tuning skills. As a guideline, you’ll want indexes on the columns used to join and filter the values in the assertion. Like all SQL tuning exercises, exactly which indexes you create is a balance between minimizing the total indexes in the schema and maximizing performance for the query you’re tuning.

Putting it all together

Imagine a student class registration system with this schema:

create table classes (
  class_id           number generated by default as identity,
  class_code         varchar2(20) not null,
  class_name         varchar2(100) not null,
  prereq_class_id    number,
  constraint classes_pk 
    primary key ( class_id ),
  constraint classes_code_uk 
    unique ( class_code ),
  constraint classes_prereq_fk 
    foreign key ( prereq_class_id )
    references classes ( class_id ),
  constraint classes_not_own_prereq_ck 
    check ( prereq_class_id is null or prereq_class_id <> class_id )
);

create table student_class_enrollments (
  enrollment_id         number generated by default as identity,
  student_id            number not null,
  class_id              number not null,
  start_date            date not null,
  end_date              date not null,
  status                varchar2(20) not null,
  constraint student_class_enrollments_pk 
    primary key ( enrollment_id ),
  constraint sce_class_fk foreign key ( class_id )
    references classes ( class_id ),
  constraint sce_student_class_start_uk 
    unique ( student_id, class_id, start_date ),
  constraint sce_date_order_ck 
    check ( start_date < end_date ),
  constraint sce_status_ck 
    check ( status in ('ENROLLED', 'COMPLETED', 'DROPPED') )
);

These rules control which courses a student can register for:

• To register for classes with a prerequisite, the student must have completed the prerequisite class.
• A student can take a class many times, but they can’t have overlapping enrolments for one class.

These assertions enforce these rules:

/* Assertion to verify no students have overlapping enrollments for a class */
create assertion no_overlapping_enrollments
check (
  all (
    select e.rowid,
           e.student_id,
           e.class_id,
           e.start_date,
           e.end_date
    from   student_class_enrollments e
  ) e
  satisfy (
    not exists (
      select 'an overlapping enrollment'
      from   student_class_enrollments other_enrollment
      where  other_enrollment.student_id = e.student_id
      and    other_enrollment.class_id = e.class_id
      and    other_enrollment.rowid <> e.rowid
      and    other_enrollment.start_date < e.start_date
      and    e.start_date < other_enrollment.end_date
    )
  )
) novalidate;

/* Assertion to verify all students have COMPLETED prereq classes */
create assertion completed_prereq_class
check (
  all (
    select e.student_id,
           c.prereq_class_id
    from   student_class_enrollments e,
           classes c
    where  e.class_id = c.class_id
    and    c.prereq_class_id is not null
  ) e
  satisfy (
    exists (
      select 'completed prerequisite enrollment'
      from   student_class_enrollments pe
      where  pe.student_id = e.student_id
      and    pe.class_id = e.prereq_class_id
      and    pe.status = 'COMPLETED'
    )
  )
) novalidate;

To ensure they’re built quickly, they’re in the NOVALIDATE state. The database creates a GTT for each table used in the assertions – one for classes and one for student_class_enrollments.

When you insert a new enrolment, both assertions need to be validated to ensure:

• There’s no overlapping enrolment for the class.
• Either the class has no prerequisites or these have been completed.

The database runs three validation queries: one for each time the table appears in an assertion. The overlap assertion queries the enrolments table twice. This is why there are more validation queries than assertions.

The validation queries are like:

/* Overlap validation query 1 */
select e.student_id e_student_id, e.class_id e_class_id
from   student_class_enrollments other_enrollment,
       ( select 'inserted rows' * from gtt ) e
where  other_enrollment.student_id = e.student_id
and    other_enrollment.class_id = e.class_id
and    other_enrollment.rowid <> e.row$$
and    other_enrollment.start_date < e.start_date
and    e.start_date < other_enrollment.end_date;

/* Overlap validation query 2 */
select e.student_id e_student_id,
       e.class_id e_class_id
from   ( select 'inserted rows' * from gtt ) other_enrollment,
       student_class_enrollments e
where  other_enrollment.student_id = e.student_id
and    other_enrollment.class_id = e.class_id
and    other_enrollment.row$$ <> e.rowid
and    other_enrollment.start_date < e.start_date
and    e.start_date < other_enrollment.end_date;

/* Completed prereq class validation query */
select e.student_id e_student_id,
       c.prereq_class_id c_prereq_class_id,
       c.class_id c_class_id
from   ( select 'inserted rows' * from gtt ) e,
       classes c
where  not exists (
  select 'completed prerequisite enrollment'
  from   student_class_enrollments pe
  where  pe.student_id = e.student_id
  and    pe.class_id = c.prereq_class_id
  and    pe.status = 'COMPLETED'
)
and    e.class_id = c.class_id
and    c.prereq_class_id is not null;

These queries have these respective plans:

/* Overlap validation query 1 plan */
------------------------------------------------------------------
| Id  | Operation          | Name                                |
------------------------------------------------------------------
|   0 | SELECT STATEMENT   |                                     |
|   1 |  NESTED LOOPS      |                                     |
|*  2 |   TABLE ACCESS FULL| ORA$SA$TE_STUDENT_CLASS_ENROLLMENTS |
|*  3 |   INDEX RANGE SCAN | SCE_STUDENT_CLASS_START_UK          |
------------------------------------------------------------------

/* Overlap validation query 2 plan */
---------------------------------------------------------------------------

| Id  | Operation                    | Name                               |

---------------------------------------------------------------------------

|   0 | SELECT STATEMENT             |                                    |

|   1 |  NESTED LOOPS                |                                    |

|   2 |   NESTED LOOPS               |                                    |

|*  3 |    TABLE ACCESS FULL         | ORA$SA$TE_STUDENT_CLASS_ENROLLMENTS|

|*  4 |    INDEX RANGE SCAN          | SCE_STUDENT_CLASS_START_UK         |

|*  5 |   TABLE ACCESS BY INDEX ROWID| STUDENT_CLASS_ENROLLMENTS          |

---------------------------------------------------------------------------

/* Completed prereq class validation query plan */
---------------------------------------------------------------------------

| Id  | Operation                            | Name                       |

---------------------------------------------------------------------------

|   0 | SELECT STATEMENT                     |                            |

|   1 |  NESTED LOOPS ANTI                   |                            |

|   2 |   NESTED LOOPS                       |                            |

|*  3 |    TABLE ACCESS FULL                 | ORA$SA$TE_STUDENT_CLASS…   |

|*  4 |    TABLE ACCESS BY INDEX ROWID       | CLASSES                    |

|*  5 |     INDEX UNIQUE SCAN                | CLASSES_PK                 |

|*  6 |   TABLE ACCESS BY INDEX ROWID BATCHED| STUDENT_CLASS_ENROLLMENTS  |

|*  7 |    INDEX RANGE SCAN                  | SCE_STUDENT_CLASS_START_UK |

---------------------------------------------------------------------------

These all perform a full scan of the GTT holding the new enrolment details. The GTT contains the new values, so it should be small. These plans also all range scan the unique constraint index on the enrolments table.

The first overlap validation query is already optimal: it only reads the GTT and the enrolment unique index. The second must also access the table to get the end_date for each enrolment.

While students can retake classes, it’s unlikely they’ll do this more than a couple of times. So scanning the unique index should return at most a handful of entries for each row you insert.

The prerequisite validation query also does a unique scan of the classes primary key. This will return one row (the class the student registers for).

So, the existing constraint indexes answer these questions efficiently. If new enrolments will only be added a few at a time, you probably don’t need any further tuning.

But the constraint indexes don’t hold all the values the last two validation queries need. They must still access the tables to get the other columns. If you often bulk load new student enrolments, this may add too much drag. Covering indexes that include all the columns needed can help.

These covering indexes are:

• classes ( class_id, prereq_class_id )
• student_class_enrollments ( class_id, student_id, start_date, end_date )
• student_class_enrollments ( class_id, student_id, status )

Doing this would give three indexes on the enrolments table that start with class_id and student_id: the two above and the existing unique index. This adds lots of overhead for minimal benefit.

You could consolidate these by defining the unique constraint to use the index including the start & end dates. Then flip the order of the first two columns in the status index:

• student_class_enrollments ( class_id, student_id, start_date, end_date )
• student_class_enrollments ( student_id, class_id, status )

Again, the existing indexes are probably good enough, especially for single row inserts. However, it’s worth considering covering indexes as part of your wider indexing strategy. It’s possible either of the two indexes above is desirable for other common queries in your app. For example, the student_id, class_id, status index, could support a query to show students their current class registrations.

Remember: the goal isn’t to make the validation queries as fast as possible. It’s to make them fast enough to meet the needs of your application.

Summary

• If you don’t want or need to check existing data, build assertions in the NOVALIDATE state for fast deployments.
• To speed up assertion validation, whether creating or altering it, force parallel query for the operation.
• Ensure assertions only access the columns they need.
• Review the indexes on columns the assertion filters or joins on. Consider extending constraint indexes to cover assertion columns if this fits with your workload or overall indexing strategy.

Thanks to Toon Koppelaars, architect of assertions, for reviewing this post.