5.2.4 Cases where an index is not used

If any of the following search conditions is specified, no index is used. In the examples below, C1 and C2 are the names of table columns.

Negative conditions are specified.

As shown in the example below, if a index that has column C1 as its indexed column is defined, but negative conditions are specified as the search conditions, that index is not used.

Example:
```
WHERE "C1"<>100
WHERE "C1" IS NOT NULL
WHERE "C1" NOT LIKE 'ABC%'
WHERE "C1" NOT IN (10,20,30)
WHERE "C1" NOT BETWEEN 20 AND 40
```
Logical operator NOT is specified.

Example:
```
WHERE NOT ("C1"=100)
```
Conditions containing scalar operations, such as arithmetic operations and CASE expressions, are specified.

As shown in the example below, if an index that has column C1 as its indexed column is defined, but the specified search condition contains a scalar operation, such as arithmetic operations or CASE expressions, that index is not used.

Example:
```
WHERE C1*10=200
```
When a scalar operation is specified in a certain way, its search condition is automatically subjected to equivalent exchange to allow the use of indexes. For details about equivalent exchange for scalar operations, see 5.11.4 Equivalent exchange for scalar operations.

If only literals are specified in a scalar operation, that scalar operation might be treated as a literal. For details about scalar operations equivalent to literals, see the table Conditions under which value expressions are equivalent to literals under Rules in Specification format and rules for value expressions in the manual HADB SQL Reference.
An IN subquery in which is specified a subquery containing an external reference column is specified.

Example:
```
WHERE "T1"."C1" IN (SELECT "C1" FROM "T2" WHERE "C2"="T1"."C2")
```
An =ANY quantified predicate in which is specified a subquery containing an external reference column is specified.

Example:
```
WHERE "T1"."C1"=ANY(SELECT "C1" FROM "T2" WHERE "C2"="T1"."C2")
```
An =SOME quantified predicate in which is specified a subquery containing an external reference column is specified.

Example:
```
WHERE "T1"."C1"=SOME(SELECT "C1" FROM "T2" WHERE "C2"="T1"."C2")
```

A quantified predicate other than =ANY or =SOME is specified.

Example:

WHERE "C1"<>ANY(SELECT "C1" FROM "T2")
WHERE "C1"<>SOME(SELECT "C1" FROM "T2")
WHERE "C1"=ALL(SELECT "C1" FROM "T2")

The EXISTS predicate is specified.

Example:
```
WHERE EXISTS(SELECT * FROM "T2")
```
The pattern character string of a LIKE predicate is specified as follows:
- The pattern character string consists only of the special character %.
  
  Example:
```
WHERE "C1" LIKE '%'
```
- The pattern character string is an empty string.
  
  Example:
```
WHERE "C1" LIKE ''
```
- The pattern character string does not contain two or more consecutive non-special characters.
  
  Example:
```
WHERE "C1" LIKE '%A%'
WHERE "C1" LIKE '%A%B%'
```
- The pattern character string specifies a column in the same table as the column in the match value.
  
  Example:
```
WHERE "T1"."C1" LIKE "T1"."C2"
```
When search conditions are specified as in the preceding examples, even if a text index is defined that has the C1 column as an indexed column, that index will not be used.

To Page Top