3 Replies Latest reply on May 12, 2014 5:16 PM by Matperry-Oracle

    IS BIND() & COALESCE() supported in SEM_MATCH and if not what's alternative?

    02cc2813-d093-4903-930b-0745cb04a886

      I want to get rid of all these UNIONs and USE virtual model VISION instead of all these models: COMPASS, PROCESS, LIMS_ANALYTIC, CLOVER in SEM_MODEL() function...

       

      Jena:       VERSION: 2.11.1

      ARQ:       VERSION: 2.11.1

      RIOT:       VERSION: 2.11.1

       

      I know the query is large but I am trying to simplify it because it takes FOREVER!!

       

      SELECT DOMAIN, AGE_OF_DATA FROM

      (

         (

        SELECT Domain, max(protDate) as age_of_data FROM

        (

        SELECT DOMAIN$_SUFFIX AS Domain, protNum, NVL(protStartDate, protCreateDate) AS protDate

        FROM TABLE(SEM_MATCH(

         '{

         ?ipc rdf:type s:Protocol .

         ?ipc s:hasDataSource :COMPASS .

         ?ipc s:protocolNumber ?protNum .

         ?ipc s:protocolCreateDate ?protCreateDate .

         OPTIONAL {?ipc s:protocolSchedStartDate ?protStartDate }

      *******BIND(COALESCE(?protCreateDate, ?protStartDate) AS ?age_of_data)  ***** <== can I use that instead of NVL() function above?

         ?ipd rdf:type s:Protocol .

         ?ipd s:hasDataSource ?domain .

         ?ipd s:protocolNumber ?protNum .

          }',SEM_Models('COMPASS','EDHRD','CLOVER'),NULL,

          SEM_ALIASES(SEM_ALIAS('','http://VISION/Data/SEMANTIC#'),SEM_ALIAS('s', 'http://VISION/DataSource/SEMANTIC#')),NULL,NULL,'ALLOW_DUP=T'))

         ORDER BY DOMAIN$_SUFFIX, protDate desc

          )

          Group by Domain

          )

         UNION

          (

         SELECT 'LIMS_ANALYTICAL' as Domain, chgDate as age_of_data FROM

         (

        SELECT chgDate

        FROM TABLE(SEM_MATCH(

         '{

         ?ipc rdf:type s:Test .

         ?ipc s:testChangeDate ?chgDate .

        }',SEM_Models('LIMS_ANALYTIC'),NULL,

        SEM_ALIASES(SEM_ALIAS('','http://VISION/Data/SEMANTIC#'),SEM_ALIAS('s',  'http://VISION/DataSource/SEMANTIC#')),NULL,NULL,'ALLOW_DUP=T'))

        ORDER BY chgDate desc

         )

        WHERE ROWNUM = 1

          )

         UNION

         (

         SELECT process as Domain, substr(max(procEndDate),1,10) as age_of_data  FROM

         (

        SELECT  process, protNum, procEndDate

        FROM TABLE(SEM_MATCH(

        '{

        ?ipra rdf:type s:ProcessAggregate .

        ?ipra s:hasProtocol ?iprot .

         ?iprot s:protocolNumber ?protNum .

        ?ipa    s:hasEquipment  ?iequ .

        ?iequ  s:pilotLineProcessName   ?process.

           ?ipra s:processAggregateCaptureTimeEnd ?procEndDate .

         }',SEM_Models('COMPASS','PROCESS'),NULL,

         SEM_ALIASES(SEM_ALIAS('','http://VISION/Data/SEMANTIC#'),SEM_ALIAS('s',  'http://VISION/DataSource/SEMANTIC#')),NULL,NULL,'ALLOW_DUP=T'))

          )

         Group by process

          )

          UNION

          (

         select 'Tooling_Design'  as Domain, protocol_start_date as age_of_data

         from

        (

         select protocol_start_date from mv_lot_detail l, mv_lot_design d

         where l.protocol_start_date is not null and l.fp_pr = d.fp_pr and l.bp_pr = d.bp_pr

         order by l.protocol_start_date desc

         )

        Where rownum = 1

         )

      );

        • 1. Re: IS BIND() & COALESCE() supported in SEM_MATCH and if not what's alternative?
          Matperry-Oracle

          SEM_MATCH supports BIND() and COALESCE() since version 12.1.0.1.0. However, you need to use SPARQL SELECT syntax instead of curly brace syntax:

          http://docs.oracle.com/cd/E16655_01/appdev.121/e17895/sdo_rdf_concepts.htm#RDFRM140


          We would also recommend that you apply the latest server patch available from MyOracleSupport:

          Patch 18239544: SPATIAL AND GRAPH RDF SEMANTIC GRAPH 12.1.0.1.0 PATCH KIT (MARCH 2014)

           

          With respect to the poor performance you're seeing for this query, please make sure to run SEM_PERF.GATHER_STATS() to make sure query optimizer statistics are up to date. You can experiment with dynamic sampling as well:

          http://docs.oracle.com/cd/E16655_01/appdev.121/e17895/sdo_rdf_concepts.htm#CIHDCBHA

           

          You may also want to replace the UNIONs in your query with UNION ALL, and you can get rid of ORDER BY DOMAIN$_SUFFIX, protDate desc after the first SEM_MATCH because this order won't be maintained through the subsequent Group by Domain.

           

          We can do further tuning if you are still seeing performance problems after trying these things.

           

          Thanks,
          Matt

          1 person found this helpful
          • 2. Re: IS BIND() & COALESCE() supported in SEM_MATCH and if not what's alternative?
            02cc2813-d093-4903-930b-0745cb04a886

            I changed it so it looks better:

             

            SELECT DOMAIN, AGE_OF_DATA FROM

            (

            (

               SELECT CASE WHEN INSTR(Domain, '#') > 0 THEN SUBSTR(Domain, 29, INSTR(Domain, '#')-1)

                      ELSE DOMAIN  END DOMAIN,

                      SUBSTR(max(age_of_data), 1, 10) AGE_OF_DATA

                FROM

                (

              SELECT domain_name AS Domain, protNum, NVL(protStartDate, age_of_data) AS age_of_data

              FROM TABLE(SEM_MATCH(

               '{

                          {

                                 ?ipl   rdf:type s:Protocol .

                                 ?ipl   s:hasDataSource :COMPASS .

                                 ?ipl   s:protocolNumber ?protNum .

                                 ?ipl   s:protocolCreateDate ?age_of_data .

                              OPTIONAL {?ipl s:protocolSchedStartDate ?protStartDate }

                                 ?iec   rdf:type s:Protocol .

                                 ?iec   s:hasDataSource ?domain_name .

                                 ?iec   s:protocolNumber ?protNum .

                           }

                           UNION

                           {

                                 ?pag   rdf:type         s:ProcessAggregate .

                                 ?pag   s:hasProtocol    ?iprot .

                                 ?iprot s:protocolNumber ?protNum .

                                 ?pag   s:pilotLineProcessName   ?domain_name.

                                 ?pag   s:processAggregateChangeDateTime ?age_of_data .

                           }

                           UNION

                           {

                                 ?lim   rdf:type   s:Submission .

                                 ?lim   s:hasDataSource  ?domain_name.

                                 ?lim   s:submissionChangeDate ?age_of_data .

                           }

                }',SEM_Models('COMPASS','EDHRD', 'CLOVER', 'PROCESS', 'LIMS_ANALYTIC'),NULL,

                SEM_ALIASES(SEM_ALIAS('','http://VISION/Data/SEMANTIC#'),SEM_ALIAS('s', 'http://VISION/DataSource/SEMANTIC#')),NULL,NULL,'ALLOW_DUP=T'))

               ORDER BY domain_name

                )

                Group by Domain

            )

            UNION

                (

               select 'Tooling_Design'  as Domain, max(protocol_start_date) age_of_data

                    from semantic_read_only.mv_lot_detail l, semantic_read_only.mv_lot_design d

                    where l.protocol_start_date is not null and l.fp_pr = d.fp_pr and l.bp_pr = d.bp_pr

               )

            );

            • 3. Re: IS BIND() & COALESCE() supported in SEM_MATCH and if not what's alternative?
              Matperry-Oracle

              Is the performance satisfactory now with the modified query? If not, did you run SEM_PERF.GATHER_STATS()?