Re: Text query

From: Stephan Born <stephan.born_at_beusen.de>
Date: Thu, 16 Mar 2000 09:51:17 +0100
Message-ID: <38D0A085.BA3AACFF@beusen.de>

> I want to search over upper and lower case text indexed columns.
>
> Haw can I do this using the index and avoiding the complete table search
> that generates "WHERE UPPER(COLUMN) = 'ABCD'"
>

If you are using Oracle 8i the you can use function indexes:

from 'Oracle8i Concepts':

---

Function-Based Indexes

You can create indexes on functions and expressions that involve one or more columns in the table being indexed. A function-based index precomputes the value of the function or expression and stores it in the index. You can create a function-based index as either B*-tree or bitmap index (see "Bitmap

Indexes").

The function used for building the index can be an arithmetic expression or an expression that contains a PL/SQL function, package function, C callout, or
SQL function. The expression cannot contain any aggregate functions, and it must be DETERMINISTIC (see "DETERMINISTIC Functions"). For building an index on a column containing an object type, the function can be a method of that object, such as a map method. However, you cannot build a function-based index on a LOB column, REF, or nested table column; nor can you build a function-based index if the object type contains a LOB, REF, or nested table.

Uses of Function-Based Indexes

Function-based indexes provide an efficient mechanism for evaluating statements that contain functions in their WHERE clauses. You can create a function-based index to materialize computational-intensive expressions in the index, so that Oracle does not need to compute the value of the expression
when processing SELECT and DELETE statements. When processing INSERT and UPDATE statements, however, Oracle must still evaluate the function to process the statement.

For example, if you create the following index:

CREATE INDEX idx ON table_1 (a + b * (c - 1), a, b);

then Oracle can use it when processing queries such as this:

SELECT a FROM table_1 WHERE a + b * (c - 1) < 100;

Function-based indexes defined on UPPER(column_name) or LOWER(column_name) can facilitate case-insensitive searches. For example, the following index:

CREATE INDEX uppercase_idx ON emp (UPPER(empname));

can facilitate processing queries such as this:

SELECT * FROM emp WHERE UPPER(empname) = 'RICHARD';

A function-based index can also be used for an NLS sort index that provides efficient linguistic collation in SQL statements.

            Additional Information:

            See the Oracle8i National Language Support Guide for information about NLS sort indexes.

---

I hope it will help you.

Regards, Stephan
--

---

Dipl.-Inf. (FH) Stephan Born   | beusen Consulting GmbH
fon: +49 30 549932-0           | Landsberger Allee 392
fax: +49 30 549932-21          | 12681 Berlin
mailto:stephan.born_at_beusen.de  | Germany
---------------------------------------------------------------
       PGP-Key verfügbar       |      PGP-Key available
---------------------------------------------------------------

Received on Thu Mar 16 2000 - 02:51:17 CST