2 Replies Latest reply on Apr 22, 2010 1:05 PM by 766393

    How to choose ADD_SEM_INDEX index_code ?


      I am working on only one model and generally need to access it via either:
      - subject
      - subject + property
      - property + object

      I cant figure out which index to create : I would have created these: SP, PC, PO, S, but in an earlier post, I saw this :

      "Which indexes are better depends upon the workload: for example, if you SPCM index will allow quicker access via subject."
      Re: ALTER_SEM_INDEX_ON_MODEL not found

      Why SPCM instead of S ?

        • 1. Re: Which ADD_SEM_INDEX to create ?
          The question is whether to 1) use the shortest-key needed for the access or 2) to also include additional columns to allow index-only access at the cost of some additional space. This may vary from workload to workload, but in general we have seen good results with the wider indexes (that is, the ones that include additional cols).

          A general comment: please include the 'M' in the index_code (stands for the MODEL_ID column in the model) as (usually the last) column in any semantic network index that you create. Doing that may improve query performance particularly when virtual models are used.

          When you create the semantic network, PCSM index is always present (to enforce uniqueness constraint). The other index that may be created by default is PSCF ('F' is a function). If PSCF is present, I'd suggest dropping that and creating PSCM index instead. Remember to make it usable for models and entailments that existed prior to creation of PSCM index. See relevant section of 11g Release 2 documentation: http://download.oracle.com/docs/cd/E11882_01/appdev.112/e11828/sdo_rdf_concepts.htm#CIHIGJAB.

          Another general comment: please use the 'C' (stands for CANON_END_NODE_ID) in the index_code when you need to access the object. This is because of the way the query always returns canonical lexical forms for the matching values for the variables. (For details see 11g Release 2 documentation at: http://download.oracle.com/docs/cd/E11882_01/appdev.112/e11828/sdo_rdf_concepts.htm#CIHEHAAD)

          Here are the alternatives, each of which would help access via:
          - subject => SM, SPM, SCM, SPCM, SCPM
          - subject + property (assuming both are available) => SPM, PSM, SPCM, PSCM
          - property + object (assuming both are available) => PCM, CPM, PCSM, CPSM

          If you go with wide indexes, you could try the following combination on your workload:
          - subject => SCPM (allows, besides SCP, S-based access, and SC-based access which the other indexes below do not)
          - subject + property (assuming both are available) => PSCM (allows P- and PS-based access, besides PSC)
          - property + object (assuming both are available) => PCSM (always present for enforcing unique constraint and allows P- and PC-based access, besides PCS)
          - (if you need) object => CPSM

          For additional details, please see relevant section of documentation for 11g Release 2: http://download.oracle.com/docs/cd/E11882_01/appdev.112/e11828/sdo_rdf_concepts.htm#CIHBEBIB

          - Souri.
          • 2. Re: Which ADD_SEM_INDEX to create ?
            Thanks a lot for all these very helpful details !