Beginning with IDS version 11.10, Informix introduced a new index scan feature, called “Index Self Join”. Priorto IDS version 11.10, a composite index scan allows to scan a single range of an index, based on start and end index key position. The Index Self Join access method permits scan many small subsequent ranges inside the composite index, instead of a large single range, based on filter on non-leading keys of a composite index.



On previous IDS versions, queries from table with composite index perform inefficiently where higher ratio of duplicate values in leading column than subsequent column(s) in the composite index. The Index Self Join is new type of index scan that can use subsets of the full range of a composite index. With this new index scan, tables can join logically to itself, and more selective non-leading index keys are applied as index bound filters to each unique combination of the leading key values.



Previously optimizer scans all index keys that fulfill the complete index key condition; otherwise it would make a sequential scan if the leading key had no WHERE clause conditions associated in the query. With this feature it will find the unique leading keys (low selectivity), and will make small queries using this unique keys and the rest of the index keys provided in the WHERE clause condition of the query.




Advantage
An Index Self Join is beneficial for situations in which:

• The lead key of an index has many duplicates, and
• Predicates on the lead key are not selective, but predicates on the non-leading index keys are selective.

How ‘Index Self Join’ works
Lets create a table ‘tab1’ with following table schema and load some data:

      CREATE TABLE tab1 (        col1  int,        col2  int,        col3  int,        col4  char(10));      CREATE INDEX idx1 ON tab1(col1,col2,col3);

Here is an illustration how IDS process following query prior version and on v11.10:

      SELECT * FROM TAB1      WHERE col1 >= 1 AND col1 <= 3      AND   col2 >= 20 AND col2 <=31      AND   col3 >= 40 AND col3 <= 62;

The simple view of the composite index ‘idx1’ on tab1(col1,col2,col3):






Prior to IDS v11.10, only possible filters could use on col1 (col1 >= 1 AND col1 <= 3) for positioning of the index scan:



Prior version
Lower Filter col1 >= 1, Upper Filter col1 <= 3






Beginning with IDS version 11.10, we can use filters on ‘col2’ and ‘col3’for positioning of the index scan that allows skip unnecessary index keys at two ends of the index. IDS scan selective index keys that are relevant, which avoid scanning a large portion of the index. This strategy will improve the query performance by reducing the portion of the index needs to scan.



IDS version 11.10
Lead Keys: col1, col2
Lower Filter col1 = col1, col2 = col2 and col3 >= 40
Upper Filter col3 <= 62






Example of ‘Index Self Join’
A table ‘tab1’ has been created with following schema:






So the table ‘tab1’ has a composite index ‘idx1’ on columns ‘col1’, ‘col2’ and ‘col3’. Now we will test following query without the leading index key ‘col1’ in WHERE clause condition:

    SELECT * FROM TAB1    WHERE col2 >= 10 AND col2 <=11    AND   col3 >= 20 AND col3 <= 22;

Following is the query access path of above SQL in IDS version prior to 11.10 where optimizer doing sequential scan:







Following is the query access path of previous SQL in IDS version 11.10 where optimizer using subsets of the full range of composite index:







Default Setting
By default, the optimizer considers ‘Index Self Join’ on IDS v.11.10. Following are couple of ways to change the default settings:

• Set onconfig parameter INDEX_SELFJOIN to 0
• Dynamically change the onconfig settings using onmode command

         onmode -wm INDEX_SELFJOIN=0 

• Using optimizer directive AVOID_INDEX_SJ

         SELECT {+AVOID_INDEX_SJ(TAB1 IDX1) *        FROM TAB1       WHERE col2 >= 10 AND col2 <=11       AND   col3 >= 20 AND col3 <= 22;

New Directives with ‘Index Self Join’
The optimizer also supports two new access-method directives:

    INDEX_SJ (table index [ , index ...] )     AVOID_INDEX_SJ (table index [ , index ...]

INDEX_SJ
Use the specified index to scan the table in an index self-join path. The optimizer is forced to scan the table using an index self-join path with the specified index (or to choose the least costly index in a comma-separated list of indexes for an index self-join path).



AVOID_INDEX_SJ
Does not use an index self-join path for the specified indexes. The optimizer does not consider the specified index for scanning the table in an index self-join path.

Sanjit Chakraborty