1 2 Previous Next 15 Replies Latest reply: Sep 20, 2012 1:03 PM by Barry McGillin RSS

    Migrate index messes up columns of index

    854743
      Hi

      I'm migrate from MS SQL 2005 to Oracle 11g using SQL Developer V3.0.04 and using the batch file migration.bat.
      Everything worked fine.

      Now I'm facing the following problem:

      In MS SQL I have table with the following definition

      CREATE TABLE [dbo].[AssociationDirectory](
           [ID] [uniqueidentifier] NOT NULL,
           [MasterID] [uniqueidentifier] NOT NULL,
           [SlaveID] [uniqueidentifier] NOT NULL,
           [AssociationClass] [varchar](50) NOT NULL,
           [AssociationType] [varchar](200) NOT NULL,
           [AuthorizationID] [uniqueidentifier] NOT NULL,
           [ConcurrencyID] [uniqueidentifier] NOT NULL,
      CONSTRAINT [PK_AssociationDirectory] PRIMARY KEY CLUSTERED

           (
                [ID] ASC
           )
      ) ON [PRIMARY]

      CREATE UNIQUE NONCLUSTERED INDEX [IX_AssociationDirectory] ON [dbo].[AssociationDirectory]

      (
           [AssociationClass] ASC,
           [MasterID] ASC,
           [SlaveID] ASC,
           [ID] ASC
      )

      The index is migrated to the follwoing definition in Oracle:

      CREATE UNIQUE INDEX IX_AssociationDirectory ON AssociationDirectory
      (
      ID,
      AssociationClass,
      MasterID,
      SlaveID
      )
      ;

      As one can see the sequence of the columns are changed.
      Where or how can I maintain the correct sequence of columns in the index?

      Thank you for informations
      Regards
      Beat
        • 1. Re: Migrate index messes up columns of index
          wkobargs-Oracle
          Hi Beat,

          never heard about such an issue. I will do some research and try it out myself and will come back to you later.

          Regards
          Wolfgang
          • 2. Re: Migrate index messes up columns of index
            854743
            Hi Wolfgang

            Thanks for the fast reply.
            Surprisingly this was working correct before.
            I found in the sqldeveloper that the capture-step already has the wrong sequence

            Regards Beat
            • 3. Re: Migrate index messes up columns of index
              wkobargs-Oracle
              Hello Beat,

              I also see the wrong order in my captured model, but the order is SlaveID, MasterID, AssociationClass, ID.

              But after the migration has finished, I see the correct order in the destination database.

              My SQL Developer version is the same as yours.

              Can you please look into your migration repository? Please show me the output of:

              select NATIVE_SQL from MD_INDEXES where INDEX_NAME='IX_AssociationDirectory';

              For me it returns:

              "PROMPT Creating Unique Index IX_AssociationDirectory on AssociationDirectory...
              CREATE UNIQUE INDEX IX_AssociationDirectory ON AssociationDirectory
              (
              AssociationClass,
              MasterID,
              SlaveID,
              ID
              )
              ;"

              Regards
              Wolfgang
              • 4. Re: Migrate index messes up columns of index
                854743
                Hi Wolfgang
                I just captured and converted again. This is my output:

                NATIVE_SQL
                -------------------------------------------------------------------------------

                PROMPT Creating Unique Index IX_AssociationDirectory on AssociationDirectory...
                CREATE UNIQUE INDEX IX_AssociationDirectory ON AssociationDirectory
                (
                ID,
                MasterID,
                SlaveID,
                AssociationClass
                )
                ;

                The captured-step in sqldeveloper shows the sequence:
                ID,
                AssociationClass,
                SlaveID,
                MasterID

                To me it looks like random order of columns

                Regards
                Beat
                • 5. Re: Migrate index messes up columns of index
                  854743
                  Hi Wolfgang

                  Forgot to say:
                  I migrate offline into sql scriptfiles.

                  Regards
                  Beat
                  • 6. Re: Migrate index messes up columns of index
                    wkobargs-Oracle
                    Hello Beat,

                    yes, that looks like a random order.

                    I am using the online migration (I can't login on the machine on that SQL Server is installed, so I can't run the files that SQL Developer creates for an offline migration). You are using the offline migration. Is it possible for you to try an online migration? If you have no connection from the machine on which SQL Developer is running to the machine on that SQL Server is running then it is not possible. But if the size of your database is the reason for using the offline migration - is it possible to make a little SQL Server test database with just this table and index and migrate that one online to see whether that makes a difference?

                    Regards
                    Wolfgang
                    • 7. Re: Migrate index messes up columns of index
                      854743
                      Hi Wolfgang

                      Here what I did and the result:
                      Created a database in MSSql. Created table and indexes as described above.
                      Created an empty Oracle database
                      Migrated to Oracle database with sqldeveloper (not migrate.bat)
                      Selected "Truncate Migration Repository". Required Online migration
                      Checked the Oracle database afterwards: Database was still empty.
                      Checked the migration output folder and loaded script to empty database:
                      Script and database shows sequence: MasterID, SlaveID, AssociationClass, ID

                      Somehow strange, that I have to load table and index with the script.
                      But the sequence is still wrong.

                      Regards
                      Beat
                      • 8. Re: Migrate index messes up columns of index
                        wkobargs-Oracle
                        Hi Beat,

                        unfortunately I can't reproduce your issue.

                        Are you using the default mapping so that uniqueidentifier is mapped to char(36)?
                        It seems that you can reproduce the wrong order of the index columns easiliy. Can you try out what happens if you create the table in SQL Server using char instead of uniqueidentifier. I just want to know whether it plays a role that the datatype uniqueidentifier is used in the index. If your table does not contain any column of type uniqueidentifier and you get also a wrong order of the columns in the index after the migration then I know that this datatype does not contribute to the problem.

                        Regards
                        Wolfgang
                        • 9. Re: Migrate index messes up columns of index
                          854743
                          Hi Wolfgang

                          I can confirm: uniqueidentifier is mapped to char(36)

                          I created more tables in MSSql using instead uniqueidentifier char(36) and one with int
                          Converted to Oracle and found sequences:
                          Table with uniqueIdentifier: ID, MasterID, SlaveID, AssociationClass
                          Table all uniqueidentifier to char(36): ID, MasterID, SlaveID, AssociationClass
                          Table all uniqueidentifier to int: MasterID, SlaveID, AssociationClass, ID

                          Looks random to me.

                          Regards
                          Beat
                          • 10. Re: Migrate index messes up columns of index
                            wkobargs-Oracle
                            Hello Beat,

                            thanks for making this test.

                            Yesterday you wrote:
                            Surprisingly this was working correct before.
                            What does that mean exactly? With an older version of SQL Developer?

                            I am using Version 3.0.04 Build MAIN-04.34. Is yours exactly the same?

                            Om which platform are you running SQL Developer, on Windows XP like me?

                            Regards
                            Wolfgang
                            • 11. Re: Migrate index messes up columns of index
                              854743
                              Hi Wolfgang

                              Sqldeveloper: Version 3.0.04 Build MAIN-04.34
                              OS used: MS XP SP3

                              I verified in other migration the result and found a couple of wrong-sequenced index-columns.

                              Therefore my statement: It worked before is NOT correct.
                              We just came up by accident (performance problem with this table) with this fact.

                              I guess:
                              The migration does not guarantee the correct sequence of columns in the index.
                              Sometimes its correct, sometimes its mixed up.
                              What points also to random sequence is the fact, that the sqldeveloper-migration-project (captured and converted step) does not provide any information about the sequence.

                              Regards
                              Beat
                              • 12. Re: Migrate index messes up columns of index
                                wkobargs-Oracle
                                Hi Beat,

                                thanks for this information. So it is not the version of SQL Developer nor the version of the OS that makes this problem happen as they are identical at your and at my side.

                                This behavior is not good and I think it should be improved. But for logging a bug we need to have a reproducer. And to be able to reproduce this we need to work more closely together as it is possible through this channel. So I suggest that you open a Service Request through My Oracle Support. Through an SR we can exchange files (perhaps we need to ask for some files from you), and we can work at a higher priority on an SR than in a forum thread.

                                So if you are interested that we should follow this up, then please log an SR.

                                Regards
                                Wolfgang
                                • 13. Re: Migrate index messes up columns of index
                                  854743
                                  Hi Wolfgang

                                  Thanks for the infos.
                                  I opened a SR

                                  Regards
                                  Beat
                                  • 14. Re: Migrate index messes up columns of index
                                    854743
                                    Hi

                                    Finally Oracle managed to reproduce the problem.
                                    It is documented in
                                    Bug 13349559 - RANDOM ORDER OF INDEX COLUMNS AFTER MIGRATION
                                    A solution is pending.

                                    Regards
                                    Beat
                                    1 2 Previous Next