Wednesday, October 2, 2013

Full Text Index / Domain Index ( create datastore, assign tablespace for storage)

Full Text Index / Domain Index ( create datastore, assign tablespace for storage)

Grant required role/Previliges to user:
GRANT EXECUTE ON CTXSYS.CTX_DDL TO myuser;

Create storage preferences and print joins (lexer - basic_lexer)

begin
    ctx_ddl.create_preference('mystore', 'BASIC_STORAGE');
    ctx_ddl.set_attribute('mystore', 'I_TABLE_CLAUSE','tablespace LOB_INDEX storage (initial 32k)');
    ctx_ddl.set_attribute('mystore', 'K_TABLE_CLAUSE','tablespace LOB_INDEX storage (initial 32k)');
    ctx_ddl.set_attribute('mystore', 'R_TABLE_CLAUSE','tablespace LOB_INDEX storage (initial 32k) lob (data) store as (disable storage in row cache)');
    ctx_ddl.set_attribute('mystore', 'N_TABLE_CLAUSE','tablespace LOB_INDEX storage (initial 32k)');
    ctx_ddl.set_attribute('mystore', 'I_INDEX_CLAUSE','tablespace LOB_INDEX storage (initial 32k) compress 2');
    ctx_ddl.set_attribute('mystore', 'P_TABLE_CLAUSE','tablespace LOB_INDEX storage (initial 32k)');
    ctx_ddl.set_attribute('mystore', 'S_TABLE_CLAUSE','tablespace LOB_INDEX storage (initial 32k)');

    ctx_ddl.create_preference('mylex', 'BASIC_LEXER');
    ctx_ddl.set_attribute('mylex', 'printjoins', '_-');
    ctx_ddl.set_attribute ( 'mylex', 'index_themes', 'NO');
    ctx_ddl.set_attribute ( 'mylex', 'index_text', 'YES'); 

end;
/

CREATE TABLE my_docs (
    id    NUMBER(10)     NOT NULL,
    name  VARCHAR2(200)  NOT NULL,
    doc   BLOB           NOT NULL
);


create index domain_idx on my_docs(doc) indextype is ctxsys.context parameters( 'lexer mylex storage mystore');

Now index data will store in tablespace "LOB_INDEX"



YOU may also like :-
basic-lexer printjoins domain indexes

No comments:

Post a Comment

web stats