| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Usenet -> c.d.o.tools -> Re: INDEX use in and related facets of Oracle
Since these are Lastname, Middlename, and Firstname which, under normal circumstances, contain only alphabetic characters, I use two items which might help you:
I use an uppercase NAME_SEARCH_KEY composed of the first five characters of the lastname and Oracle's soundex function
Table names:
Lastname varchar2(),
Middlename varchar2(),
Firstname varchar2(),
:
NAME_SEARCH_KEY varchar2(5),
SOUNDEX_SEARCH_KEY varchar2(4)
Consider:
SQL> select soundex('JONES') from dual;
SOUN
SQL> select soundex('Jones') from dual;
SOUN
"Ken Ho" <hoke_at_gse.harvard.edu> wrote in message
news:39cfbbd1.26321534_at_news.harvard.edu...
> 18000+ row table with mixed case last, first, and middle names. Users
> frequently do SELECT operations, but they have upper/lowercase leeway
> when requesting data via an HTML form. We upper() their data request
> for each field and check that it is LIKE upper() of the respective
> stored data || '%' (actual WHERE clause details below).
>
> The amount of disk space for an appropriate index (and auxiliary
> columns
> -- see below) is not an issue and improving speed of SELECT operations
> outweighs the consequent slowing down of INSERT or UPDATE operations.
>
> (Q1) If the existing mixed-case data columns are LAST, FIRST, MIDDLE,
> is it
> normal and reasonable to create additional columns LAST_UC, FIRST_UC,
> MIDDLE_UC, where the same data is loaded after being upper()'d, and
> creating a trigger so that those three columns are updated accordingly
> whenever there is an insert into or update of LAST, FIRST, or MIDDLE?
> That would then allow me to CREATE INDEX PERSONS_IX ON
> PERSONS(LAST_UC,FIRST_UC,MIDDLE_UC) which could be used, after
> I change the SELECT statement's WHERE clause from:
>
> (last_from_user IS NULL OR upper(last_from_user) LIKE upper(LAST) ||
> '%') AND
> (first_from_user IS NULL OR upper(first_from_user) LIKE upper(first)
> || '%') AND
> (middle_from_user IS NULL OR upper(middle_from_user) LIKE
> upper(middle) || '%')
> AND a.id = b.id
>
> (a. and b. are two tables for which I get a unique entry for a given
> person's ID, which is *not* input by the user; a. is in fact PERSONS,
> the one from which LAST, first, and middle come)
>
> to:
>
> (last_from_user IS NULL OR upper(last_from_user) LIKE LAST_UC
> OR upper(last_from_user) LIKE LAST_UC || '%') AND
> (first_from_user IS NULL OR upper(first_from_user) LIKE FIRST_UC
> OR upper(first_from_user) LIKE FIRST_UC || '%') AND
> (middle_from_user IS NULL OR upper(middle_from_user) LIKE MIDDLE_UC OR
> upper(middle_from_user) LIKE MIDDLE_UC || '%')
> AND a.id = b.id
>
> (Q2) Does the a.id = b.id criterion prevent the PERSON_IX index from
> being used?
>
> (Q3) If the PERSON_IX index is usable with the a.id = b.id criterion,
> will it at least speed up queries where a match can be found in the
> middle comparison (#2 of 3 for each name part comparison, the one
> without NULL and without LIKE) of:
>
> (upper(namepart_from_user) like namepart_uc)
>
> ? To put it another way, will Oracle (7.3.4 on NT) quit immediately
> out
> of that OR clause once it finds a match, so that if the WHERE clause
> is:
>
> X or Y
>
> and X is true, will it skip evaluating Y? I am trying to allow some
> freedom for name-matching but still use an index, but I realize that
> might not be possible.
>
> I gather that virtually all functions (and in any case, certainly
> upper()) when applied to a column prevent use of an index on that
> column, thus the %_UC (uppercased) columns.
>
> (Q4) Is there a better / any solution to what I am trying to
> accomplish?
Received on Mon Sep 25 2000 - 17:11:53 CDT
 |
 |