3 Replies Latest reply on Sep 6, 2016 12:23 PM by 3054700

    Default semantic index

    3054700

      Dear all,

       

      I mistakenly removed the default PCSGM index and now can't bulk load data in the models:

      BEGIN
       sem_apis.bulk_load_from_staging_table('mymodel', 'MYUSER', 'STAGE_TABLE');
      END;
      (error) DBD::Oracle::db do failed: ORA-13199:  EXCH: SQLERRM=ORA-14098: index mismatch for tables in ALTER TABLE EXCHANGE PARTITION
      ORA-06512: at "MDSYS.MD", line 1723
      ORA-06512: at "MDSYS.MDERR", line 17
      ORA-06512: at "MDSYS.SDO_RDF_INTERNAL", line 7815
      ORA-06512: at "MDSYS.SDO_RDF", line 1014
      ORA-06512: at "MDSYS.RDF_APIS", line 926
      ORA-06512: at line 2 (DBD ERROR: OCIStmtExecute)
      

       

      I tried to recreate it but probably not the correct way (not unique?)

       

      EXECUTE SEM_APIS.ADD_SEM_INDEX('PCSGM');
      

       

      On an other model already created ( MODEL_331),  the following query gives:

       select *
        From Dba_Segments
        where PARTITION_NAME = 'MODEL_331';
      

       

      MDSYS    RDF_LNK_PCSGM_IDX    MODEL_331    INDEX PARTITION    ASSM    RDF_USERS    4    2359938    4630511616    565248    252    8388608    1048576    1    2147483645    2147483645    1024    DEFAULT    DEFAULT    DEFAULT    DISABLED

      MDSYS    RDF_LINK$            MODEL_331    TABLE PARTITION    ASSM    RDF_USERS    4    8398338    429391872      52416      124    65536      1048576    1    2147483645    2147483645    1024    DEFAULT    DEFAULT    DEFAULT    DISABLED

       

      and the model I'm trying to create

        select *
        From Dba_Segments
        where PARTITION_NAME = 'MODEL_378'; 
      

      returns no rows;

       

      I'd like to know how to restore default indexes and drop all others.

       

      Thank you!

      Fred

        • 1. Re: Default semantic index
          Sdas-Oracle

          If there are no duplicates that sneaked in to RDF_LINK$ partitions, then you can do the following stepsL

          1) obtain the DDL for the NONUNIQUE PCSGM index creation using DBMS_METADATA.GET_DDL

          2) use sem_apis.drop_sem_index('PCSGM') to drop the NONUNIQUE PCSGM index

          3) replace CREATE INDEX with CREATE UNIQUE INDEX in the DDL command and then invoke the CREATE UNIQUE INDEX command and see if it succeeds (it will fail if there are duplicates).

           

          Here is an example:

           

          SQL> conn system/manager

           

          Connected.

          SQL> set long 10000

          SQL> set pages 10000

          SQL> select dbms_metadata.get_ddl('INDEX','RDF_LNK_PCSGM_IDX','MDSYS') from dual;

          DBMS_METADATA.GET_DDL('INDEX','RDF_LNK_PCSGM_IDX','MDSYS')

          --------------------------------------------------------------------------------

            CREATE INDEX "MDSYS"."RDF_LNK_PCSGM_IDX" ON "MDSYS"."RDF_LINK$" ("P_VALUE_ID",

          "CANON_END_NODE_ID", "START_NODE_ID", "G_ID", "MODEL_ID")

            PCTFREE 10 INITRANS 2 MAXTRANS 255  NOLOGGING

            STORAGE(

            BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT) LOCAL

          (PARTITION "MODEL_0"

            PCTFREE 10 INITRANS 2 MAXTRANS 255 NOLOGGING

            STORAGE(

            BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)

            TABLESPACE "TBS_3" ,

          PARTITION "MODEL_1"

            PCTFREE 10 INITRANS 2 MAXTRANS 255 NOLOGGING

            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 "TBS_3" ) COMPRESS 2

           

          SQL> exec sem_apis.drop_sem_index('PCSGM');

          PL/SQL procedure successfully completed.

           

          SQL> CREATE UNIQUE INDEX "MDSYS"."RDF_LNK_PCSGM_IDX" ON "MDSYS"."RDF_LINK$" ("P_VALUE_ID",

          "CANON_END_NODE_ID", "START_NODE_ID", "G_ID", "MODEL_ID")

            PCTFREE 10 INITRANS 2 MAXTRANS 255  NOLOGGING

            STORAGE(

            BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT) LOCAL

          (PARTITION "MODEL_0"

            PCTFREE 10 INITRANS 2 MAXTRANS 255 NOLOGGING

            STORAGE(

            BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)

            TABLESPACE "TBS_3" ,

          PARTITION "MODEL_1"

            PCTFREE 10 INITRANS 2 MAXTRANS 255 NOLOGGING

            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 "TBS_3" ) COMPRESS 2;

            2    3    4    5    6    7    8    9   10   11   12   13   14   15   16 

          Index created.

           

          SQL>

           

          In case step number 3 above fails with "ORA-01452: cannot CREATE UNIQUE INDEX; duplicate keys found", you will need to carefully remove the duplicates (i.e., rows having same PCSGM values), from the appropriate RDF_LINK$ partitions and replace each such set of duplicates with a single row with that PCSGM value and COST column value being the sum of COST column values all the corresponding duplicate rows. This step is complicated and must be done carefully to avoid data corruption. If you have problem, please let us know.

           

          Thanks,

          - Souri.

          1 person found this helpful
          • 2. Re: Default semantic index
            Sdas-Oracle

            When CREATE UNIQUE INDEX attempt (step number 3 in my last reply) fails with "ORA-01452: cannot CREATE UNIQUE INDEX; duplicate keys found", I used the commands shown below to handle that situation:

             

            SQL> conn system/manager

            Connected.

            SQL> CREATE UNIQUE INDEX "MDSYS"."RDF_LNK_PCSGM_IDX" ON "MDSYS"."RDF_LINK$" ("P_VALUE_ID",

            "CANON_END_NODE_ID", "START_NODE_ID", "G_ID", "MODEL_ID")

              PCTFREE 10 INITRANS 2 MAXTRANS 255  NOLOGGING

              STORAGE(

              BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT) LOCAL

            (PARTITION "MODEL_0"

              PCTFREE 10 INITRANS 2 MAXTRANS 255 NOLOGGING

              STORAGE(

              BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)

              TABLESPACE "TBS_3" ,

            PARTITION "MODEL_1"

              PCTFREE 10 INITRANS 2 MAXTRANS 255 NOLOGGING

              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 "TBS_3" ) COMPRESS 2;

              2    3    4    5    6    7    8    9   10   11   12   13   14   15   16  CREATE UNIQUE INDEX "MDSYS"."RDF_LNK_PCSGM_IDX" ON "MDSYS"."RDF_LINK$" ("P_VALUE_ID",

                                                                       *

            ERROR at line 1:

            ORA-01452: cannot CREATE UNIQUE INDEX; duplicate keys found

             

             

             

             

            SQL> create table dup_table as select p_value_id, canon_end_node_id, start_node_id, NVL(g_id,0) g_id_NVL, model_id, sum(cost) sum_cost from mdsys.rdfm_mymodel group by p_value_id, canon_end_node_id, start_node_id, g_id, model_id having count(*) > 1;

             

             

            Table created.

             

             

            SQL> delete from mdsys.rdf_link$ where (p_value_id, canon_end_node_id, start_node_id, NVL(g_id,0), model_id) IN (select p_value_id, canon_end_node_id, start_node_id, g_id_NVL, model_id from dup_table);

             

             

            3 rows deleted.

             

             

            SQL> insert into mdsys.rdf_link$ (p_value_id, canon_end_node_id, start_node_id, g_id, model_id, COST) select  p_value_id, canon_end_node_id, start_node_id, (case g_id_NVL when 0 then NULL else g_id_NVL end) g_id, model_id, sum_cost from dup_table;

             

             

            1 row created.

             

             

            --

            -- CAUTION: BEFORE INVOKING "commit" command BELOW, VERIFY THE CURRENT CONTENT OF MDSYS.RDF_LINK$ TO MAKE SURE ONLY THE TARGETED ROWS HAVE BEEN REPLACED

            -- PROCEED WITH THE "commit" command below ONLY WHEN YOU ARE SURE

            --

            SQL> commit;

             

             

            Commit complete.

             

             

            SQL> CREATE UNIQUE INDEX "MDSYS"."RDF_LNK_PCSGM_IDX" ON "MDSYS"."RDF_LINK$" ("P_VALUE_ID",

            "CANON_END_NODE_ID", "START_NODE_ID", "G_ID", "MODEL_ID")

              PCTFREE 10 INITRANS 2 MAXTRANS 255  NOLOGGING

              STORAGE(

              BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT) LOCAL

            (PARTITION "MODEL_0"

              PCTFREE 10 INITRANS 2 MAXTRANS 255 NOLOGGING

              STORAGE(

              BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)

              TABLESPACE "TBS_3" ,

            PARTITION "MODEL_1"

              PCTFREE 10 INITRANS 2 MAXTRANS 255 NOLOGGING

              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 "TBS_3" ) COMPRESS 2;

              2    3    4    5    6    7    8    9   10   11   12   13   14   15   16 

            Index created.

             

             

            SQL>

            1 person found this helpful
            • 3. Re: Default semantic index
              3054700

              There was no duplicates and I managed to recreate the index and load the data into the model following the steps you provided. Problem solved, thank you very much!