Background: 

When executing queries on SDV views that have Column-Based Security (CBS), performance issues arise when the query includes a WHERE clause or other filtering conditions on CBS columns.

The issue is that TDV does not push these queries down to Oracle, which results in significantly slower performance compared to queries on non-CBS columns.

• This happens because Oracle does not recognize the custom function or CBS policy applied in SDV.
• Since the function is unknown to Oracle, SDV executes the filtering internally, leading to slower performance.

To resolve this, we need to map SDV’s custom CBS function to an Oracle function that supports pushdown. By doing this, Oracle natively processes the filtering, preventing SDV from handling it internally, thereby resolving the performance issue.

1. Find an Alternative function that is supported in Oracle.

This can be done by either finding an equivalent Oracle built-in function or by creating a custom function in Oracle.

To validate this, the user can connect directly to the Oracle database using another tool (such as DBVisualizer) and create/test the function there to ensure it executes successfully in Oracle.

Once confirmed, the SDV function can then be mapped to the Oracle function. This ensures that whenever SDV calls the custom function, it will be rewritten and executed as the corresponding Oracle function.

2. Map the SDV Function to Oracle’s Function:

Navigate to the Oracle capabilities file in TDV, located at:

 <Installation_dir>\apps\dlm\cis_ds_oracle\conf\oracle_<driver_version>.oracle

Add the line to map the SDV's custom function (eg. 6c_mask) to Oracle's Function:

Use the schema name in the capabilities file mapping. 

<TDV_custom_function>: <schema_name>.<Oracle_function_name>

Example:

-> 1e_mask(~string): XYZ.funct_1e_mask($1)

-> 6c_mask(~string):SCHEMA.REGEXP_REPLACE($1, '[^-]', ''), 0)

This mapping enables Oracle to recognize and process the SDV's function during query execution.

3. Rerun the query with the WHERE clause applied to the CBS column.

This approach allows queries with filtering on CBS columns to be efficiently pushed down to Oracle, improving query performance.

This article outlines the process to enable query pushdown with custom functions (e.g., columns with CBS applied) in SDV (formerly known as TDV).