10 Replies Latest reply: Nov 28, 2012 4:10 PM by Lombo RSS

    DAC - "Create Index" Failure

    936133
      I am running a Learning Mgmt execution plan and one of the the tasks SDE_PSFT_LearningEnrollmentFact is failing and the details are below:

      1 - Truncate Table W_LM_ENROLLMENT_ACC_SNP_FS
      2 - Drop Index INDEX W_LM_ENROLLMENT_ACC_SNP_FS_U1
      3 - SDE_PSFT_LearningEnrollmentFact_Full
      *4 - Create Index INDEX W_LM_ENROLLMENT_ACC_SNP_FS_U1*
      5 - Analyze W_LM_ENROLLMENT_ACC_SNP_FS

      Step 4 is failing to create the index - how can I enable index creation (steps to take)

      Please share ideas/leads if you have come across a similar situation

      Thank you
        • 1. Re: DAC - "Create Index" Failure
          Voltaire
          Hi,

          Looks like you are facing a duplicate issue the with "1135313.1". If that is a duplicate issue you are running into the below sql should help you identify.

          SELECT * FROM W_LM_ENROLLMENT_ACC_SNP_F A
          WHERE ROWID >
          (
          SELECT MIN(ROWID) FROM W_LM_ENROLLMENT_ACC_SNP_F B
          WHERE
          A.INTEGRATION_ID = B.INTEGRATION_ID AND
          A.DATASOURCE_NUM_ID = B.DATASOURCE_NUM_ID
          ) ;

          SELECT * FROM W_LM_ENROLLMENT_EVENT_F A
          WHERE ROWID >
          (
          SELECT MIN(ROWID) FROM W_LM_ENROLLMENT_EVENT_F B
          WHERE
          A.INTEGRATION_ID = B.INTEGRATION_ID AND
          A.DATASOURCE_NUM_ID = B.DATASOURCE_NUM_ID
          ) ;

          your can delete the duplicates the if that is true. The status details on the execution plan task should you the detail error. Can you provide the log for index creation.

          Let me know if this helps.

          Regards,
          Jay
          • 2. Re: DAC - "Create Index" Failure
            936133
            Thanks Jay
            I will try it ASAP and will let you know how it goes- let me c if i can put up the log section without violating seciurity policies etc. Many thanks
            • 3. Re: DAC - "Create Index" Failure
              936133
              Jay,
              I have no data in OBAW for Learning Management- this is actually my first full load. The sql you provided wld be good to search for duplicate integration_id's in TOAD or Developer environment, and Im suspectin thats the issue, but how do you search for the duplicate integration_id on etl/dac level
              • 4. Re: DAC - "Create Index" Failure
                Srini VEERAVALLI
                It would nice if you share the actual dac error related to index..

                I would suggest to create index manually and find out the reasons..
                Since indexes are at dac side you have to look at database level based on dac error..

                let us know updates
                • 5. Re: DAC - "Create Index" Failure
                  Voltaire
                  Hi,

                  Can you get the "Create Index INDEX W_LM_ENROLLMENT_ACC_SNP_FS_U1" create statement. The actual physical index create statement would be part of the Status Description of the Index creation DAC task in the Run History.

                  The same "Status Description" would give the reason for the failure.

                  Regards,
                  Jay
                  • 6. Re: DAC - "Create Index" Failure
                    936133
                    Ok let me get that out - thanks guys.
                    • 7. Re: DAC - "Create Index" Failure
                      936133
                      Found this section in the log file


                      ANOMALY INFO::: Error while executing : CREATE INDEX:W_LM_ENROLLMENT_ACC_SNP_FS:W_LM_ENROLLMENT_ACC_SNP_FS_U1
                      MESSAGE:::java.lang.Exception: Error while execution : CREATE UNIQUE INDEX
                      W_LM_ENROLLMENT_ACC_SNP_FS_U1
                      ON
                      W_LM_ENROLLMENT_ACC_SNP_FS
                      (
                      INTEGRATION_ID Asc
                      ,DATASOURCE_NUM_ID Asc
                      )
                      NOLOGGING TABLESPACE BIAPPS_IDX
                      with error DataWarehouse:CREATE UNIQUE INDEX
                      W_LM_ENROLLMENT_ACC_SNP_FS_U1
                      ON
                      W_LM_ENROLLMENT_ACC_SNP_FS
                      (
                      INTEGRATION_ID Asc
                      ,DATASOURCE_NUM_ID Asc
                      )
                      NOLOGGING TABLESPACE BIAPPS_IDX
                      ORA-01452: cannot CREATE UNIQUE INDEX; duplicate keys found
                      • 8. Re: DAC - "Create Index" Failure
                        Voltaire
                        Hi,

                        This is a duplicate issue in W_LM_ENROLLMENT_ACC_SNP_FS table.

                        Try this
                        select rowid, INTEGRATION_ID, DATASOURCE_NUM_ID from W_LM_ENROLLMENT_ACC_SNP_FS where rowid not in (select min(rowid) from W_LM_ENROLLMENT_ACC_SNP_FS group by INTEGRATION_ID ,DATASOURCE_NUM_ID)

                        This would give the 2nd occurrence of the record. you can delete the rowid's returned. Or just to look for the duplicates this should help:

                        select INTEGRATION_ID, DATASOURCE_NUM_ID from W_LM_ENROLLMENT_ACC_SNP_FS group by INTEGRATION_ID, DATASOURCE_NUM_ID having count(*) > 1

                        Let me know if this helps.

                        Regards,
                        Jay
                        • 9. Re: DAC - "Create Index" Failure
                          936133
                          I really aprpeciate the help - i will get back to you ASAP with results.
                          • 10. Re: DAC - "Create Index" Failure
                            Lombo
                            Hi,

                            Usually the create index error occurs due to errors coming from the datasource... so it cannot created the index U1 (with the columns defined) due to duplicate information coming from the datasource.

                            Antonio
                            BExpert, Brazil