IDMS: LIKE clause optimization

Products

• IDMS

Issue/Introduction

IDMS SQL SELECT statements containing a LIKE clause can be problematic to optimize and as a result may perform less than optimally.

Environment

Release : 19.0
Component : IDMS/SQL Option.

Cause

The IDMS optimizer has no way of knowing ultimately how many rows will meet the criteria specified in the LIKE clause.

Resolution

When optimizing a SELECT which uses a LIKE clause, even if the record/table has a MANDATORY AUTOMATIC system-owned index on the field/column, there are two primary access paths open to the optimizer.

1. Perform an area sweep of all of the data, looking in every record, and testing the field to see if it meets the LIKE clause criteria.

2. Perform a sweep of the index, test the field in the index to see if it meets the LIKE clause criteria. If it does, read the actual record data.

Which path is more efficient will depend on how many records ultimately meet the LIKE clause. The fewer records that do, the more likely it is that path 2 will be faster. However, as the number of records that meet the LIKE clause increases, it will be more likely that path 1 is faster.

The problem is that IDMS has no way of knowing in advance how many records will meet that criteria.

The OPTIMIZE FOR n ROWS clause can be used to influence this. The smaller the value for n, the more likely it is that it will choose path 2, depending on the other statistics available to the optimization process. A value of 1 will almost always result in path 2.

Example:

SELECT JOB_ID_0440, TITLE_0440
FROM EMPNET.JOB
WHERE TITLE_0440 LIKE '%COMPUTER%'
OPTIMIZE FOR 1 ROWS;
*+
*+ JOB_ID_0440  TITLE_0440
*+ -----------  ----------
*+        3029  COMPUTER OPERATOR
*+        3003  MGR COMPUTER OPS
*+
*+ 2 rows processed