1 Reply Latest reply on Mar 20, 2007 4:25 PM by Mannamal-Oracle

    Usability of indexes

    563141
      Hi!

      The question I have is

      Do indexes on subjectID, propertyID, objectID, tripleID improve performance of semantic queries (SDO_RDF_MATCH)? and how significantly?

      And probably we should think in other direction: do indexes decrease the performance of batch loading, inserts and updates?

      Anton
        • 1. Re: Usability of indexes
          Mannamal-Oracle
          Hi Anton,

          Do you mean the indexes you can create on the application table, for example:

          create index <index_name> on <application_table_name>(triple.get_subject()) ?

          These indexes increase the performance of queries at the application table level (such as queries that use get_subject(), get_property() and get_object()). These help updates and deletes done using SQL if these operations use these methods to identify the triple to be updated/deleted. Insert would require maintainance of the index so having the index would be an overhead for SQL insert statements. The same thing applies to batch loading.

          For queries involving SDO_RDF_MATCH (which operates directly on the internal model), there should be no need for the user to create any indexes. All the necessary indexes are created on the internal tables.

          To improve performance, gathering statistics might help (run as mdsys):

          begin
          dbms_stats.gather_table_stats(ownname => 'MDSYS',
          tabname => 'RDF_VALUE$',
          cascade => TRUE,
          no_invalidate => FALSE
          );
          dbms_stats.gather_table_stats(ownname => 'MDSYS',
          tabname => 'RDF_LINK$',
          cascade => TRUE,
          no_invalidate => FALSE
          );
          dbms_stats.gather_table_stats(ownname => 'MDSYS',
          tabname => 'RDF_BLANK_NODE$',
          cascade => TRUE,
          no_invalidate => FALSE
          );
          end;
          /

          More importantly, I strongly recommending applying the most recent patch. Several performance bugs were fixed, and performance of some queries is increased by orders of magnitude. There are two ways of doing this:

          (1) You could keep Oracle database as 10.2.0.1, and apply an RDF specific patch. That is available at metalink under "Patch: 5557859 - RDF RELEASE BUNDLE BLR FOR 10.2.0.1". If you are mostly interested in RDF improvements then you could apply this one. This patch should be applied on Oracle database 10.2.0.1.

          (2) You can upgrade your Oracle database to 10.2.0.3, and then apply a 10.2.0.3 RDF specific patch. This will enable you to take advantage of improvements in other database features (not just RDF features). This would also make sense if the database is already upgraded to Oracle 10.2.0.3. The RDF patch on metalink would be "Patch: 5754970 - RDF RELEASE BUNDLE BLR FOR 10.2.0.3". This patch should be applied on Oracle Database 10.2.0.3.

          Melli

          NOTE: THE PATCH INFORMATION HAS BEEN UPDATED AFTER THE INITIAL POST ON MARCH 19, 2007. PLEASE NOTE THAT THE PATCH NUMBERS AND OTHER INFORMATION PROVIDED IN THE UPDATED VERSION ARE ACCURATE AND CORRECT AND ARE THE ONES TO BE USED.

          Melli