Tip: Function-Based Indexes

Has anyone moved to 25R2 or 26R1 with function based indexes? Looks like they are being ignored?

I posted this 6 years ago, before I got my hands on IFS Cloud. In IFS Enterprise Explorer, case-insensitive queries were sent to the database with the UPPER() function, but in IFS Web Client (formerly known as IFS Aurena), the queries get sent with the LOWER() function. You’ll want to drop and recreate your function-based indexes to accommodate this.

Here’s now I know this:

As IFSADMIN on a [mostly] fresh install, I went to My Tasks and looked for “2026-07-23" in the title:

This is a Remote installation where I have database access AS SYSDBA. I used the SQL bind capture to find which recent SQL statement contains that value, then read the SQL text to see how the query got sent to the database. (Concatenating the search string rather than sending it as one big string ensures that I don’t pick up this query that I’m currently sending, although this is more of a habit from when I search the SQL text since my query is sending my value with hard-parsing.)

SELECT sql_fulltext
  FROM v$sql
 WHERE sql_id IN (SELECT sql_id
                    FROM v$sql_bind_capture
                   WHERE UPPER(value_string) LIKE '%2026' || '-07-23%');

The import part of the result is in the WHERE clause:

WHERE (((LOWER(A1.title)) LIKE :1  || '%'))

Many early computer systems only supported uppercase characters, and they became a de facto default in certain environments or cultures, especially BASIC and DOS. (UNIX is a notable exception to this.) I suspect that’s why UPPER() has always looked like a more natural default to a developer like me.

However, I found an interesting comment on that StackOverflow link I posted above:

Based on strings tending to have more lowercase entries, ToLower should theoretically be faster (lots of compares, but few assignments).

https://stackoverflow.com/a/234615/1686522

This means that this historical holdover is actually the wrong direction for performance optimization. Just inside this one comment I’m currently writing, you’ll find 765 lowercase characters and 44 uppercase ones (even with all the uppercase code). I originally scoffed that it looked like IFS had hired web developers with no experience with their existing framework using UPPER(), but they were actually right all along to make this switch to LOWER().

I had wondered whether this could get reported to Oracle as a feature request: If a function-based index exists on UPPER(), could a LOWER() query predicate be rewritten by the optimizer to use it? Well, their standard of quality is extremely high, so I wanted to prove this could work in the most pedantic sense before raising it. Unfortunately, even just looking at the Basic Multilingual Plane of Unicode code points up to 65535, I found a few edge cases where UPPER() and LOWER() don’t map one-to-one, mostly for mathematical symbols that don’t interchange between their math-specific symbols versus their true Greek equivalents.

SELECT *
  FROM (SELECT n,
               c,
               UPPER(c) AS upper_c,
               LOWER(c) AS lower_c,
               LOWER(UPPER(c)) AS lower_upper_c,
               UPPER(LOWER(c)) AS upper_lower_c
          FROM (
              SELECT LEVEL AS n,
                     UNISTR('\' || TO_CHAR(LEVEL, 'FM000X')) AS c
                FROM DUAL CONNECT BY LEVEL <= 65535))
 WHERE c             IS NULL
    OR upper_c       IS NULL
    OR lower_c       IS NULL
    OR lower_upper_c IS NULL
    OR upper_lower_c IS NULL
    OR upper_c != upper_lower_c
    OR lower_c != lower_upper_c;