Implementation and Overhead of Secondary Indexes
Secondary indexes are actually tables that are automatically tied to the base table. Secondary indexes are automatically updated whenever the base table is changed, so they must be used sparingly. The user need not explicitly reference the secondary index for it to be used in a query. In fact, you cannot directly update a secondary index and probably never reference it. If the Ingres optimizer sees that an index is available to help solve the query, generally the index is used.
By default, secondary indexes are created as ISAM tables. You can change the storage structure of the index by modifying the secondary index once it is created, or by specifying another structure when you create the index.
The following example shows the relationship of a secondary index to a base table:
Select * from xnameselect name,tid from employee
|name |tidp||name |tid |
|-------------------------- ||-------------------------|
|Aitken |3072||Gregori | 0|
|Blumberg | 512||Sabel | 1|
|Brodie |3584||Blumberg | 512|
|Cameron |1024||Kay | 513|
|Clark |4096||Shigio | 514|
|Curan |1536||Cameron |1024|
|Curry |4608||Mandic |1025|
|Giller |2048||Stannich |1026|
|Gordon |5120||Curan |1536|
|Green |2560||McTigue |1537|
|Gregori | 0||Stover |1538|
|Huber |3073||Giller |2048|
|Kay | 513||Ramos |2049|
|Kreseski |3585||Verducci |2050|
|Mandic |1025||Green |2560|
|McShane |4097||Ross |2561|
|McTigue |1537||Aitken |3072|
|Ming |4609||Huber |3073|
|Ramos |2049||Saxena |3074|
|Robinson |5121||Brodie |3584|
|Ross |2561||Kreseski |3585|
|Sabel | 1||Smith |3586|
|Saxena |3074||Clark |4096|
|Shigio | 514||McShane |4097|
|Smith |3586||Stein |4098|
|Stannich |1026||Curry |4608|
|Stein |4098||Ming |4609|
|Stover |1538||Sullivan |4610|
|Sullivan |4610||Gordon |5120|
|Verducci |2050||Robinson |5121|
|Zimmerman |5122||Zimmerman |5122|
|---------------------------||-------------------------|
There is a row in the secondary index xname for every row in the employee table. There is also a column called tidp. This is the
tid of the row in the base table. Tids identify every row on every data page. For a complete discussion of tids, see
Tids. The tidp entry for an employee is the tid of the employee’s record in the base table.
There are no limits to the number of secondary indexes that can be created on a table. However, there is overhead involved in the maintenance and use of a secondary index that you must be aware of:
• When you add a row to the base table, you add an entry into every secondary index on the table as well.
• When a row in the base table moves, causing the tid to change, every secondary index must be updated to reflect this change. In a base table, rows move when the key is updated or if the table is compressed and a row is replaced that no longer fits in the same page.
Note: For a compressed table, when a varchar(width) column is updated and then recompressed, the row size can change.
• When the base table is updated, so that there is a change of the value in a column, which is used as the key of a secondary index, the key of the secondary index has to be updated as well.
• When processing a query execution plan for a query, the more indexes and plans possible for the query, the longer it takes to decide what query execution plan to use.