This discussion is archived
5 Replies Latest reply: Sep 26, 2012 11:55 PM by 903623 RSS

migrating a Sybase Index with ignore_dup_key

903623 Newbie
Currently Being Moderated
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 Expert
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Expert
    Currently Being Moderated
    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
    84959 Explorer
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    Oracle 11.2.0.3 has IGNORE_ROW_ON_DUPKEY_INDEX hint which can be used for this functionality.

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points