6 Replies Latest reply on Jun 8, 2011 4:02 PM by 863377

    Function Based Index for dateTime

    863377
      Hello:

      Our SPARQL queries frequently use date ranges to filter queries. I tried to create a function based index based on the recommendations in the developer guide (1.6.3.2) using the following SQL:

      CREATE INDEX v_date_idx ON
      MDSYS.RDF_VALUE$ (SEM_APIS.getV$DateTZVal(value_type, vname_prefix,
      vname_suffix, literal_type, language_type));

      The result:

      Error starting at line 1 in command:
      CREATE INDEX v_date_idx ON
      MDSYS.RDF_VALUE$ (SEM_APIS.getV$DateTZVal(value_type, vname_prefix,
      vname_suffix, literal_type, language_type))
      Error at Command Line:2 Column:20
      Error report:
      SQL Error: ORA-00904: "SEM_APIS"."GETV$DATETZVAL": invalid identifier
      00904. 00000 - "%s: invalid identifier"

      We are using 11.2.02 on Linux. This was done as a user with DBA privileges, and I have been successful in creating the string version of this index. Can anyone point out where I've gone wrong?

      TIA,

      // Kbd4hire
        • 1. Re: Function Based Index for dateTime
          alwu-Oracle
          Hi,

          What happens if you do the following as the same user?

          SQL> select SEM_APIS.getV$DateTZVal(value_type, vname_prefix,
          vname_suffix, literal_type, language_type) from mdsys.rdf_value$ where rownum < 10;


          Thanks,

          Zhe Wu
          • 2. Re: Function Based Index for dateTime
            863377
            The query:

            select SEM_APIS.getV$DateTZVal(value_type, vname_prefix,
            vname_suffix, literal_type, language_type) from mdsys.rdf_value$ where rownum < 10;

            produces:

            ORA-00904: "SEM_APIS"."GETV$DATETZVAL": invalid identifier
            00904. 00000 - "%s: invalid identifier"
            *Cause:   
            *Action:
            Error at Line: 14 Column:

            BTW, we have no trouble getting dates out of the triplestore.

            // Kbd4hire
            • 3. Re: Function Based Index for dateTime
              Matperry-Oracle
              Hi,

              Can you please show the result of this query, which needs to be run as a DBA.

              select * from mdsys.rdf_parameter;

              Thanks,
              Matt
              • 4. Re: Function Based Index for dateTime
                863377
                That query results in:

                NAMESPACE ATTRIBUTE VALUE DESCRIPTION
                ----------------------------------------------------------------------------
                COMPONENT     RDFCTX     INSTALLED     Semantic (Text) Search component
                COMPONENT     RDFOLS     INSTALLED     RDF Optional component for OLS support
                COMPONENT     RDFOWM     INSTALLED     RDF Optional component for Workspace Manager support
                COMPONENT     RDFVPD     INSTALLED     RDF Optional component for VPD support
                MDSYS     SEM_VERSION     112     VALID

                // Kbd4hire
                • 5. Re: Function Based Index for dateTime
                  Matperry-Oracle
                  Semantic Technologies version 11.2.0.1.0 is installed on your machine. The getV$DateTZVal function was introduced in 11.2.0.2.0.

                  If you have already upgraded the database to 11.2.0.2.0 but Semantic Technologies did not get upgraded, you can email me at matthew dot perry at oracle dot com, and we can diagnose the upgrade problem offline.

                  Thanks,
                  Matt
                  1 person found this helpful
                  • 6. Re: Function Based Index for dateTime
                    863377
                    Just wanted to follow-up on the solution that worked for us. The steps provided by Matt finished the semantic upgrade to 11.2.0.2

                    "It looks like somehow the semantic technologies component upgrade script did not get invoked during the upgrade. It is an easy fix to just invoke it now. You just need to run a couple of scripts from SQL Plus.

                    conn / as sysdba;
                    alter session set current_schema=MDSYS;
                    @ $ORACLE_HOME/md/admin/sdordfctx.sql -- this one will generate a few errors, but you can ignore these
                    @ $ORACLE_HOME/md/admin/semrelod.sql

                    You can check if the upgrade was successful by querying the mdsys.rdf_parameter table. You should see 11202 as the SEM_VERSION."

                    Once again, thank you, Matt.

                    // Kbd4hire