WHERE

BETTER^ROWID = <contsant>Entire unique concatenated index = <constant>Unique indexed column = <constant>Entire concatenated index = <constant>Indexed column = <constant>Leading part of concatenated index = <constant>Unique indexed column BETWEEN 'x' and 'y' Or indexed column like 'C%'Unique indexed column '<' or '>' constantIndexed column '<' or '>' constantMax or Min of single indexed columnFull table scanvWORSE

=

SELECT myinteger

  FROM mytable

 WHERE myvarchar = 'MIKE';

If mycolumn has an index, it will be used.

SELECT myinteger

  FROM mytable

 WHERE myvarchar = N'MIKE';

Specifies that the literal string is subject to national language conversion... which will prevent use of regular indexes. To work around this, create a function based index on the myvarchar column for TO_NCHAR(myvarchar). Or, don't use N.. but note that it's quite common for things like Entity Framework to add these by default.

SELECT *

  FROM mytable

 WHERE UPPER(mycolumn) = UPPER('mystring');

The use of UPPER in this example will prevent use of regular indexes. Consider using a function-based index for UPPER(mycolumn).
Also see "case-insensitive comparison" section later on this page.

IS NULL

SELECT myinteger

  FROM mytable

 WHERE myvarchar IS NULL;

No Index will be used.

SELECT myinteger

  FROM mytable

 WHERE myvarchar IS NOT NULL;

No Index will be used.

BETWEEN

SELECT myinteger

  FROM mytable

 WHERE myinteger BETWEEN 111 AND 999;

IN

If the sub-query is likely to return a small number of rows, which contain a small amount of information then use 'IN', else use 'EXISTS'.

EXISTS

If the sub-query is likely to return a small number of rows, which contain a small amount of information then use 'IN', else use 'EXISTS'.

LIKE

SELECT myinteger

  FROM mytable

 WHERE myvarchar LIKE 'MI%';

REGEXP_LIKE

By default, acts in a similar war to 'LIKE'...

SELECT *

  FROM mytable

 WHERE REGEXP_LIKE(mycolumn, 'mystring');

To make the comparison case-insensitive...

SELECT *

  FROM mytable

 WHERE REGEXP_LIKE(mycolumn, 'mystring', 'i');

To make REGEXP_LIKE act like '='...

SELECT *

  FROM mytable

 WHERE REGEXP_LIKE(mycolumn, '^mystring$');

Also see "case-insensitive comparison" section later on this page.

AND

SELECT myinteger

  FROM mytable

 WHERE myinteger = 111

   AND myvarchar = 'MIKE';

OR

SELECT myinteger

  FROM mytable

 WHERE myinteger = 111

    OR myvarchar = 'MIKE';

Case-Insensitive Comparison

Session Level

ALTER SESSION SET NLS_SORT=BINARY_CI;

ALTER SESSION SET NLS_COMP=LINGUISTIC;

Note that you could set this at a database level is you were sure every comparison should be case-insensitive. 

Index Level

CREATE INDEX myindex

          ON mytable

    (NLSSORT(mycolumn,'NLS_SORT=BINARY_CI'));

Query Level

SELECT *

  FROM mytable

 WHERE REGEXP_LIKE(mycolumn, 'mystring', 'i');

See REGEXP_LIKE section earlier on this page. REGEXP_LIKE was introduced in Oracle 10g.

SELECT *

  FROM mytable

 WHERE mycolumn COLLATE BINRY_CI = 'mystring';

The COLLATE operator was introduced in Oracle 12c R2.

Videos

Bibliography