Re: Full text indexes and hints

"Lord0" <lawrence.tierney_at_bipsolutions.com> wrote in news:zJZ_c.522$Pw.67_at_newsfe5-gui.ntli.net:

> Hi there,
>
> We are using Oracle 9i Enterprise and taking advantage of it's
> interMedia text/full-text indexes functionality. We have one case
> INSENSITIVE index (contract_text_insensitive_idx) on the contract_text
> CLOB column and one case SENSITIVE index (contract_text_sensitive_idx)
> on the same column (I believe they have to be either or)
>
> I'm not very good with hints and what I would like to is (pseudo-ish):
>
> SELECT /*+ INDEX(contract_text_sensitive_idx
> contract_text_insensitive_idx)*/ id
> FROM contracts
> WHERE CONTAINS(contract_text, 'bigdog')>0 AND CONTAINS(contract_text,
> 'BigDog')>0
>
> So the query would return all contract ids where the contract_text
> contains 'bigdog' AND 'BigDog', with each CONTAINS using the relevant
> index i.e. case sensitive or not. I can't seem to get the first
> CONTAINS to use the case insensitive index and the second CONTAINS to
> use the case sensitive index. Maybe my approach is wrong?
>

Why don't you just UPPER the text & compare against 'BIGDOG'? Received on Mon Sep 06 2004 - 09:33:30 CDT