Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.tools -> INDEX use in and related facets of Oracle

INDEX use in and related facets of Oracle

From: Ken Ho <hoke_at_gse.harvard.edu>
Date: Mon, 25 Sep 2000 20:57:09 GMT
Message-ID: <39cfbbd1.26321534@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 - 15:57:09 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US