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

Home -> Community -> Usenet -> c.d.o.server -> Partitioned Text index on partitioned table

Partitioned Text index on partitioned table

From: <paul_at_brownjohn.me.uk>
Date: 8 Mar 2007 08:11:04 -0800
Message-ID: <1173370264.260438.113980@v33g2000cwv.googlegroups.com>


Hi

Any Oracle text indexing gurus out there?

The application is an online translator's dictionary with a Java/ intranet front end and an Oracle 9i DB . A version using a global (nonpartitioned)  text index has been in production for some years but there are performance issues. By partitioning the main searchable data I was hoping to achieve a marked increase in performance

I am therefore trying to create a text index on a list partitioned table. The table is partitioned by L_ID (Number(5), a code indicating the language of the entry) and the text indexed column is Term (a varchar2 (1000) Char column containing the word or phrase in various languages).

I have tried every possible combination of syntax I can find in the Oracle doc...I have copied examples from the "asktom" website; whatever I do I get an error of some sort when attempting to build the index. Does anybody know if this can be done and if so how?

The DB is 9i (9.2.0.8) running on 64 bit Solaris.

I have created a mini-test table as follows:

CREATE TABLE TEST_PART_TEXT
(

  LIL_ID     NUMBER(10),
  TERM       VARCHAR2(1000 CHAR),
  L_ID       NUMBER(5),

  TEXT_LANG VARCHAR2(25 CHAR)
)
PARTITION BY LIST (L_ID)
(
  PARTITION PART_EN VALUES (6),
  PARTITION PART_FR VALUES (10)
)
NOCOMPRESS
NOCACHE
NOPARALLEL
NOMONITORING;
SET DEFINE OFF; Insert into TEST_PART_TEXT_IDX

(LIL_ID, TERM, L_ID, TEXT_LANG)

 Values
(1, 'Test', 6, 'ENGLISH');

Insert into TEST_PART_TEXT_IDX

(LIL_ID, TERM, L_ID, TEXT_LANG)

 Values
(2, 'Essai', 10, 'FRENCH');

COMMIT; Then I try to execute this index creation script against the above table:

create index part_idx on TEST_PART_TEXT(term) indextype is ctxsys.context
local (partition p_idx1, partition p_idx2);

This I took from the Oracle help files and just replaced their table names etc with mine.

When I try to execute it I get the error: Error at line 1
ORA-29850: invalid option for creation of domain indexes.

I have tried all sorts of other variations on the syntax...I have searched the AskTom website (and found slightly different code that produces the same error). I therefore concede defeat...I cannot get this working although I am convinced it should work.

Can anyone help?

TIA Paul BJ Received on Thu Mar 08 2007 - 10:11:04 CST

Original text of this message

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