5 Replies Latest reply: Sep 27, 2012 1:55 AM by 903623 RSS

    migrating a Sybase Index with ignore_dup_key

    903623
      HI All,

      I am involved with Sybase to Oracle migration.

      I need to migrate a Index with ignore_dup_key.

      I am currently inserting the data into the table and then deleting the duplicate enteries.

      Can someone suggest me a better approach..


      Thanks,
      Riddhisha

      Edited by: Riddhisha on Apr 13, 2012 3:18 PM
        • 1. Re: migrating a Sybase Index with ignore_dup_key
          Mkirtley-Oracle
          Riddhisha,
          When are you seeing this problem ?
          Have you now migrated the data into Oracle but are getting duplicate values or errors from Oracle inserts ? The 'ignore_dup_key' prevents duplicate data from being inserted on the Sybase side, so what is the "create index" statement for the table in Sybase and Oracle for the table giving you problems ?

          Regards,
          Mike
          • 2. Re: migrating a Sybase Index with ignore_dup_key
            903623
            Hi Mike,

            This table gets populated through a procedure in oracle, so when the procedure runs in oracle there are additional rows in the table, which are not inserted in Sybase due to the ignore_dup_key.

            So i need to a remove those records from oracle table.

            I wanted to know if there is way through which we can replicate the behaviour of ignore_dup_key in oracle.

            Thanks,
            Riddhisha
            • 3. Re: migrating a Sybase Index with ignore_dup_key
              Mkirtley-Oracle
              Riddhisha,
              That is why I asked if you had an index on the Oracle table. A unique constraint or similar would prevent duplicate rows. However, I am not sure if there is an equivalent to ignore_dup_key where only the duplicate rows are rejected if it is set to yes.
              I think your best option would be ask in the SQL and PL/SQL forum -

              SQL and PL/SQL

              They will be better able to help with a question like that.

              Regards,
              Mike
              • 4. Re: migrating a Sybase Index with ignore_dup_key
                Pnauduri-Oracle
                I think there is some confusion around ignore_dup_key functionality. Ignore_dup_key in Sybase essentially allows an application to continue without aborting a on-going transaction even if there was an attempt to insert duplicate values for columns that have a Unique index on them with ignore_dup_key clause. But (I emphasize) Sybase does not allow duplicate values in a column on which a unique index exists.

                Typically when you try to insert duplicate values for columns with unique/primary indexes, you get a "unique constraint violation error". This error under normal conditions will result in the failure of the complete transaction. With IGNORE_DUP_KEY clause, the sybase database simply ignores that "INSERT/UPDATE" statement and continues with the rest of the transaction.

                What that means is if you have a index on a table in Sybase with Ignore_dup_key clause then most likely that index is a UNIQUE index. So that means you shouldn't see duplicate values in the affected column(s). If you do see duplicate values then the implication is that the index is not a UNIQUE index and hence IGNORE_DUP_KEY clause does not really add value. Customer may have changed their schema design later realizing that those columns will have duplicate values but forgot to take out the IGNORE_DUP_KEY clause. Also if you already have duplicate values for a column, you cannot create a UNIQUE index even in Sybase with this clause.

                Since you are seeing duplicate values in the table so I am guessing that it is a regular B*tree index so you can do the same in Oracle and ignore the IGNORE_DUP_KEY clause as Oracle does not offer such functionality.

                If you really want to create a unique index in Oracle and not have the duplicate values then you will have to try couple of different approaches. Create an External table and use PL/SQL procedure/function to load the data using MERGE statements or INSERT INTO... SELECT FROM EXTERNAL_TABLE with the LOG ERROR Clause to reject the duplicate values without failing the overall data load.

                Hope this helps..

                Regards

                Prakash
                • 5. Re: migrating a Sybase Index with ignore_dup_key
                  903623
                  Oracle 11.2.0.3 has IGNORE_ROW_ON_DUPKEY_INDEX hint which can be used for this functionality.