Skip to Main Content

Breadcrumb

Question and Answer

Connor McDonald

Thanks for the question, Adam.

Asked: January 17, 2023 - 12:16 pm UTC

Last updated: January 19, 2023 - 12:20 am UTC

Version: 19c

Viewed 1000+ times

You Asked

Hello.
I create text index
CREATE INDEX IDXC_SEARCH_FULL_NAME ON T_SEARCH (main.name_full)
            INDEXTYPE IS CTXSYS.context
        PARAMETERS
        (''
            datastore     main.name_search_ds
            lexer         main.name_search_lexer
            wordlist      main.name_search_wl
            section group main.name_search_sg
            stoplist      CTXSYS.empty_stoplist
            storage       main.name_store
            sync          (ON COMMIT)
        '')'

After that
SELECT dbms_metadata.get_ddl(object_type => 'INDEX',
                             NAME        => 'IDXC_SEARCH_FULL_NAME',
                             SCHEMA      => 'MAIN')
  FROM dual;

returns correct PARAMETERS string and also it is shown in
SELECT parameters FROM dba_indexes WHERE index_name = 'IDXC_SEARCH_FULL_NAME';

Then, when I do
ALTER INDEX IDXC_SEARCH_FULL_NAME REBUILD

parameters disappear
SELECT parameters FROM dba_indexes WHERE index_name = 'IDXC_SEARCH_FULL_NAME';

returns NULL

Why is that?
Do I should add PARAMETERS with REPLACE when REBUILDing? Even if I don't change parameters?

and Connor said...

You don't need to re-specify the parameters, they are persisted and burnt into the index definition on creation.

(for example, this is why you could drop a preference that an index is using and the index will be fine - it picked up the *details* of the preference and included it in the index definition)

This is more an issue with dbms_metadata not really understanding this concept.

So there is no issue with rebuilding. But if you have (say) lost the original DDL, then you can resurrect it using CTX_REPORT, eg

SQL> begin
  2    ctxsys.ctx_ddl.drop_preference('SIMPLE_STORAGE');
  3    ctxsys.ctx_ddl.create_preference('SIMPLE_STORAGE','BASIC_STORAGE');
  4    ctx_ddl.set_attribute       ('SIMPLE_STORAGE', 'g_table_clause', 'tablespace largets');
  5    ctx_ddl.set_attribute       ('SIMPLE_STORAGE', 'g_index_clause', 'tablespace largets');
  6    ctx_ddl.set_attribute       ('SIMPLE_STORAGE', 'i_table_clause', 'tablespace largets');
  7    ctx_ddl.set_attribute       ('SIMPLE_STORAGE', 'i_index_clause', 'tablespace largets');
  8  end;
  9  /

PL/SQL procedure successfully completed.

SQL>
SQL> create table t as select * from dba_objects;

Table created.

SQL>
SQL> create index tmp_ix on t (object_name)
  2  indextype is ctxsys.context
  3  parameters ('storage simple_storage sync (on commit)');

Index created.

SQL> alter index tmp_ix rebuild;

Index altered.

SQL> select dbms_metadata.get_ddl(object_type => 'INDEX',
  2                               NAME        => 'TMP_IX',
  3                               SCHEMA      => user)
  4  from dual;

DBMS_METADATA.GET_DDL(OBJECT_TYPE=>'INDEX',NAME=>'TMP_IX',SCHEMA=>USER)
--------------------------------------------------------------------------------

  CREATE INDEX "MCDONAC"."TMP_IX" ON "MCDONAC"."T" ("OBJECT_NAME")
   INDEXTYPE IS "CTXSYS"."CONTEXT"

SQL> variable c clob
SQL> begin
  2    dbms_lob.createtemporary(:c,true);
  3    ctx_report.create_index_script('TMP_IX',:c);
  4  end;
  5  /

PL/SQL procedure successfully completed.

SQL> print c

C
--------------------------------------------------------------------------------
begin
  ctx_ddl.create_preference('"TMP_IX_DST"','DIRECT_DATASTORE');
end;
/

begin
  ctx_ddl.create_preference('"TMP_IX_FIL"','NULL_FILTER');
end;
/

begin
  ctx_ddl.create_section_group('"TMP_IX_SGP"','NULL_SECTION_GROUP');
end;
/

begin
  ctx_ddl.create_preference('"TMP_IX_LEX"','BASIC_LEXER');
end;
/

begin
  ctx_ddl.create_preference('"TMP_IX_WDL"','BASIC_WORDLIST');
  ctx_ddl.set_attribute('"TMP_IX_WDL"','STEMMER','ENGLISH');
  ctx_ddl.set_attribute('"TMP_IX_WDL"','FUZZY_MATCH','GENERIC');
end;
/

begin
  ctx_ddl.create_stoplist('"TMP_IX_SPL"','BASIC_STOPLIST');
  ctx_ddl.add_stopword('"TMP_IX_SPL"','Mr');
  ctx_ddl.add_stopword('"TMP_IX_SPL"','Mrs');
  ctx_ddl.add_stopword('"TMP_IX_SPL"','Ms');
  ctx_ddl.add_stopword('"TMP_IX_SPL"','a');
  ctx_ddl.add_stopword('"TMP_IX_SPL"','all');
  ctx_ddl.add_stopword('"TMP_IX_SPL"','almost');
  ctx_ddl.add_stopword('"TMP_IX_SPL"','also');
  ctx_ddl.add_stopword('"TMP_IX_SPL"','although');
  ctx_ddl.add_stopword('"TMP_IX_SPL"','an');
  ctx_ddl.add_stopword('"TMP_IX_SPL"','and');
  ctx_ddl.add_stopword('"TMP_IX_SPL"','any');
  ctx_ddl.add_stopword('"TMP_IX_SPL"','are');
  ctx_ddl.add_stopword('"TMP_IX_SPL"','as');
  ctx_ddl.add_stopword('"TMP_IX_SPL"','at');
  ctx_ddl.add_stopword('"TMP_IX_SPL"','be');
  ctx_ddl.add_stopword('"TMP_IX_SPL"','because');
  ctx_ddl.add_stopword('"TMP_IX_SPL"','been');
...
...
  ctx_ddl.add_stopword('"TMP_IX_SPL"','your');
  ctx_ddl.add_stopword('"TMP_IX_SPL"','yours');
end;
/

begin
  ctx_ddl.create_preference('"TMP_IX_STO"','BASIC_STORAGE');
  ctx_ddl.set_attribute('"TMP_IX_STO"','I_TABLE_CLAUSE','tablespace largets');
  ctx_ddl.set_attribute('"TMP_IX_STO"','R_TABLE_CLAUSE','lob (data) store as (ca
che)');
  ctx_ddl.set_attribute('"TMP_IX_STO"','I_INDEX_CLAUSE','tablespace largets');
  ctx_ddl.set_attribute('"TMP_IX_STO"','G_TABLE_CLAUSE','tablespace largets');
  ctx_ddl.set_attribute('"TMP_IX_STO"','G_INDEX_CLAUSE','tablespace largets');
end;
/

create index "MCDONAC"."TMP_IX"
  on "MCDONAC"."T"
      ("OBJECT_NAME")
  indextype is ctxsys.context
  parameters('
    datastore       "TMP_IX_DST"
    filter          "TMP_IX_FIL"
    section group   "TMP_IX_SGP"
    lexer           "TMP_IX_LEX"
    wordlist        "TMP_IX_WDL"
    stoplist        "TMP_IX_SPL"
    storage         "TMP_IX_STO"
    fast_dml
    memory  67108864
    sync (on commit)
  ')
/



Is this answer out of date? If it is, please let us know via a Comment

More to Explore

Design

New to good database design? Check out Chris Saxon's full fundamentals class.