I have mentioned in the past two situations where DDL optimization is not possible:
- When the compatible mode is set to a release where this feature was not yet implemented
- When the table is compressed using BASIC mode
In this article, I will present a new restriction that has been signaled to me by Moustafa Ahmed @moustapha_dba. He agreed of course that I highjack his finding and write about it so that I will have a series of DDL optimization impeachment articles gathered at a single place.
Simply put, the DDL optimization is impossible on a table on which a VPD policy is applied.
SQL> select banner_full from v$version;
BANNER_FULL
------------------------------------------------------------------------
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.3.0.0.0
SQL> create table t1 as select rownum n1, lpad('x',5) v1
from dual connect by level <==5;
SQL> alter table t1 add c1 number default 42 not null;
SQL> select count(1) from t1 where c1=42;
COUNT(1)
----------
5
SQL> select * from table(dbms_xplan.display_cursor);
SQL_ID a4v8hg2qxzp1g, child number 0
-------------------------------------
select count(1) from t1 where c1=42
Plan hash value: 3724264953
---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 3 (100)| |
| 1 | SORT AGGREGATE | | 1 | 3 | | |
|* 2 | TABLE ACCESS FULL| T1 | 5 | 15 | 3 (0)| 00:00:01 |
---------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter(NVL("C1",42)=42) –- spot the presence of the NVL
The presence of the NVL function in the predicate part is enough to confirm that the DDL optimization kicked in during the above alter table.
Let’s now create a VPD policy, apply it to the t1 table and add a new column
-- create policy function
create or replace function
f_t1_policy(piv_schema in varchar2
,piv_object in varchar2)
return varchar2
is
lv_return_value varchar2(4000);
begin
if sys_context('USERENV','LANG') = 'US'
then
lv_return_value := '1=1';
else
lv_return_value := '1=0';
end if;
return lv_return_value;
end f_t1_policy;
/
-- assign this policy to t1 table
begin
dbms_rls.add_policy
(object_schema => user,
object_name => 'T1',
policy_name => 'F_T1_POLICY',
function_schema => user,
policy_function => 'F_T1_POLICY',
statement_types => 'SELECT'
);
end;
/
SQL> alter table t1 add c2 number default 43 not null;
SQL> select count(1) from t1 where c2=43;
COUNT(1)
----------
0
SQL> select * from table(dbms_xplan.display_cursor);
SQL_ID 6vk08skyq9v43, child number 0
-------------------------------------
select count(1) from t1 where c2=43
Plan hash value: 4294799605
----------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 1 (100)| |
| 1 | SORT AGGREGATE | | 1 | 3 | | |
|* 2 | FILTER | | | | | |
|* 3 | TABLE ACCESS FULL| T1 | 5 | 15 | 3 (0)| 00:00:01 |
----------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter(NULL IS NOT NULL)
3 - filter("C2"=43)
Two things can be spotted out from the predicate part (always keep an eye on the predicate part😊):
- filter(NULL IS NOT NULL): indicates that a policy has been applied to the select
- filter(“C2″=43): the absence of the NVL function indicates that Oracle didn’t use the DDL Optimization feature when it has been asked to add the c2 column
Despite there is a mix of two columns, c1 added using DDL optimization and c2 added without any optimization, Oracle is, nevertheless, able to manage this mix as shown via the below query where both columns are involved:
SQL> select count(1) from t1 where c1=42 and c2=43;
COUNT(1)
----------
0
SQL> select * from table(dbms_xplan.display_cursor);
SQL_ID 3n5t3vpw2an0k, child number 0
-------------------------------------
select count(1) from t1 where c1=42 and c2=43
SQL_ID 3n5t3vpw2an0k, child number 0
-------------------------------------
select count(1) from t1 where c1=42 and c2=43
Plan hash value: 4294799605
----------------------------------------------------
| Id | Operation | Name | Rows | Bytes |
----------------------------------------------------
| 0 | SELECT STATEMENT | | | |
| 1 | SORT AGGREGATE | | 1 | 3 |
|* 2 | FILTER | | | |
|* 3 | TABLE ACCESS FULL| T1 | 5 | 15 |
----------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter(NULL IS NOT NULL)
3 - filter((NVL("C1",42)=42 AND "C2"=43))
Ahmed Moustafa mentioned that his alter table add column performance pain is not present when he alter his user table using the SYS account. This is correct because the Oracle documentation says that SYS is free of any security policy. This is, indeed, backed up, via the following example:
SQL> show user
USER is "SYS"
SQL> alter table c##mhouri.t1 add c3 number default 44 not null;
Table altered.
SQL> select count(1) from c##mhouri.t1 where c3=44;
COUNT(1)
----------
5
SQL> select * from table(dbms_xplan.display_cursor);
SQL_ID 457vn8dqdu25t, child number 0
-------------------------------------
select count(1) from c##mhouri.t1 where c3=44
Plan hash value: 3724264953
---------------------------------------------------
| Id | Operation | Name | Rows | Bytes |
---------------------------------------------------
| 0 | SELECT STATEMENT | | | |
| 1 | SORT AGGREGATE | | 1 | 3 |
|* 2 | TABLE ACCESS FULL| T1 | 5 | 15 |
---------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter(NVL("C3",44)=44)
References
- DDL optimization for French readers
- DDL optimization for Chinese readers
- DDL optimization for English readers (the otn link is unobtainable)
Mohamed Houri’s Oracle Notes 