Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
![]() |
![]() |
Home -> Community -> Usenet -> c.d.o.misc -> 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?
-- Kind regards Lord0Received on Mon Sep 06 2004 - 08:36:31 CDT
![]() |
![]() |