Re: Full text indexes and hints

"Lord0" <lawrence.tierney_at_bipsolutions.com> wrote in news:B9%_c.541$Pw.109_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

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

> I want to query both of these indexes

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 Received on Mon Sep 06 2004 - 10:29:59 CDT