developers

    Forum Stats

  • 3,873,653 Users
  • 2,266,622 Discussions
  • 7,911,597 Comments

Discussions

NOT NULL constraints not validated by DBMS_REDEFINITION.COPY_TABLE_DEPENDENTS

User_6AT2M
User_6AT2M Member Posts: 1 Green Ribbon
edited May 4, 2022 6:46AM in SQL & PL/SQL

Hello,

when getting familiar with DBMS_REDEFINITION, I came across an (for me) unexpected behaviour: NOT-NULL-constraints might not be validated by COPY_TABLE_DEPENDENTS. This results in the possibility of data integrity corruption during rebuild.

I would like to illustrate this behaviour based on an example (Tested in Oracle 19.14). In the following I create a table with an index and a NOT-NULL-constraint on a column "text". The redefined "text" column shall hold a modified version of the original values. This modification shall ("unintentionally") result in some NULL values.

I would expect the NOT-NULL-constraint to prevent a successfull rebuild of the table, but the rebuild is performed with no errors:

-- Create schema
create table TEST_NULLS (
  id NUMBER (8) primary key,
  text VARCHAR2 (25) not null
);
create unique index I_TEST_NULLS on TEST_NULLS(text);

-- Insert some values
insert into TEST_NULLS (id, text) 
select rownum, 'some text '||rownum from dual connect by level <= 50;

-- Create interim table
create table TEST_NULLS_INTERIM (
  id NUMBER (8),
  text VARCHAR2 (20)
);

commit;

/* Redefinition */

BEGIN
  DBMS_REDEFINITION.START_REDEF_TABLE(
    uname=>user,
    orig_table=>'TEST_NULLS',
    int_table=>'TEST_NULLS_INTERIM',
    col_mapping=>'id id, substr(text, id, 2) text'
  );
END;
/

DECLARE
  num_errors PLS_INTEGER;
BEGIN
  DBMS_REDEFINITION.COPY_TABLE_DEPENDENTS(
    uname=>user,
    orig_table=>'TEST_NULLS',
    int_table=>'TEST_NULLS_INTERIM',
    copy_indexes=>DBMS_REDEFINITION.CONS_ORIG_PARAMS,
    num_errors=>num_errors
  );  
  DBMS_OUTPUT.PUT_LINE('Error count: '||num_errors);
END;
/
--> OK (no errors)

BEGIN
  DBMS_REDEFINITION.FINISH_REDEF_TABLE(user, 'TEST_NULLS', 'TEST_NULLS_INTERIM');
END;
/
drop table TEST_NULLS_INTERIM cascade constraints purge;
--> OK (no errors)

Since the NOT-NULL-constraint is not validated, NULL values are passed to the rebuilded table. One of the consequences is that some rows might be "hidden" for some queries:

select * from TEST_NULLS;
--> Only 12 rows are returned instead of 50!

select count(*) from TEST_NULLS;
--> 12, since the index I_TEST_NULLS is used (which only holds not null values)

select /*+ FULL(TEST_NULLS) */ count(*) from TEST_NULLS;
--> 50, as I would expect

select /*+ FULL(TEST_NULLS) */ count(*) from TEST_NULLS where text is null;
--> very odd result of 0!

Is this behaviour of not validating the NOT-NULL constraints intended? Is there an option to force the validation?

Regards,

Sebastian

DanielStein
developers