7 Replies Latest reply on Dec 5, 2014 3:09 PM by Matperry-Oracle

    Can I get SEM_MATCH to generate the same SQL query as my Joseki endpoint?

    user8897213

      Hi

      From Joseki my SPARQL runs reasonible fast (2-3 sec). But if I use SEM_MATCH for the same SPARQL, it takes more than 10 minutes on the very same database. When I open SGA Trace and look at the two different SQL queries behind, I see two completely different queries. The Joseki generated can easily be executed, but the SEM_MATCH query is really "unmanagable".

       

      I could of course use HTTP_UTIL and ask my Joseki endpoint and the parse the result, but I would like avoid the overhead. I could also split my SPARQL in pieces and the join but this is a time consuming activity.

       

      From my point of view, it would be better if I could ask SEM_MATCH to use the Joseki SQL generator. Could that be arranged? Deploy the Java packages in the server and provide a PL/SQL API?

       

      Thanks

      Per Øyvind

        • 1. Re: Can I get SEM_MATCH to generate the same SQL query as my Joseki endpoint?
          alwu-Oracle

          Hi Per Øyvind,

           

          Are you running SPARQL 1.0 or SPARQL 1.1 queries? Behind the scenes, Jena Adapter and SEM_MATCH are using the same SPARQL to SQL translator.

           

          For SPARQL 1.0 queries, Jena Adapter first converts the query into SEM_MATCH and then translate it into pure SQL (see the S2S feature). For SPARQL 1.1 queries, Jena Adapter walks down the syntax tree and convert sub tree into SEM_MATCH and then to pure SQL. So for SPARQL 1.1. queries, you will likely see different queries.

           

          Thanks,


          Zhe Wu

          • 2. Re: Can I get SEM_MATCH to generate the same SQL query as my Joseki endpoint?
            user8897213

            Hi Zhe,

             

            We are using SPARQL 1.1.   Is it possible to get the Jena Adapter "walk" available from inside my PL/SQL environment?

             

            Thanks

             

            Per Øyvind

            • 3. Re: Can I get SEM_MATCH to generate the same SQL query as my Joseki endpoint?
              alwu-Oracle

              Hi Per Øyvind,

               

              It is possible to load all those Java classes and libraries into Oracle's DB JVM and then expose the Java APIs as stored procedures.

               

              In this particular case, I would recommend however we look into the reason why SEM_MATCH does not have an efficient SQL plan. Could you please file an SR with a test case? Once you do that, please email me the SR #.

               

              Thanks,


              Zhe Wu

              • 4. Re: Can I get SEM_MATCH to generate the same SQL query as my Joseki endpoint?
                user8897213

                Hi

                 

                I know it's possible to load the Java classes into DB JM. It is a very time consuming job and I feel it would be much better to provide this feature with the product as such with a standard PL/SQL interface from your side.

                 

                It is a very time consuming job to build a standalone test case for us and I can not defend the expences. My Joseki endpoint works fine in this particular case.

                Performance and your interpretation of canonical lexical form is a general issue for us.

                 

                Thanks

                 

                Per Øyvind

                • 5. Re: Can I get SEM_MATCH to generate the same SQL query as my Joseki endpoint?
                  Matperry-Oracle

                  Hi Per Oyvind,

                   

                  Is it possible for you to post (or send privately) the SEM_MATCH that is giving you problems? Perhaps there are a few tweaks or query options that can be used to speed it up.

                   

                  Thanks,
                  Matt

                  • 6. Re: Can I get SEM_MATCH to generate the same SQL query as my Joseki endpoint?
                    user8897213

                    Hi Matt,

                     

                    See SR 3-9966406491

                     

                    We can not live with your implementation of canonical lexical form. We need GET_EXACT_VALUES in all our queries. The performance issue above was a sideeffect of the bug we have discovered.

                    This is my query which started the "game":

                    select

                      ID,

                      LABEL,

                      DESCRIPTION,

                      TO_NUMBER(OUTSIDE_DIAMETER_MM, '9999.9999') AS OUTSIDE_DIAMETER_MM,

                      TO_NUMBER(INSIDE_DIAMETER_MM, '9999.9999') AS INSIDE_DIAMETER_MM,

                      TO_NUMBER(WALL_THICKNESS_MM, '9999.9999') AS WALL_THICKNESS_MM,

                      NPS_ID,

                      NPS_LABEL,

                      CCS_ID

                    from

                      XMLTABLE(XMLNamespaces ('http://www.w3.org/2005/sparql-results#' as "sp"),

                          '/sp:sparql/sp:results/sp:result'

                          PASSING SPARQL_UTIL.xml_query('http://data.aibel.com/endpoint/oracle', 'PREFIX rdfs: <http://www.w3.org/2000/01/rdf-schema#>

                          PREFIX rdl: <http://data.aibel.com/rdl/>

                          PREFIX xsd: <http://www.w3.org/2001/XMLSchema#>

                          PREFIX owl:   <http://www.w3.org/2002/07/owl#>

                          PREFIX ORACLE_SEM_HT_NS: <http://oracle.com/semtech#GET_EXACT_VALUES>

                         

                          SELECT ?id ?label ?description ?wall_thickness_mm ?outside_diameter_mm ?nps_id ?nps_label ?ccs_id ?inside_diameter_mm

                          WHERE {

                              # X101001916 - Circular Hollow Cross Section Object

                            rdl:X101001916 (^rdfs:subClassOf) ?id .

                             

                              ?id rdfs:label ?label ;

                                  rdl:X101004229 ?outside_diameter_mm ; # Ann.prop: Specified Outside Diameter (mm)

                                  rdl:X101005319 ?wall_thickness_mm ;   # Ann.prop: Specified Wall Thickness (mm)

                                  rdfs:subClassOf ?v .

                                 

                              ?v  a   owl:Restriction ;

                                  owl:hasValue   ?inside_diameter_mm ;

                                  owl:onProperty rdl:X101001928 # inner diameter (mm)

                                  .

                     

                     

                              OPTIONAL {

                                 ?id rdfs:comment ?description .

                              }

                             

                              # X101004228 - Nominal Pipe Size Object

                              rdl:X101004228 (^rdfs:subClassOf) ?nps_id .

                              ?nps_id rdfs:label ?nps_label .

                              #  - Circular Cross Section Object

                              rdl:X101001915 (^rdfs:subClassOf) ?ccs_id .

                             

                            ?ccs_id rdfs:subClassOf ?nps_id .

                              ?id rdfs:subClassOf ?ccs_id .

                          }')

                          COLUMNS

                            ID                    varchar2(255) PATH 'sp:binding[@name="id"]/sp:uri/text()',

                            LABEL                 varchar2(60)  PATH 'sp:binding[@name="label"]/sp:literal/text()',

                            DESCRIPTION           varchar2(500) PATH 'sp:binding[@name="description"]/sp:literal/text()',

                            OUTSIDE_DIAMETER_MM   varchar2(25)  PATH 'sp:binding[@name="outside_diameter_mm"]/sp:literal/text()',

                            INSIDE_DIAMETER_MM    varchar2(25)  PATH 'sp:binding[@name="inside_diameter_mm"]/sp:literal/text()',

                            WALL_THICKNESS_MM     varchar2(25)  PATH 'sp:binding[@name="wall_thickness_mm"]/sp:literal/text()',

                            NPS_ID                varchar2(255) PATH 'sp:binding[@name="nps_id"]/sp:uri/text()',

                            NPS_LABEL             varchar2(60)  PATH 'sp:binding[@name="nps_label"]/sp:literal/text()',

                            CCS_ID                varchar2(255) PATH 'sp:binding[@name="ccs_id"]/sp:uri/text()'

                            )

                     

                    Jena is very happy and respects my GET_EXACT_VALUES hint and produces a nice SQL query.

                    Anyway, do you have some ideas,?

                     

                    Thanks

                    Per Øyvind

                    • 7. Re: Can I get SEM_MATCH to generate the same SQL query as my Joseki endpoint?
                      Matperry-Oracle

                      Per Oyvind,

                       

                      Thanks for posting the query. I'll take this offline and contact you directly.

                       

                      - Matt