Home » Server Options » Text & interMedia » How do I list Index Preferences I have created? (Oracle XE, 10.2.01 Linux)
How do I list Index Preferences I have created? [message #305539] Tue, 11 March 2008 04:12 Go to next message
gurunandan
Messages: 5
Registered: November 2007
Junior Member
Hi,

Is there a way to list Index Preferences that I have created earlier? I had created a couple of MULTI-COLUMN-DATASTORE and DETAIL-DATASTORE preferences and I want to see what columns and tables were used in those.

A Data Dictionary View perhaps?

Thanks and regards
Gurunandan
Re: How do I list Index Preferences I have created? [message #305664 is a reply to message #305539] Tue, 11 March 2008 08:47 Go to previous messageGo to next message
Barbara Boehmer
Messages: 9077
Registered: November 2002
Location: California, USA
Senior Member
You can see this information using either ctx_report.describe_index or ctx_report.create_index_script, as demonstrated below.

-- test index:
SCOTT@orcl_11g> CREATE Table your_table
  2    (col1  VARCHAR2(10),
  3  	col2  VARCHAR2(10),
  4  	col3  VARCHAR2(10))
  5  /

Table created.

SCOTT@orcl_11g> BEGIN
  2    CTX_DDL.CREATE_PREFERENCE ('your_datastore', 'MULTI_COLUMN_DATASTORE');
  3    CTX_DDL.SET_ATTRIBUTE ('your_datastore', 'COLUMNS', 'col1, col2, col3');
  4  END;
  5  /

PL/SQL procedure successfully completed.

SCOTT@orcl_11g> CREATE INDEX your_index ON your_table (col1)
  2  INDEXTYPE IS CTXSYS.CONTEXT
  3  PARAMETERS ('DATASTORE  your_datastore STOPLIST CTXSYS.EMPTY_STOPLIST')
  4  /

Index created.


-- ctx_report.describe_index:
SCOTT@orcl_11g> select ctx_report.describe_index ('YOUR_INDEX') from dual
  2  /

CTX_REPORT.DESCRIBE_INDEX('YOUR_INDEX')
--------------------------------------------------------------------------------
===========================================================================
                             INDEX DESCRIPTION
===========================================================================
index name:                    "SCOTT"."YOUR_INDEX"
index id:                      4025
index type:                    context

base table:                    "SCOTT"."YOUR_TABLE"
primary key column:
text column:                   COL1
text column type:              VARCHAR2(10)
language column:
format column:
charset column:
Query Stats Enabled:           NO


status:                        INDEXED
full optimize token:
full optimize count:
docid count:                   0
nextid:                        1

===========================================================================
                               INDEX OBJECTS
===========================================================================
datastore:                     MULTI_COLUMN_DATASTORE
   columns:                       col1, col2, col3

filter:                        NULL_FILTER

section group:                 NULL_SECTION_GROUP

lexer:                         BASIC_LEXER

wordlist:                      BASIC_WORDLIST
   stemmer:                       ENGLISH
   fuzzy_match:                   GENERIC

stoplist:                      BASIC_STOPLIST

storage:                       BASIC_STORAGE
   r_table_clause:                lob (data) store as (cache)
   i_index_clause:                compress 2


-- ctx_report.create_index_script:
SCOTT@orcl_11g> select ctx_report.create_index_script ('YOUR_INDEX') from dual
  2  /

CTX_REPORT.CREATE_INDEX_SCRIPT('YOUR_INDEX')
--------------------------------------------------------------------------------
begin
  ctx_ddl.create_preference('"YOUR_INDEX_DST"','MULTI_COLUMN_DATASTORE');
  ctx_ddl.set_attribute('"YOUR_INDEX_DST"','COLUMNS','col1, col2, col3');
end;
/

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

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

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

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

begin
  ctx_ddl.create_stoplist('"YOUR_INDEX_SPL"','BASIC_STOPLIST');
end;
/

begin
  ctx_ddl.create_preference('"YOUR_INDEX_STO"','BASIC_STORAGE');
  ctx_ddl.set_attribute('"YOUR_INDEX_STO"','R_TABLE_CLAUSE','lob (data) store as
 (cache)');
  ctx_ddl.set_attribute('"YOUR_INDEX_STO"','I_INDEX_CLAUSE','compress 2');
end;
/


begin
  ctx_output.start_log('YOUR_INDEX_LOG');
end;
/

create index "SCOTT"."YOUR_INDEX"
  on "SCOTT"."YOUR_TABLE"
      ("COL1")
  indextype is ctxsys.context
  parameters('
    datastore       "YOUR_INDEX_DST"
    filter          "YOUR_INDEX_FIL"
    section group   "YOUR_INDEX_SGP"
    lexer           "YOUR_INDEX_LEX"
    wordlist        "YOUR_INDEX_WDL"
    stoplist        "YOUR_INDEX_SPL"
    storage         "YOUR_INDEX_STO"
  ')
/

begin
  ctx_output.end_log;
end;
/

Re: How do I list Index Preferences I have created? [message #305670 is a reply to message #305539] Tue, 11 March 2008 08:55 Go to previous messageGo to next message
Barbara Boehmer
Messages: 9077
Registered: November 2002
Location: California, USA
Senior Member
There are also various data dictionary views, such as ctx_user_index_values:

SCOTT@orcl_11g> select * from ctx_user_index_values where ixv_index_name = 'YOUR_INDEX'
  2  /

IXV_INDEX_NAME                 IXV_CLASS
------------------------------ ------------------------------
IXV_OBJECT                     IXV_ATTRIBUTE
------------------------------ ------------------------------
IXV_VALUE
--------------------------------------------------------------------------------
YOUR_INDEX                     WORDLIST
BASIC_WORDLIST                 STEMMER
ENGLISH

YOUR_INDEX                     WORDLIST
BASIC_WORDLIST                 FUZZY_MATCH
GENERIC

YOUR_INDEX                     DATASTORE
MULTI_COLUMN_DATASTORE         COLUMNS
col1, col2, col3

YOUR_INDEX                     STORAGE
BASIC_STORAGE                  R_TABLE_CLAUSE
lob (data) store as (cache)

YOUR_INDEX                     STORAGE
BASIC_STORAGE                  I_INDEX_CLAUSE
compress 2


SCOTT@orcl_11g> 

Re: How do I list Index Preferences I have created? [message #305673 is a reply to message #305539] Tue, 11 March 2008 09:11 Go to previous message
Barbara Boehmer
Messages: 9077
Registered: November 2002
Location: California, USA
Senior Member
And ctx_user_preference_values if you just want to search for one preference at a time, rather than a whole index:

SCOTT@orcl_11g> select * from ctx_user_preference_values where prv_preference = 'YOUR_DATASTORE'
  2  /

PRV_PREFERENCE                 PRV_ATTRIBUTE
------------------------------ ------------------------------
PRV_VALUE
--------------------------------------------------------------------------------
YOUR_DATASTORE                 COLUMNS
col1, col2, col3


SCOTT@orcl_11g> 

Previous Topic: BITMAP CONVERSION - Performance problem
Next Topic: How download files?? -- file_datastore
Goto Forum:
  


Current Time: Thu Mar 28 18:06:59 CDT 2024