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?
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)
')
/