Using Notification Directives with True Cache

Use notification directives to check whether a True Cache read includes the latest committed change for a row.

Notification directives help ensure external consistency and data freshness when it’s essential that reads are not stale.

How the Data Freshness Check Works

A notification directive records committed changes for one table or JSON-relational duality view. It exposes a notification view that includes the exact COMMIT_SCN value for each tracked change.

Notification rows are visible only after the transaction commits.

Your application compares two SCNs:

• The snapshot SCN from the True Cache read
• The latest commit SCN for the same business key from the notification view on the primary database

Use the following pattern for reads that must avoid stale results but can still use True Cache when it has current data:

1. Read the row from True Cache and capture the snapshot SCN for that read.
2. At the same time, query the notification view on the primary database for the latest commit SCN for the row’s key.
3. Use the True Cache result when the snapshot SCN is greater than or equal to the commit SCN.
4. Read from the primary database when the commit SCN is newer.

Before You Start

• Review the notification directive prerequisites and restrictions in Oracle AI Database JSON-Relational Duality Developer’s Guide.
• Keep the retention window longer than the time your application expects True Cache to lag behind the primary database. Oracle suggests starting with at least 5 minutes.
• Make sure your application can connect to both True Cache and the primary database. For connection options, see Methods for Connecting to True Cache.
• Plan the data access method so the True Cache read and primary commit SCN lookup can run concurrently when latency matters.

Create the Notification Directive

Create the notification directive for the table that backs the True Cache read. Include the key columns your app uses to find the latest change. The key columns must also appear in the logged column list.

The notification table and notification view are created in the directive schema.

For example, this directive tracks inserts, updates, and deletes for an orders_base table:

CREATE DIRECTIVE orders_notify_dir
  FOR orders_base
  NOTIFY ON INSERT UPDATE DELETE
  USING NOTIFICATION TABLE orders_notify_tab
        NOTIFICATION VIEW orders_notify_vw
  WITH LOGGED COLUMNS (order_id, customer_id, order_total, status)
       KEY (order_id)
  ENABLE
  RETENTION 5 MINUTES;

For more examples and details, see Creating Notification Directives in Oracle AI Database JSON-Relational Duality Developer’s Guide.

Capture the True Cache Snapshot SCN

Capture the snapshot SCN as part of the True Cache query path. Keep the read and SCN capture close together, so your app compares the right SCN with the primary commit SCN.

Example: OCI Applications

For OCI applications, call the OCIAttrGet() function with the OCI_ATTR_SCN_BASE and OCI_ATTR_SCN_WRAP attribute types. For example:

OCIAttrGet((void *)ocisnap,
     (ub4)OCI_DTYPE_SNAP,
     (void *)&baseIn2,
     (ub4 *)0,
     (ub4) OCI_ATTR_SCN_BASE,
     errhp);

OCIAttrGet((void *)ocisnap,
     (ub4)OCI_DTYPE_SNAP,
     (void *)&wrapIn,
     (ub4 *)0,
     (ub4) OCI_ATTR_SCN_WRAP,
     errhp);

snapshot_scn = (ub8)((ub8)(wrapIn) << 32 | (ub8)(baseIn));

Example: JDBC Applications

For JDBC applications, run this query on the True Cache connection as a separate statement immediately after the row read:

SELECT current_scn
  FROM v$database;

Query Commit SCNs on the Primary Database

Run the commit SCN lookup on the primary database for the same business key you used for the True Cache read.

This query returns the latest retained commit SCN for one order. For example:

SELECT MAX(commit_scn)
  FROM orders_notify_vw
 WHERE order_id = :order_id;

To return logged columns from the primary lookup, get the latest notification row for the key. For example:

SELECT *
  FROM (
    SELECT *
      FROM orders_notify_vw
     WHERE order_id = :order_id
     ORDER BY commit_scn DESC
  )
WHERE ROWNUM = 1;

Related Topic

Notification Directives in Oracle AI Database JSON-Relational Duality Developer’s Guide