5 Replies Latest reply on Aug 18, 2006 11:39 AM by Mannamal-Oracle

    Problem with Delete command.

    528439
      1 step.
      I have table (named artemGraph_test3) with one column triple (SDO_RDF_TRIPLE_S). I create rdf model (named artemGraph_test3) and insert two rows into table:

      INSERT INTO artemGraph_test3 VALUES (sdo_rdf_triple_s('artemGraph_test3',
      '<http://localhost/subjTest>',
      '<http://localhost/predTest>',
      '"1"^^<http://www.w3.org/2001/XMLSchema#string>'))

      INSERT INTO artemGraph_test3 VALUES (sdo_rdf_triple_s('artemGraph_test3',
      '<http://localhost/subjTest>',
      '<http://localhost/predTest>',
      '"1"^^<http://www.w3.org/2001/XMLSchema#int>'))

      Step 2.
      I execute SQL command

      DELETE
      FROM artemGraph_test3 a WHERE a.triple = sdo_rdf_triple_s('artemGraph_test3', 'http://localhost/subjTest',
      'http://localhost/predTest',
      '1^^http://www.w3.org/2001/XMLSchema#string')

      Step 3.
      I execute sql command

      SELECT z, z$RDFVTYP, z$RDFLTYP
      FROM TABLE
      (SDO_RDF_MATCH('(<http://localhost/subjTest> <http://localhost/predTest> ?z)',
      SDO_RDF_Models('artemGraph_test3'),
      null,
      null,
      null
      ))

      The result of execution is two triples. Why? Table artemGraph_test3 contains only one row, but model artemGraph_test3 - two. In documentation (Oracle® Spatial
      Resource Description Framework (RDF)) there is no sample with delete operation.

      NOTE: When I execute sql command
      DELETE
      FROM artemGraph_test3 a WHERE a.triple.GET_TRIPLE() = sdo_rdf_triple('http://localhost/subjTest',
      'http://localhost/predTest',
      '1^^http://www.w3.org/2001/XMLSchema#string')

      It works fine.

      And, at last: performance of execution delete triple sql command is very poor, when count of triple ~100 000.

      How can I delete triple correctly and with good performance.
      Thank you very match.

      Message was edited by:
      user525436
        • 1. Re: Problem with Delete command.
          528439
          Right answer:

          DELETE FROM %table_name% s
          WHERE s.triple.RDF_T_ID IN (
          SELECT SDO_RDF.GET_TRIPLE_ID
          ( '%model_name%', '%s', '%s', '%s') AS ID FROM DUAL)

          ?
          • 2. Re: Problem with Delete command.
            Mannamal-Oracle
            I think in the first example you have stumbled upon a bug. Thank you for pointing it to us. As the second example works, you should be all set to continue working on your application? I will also make a note to include a delete example in the documentation.

            Melli
            • 3. Re: Problem with Delete command.
              Mannamal-Oracle
              I am not sure I understand your second post on this thread - did you find that this example of delete (the third example) was fast?

              The second example of using delete is slow as the data size grows because the object equality check is done row by row. This can be improved by building an index on some of the attributes. For example,

              create index idx on artemGraph_test3(triple.get_subject());
              or
              create index idx on artemGraph_test3(triple.get_subject(), triple.rdf_t_id, triple.rdf_m_id);

              However, if the third example works well for you, you should be all set.

              Do you anticipate frequent deletes in your application? That is interesting because we typically see applications with frequent inserts and occasional deletes. If there deletes of blocks of data, you might want to consider storing the blocks in separate models and dropping the model (which will be very quick operation) instead of going through the triples and deleting them. For example, you might have triples representing different years - 2004, 2005, 2006 and so on. If you want to delete triples representing the year 2004, if those triples had been stored as a separate model, then that model can be dropped, instead of going through the model and identifying and deleting the triples that represent 2004. The application table (artemGraph_test3) can have a partition for each year, and a model can be created for each partition. Of course the feasibility of these possibilities depend on your application.

              Melli

              Message was edited by:
              mannamal
              • 4. Re: Problem with Delete command.
                528439
                Thank you, Melli, for the answers.

                In my second post on this thread I post query that works well. But it's also some slow when I have a lot of triples.

                DB - more then 1 500 000 triples.
                CPU - 2 processor, 2GB OM.

                This query executes more then 15 sec.
                But we will work on increase db performance and we'll build an index on rdf_t_id.

                Our application manipulates with ontology object (concept, instance and etc.), that describes by set of triples. Create and delete operations is very frequent for our application.

                We can't storing blocks of triples for each ontology object in separate rdf model, because we have a lot of them.

                -----------

                Sorry, but my post (another post in Semantic Technologies forum) about problem with select statement is very and very critical for us. Can you help? Thanks.

                Message was edited by:
                user525436

                Message was edited by:
                user525436
                • 5. Re: Problem with Delete command.
                  Mannamal-Oracle
                  Yes, we are working on understanding the select issue. It might be easier if we interact directly - can you write to me at melliyal <dot> annamalai <at> oracle <dot> com?

                  Thanks,
                  Melli