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.
> >
> >
> >

>

>
> IMO,this statement is 100% nonsensical in an Oracle context.
> Simply put, you do NOT query any index.
> You query a column.
> The optimizer may choose use an index (or NOT).
> AFAIK, you'll actually need two columns.
> Of course some clever person may prove me wrong.
>
> HTH & YMMV

Before we proceed: thanks for your time.

I need to query the contract_text column (a bunch of text) case sensitively and case insensitively using Oracle full-text via the CONTAINS function. When you "build" a full text index it can be created either case insensitively (this is the default) or case sensitively. Therefore if I want to query the column in a case sensitive and insensitive manner I need two "full text indexes" (I think :-)) So what I want to do is:

SELECT id FROM contracts
WHERE CONTAINS(contract_text, 'dog')>0 -- case insensitive AND CONTAINS (contract_text, 'Dog')>0 -- case sensitive

AKAIK you cannot create a full-text index which can be searched in a case sensitive AND insensitive manner, hence my multiple index approach. You could create a sensitive index but you could not query it in an insensitive manner.

i.e. Case sensitive index contains

Dog
dOg

if we force the search terms to UPPER or LOWER it will not match the case sensitive values in the index. Received on Mon Sep 06 2004 - 10:45:51 CDT