5 Replies Latest reply: Jun 7, 2013 12:54 PM by alwu-Oracle RSS

    SPARQL Update with Jena Adapter

    martani
      Hi,

      I am using the following SPARQL Update query:
      DELETE { ?s ?p ?o }
      WHERE {
          ?s  :isUpdatedQRY  "no".
          ?s  ?p  ?o .
      }
      However, when I run it (with debug messages enabled), I notice that some operations are taking too long to run:
      <<[DEBUG 18:48:28] [OracleTranslator:202] [main] >> encode: hit a plain literal, val = no
      <<[DEBUG 18:48:38] [OracleTranslator:202] [main] >> encode: hit a plain literal, val = Graph4
      <<[DEBUG 18:49:05] [OracleTranslator:202] [main] >> encode: hit a plain literal, val = no
      <<[DEBUG 18:49:14] [OracleTranslator:202] [main] >> encode: hit a plain literal, val = Graph3
      As you can see, it takes between 9 seconds to 17 seconds for the next operation to execute.
      Do you have any idea what might be causing this?

      Regards.
        • 1. Re: SPARQL Update with Jena Adapter
          alwu-Oracle
          Hi,

          Could you please cut & paste here the execution plan (from EM)?

          Thanks,

          Zhe Wu
          • 2. Re: SPARQL Update with Jena Adapter
            martani
            Hi,

            I notice this SQL query executed:
            DELETE    /*+ index(app MODEL_A_IDX) */
            FROM  MY_SCHEMA.MODEL_TAB_A APP
            WHERE BITAND (APP."TRIPLE".RDF_M_ID, 79228162514264337593543950335 - 4294967295) / 4294967296 = 
                    (CASE
                         WHEN INSTR (:B1, 'g') = 0
                         THEN 0
                         ELSE SYS_OP_RAWTONUM ( SUBSTR (:B1, INSTR (:B1, 'g') + 1, INSTR (:B1, '_') - INSTR (:B1, 'g') - 1))
                      END)
                 AND (APP."TRIPLE".RDF_S_ID) = 
                           SYS_OP_RAWTONUM (SUBSTR (:B1, INSTR (:B1, '_') + 1, INSTR (:B1, '_', INSTR (:B1, '_') + 1) - INSTR (:B1, '_') - 1))
                 AND (APP."TRIPLE".RDF_P_ID) = 
                           SYS_OP_RAWTONUM (SUBSTR (:B1, INSTR (:B1, '_', 1, 2) + 1, INSTR (:B1, '_', 1, 3) - INSTR (:B1, '_', 1, 2) - 1))
                 AND (APP."TRIPLE".RDF_C_ID) =
                           SYS_OP_RAWTONUM (SUBSTR (:B1, INSTR (:B1, '_', 1, 3) + 1, LENGTH (:B1) - INSTR (:B1, '_', 1, 3)))
            And this is the execution plan:
            PLAN_TABLE_OUTPUT
            --------------------------------------------------------------------------------
            | Id  | Operation          | Name      | Rows  | Bytes | Cost (%CPU)| Time     |
            --------------------------------------------------------------------------------
            |   0 | DELETE STATEMENT   |           |     1 |    59 | 10915  (15)| 00:02:11 |
            |   1 |  DELETE            | NDB_TPL_A |       |       |            |          |
            |*  2 |   TABLE ACCESS FULL| NDB_TPL_A |     1 |    59 | 10915  (15)| 00:02:11 |
            --------------------------------------------------------------------------------
            
            Predicate Information (identified by operation id):
            ---------------------------------------------------
            
               2 - filter("APP"."SYS_NC00003$"=SYS_OP_RAWTONUM(HEXTORAW(SUBSTR(:B1,I
                          NSTR(:B1,'_',1,3)+1,LENGTH(:B1)-INSTR(:B1,'_',1,3)))) AND
                          "APP"."SYS_NC00006$"=SYS_OP_RAWTONUM(HEXTORAW(SUBSTR(:B1,INSTR(:B1,'_',1
                          ,2)+1,INSTR(:B1,'_',1,3)-INSTR(:B1,'_',1,2)-1))) AND
                          "APP"."SYS_NC00005$"=SYS_OP_RAWTONUM(HEXTORAW(SUBSTR(:B1,INSTR(:B1,'_')+
                          1,INSTR(:B1,'_',INSTR(:B1,'_')+1)-INSTR(:B1,'_')-1))) AND
                          BITAND("APP"."SYS_NC00004$",79228162514264337589248983040)/4294967296=CA
                          SE INSTR(:B1,'g') WHEN 0 THEN 0 ELSE
            
            PLAN_TABLE_OUTPUT
            -----------------------------------------------------------------------------------------------
                          SYS_OP_RAWTONUM(HEXTORAW(SUBSTR(:B1,INSTR(:B1,'g')+1,INSTR(:B1,'_')-INSTR(:B1,'g')-1))) END )
            Notice that we do not have a MODEL_A_IDX index; is this created automatically after a call to SEM_APIS.CREATE_SEM_MODEL?

            Thanks.
            • 3. Re: SPARQL Update with Jena Adapter
              alwu-Oracle
              Hi,

              You can invoke the rebuildApplicationTableIndex() method against a GraphOracleSem object.
              It will re-recreate the index that is critical to delete performance.

              Thanks,

              Zhe Wu
              • 4. Re: SPARQL Update with Jena Adapter
                martani
                Hi,

                Thank you very much for the reply, it works indeed. I can see the index range scan in the execution plan now:
                DELETE STATEMENT, GOAL = ALL_ROWS
                 DELETE                              USER    MODEL_A               
                  TABLE ACCESS BY INDEX ROWID               USER    MODEL_A
                   INDEX RANGE SCAN                    USER    MODEL_A_IDX     
                I am wondering if there is a way to create this index (or indexes) using SEM_APIS Package Subprograms?

                Thank you.
                • 5. Re: SPARQL Update with Jena Adapter
                  alwu-Oracle
                  Hi,

                  SEM_APIS package does not have this index creation. The reason is that this index belong more or less to the application area. One can, of course, get the underlying index DDL and run it in SQL*Plus.

                  Thanks,

                  Zhe Wu