1 Reply Latest reply: Dec 5, 2012 10:06 AM by sperkmandl RSS

    Mismatch between created index and parameters

    sperkmandl
      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