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

Home -> Community -> Usenet -> c.d.o.server -> Re: Column Questions

Re: Column Questions

From: Andrew Mobbs <andrewm_at_chiark.greenend.org.uk>
Date: 29 May 2001 11:35:29 +0100 (BST)
Message-ID: <nYn*C7nXo@news.chiark.greenend.org.uk>

Spencer <spencerp_at_swbell.net> wrote:
>"Dr. Mueller" <nospam_at_nospam.com> wrote in message
>>
>> Yes, I mean to search for a particular word in the description, for
 example
>> "Microsoft" or "Product". I just wondered if it were acceptable to index
>> such a column. So Oracle wouldn't use an index on this column? ...even
>> although I created one?
>>
>
>oracle may use the index if it can perform a range scan on the column
>e.g.
> WHERE DESCRIPTION = 'Product'
> WHERE DESCRIPTION LIKE 'Product%'
>
>but oracle will NOT use the index if you use a function on the indexed
>column or you are not searching on the leading portion of the column
>e.g.
> WHERE UPPER(DESCRIPTION) = 'PRODUCT'
> WHERE DESCRIPTION LIKE '%Product%'
>
>for "keyword" search capability, we developed our own custom keyword
>"index" scheme. this involved adding separate "keyword" tables and a
>different approach to writing the SELECT statements.
>
>here is builtin functionality (introduced with Oracle8) for full text
>searching
>(as part of intermedia CONTEX ?) which i have not used before.

See function based indexes in Oracle8i (EE only I think), e.g:

CREATE INDEX fbi_upper_firstname ON emp(UPPER(first_name));

Oracle will then use this index for queries such as:

SELECT * from emp where UPPER(first_name) = 'SCOTT';

Make sure that you set 'query_rewrite_enabled = true' either in init.ora or on a session level.

-- 
Andrew Mobbs - http://www.chiark.greenend.org.uk/~andrewm/
Received on Tue May 29 2001 - 05:35:29 CDT

Original text of this message

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