Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.misc -> Full text indexes and hints

Full text indexes and hints

From: Lord0 <lawrence.tierney_at_bipsolutions.com>
Date: Mon, 06 Sep 2004 13:36:31 GMT
Message-ID: <zJZ_c.522$Pw.67@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?

-- 
Kind regards

Lord0
Received on Mon Sep 06 2004 - 08:36:31 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US