Help

 

DSNIRIDR

Module DSNIRIDR is used for index only access to retrieve a list of RIDS for data retrieval or for inserting into the RID Pool. The RIDS can be used during list prefetch and multiple index access, if they were placed into the RID pool and subsequently sorted.

Hints

List prefetch is always used with multiple index accesses and frequently used for the inner table of a hybrid join or access through a low cluster ratio index.

An L will appear in the PREFETCH column in the PLAN_TABLE when list prefetch has occurred as a result of a hybrid join or single index access. This indicator does not appear for multiple index access but list prefetch is always invoked for these accesses.

A problem occurs when, at execution time, the optimizer turns off list prefetch and fails back to a tablespace scan because more than 25% of the rows of a table will need to be accessed (when that 25% exceeds 4075 rows). Once this occurs prefetch is disabled for the remainder of the execution of the plan. The failure of a list prefetch back to a tablespace scan is noted on the Db2PM Accounting Summary Report.

List prefetch may also be a disadvantage in an online environment since the sort delays the return of the first row to the application.

List prefetch can be eliminated at bind time by using the OPTIMIZE FOR n ROWS clause, setting the value to 1.

Reference Sources

IBM Db2 for OS/390, Messages and Codes, GC26-8979

IBM Db2 for OS/390, Application Programming and SQL Guide, SC26-8958

IBM Db2 for OS/390, Administration Guide, SC26-8957