6 Replies Latest reply on Sep 18, 2009 3:44 PM by 718949

    SEM_MATCH performance

    718949
      I have a question regarding the performance of the SEM_MATCH function. When I execute the following query the performance of SEM_MATCH is very good as it is using the appropriate indexes to retrieve the data.

      select predicate, object
      FROM TABLE(SEM_MATCH('(<http://www.xyz.com/Student/100017> ?predicate ?object )',
      SEM_Models('students'),
      null,
      null,
      null))

      However, if I execute the following query, the performance is very bad as it is doing a full table scan on the MDSYS.RDF_LINK$ table.

      select subject, predicate, object
      FROM TABLE(SEM_MATCH(' (?subject ?predicate ?object )',
      SEM_Models('students'),
      null,
      null,
      null))
      where subject = 'http://www.xyz.com/Student/100017'

      I believe the reason for this is that the Table function must be resolved first, thus without any selection criteria in the SEM_MATCH function, a resulting full table scan is performed.

      Is there any way to resolve the outer part of the query first (subject = 'http://www.xyz.com/Student/100017' ) and project those results into the Table Function?

      Kevin
        • 1. Re: SEM_MATCH performance
          Sdas-Oracle
          if on 11.1.0.7, you could install patch 7600122, and then use the curly-brace syntax which just involves changing the pattern to
          ' {?subject ?predicate ?object } '

          Optionally, you could also use HINT0 as follows:

          (For more information, please see HINT0 related information in
          http://download.oracle.com/docs/cd/B28359_01/appdev.111/b28397/sdo_rdf_concepts.htm#CHDJACII
          and
          curly-brace syntax related info in
          http://download.oracle.com/docs/cd/B28359_01/appdev.111/b28397/sdo_rdf_concepts.htm#CIHEHAAD)

          select subject, predicate, object
          FROM TABLE(SEM_MATCH(' { ?subject ?predicate ?object } ',
          SEM_Models('students'),
          null,
          null,
          null
          ,null, ' HINT0 = { LEADING(?subject) } '
          ))
          where subject = 'http://www.xyz.com/Student/100017'
          ;
          • 2. Re: SEM_MATCH performance
            718949
            I tried your suggestions, with the results being approximately the same. The query results in several full table scans on the RDF_LINK$ and RDF_VALUE$ table. I have tried to add several different indexes via the EXECUTE SEM_APIS.ADD_SEM_INDEX( 'SPO');
            and EXECUTE SEM_APIS.ALTER_INDEX_ON_SEM_GRAPH('students','SPO','REBUILD'); commands. Of which none of the indexes has improved the performance of this query.

            Kevin
            • 3. Re: SEM_MATCH performance
              Sdas-Oracle
              Which version of Oracle RDBMS Server are you using?
              Also, have you installed patch 7600122?
              • 4. Re: SEM_MATCH performance
                718949
                I am running 11.1.0.7 and have applied patch 7600122.
                • 5. Re: SEM_MATCH performance
                  Sdas-Oracle
                  We may need some additional information to try to reproduce the behavir you are seeing.
                  Please feel free to contact me directly at souripriya dot das at oracle dot com.
                  • 6. Re: SEM_MATCH performance
                    718949
                    For those of you that stumble upon the thread at a later date, the query performance was resolved using the following query:

                    select subject$_suffix, REGEXP_REPLACE(predicate,'http://www.pearson.com/ai/CandidateManagement/Student/','') predicate, object
                    FROM TABLE(SEM_MATCH(' { ?subject ?predicate ?object } ',
                    SEM_Models('students'),
                    null,
                    null,
                    null
                    ,null, ' HINT0 = { LEADING(?subject) } '
                    ))
                    where subject$_prefix = sem_apis.value_name_prefix('http://www.xyz.com/Student/','UR')
                    and subject$_suffix between '115000' and '117920'


                    Thanks Souri for all of your help on this one.

                    Kevin