Column Level VPD on Indexed Columns Can Lead to Slow Execution Plan (Full Table Scan Instead of Index Access) (Doc ID 602280.1)

Last updated on AUGUST 25, 2015

Applies to:

Oracle Database - Enterprise Edition - Version 10.1.0.2 to 11.1.0.7 [Release 10.1 to 11.1]
Information in this document applies to any platform.
Checked for relevance on 23-Oct-2013


Symptoms

When using the SEC_RELEVANT_COLS_OPT clause for a VPD function (as documented, this is the method to obfuscate the contents of the the columns in the VPD function by displaying NULL instead of the actual value), when the protected columns are part of an index and the index is used in the execution plan before applying the VPD, the resulting predicate after applying the VPD becomes a CASE expression and, as a result, instead of index access into the table, a full table scan (FTS) is used by the execution plan.
The following example demonstrates the above:

--TESTCASE

create user test identified by test;
grant dba to test;
connect test/test

CREATE TABLE test_vpd (
id                 INTEGER      NOT NULL,
column_under_vpd   NUMBER     NULL,
column_without_vpd VARCHAR2(10)     NULL
) TABLESPACE users;

INSERT INTO test_vpd (id, column_under_vpd, column_without_vpd)
SELECT LEVEL, TO_CHAR(LEVEL), TO_CHAR(LEVEL)
FROM dual
CONNECT BY LEVEL <= 1000000;

CREATE INDEX vpd_index ON test_vpd(column_under_vpd) TABLESPACE users;
CREATE INDEX non_vpd_index ON test_vpd(column_without_vpd) TABLESPACE users;

BEGIN
DBMS_STATS.GATHER_TABLE_STATS (
ownname          => 'test',
tabname          => 'TEST_VPD',
partname         => NULL,
estimate_percent => 100,
method_opt       => 'FOR ALL COLUMNS SIZE AUTO',
cascade          => TRUE
);
END;
/

CREATE OR REPLACE FUNCTION test_vpd_policy_function
(p_schema IN VARCHAR2,
p_object IN VARCHAR2)
RETURN VARCHAR2
IS
    v_predicate VARCHAR2(1000);
BEGIN
    IF (SYS_CONTEXT('USERENV', 'SESSION_USER') = 'TEST') 
    THEN 
        v_predicate := 'column_under_vpd BETWEEN 1 AND 5000';
    ELSE
        v_predicate := '1 = 2';
    END IF;
    RETURN v_predicate;
END test_vpd_policy_function;
/

BEGIN
SYS.DBMS_RLS.ADD_POLICY     (
object_schema          => 'test'
,object_name           => 'TEST_VPD'
,policy_name           => 'TEST_VPD_COLUMN_POLICY'
,function_schema       => 'test'
,policy_function       => 'TEST_VPD_POLICY_FUNCTION'
,statement_types       => 'SELECT'
,policy_type           => dbms_rls.dynamic
,long_predicate        => TRUE
,sec_relevant_cols     => 'COLUMN_UNDER_VPD'
,sec_relevant_cols_opt => dbms_rls.all_rows
,update_check          => FALSE
,static_policy         => FALSE
,enable                => TRUE );
END;
/

BEGIN
dbms_rls.enable_policy(
object_schema => 'test',
object_name   => 'TEST_VPD',
policy_name   => 'TEST_VPD_COLUMN_POLICY',
enable        => TRUE);
END;
/

SELECT *
FROM test_vpd
where column_under_vpd = '2000';

------------------------------------------------------------------------------
| Id  | Operation         | Name     | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |          | 20000 |   312K|  1779   (8)| 00:00:22 |
|*  1 |  TABLE ACCESS FULL| TEST_VPD | 20000 |   312K|  1779   (8)| 00:00:22 |
------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter(CASE  WHEN ("COLUMN_UNDER_VPD">=1 AND
              "COLUMN_UNDER_VPD"<=5000) THEN "COLUMN_UNDER_VPD" ELSE NULL END =2000)


As seen above, a FTS will be used as access path, because of the above created filter. This can be particularly bad for very large tables, because the index that was used before applying the VPD policy(column_under_vpd = '2000'), is no longer used.
If one disables the policy and then uses the generated predicate in the where clause, trying to get the same result as if it was a vpd predicate applied:

BEGIN
dbms_rls.enable_policy(
object_schema => 'test',
object_name => 'TEST_VPD',
policy_name => 'TEST_VPD_COLUMN_POLICY',
enable => FALSE);
END;
/

SQL> SELECT CASE WHEN (COLUMN_UNDER_VPD>=1 AND
COLUMN_UNDER_VPD<=5000) THEN "COLUMN_UNDER_VPD" ELSE NULL END "out"
FROM test_vpd
where
column_under_vpd = '2000';

------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 2 | 10 | 3 (0)| 00:00:01 |
|* 1 | INDEX RANGE SCAN| VPD_INDEX | 2 | 10 | 3 (0)| 00:00:01 |
------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

1 - access("COLUMN_UNDER_VPD"=2000)


a much better execution plan is obtained.

As well, this scenario does not occur if the columns in the index used by the plan are not protected with VPD:

SELECT *
FROM test_vpd
where column_without_vpd = '2000'


Execution Plan
----------------------------------------------------------
Plan hash value: 1794586750

---------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 16 | 4 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| TEST_VPD | 1 | 16 | 4 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | NON_VPD_INDEX | 1 | | 3 (0)| 00:00:01 |
---------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

2 - access("COLUMN_WITHOUT_VPD"='2000')



Cause

Sign In with your My Oracle Support account

Don't have a My Oracle Support account? Click to get started

My Oracle Support provides customers with access to over a
Million Knowledge Articles and hundreds of Community platforms