10 Replies Latest reply on Jan 6, 2011 9:32 PM by 3004

    New  TableRewriteSQL Interface

    99563
      The following http://www.oracle.com/technology/tech/semantic_technologies/pdf/vldb_2005.pdf article (paragraph 4.2) describes the TableRewriteSQL table function interface that speeds up rdf queries by generating sql scripts instead of retrieving an array. Is this interface available in any version?
        • 1. Re: New  TableRewriteSQL Interface
          Mannamal-Oracle
          This feature (where the table function interface for RDF querying generates SQL) is expected to be available in the upcoming database release. The implementation which retrieves an array of values is available from 10gR2 onwards.

          Melli
          Semantic Technologies development team
          • 2. Re: New  TableRewriteSQL Interface
            552932
            Is the TableRewriteSQL feature available in version 11g that is just released?
            • 3. Re: New  TableRewriteSQL Interface
              Mannamal-Oracle
              Yes. 11g has been launched, and will be released shortly.

              Note that the user visible query interface remains the same. The use of the TableRewrite functionality is during execution of the query in 11g.

              Melli
              • 4. Re: New  TableRewriteSQL Interface
                552932
                We are developing solutions (using RDF) for other companies. Some of them will use Oracle 10g and some Oracle 11g.

                Will there be any way to solve this speed issue in Oracle 10g? E.g. will Oracle release any Stored Procedures that will help on speed issues for 10g?
                • 5. Re: New  TableRewriteSQL Interface
                  alwu-Oracle
                  Can you be more specific about the query performance issues encountered?
                  • 6. Re: New  TableRewriteSQL Interface
                    552932
                    In our test-case (which mimics a real case), we have inserted 100 000 Triples which are something like this: (aPerson, hasAmount, amount). To get the Sum of all the amounts, I do the following:
                    SELECT SUM(Y) FROM TABLE (SDO_RDF_MATCH('(?X :hasAmount ?Y)', SDO_RDF_Models('testmodel'), null SDO_RDF_Alias('', 'http://www.example.org/Account/')), null));

                    This query will take approx. 40 seconds. If I rewrite this specific query using the TableRewriteSQL approach, the resulting query takes less than a second.

                    My problem is that I don't know the queries I will use in advance, so in order to use this technique, I need a dynamic way of "translating" the first kind of query into the second kind of query.
                    So my question is if there is some kind of "translation" procedure already in 10g; or if there are other ways of solving this kind of problem?
                    • 7. Re: New  TableRewriteSQL Interface
                      alwu-Oracle
                      Your query is indeed simple, but not very selective. Using table function rewrite (only in 11g) will certainly improve the performance, as verified by yourself.

                      Tuning SGA/PGA, etc. may reduce that 40 seconds query time. But it is unlikely that
                      it can reduce the query time to a sub second level, assuming that single graph query
                      pattern has a match of 100K rows.

                      Please show me how you did the translation yourself.
                      • 8. Re: New  TableRewriteSQL Interface
                        552932
                        Reading from the internet from different places (e.g. here: http://www.cs.wayne.edu/~artem/main/research/TR-DB-052006-CLJF.pdf), I found out that (using a general algorithm) my query could be translated to the following query:

                        SELECT sum(y) FROM
                        (Select UriMap.Value_Name.url y, UriMap.Value_Type y$type from mdsys.rdfm_newrdftest triple_table, mdsys.rdf_value$ UriMap WHERe triple_table.P_VALUE_ID = 47 AND triple_table.MODEL_ID = 16 AND UriMap.Value_Id = triple_table.END_NODE_ID);

                        Here the predicate Amount has ID 47, and our model has ID 16.

                        This way I could deal with at least some of my problems. However; I did the translation 'manually', and now need a Stored Procedure to do the job. It would be much easier for me (and with less errors) if these kind of Stored Procedures already existed (e.g. the TableRewriteSQL must have already been made?).
                        So; Is the TableRewriteSQL source code available somewhere? Are there other kinds of Translation algorithms that has been implemented using PL/SQL?
                        • 9. Re: New  TableRewriteSQL Interface
                          Mannamal-Oracle
                          Thank you for the detailed post.

                          Based on your feedback, we are planning to look into providing a table function rewrite interface (perhaps a stored procedure) for 10gR2. We will keep you updated on how our plans develop.

                          Meanwhile your 11g users should be able to take advantage of the table function rewrite interface that is part of 11g. Note that the table function rewrite happens transparently to the user/application developer (in other words there are no additional stored procedures to call).

                          Melli
                          • 10. Re: New  TableRewriteSQL Interface
                            3004
                            -- Irrelevant contents removed --

                            Edited by: alwu on Jan 6, 2011 1:32 PM