Re: Partitioned Text index on partitioned table

<paul_at_brownjohn.me.uk> wrote in message news:1173370264.260438.113980_at_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 (non-
> partitioned) 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
>

Paul,

LIST partitions are not supported.
This is from the docs:
http://download-east.oracle.com/docs/cd/A91202_01/901_doc/text.901/a90121/csql5.htm "Specify LOCAL to create a local partitioned context index on a partitioned table. The partitioned table must be partitioned by range. Hash, composite and list partitions are not supported."

Matthias Received on Thu Mar 08 2007 - 14:54:11 CST