String Comparison that is Not Case Sensitive - How to avoid Table Scan?

, , , , ,
1

How can we avoid a table scan when we need to use the following function in an sql query:

select col1 from table where lower(table_name) like ‘name%’

select col1 from table where table_name like ‘%name%’

I need to do a string comparison that is not case-sensitive.

The file table has 50M records in Adabas. The column table_name is a descriptor. This is needed in an online application.

Would turning on the CONNX parameter CASESENSITIVE=0 a better solution?

Thanks.

2

You have presented two different test cases.

For the first case: select col1 from table where lower(table_name) like ‘name%’
This would require the capability of performing a case-insensitive descriptor search, and this is currently not possible with adabas.

For the second case: select col1 from table where table_name like ‘%name%’
Since there is a wildcard character in the beginning of the like comparison - it is not possible to use a descriptor for this search with adabas.

Using the CASESENSITIVE=0 will have no impact on performance, because in both of the above test cases a descriptor cannot be used.

3

Larry,

That is what we have observed: consistent table scan in both cases. Unfortunateky, most of our core tables have > 30M rows and we need to allow users to do wild card search on name, address, state, organization, etc.

Thanks for responding so quickly.

Min

4

Let me detail this a bit more:

The statement is correct but if you really wanted to perform case-insensitive descriptor search in Adabas you could use Collation Descriptors or Hyper Descriptors.

Both are derived from the parent field and would be specified in place of the NAME = ‘name%’ :
COLL_NAME = ‘name%’ or
HYPER_NAME = ‘name%’ or UPPER(‘name%’).

Setting up these descriptors comes with an extra effort that also depends on the platform and the Adabas options:

  1. on Adabas Open Systems the collation descriptors work only on W fields but come out-of-the-box providing full collation functionality defined by the Unicode standard. This includes case-insensitivity but also insensitivity of special characters or diacritical characters used in languages like French or German

For example: COLL_NAME = ‘mueller*’ would find the following records

‘Mueller’
‘MUELLER’
‘Müller’
‘MÜLLER’

The collation also allows to ignore punctuation characters like . - or blank:
COLL_NAME = ‘stpaul*’ would find

‘StPauli’
‘St. Pauli’

If the data is not yet stored in W fields it is possible to employ a hyper exit on alpha fields.

There is no exit shipped that has the requested functionality but it is possible to write one.
The exit depends on the code page used - ASCII or Latin-1 should be fine for the US:
It should be easy to perform an upper casing of the field values.
When the hyper exit is defined with the HE option, the application can set the original search value otherwise it would have to do an UPPER(‘name*’).

  1. On Mainframe, Collation Descriptors and Hyper Descriptors exist as well with the following differences:

Collation Descriptors are based on exits and are not yet ready-to-use as in Adabas Open Systems.

They require the Universal Encoding option (UES=YES) and they can be defined on both W or A format fields.
There is an example exit providing the ASCII sort order for A format. That may easily modified to support upper casing and EBCDIC sort order.

The hyper exit does not support the HE option but this functionality is covered with the collation descriptor.