Concatenated expressions

The Infor application can use concatenated expressions, which operate on a combined column. Concatenated expressions that exist on combined columns are as follows:

  • select >=
  • select >
  • select <=
  • select <
  • select between and

For example, an SQL statement can include a where clause such as: 

WHERE comb >= {"tt", "adv", "000"}

In this example, comb is a combined column of columns c1, c2, and c3. This expression selects these ranges of rows:

  • c1 = "tt" and c2 = "adv" and c3 >= "000"
  • c1 = "tt" and c2 > "adv"
  • c1 > "tt"

The Oracle driver can allow Oracle to solve the WHERE clause using three various techniques: nested, iterative, and filter. These techniques are introduced because the Oracle cost-based optimizer cannot efficiently handle these queries in all situations. For these queries, you can introduce full table scans and sort operations.

If you specify a different technique, the Oracle optimizer can make different decisions on how to run a query, and also provide workarounds for typical optimizer behavior. However, the optimizer behavior can change from version to version in Oracle; therefore, you can tune each version differently, which makes providing guidelines difficult.

To detect long running or bad performing queries, and then experiment with these various techniques, use the ORAPROF variable. For more information on ORAPROF, see Database Driver Resources and Environment Variable.

This list describes the nested, iterative, and filter techniques:

  • The nested technique:

    The three conditions are ORed to this expression:

    • c1 = "tt" and c2 = "adv" and c3 >= "000" OR
    • c1 = "tt" and c2 > "adv" OR
    • c1 > "tt"

    This can be rewritten as:

    • c1 > "tt" OR
    • c1 = "tt" AND (c2 > "adv" OR
    • c2 = "adv" AND (c3 >= "000"))

    The last expression has a nested AND/OR condition and is, therefore, referred to as the nested technique.

  • The iterative technique:

    To resolve one query, multiple SQL statements are issued; these statements do not contain OR conditions, and therefore can be efficiently handled by Oracle. You can only use the iterative technique for unbounded queries.

  • The iterative technique uses these three conditions:
    • c1 = "tt" and c2 = "adv" and c3 >= "000"
    • c1 = "tt" and c2 > "adv"
    • c1 > "tt"

    First, a query with Step 1 is issued. If the query does not return a row, the process continues with Step 2. If Step 2 does not return a row, the process continues with Step 3. Also, if one step has returned all rows, but more rows are required, the driver continues with the next step.

  • The filter technique:

    This technique is related to the nested technique but has a different approach. This technique initially selects too many rows, but then filters out those rows that do not match the total WHERE clause. This technique selects based on the first column in a concatenated index and filters out rows with the NOT() operator. The query is solved as:

    • c1 >= "tt" AND NOT( c1 = "tt" AND (c2 < "adv" OR
    • c2 = "adv" AND (c3 < "000") )

    The NOT() expression is like an inverted nested query. These rows are filtered out of the initial set determined by the first condition: c1 >= "tt".