Load Ingest Data
Results 1 to 3 of 3

Thread: CDC Designer Creating Primary Keys Different from Source Database

  1. #1
    vgg659 is offline Junior Member
    Join Date
    Apr 2014
    Posts
    9
    Rep Power
    0

    CDC Designer Creating Primary Keys Different from Source Database

    When creating the mirror tables' primary keys, the CDC Designer appears to create the primary key column order differently then the source database primary key order.

    For example:
    VPDI VARCHAR2(6 CHAR)
    CODE VARCHAR2(4 CHAR)
    ID NUMBER(8,0)

    Oracle source table primary key:
    VPDI, CODE, ID

    CDC Mirror table primary key:
    ID , CODE, VPDI

    I am worried that this will impact performance. Why is Designer reordering the primary key columns?

  2. #2
    vgg659 is offline Junior Member
    Join Date
    Apr 2014
    Posts
    9
    Rep Power
    0

    Example

    See the following Oracle Create Table command, with the primary key column order PK1, PK3, and PK2:
    CREATE TABLE "TESTSCHEMA"."TEST"
    ("PK2" VARCHAR2(20 BYTE) NOT NULL ENABLE,
    "COL2" VARCHAR2(20 BYTE),
    "COL3" VARCHAR2(20 BYTE),
    "PK1" VARCHAR2(20 BYTE) NOT NULL ENABLE,
    "PK3" VARCHAR2(30 BYTE) NOT NULL ENABLE,
    CONSTRAINT "PK_TEST" PRIMARY KEY ("PK1", "PK3", "PK2")
    USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255
    STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
    PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
    BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
    TABLESPACE "USERS" ENABLE
    ) SEGMENT CREATION IMMEDIATE
    PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255
    NOCOMPRESS LOGGING
    STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
    PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
    BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
    TABLESPACE "USERS" ;


    See the CDC Instance Deployment Script for the same table, noticing that the order of the primary key columns are now PK1, PK2, and PK3.
    IF NOT EXISTS (SELECT 1 FROM [sys].[tables] WHERE SCHEMA_NAME([schema_id]) = N'TESTSCHEMA' AND [name] = N'TEST')
    CREATE TABLE [TESTSCHEMA].[TEST] (
    [PK2] nvarchar(20),
    [COL2] nvarchar(20),
    [COL3] nvarchar(20),
    [PK1] nvarchar(20),
    [PK3] nvarchar(30),
    CONSTRAINT [PK_TEST] PRIMARY KEY CLUSTERED (
    PK1 ASC,
    PK2 ASC,
    PK3 ASC
    )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
    ) ON [PRIMARY];


    I ran the following, and got these results:
    SELECT i.name AS index_name
    ,ic.index_column_id
    ,key_ordinal
    ,c.name AS column_name
    ,TYPE_NAME(c.user_type_id)AS column_type
    ,is_identity
    FROM sys.indexes AS i
    INNER JOIN sys.index_columns AS ic
    ON i.object_id = ic.object_id AND i.index_id = ic.index_id
    INNER JOIN sys.columns AS c
    ON ic.object_id = c.object_id AND c.column_id = ic.column_id
    WHERE i.is_primary_key = 1 and i.name = 'PK_TEST'
    order by index_name, column_name

    index_name index_column_id key_ordinal column_name column_type is_identity
    PK_TEST 2 3 PK1 nvarchar0
    PK_TEST 1 2 PK2 nvarchar 0
    PK_TEST 3 1 PK3 nvarchar 0

    This has to be a bug in CDC Designer. Does anyone have a work around or can I hope for a quick fix?
    Last edited by vgg659; 05-08-2014 at 03:29 PM.

  3. #3
    vgg659 is offline Junior Member
    Join Date
    Apr 2014
    Posts
    9
    Rep Power
    0

    Problem found

    I found the problem. The SQL Server stored procedure that gets the primary key index columns is not ordering the return set from Oracle's all_cons_columns.
    This problem is likely caused by adding columns to the base table and/or primary key subsequent to the original creation. In my case, I added the column TESTPK3 to the table and primary key after initially creating the table TEST and the primary key PK_TEST. This is a common occurrence in many of my source tables.
    Executing the following query returns the correct information, but not ordered by POSITION, and I suspect that the SQL Server store procedure is executing a similar query (missing an order by).

    select * from sys.all_cons_columns where owner = 'TESTSCHEMA' and table_name = 'TEST';
    TESTSCHEMAPK_TEST TESTPK1 1
    TESTSCHEMAPK_TEST TESTPK2 3
    TESTSCHEMAPK_TEST TESTPK3 2

    The SQL Server stored procedure should enforce the proper order using an order by in its all_cons_columns query.
    I confirmed the above, and created a local fix, by creating the following view within the CDC user's schema:
    create view all_cons_columns as select * from sys.all_cons_columns order by owner, constraint_name, table_name, position;
    Now, when I create a new Oracle CDC instance, I log in using my CDC user credentials, which uses my all_cons_columns view with an order by, and everything works.

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •