Showing results for 
Search instead for 
Do you mean 

Setup full-text search (How To)

by Technical Evangelist on ‎09-02-2015 01:32 AM (930 Views)

Oracle


User/Rights

 

 

GRANT CTXAPP TO [MYUSER];

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];



Index

 

 
Note: A Custom Lexer for case insensitivity MUST always be created.

 


Case Insensitive - Custom Lexer

 

 

--Creation of preference for custom LEXER
begin
ctx_ddl.create_preference(
preference_name => 'MCNO_LEXER',
object_name => 'BASIC_LEXER'
);

-- turn off MIXED_CASE
ctx_ddl.set_attribute(
preference_name => 'MCNO_LEXER',
attribute_name => 'MIXED_CASE',
attribute_value => 'NO'
);
end;

--Creation of text index with sync and custom lexer parameter
CREATE INDEX [IndexName]
ON [Table](SearchColumn)
INDEXTYPE IS CTXSYS.CONTEXT
PARAMETERS ('DATASTORE [PreferenceName] SYNC ( ON COMMIT ) LEXER MCNO_LEXER');

 

 

 

Note: One of the following text index should be created. So either Single Column or Multi column, but NOT both of them is recommended.

 


Single Column

 

--Simple Oracle text index 
CREATE INDEX [IndexName] ON [Table]([Column])
INDEXTYPE IS CTXSYS.CONTEXT 
PARAMETERS ('DATASTORE CTXSYS.DEFAULT_DATASTORE');

--Oracle text index with sync parameter 
CREATE INDEX [IndexName] ON [Table]([Column])
INDEXTYPE IS CTXSYS.CONTEXT 
PARAMETERS ('DATASTORE CTXSYS.DEFAULT_DATASTORE SYNC ( ON COMMIT)');


Multi Column 


Note: A dummy column (for example SearchColumn (varchar2(1)) is required.

 

--Creation of preference of custom MULTI_COLUMN_DATASTORE
begin
ctx_ddl.create_preference('[PreferenceName]', 'MULTI_COLUMN_DATASTORE');
ctx_ddl.set_attribute('[PreferenceName]', 'COLUMNS', '[COLUMN1],[COLUMN2],...');
end;

--Creation of text index with sync parameter
CREATE INDEX [IndexName]
ON [Table](SearchColumn)
INDEXTYPE IS CTXSYS.CONTEXT
PARAMETERS ('DATASTORE [PreferenceName] SYNC ( ON COMMIT )');


For more information about full-text search in oracle have a look at:

 

 


SQL Server

Configuration

 

  • Install full-text search with the SQL Server setup.

Fulltext_sql.png

  • Create full-text index with Management Studio.

Index_sql1.png

Note: The rest is pretty straightforward concerning the definition of full-text search in SQL Server.

 

For more information about full-text search in SQL Server have a look at:

Courses
Contributors