This discussion is archived
1 Reply Latest reply: Dec 5, 2012 8:06 AM by sperkmandl RSS

Mismatch between created index and parameters

sperkmandl Newbie
Currently Being Moderated
Hi all,
I'm using DB version 11g, Release 11.2.0.1.0 and I created an index like this:

BEGIN
CTX_DDL.CREATE_STOPLIST('GERMAN_STOPLIST');
CTX_DDL.ADD_STOPWORD('GERMAN_STOPLIST','aaaaa');
CTX_DDL.ADD_STOPWORD('GERMAN_STOPLIST','ab');
END;

BEGIN
CTX_DDL.CREATE_PREFERENCE('mylex', 'BASIC_LEXER');
CTX_DDL.SET_ATTRIBUTE('mylex', 'mixed_case', 'YES');
CTX_DDL.SET_ATTRIBUTE('mylex', 'new_german_spelling', 'YES');
CTX_DDL.SET_ATTRIBUTE('mylex', 'alternate_spelling', 'GERMAN');
CTX_DDL.SET_ATTRIBUTE('mylex', 'composite', 'GERMAN');
CTX_DDL.SET_ATTRIBUTE('mylex', 'index_stems', 'NONE');
END;

BEGIN
CTX_DDL.CREATE_PREFERENCE('mywordlist', 'BASIC_WORDLIST');
CTX_DDL.SET_ATTRIBUTE('mywordlist', 'STEMMER', 'GERMAN');
CTX_DDL.SET_ATTRIBUTE('mywordlist', 'substring_index', 'NO');
CTX_DDL.SET_ATTRIBUTE('mywordlist', 'prefix_index', 'NO');
END;

CREATE INDEX store1.URL_FULLTEXT_IDX ON store1.FULLTEXT_TABLE (url)
INDEXTYPE IS CTXSYS.CONTEXT
parameters ('
DATASTORE CTXSYS.URL_DATASTORE
FILTER CTXSYS.AUTO_FILTER
LEXER mylex
WORDLIST mywordlist
STOPLIST GERMAN_STOPLIST
SYNC (ON COMMIT)
')

then I got index description as:

===========================================================================
INDEX DESCRIPTION
===========================================================================
index name: "STORE1"."URL_FULLTEXT_IDX"
index id: 1405
index type: context
base table: "STORE1"."FULLTEXT_TABLE"
primary key column:
text column: URL
text column type: VARCHAR2(1600)
language column:
format column:
charset column:
Query Stats Enabled: NO
sync type: on commit
status: INDEXED
full optimize token:
full optimize count:
docid count: 2
nextid: 3
===========================================================================
INDEX OBJECTS
===========================================================================
datastore: URL_DATASTORE
timeout: 30
maxthreads: 8
urlsize: 256
maxurls: 256
maxdocsize: 2097152
filter: AUTO_FILTER
section group: HTML_SECTION_GROUP
lexer: BASIC_LEXER
mixed_case: NO
alternate_spelling: GERMAN
new_german_spelling: NO
wordlist: BASIC_WORDLIST
stoplist: BASIC_STOPLIST
stop_word: deinen
stop_word: deiner
stop_word: deines
...
storage: BASIC_STORAGE
r_table_clause: lob (data) store as (cache)
i_index_clause: compress 2

Now it appears that:

- mixed_case/new_german_spelling are set to NO while they should be set to YES.
- lexer is BASIC_LEXER instead of mylex;
- wordlist is BASIC_WORDLIST instead of mywordlist
- stoplist is BASIC_STOPLIST instead of GERMAN_STOPLIST. Indeed entries are totally different.

It seems that index parameters were almost ignored (silently). And BTW, current schema has been set not to the current user.
I will appreciate any explanation.
Thanks,

Renzo

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points