Re: Full text indexes and hints

> > 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'?

(LT) UPPERing the text would be okay for the case insensitive matches but if I UPPERed 'BigDog' and 'bIGdOG' (looking for them in a case sensitive manner) I'd get the same results and only if the case sensitive index contained BIGDOG.

Maybe my initial background info was unclear: I want to be able to query a column (contract_text) in a case sensitive and case insensitive manner using the Oracle CONTAINS function. I have two full-text indexes, one case sensitive, one case insensitive. For each select I want to query both of these indexes, which refer to the same column: contract_text. Received on Mon Sep 06 2004 - 10:14:41 CDT