I am trying to understand how indexing works when extended tables are involved. What do we expect in this scenario where we have a base table and an extended table:
Base Table (Index A = Field 1 + Field 2)
Extended Table (Index B = Ext Field 1)
Situation:
-
A query retrieves Field 1 and Field 2 from the base table, plus Ext Field 1 from the extended table.
-
The base table has an index covering Field 1 and Field 2.
-
The extended table has a separate index covering Ext Field 1.
Question:
When the query is executed, are these indexes treated as two completely separate indexes (one per table), or does the system internally merge them in any way when extended fields are involved?
No, extension table indexes are not merged into base table indexes. However, this does not mean an extension table cannot use base fields in its index.
In your example, the underlying table would have two separate indexes–one from the base definition, and one from the extension definition. If a query was executed against all 3 fields, it would be up to the query planner to determine which, if any, index gets used (query planner is a whole other topic and something we don’t have direct control over). But there would not be any kind of merging action to treat the two indexes as one.
If you wanted to have an index over base fields + extension fields, you can still do so. In the extension table’s definition, you can simply add the base field to an index. Going back to your example, your extension table can define an index over (Field1, Field2, Ext Field1). This will allow queries to be performant when querying all 3 fields rather using either the base index or the extension index.
3 Likes