Home > Oracle > Oracle Text Setup Script

Oracle Text Setup Script

— Oracle Text Setup
— Provide grants to MYUSER to run Oracle Text
CONNECT / as sysdba
GRANT RESOURCE, CONNECT, CTXAPP TO MYUSER;

CONNECT ctxsys/ctxsys
GRANT EXECUTE ON CTXSYS.CTX_CLS TO MYUSER;
GRANT EXECUTE ON CTXSYS.CTX_DDL TO MYUSER;
GRANT EXECUTE ON CTXSYS.CTX_DOC TO MYUSER;
GRANT EXECUTE ON CTXSYS.CTX_OUTPUT TO MYUSER;
GRANT EXECUTE ON CTXSYS.CTX_QUERY TO MYUSER;
GRANT EXECUTE ON CTXSYS.CTX_REPORT TO MYUSER;
GRANT EXECUTE ON CTXSYS.CTX_THES TO MYUSER;
GRANT EXECUTE ON CTXSYS.CTX_ULEXER TO MYUSER;

— Setup preferences for Oracle text
BEGIN
— Define table to use oracle text
CTX_DDL.CREATE_PREFERENCE(‘mytable_columns’, ‘MULTI_COLUMN_DATASTORE’);
CTX_DDL.SET_ATTRIBUTE(‘mytable_columns’, ‘COLUMNS’,’MYTABLE_COL1, MYTABLE_COL2, MYTABLE_COL3, MYTABLE_COL4′);

— basic_lexer will cover english and most european languagues
CTX_DDL.CREATE_PREFERENCE(‘mytable_lexer’,’BASIC_LEXER’);

— Basic_wordlist preference to improve wildcard performance
CTX_DDL.CREATE_PREFERENCE(‘mytable_wordlist’, ‘BASIC_WORDLIST’);
CTX_DDL.SET_ATTRIBUTE(‘mytable_wordlist’,’PREFIX_INDEX’,’TRUE’);
CTX_DDL.SET_ATTRIBUTE(‘mytable_wordlist’,’PREFIX_MIN_LENGTH’, ‘3’);
CTX_DDL.SET_ATTRIBUTE(‘mytable_wordlist’,’PREFIX_MAX_LENGTH’, ‘4’);
CTX_DDL.SET_ATTRIBUTE(‘mytable_wordlist’,’SUBSTRING_INDEX’, ‘YES’);

— Storage preference for Oracle text indexes
CTX_DDL.CREATE_PREFERENCE(‘mytable_storage’, ‘BASIC_STORAGE’);
CTX_DDL.SET_ATTRIBUTE(‘mytable_storage’,’i_table_clause’,’TABLESPACE TABLESPACE_INDEXES’);
CTX_DDL.SET_ATTRIBUTE(‘mytable_storage’,’k_table_clause’,’TABLESPACE TABLESPACE_INDEXES’);
CTX_DDL.SET_ATTRIBUTE(‘mytable_storage’,’n_table_clause’,’TABLESPACE TABLESPACE_INDEXES’);
CTX_DDL.SET_ATTRIBUTE(‘mytable_storage’,’r_table_clause’,’TABLESPACE TABLESPACE_INDEXES’);
CTX_DDL.SET_ATTRIBUTE(‘mytable_storage’,’i_index_clause’,’TABLESPACE TABLESPACE_INDEXES’);

END;
/
— Setup Oracle text index
CONNECT MYUSER/MYUSER

ALTER TABLE MYTABLE_DETAILS ADD SEARCH_COL varchar2(1);

— Sync on commit will sync the index as soon as base table is updated
CREATE INDEX MYTABLE_TEXT_IDX ON MYTABLE_DETAILS (SEARCH_COL)
INDEXTYPE IS CTXSYS.CONTEXT PARAMETERS (‘DATASTORE CTXSYS.mytable_columns
LEXER CTXSYS.mytable_lexer
WORDLIST CTXSYS.mytable_wordlist
STORAGE CTXSYS.mytable_storage
SYNC (ON COMMIT)’);

— Query to use oracle text search
SQL> SELECT * FROM MYTABLE_DETAILS WHERE CONTAINS (SEARCH_COL, ‘FINDME’) > 0;

Advertisements
Categories: Oracle
  1. No comments yet.
  1. No trackbacks yet.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: