Delete with no rollback chooses traf_delete rather than vsbb_delete

If the key column is not defined with “not null”, then the plan will choose “traf_delete”

If the key column is defined with “not null”, then the plan will choose “vsbb_delete”.

 

Below are my test case,

 

---------------1

SQL>cqd allow_nullable_unique_key_constraint 'on';

SQL>create table test_1(a int, b int) store by (a);

SQL>explain options 'f' delete with no rollback from test_1;

 

LC   RC   OP   OPERATOR              OPT       DESCRIPTION           CARD  

---- ---- ---- --------------------  --------  --------------------  ---------

 

3    .    4    root                                                  1.00E+002

1    2    3    tuple_flow                                            1.00E+002

.    .    2    trafodion_delete      h         TEST_1                1.00E+000

.    .    1    trafodion_scan                  TEST_1                1.00E+002

 

---------------2

SQL>create table test_2(a int not null, b int) store by(a);

SQL>explain options 'f' delete with no rollback from test_2;

LC   RC   OP   OPERATOR              OPT       DESCRIPTION           CARD  

---- ---- ---- --------------------  --------  --------------------  ---------

 

3    .    4    root                                                  1.00E+002

1    2    3    tuple_flow                                            1.00E+002

.    .    2    trafodion_vsbb_delet            TEST_2                1.00E+000

.    .    1    trafodion_scan                  TEST_2                1.00E+002