This content has been marked as final. Show 2 replies
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
Thanks a lot for all these very helpful details !