Embedded Files
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.
Also see "case-insensitive comparison" section later on this page.
IS NULL
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
BETWEEN
SELECT myinteger
FROM mytable
WHERE myinteger BETWEEN 111 AND 999;
IN
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
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
LIKE
SELECT myinteger
FROM mytable
WHERE myvarchar LIKE 'MI%';
REGEXP_LIKE
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
AND
SELECT myinteger
FROM mytable
WHERE myinteger = 111
AND myvarchar = 'MIKE';
OR
OR
SELECT myinteger
FROM mytable
WHERE myinteger = 111
OR myvarchar = 'MIKE';
Case-Insensitive Comparison
Case-Insensitive Comparison
Session Level
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
Index Level
CREATE INDEX myindex
ON mytable
(NLSSORT(mycolumn,'NLS_SORT=BINARY_CI'));
Query Level
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
Videos
Bibliography
Bibliography
https://www.techonthenet.com/mysql/where.php
BETWEENhttps://www.techonthenet.com/mysql/between.php
REGEXP_LIKEhttps://stackoverflow.com/questions/5391069/case-insensitive-searching-in-oraclehttp://www.sqlfiddle.com/#!4/0959f/4https://stackoverflow.com/questions/12613514/what-is-the-n-before-a-string-argument
Case-Insensitive Comparisonhttps://stackoverflow.com/questions/5391069/case-insensitive-searching-in-oracle
BETWEENhttps://www.techonthenet.com/mysql/between.php
REGEXP_LIKEhttps://stackoverflow.com/questions/5391069/case-insensitive-searching-in-oraclehttp://www.sqlfiddle.com/#!4/0959f/4https://stackoverflow.com/questions/12613514/what-is-the-n-before-a-string-argument
Case-Insensitive Comparisonhttps://stackoverflow.com/questions/5391069/case-insensitive-searching-in-oracle
Page updated
Google Sites
Report abuse