Re: Case insensitive searching

From: Henk Rook <enctdep_at_ibm.net>
Date: 1997/04/16
Message-ID: <01bc4ab8$df340ee0$ad852581@ibm.ibm.net>#1/1

If you query a table with a statement like: SELECT * FROM EMPLOYEES WHERE UPPER(EMPLOYEE_NAME)='LARRY ELLISON'; you have to realize that Oracle will make a full table scan, because there is no index on UPPER(EMPLOYEE_NAME).

If yoy read the Forms 4.5 Online Help on the item 'Case Insensitive Query Property'
may be you will get some hints. It says:

Case-insensitive queries are optimized to take advantage of an index. For example, assume you perform the following steps:

 · Create an index on the EMP table. 
 · Set the Case Insensitive Query property on ENAME to True. 
 · In Enter Query mode, enter the name 'BLAKE' into :ENAME. 
 · Execute the query. 

Oracle Forms constructs the following statement:

SELECT * FROM EMP WHERE UPPER(ENAME) = 'BLAKE' AND    (ENAME LIKE 'Bl%' OR ENAME LIKE 'bL%' OR    ENAME LIKE 'BL%' OR ENAME LIKE 'bl%');

The last part of the WHERE clause is performed first, making use of the index.
Once the database finds an entry that begins with bl, it checks the UPPER(ENAME) = 'BLAKE' part of the statement, and makes the exact match.

-- 
Henk Rook
ENNIA Caribe NV, Curaçao
ENCTDEP_at_IBM.NET

Ari Kaplan <akaplan_at_psycfrnd.interaccess.com> wrote in article
<5j2oio$oo7_at_psycfrnd.interaccess.com>...


> cmunday_at_neumann.une.edu.au (Craig Munday) writes:

> 

> >Being a DBA currently moving our systems from Sybase to Oracle I have a

 newbie


> >question concerning sort ordered.

> >Is it possible to configure an Oracle Database/Instance with a case 

> >insensitive sort order?

> >Your help would be much appreciated.

> 

> Craig,

> 

> Making a search case insensitive is done on a search-by-search basis and

> cannot be configured Instance-wide.

> 

> To make a search ("query") case-insensitive, use the UPPER function. For 

> example,

> 

> SELECT * FROM EMPLOYEES WHERE UPPER(EMPLOYEE_NAME)='LARRY ELLISON';

> 

> To order the results of a query regardless of the case, also use the 

> UPPER function:

> 

> SELECT * FROM EMPLOYEES ORDER BY UPPER(EMPLOYEE_NAME);

> 

> -Ari Kaplan

> Independent Oracle DBA Consultant

Received on Wed Apr 16 1997 - 00:00:00 CDT