Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.server -> Re: Column Questions
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