Sometimes, system properties are added by the Content Platform Engine to the database SQL query. Most system columns that require indexes are already indexed automatically when an object store is created.
WHERE T0.object_class_id
IN (?, ?)
AND T0.home_id Is Null
AND (T0.is_current = ? OR ( T0.version_status = ? AND T0.is_reserved = ?))
System Column | Comments |
---|---|
home_id | A column such as home_id is
used for cross-object store references. It is always null or perhaps
has one value (for example, in an IBM® Enterprise Records system). Indexing this
column does not typically help search performance except in some IBM Enterprise Records cases. An index on this
column is not used in a query execution plan if all rows are null
because no row filtering can occur. Also, adding home_id to
a composite index does not help performance either in cases where
no row filtering can occur. Add a column to a composite index only
if the search condition significantly reduces the number of rows that
are filtered. For more information, see Index tuning: Selective
search conditions. The presence of home_id in the search conditions also does not incur any noticeable expense in the performance of a search. Because the value is rarely not null, there is always a hit on the row that is returned from the other search conditions. Although the row that is returned from the main data page has to be checked for the home_id is null condition, this filter step is fast. In all cases, reports that a query can be made faster by removing the home_id is null clause were the result of faulty analysis. |
is_current, version_status, is_reserved | These columns on the DocVersion table have low cardinality (the number of unique values is small), and as such are not good candidates for indexing: the value that is searched for is typically the same value that most rows have. When the values are mostly the same, an index is likely not used because scanning the table is faster than the alternative: scanning the index and switching back and forth to the main table to access all of the rows. |
object_class_id | This column stores the ID of the class for the object. It is also of typically low cardinality. The same arguments apply here as with the other system columns, but a distinction can be made in the case of searching for a specific class. For more information, see Index tuning: No search conditions (continuable query). |