In this release, SQL expressions such as, CONCAT, SUM, TRIM, MIN, or MAX, that involve a redacted column can be used in the SELECT list of a CREATE VIEW definition or an inline view.

In the following examples, c1 has a redaction policy defined.

create view v1 as select sum(c1) sum_c1 from t1 order by c1;
select * from v1;

select * from (select sum(c1) sum_c1 from t1 order by c1);

Oracle Data Redaction supports redaction policies on the base columns of function-based indexes and extended statistics beginning with Oracle Database 23ai.

This allows customers to combine these database performance features with database security capabilities. For more information on function-based indexes and extended statistics see the Oracle Database Development Guide and the Oracle Database SQL Tuning Guide.

In this release, SQL expression on columns with redaction policy can be used in the SELECT list and GROUP BY clause.

A GROUP BY clause allows you to group the selected rows based on the value for each row and return a single row of summary information for each group. In the following example, c1 has a redaction policy defined on it.

select (c1+5)
from t1
group by (c1+5);

In this release, a DISTINCT clause with ORDER BY clause on columns containing a Data Redaction policy can be used.

A DISTINCT clause specifies you want the database to return only one copy of each set of duplicate rows selected.

A ORDER BY clause allows you to specify the order in which you want the selected rows returned.

In the following example, c1 has a redaction policy defined on it.

select distinct c1
from t1
order by c1;

This release supports columns that contain a Data Redaction policy used by queries that have an inline view with set operation having a subquery.

A subquery in the FROM clause of a SELECT statement is also called an inline view.

In this release, a column in a set operation can have a redaction policy with different values for its properties than the redaction policy in the corresponding column of the set operator.

In addition, a set operation query can have a column with redaction policy on one side of the set operation with the corresponding column on the other side of the set operation having no redaction policy.

Set operators combine the rows returned by two SELECT statements into a single result using UNION, UNION ALL, INTERSECT, INTERSECT ALL, EXCEPT, EXCEPT ALL, MINUS, or MINUS ALL.

select c1 from t1
union
select c2 from t1

The result of this query will be fully redacted as described in Redaction Results for Queries with SQL Expressions and Set Operation When Using Columns With Redaction Policies.

In this release, if a SQL expression on columns with redaction policy is present in the SELECT list of a query, the result of the SQL expression is redacted using full redaction regardless of the underlying redaction type.

Similarly, if columns with redaction policy are present on either side of a set operation, the result of the set operation is redacted using full redaction regardless of the underlying redaction type.