• Share
  • ?
  • Profiles ▼
  • Communities ▼
  • Apps ▼

Blogs

  • My Blogs
  • Public Blogs
  • My Updates

This community can have members from outside your organization. Informix Experts

  • Log in to participate

▼ Tags

▼ Similar Entries

How to achieve bette...

Blog: Thoughts from...
AjayMPatil 31000030FM
Updated
0 people like thisLikes 0
No CommentsComments 0

IBM Sterling Integra...

Blog: Malarvizhi K ...
Malarvizhi_Kandasamy 060000VYUA
Updated
0 people like thisLikes 0
No CommentsComments 0

Search 'n' Rescue: Q...

Blog: CSE-WebSphere...
Eric-Scott 270006G0Q2
Updated
0 people like thisLikes 0
No CommentsComments 0

Schema consideration...

Blog: Thoughts from...
imaione 2700007WSP
Updated
0 people like thisLikes 0
No CommentsComments 0

When editing the des...

Blog: Asset Managem...
LeandroGarcia 27000412TT
Updated
1 people likes thisLikes 1
No CommentsComments 0

▼ Archive

  • February 2017
  • January 2017
  • December 2016
  • September 2016
  • August 2016
  • January 2015
  • October 2014
  • May 2014
  • April 2014
  • March 2014
  • February 2014
  • January 2014
  • December 2013
  • October 2013
  • September 2013
  • August 2013
  • June 2013
  • May 2013
  • April 2013
  • March 2013
  • February 2013
  • January 2013
  • December 2012
  • November 2012
  • October 2012
  • September 2012
  • August 2012
  • July 2012
  • June 2012
  • May 2012
  • April 2012
  • March 2012
  • October 2010
  • March 2010
  • December 2009
  • June 2009
  • May 2009
  • April 2009
  • March 2009
  • February 2009
  • December 2008
  • November 2008
  • October 2008
  • August 2008
  • June 2008
  • May 2008
  • March 2008
  • January 2008
  • December 2007
  • November 2007
  • October 2007
  • September 2007
  • August 2007
  • July 2007
  • June 2007
  • May 2007
  • April 2007
  • March 2007

▼ Blog Authors

Informix Experts

View All Entries
Clicking the button causes a full page refresh. The user could go to the "Entry list" region to view the new content.) Entry list

Index Self-Join

cheetahblog 0600028TE8 | | Tags:  selfjoin index ‎ | 3 Comments ‎ | 8,526 Views
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):


Diagram of a composite index




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


Diagram of an index scan prior to v11.10




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


Diagram of an index scan on v11.10




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


Table schema of TAB1



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:


Explain output prior to IDS v.11.10




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:


Explain output on IDS v.11.10




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
  • Add a Comment Add a Comment
  • Edit
  • More Actions v
  • Quarantine this Entry
Notify Other People
notification

Send Email Notification

+

Quarantine this entry

deleteEntry
duplicateEntry

Mark as Duplicate

  • Previous Entry
  • Main
  • Next Entry
Feed for Blog Entries | Feed for Blog Comments | Feed for Comments for this Entry