How do the ALTER TABLE to add column differ in SharePlex 9.1 and up versus earlier versions when configuring vertical partitioning with exclusion clause? (4299892)

How do the ALTER TABLE to add column differ in SharePlex 9.1 and up versus earlier versions when configuring vertical partitioning with exclusion clause?

How do the ALTER TABLE to add column differ in SharePlex 9.1 and up versus earlier versions when configuring vertical partitioning with exclusion clause?

General information. 

There is one major difference in the way the vertical partitioning treats the newly added column for tables with exclusion column partition in vertical partitioning where the vertical partitioned replication existed prior to the adding of new column:

For versions 9.1 and up:

The newly added column will be brought into replication. For example, if I initially had the following columns in my table named table2:

SQL> desc table2;

 Name                                      Null?    Type

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

 NUM_COL                                            NUMBER

 CHAR_COL1                                          VARCHAR2(10)

 CHAR_COL2                                          VARCHAR2(10)

The following is my active config file:

sp_ctrl (alvsupl14:8653)> view config verticaltest3

datasource:o.ORA11GR2

sha91.table2 !(CHAR_COL2) sha91.table2 alvsupl18@o.ora11gr2

If I now add another column named CHAR_COL3 to my source table as:

SQL> alter table table2 add (char_col3 varchar2(10));

The DML to this column named  CHAR_COL3 will be replicated. The table named TABLE2 will look like this:

SQL> desc table2;

 Name                                      Null?    Type

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

 NUM_COL                                            NUMBER

 CHAR_COL1                                          VARCHAR2(10)

 CHAR_COL2                                          VARCHAR2(10)

 CHAR_COL3                                          VARCHAR2(10)

Where the DML to column CHAR_COL2 is not replicated due to the exclusion clause in vertical partitioning.         

The dump of the object cache will also corroborate this fact. Here is the dump named source.out:

cat source.out

"SHA91"."TABLE2"

998281

   Type: TABLE Vertically partitioned

   Key Type: No unique key Number of key columns: ..

   Number of columns: 4

   Status: In Replication

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

Col |Length|Type      |Name                          |Column info

   1     22     NUMBER NUM_COL                        Key, Nullable

   2     10   VARCHAR2 CHAR_COL1                      Key, Nullable

   3     10   VARCHAR2 CHAR_COL2                      Column not replicated

   4     10   VARCHAR2 CHAR_COL3                      Key, Nullable

                             

                                        

For versions below 9.1:

There is a marked difference in the behavior of exclusion clause in versions prior to SharePlex 9.1.

I initially had the following columns in my table named table2:

SQL> desc table2;

 Name                                      Null?    Type

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

 NUM_COL                                            NUMBER

 CHAR_COL1                                          VARCHAR2(10)

 CHAR_COL2                                          VARCHAR2(10)

The following is my active config file:

sp_ctrl (alvsupl14:8653)> view config verticaltest3

datasource:o.ORA11GR2

sha91.table2 !(CHAR_COL2) sha91.table2 alvsupl18@o.ora11gr2

If I now add another column named CHAR_COL3 to my source table as:

SQL> alter table table2 add (char_col3 varchar2(10));

The DML to this column named  CHAR_COL3 will not be replicated. The table named TABLE2 will look like this:

SQL> desc table2;

 Name                                      Null?    Type

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

 NUM_COL                                            NUMBER

 CHAR_COL1                                          VARCHAR2(10)

 CHAR_COL2                                          VARCHAR2(10)

 CHAR_COL3                                          VARCHAR2(10)

Where the DML to column CHAR_COL2 and CHAR_COL3 is not replicated due to the exclusion clause in vertical partitioning. In essence, the exclusion clause initially excludes all columns forming part of the clause. However, all columns subsequently added to the source table are construed as being part of the exclusion clause and are not replicated.    The dump of the object cache confirms this fact:

cat source.out

"SHA91"."TABLE2"

998281

   Type: TABLE Vertically partitioned

   Key Type: No unique key Number of key columns: ..

   Number of columns: 4

   Status: In Replication

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

Col |Length|Type      |Name                          |Column info

   1     22     NUMBER NUM_COL                        Key, Nullable

   2     10   VARCHAR2 CHAR_COL1                      Key, Nullable

   3     10   VARCHAR2 CHAR_COL2                      Column not replicated

   4     10   VARCHAR2 CHAR_COL3                      Column not replicated

The object cache dump was only shown here to illustrate the point. To confirm the above behavior, one can run DML test instead of depending on dumping the object cache. If interested verifying this by object cache dump, please refer to numerous articles that delve on dumping the object cache. For details on the setting of vertical partitioning, please refer to SharePlex Administrator Guide.