What type of index should be used in Oracle

Question

I have a large table of 7 column in Oracle 11G. Total size of the table is more than 3GB and total row in this table is 1876823. Query we are using select doc_mstr_id from index_mstr where page_con1 like('%sachin%') it is taking almost a minute. please help me to optimize the query as well as proper indexing for this table. Please let me know if partitioned is required for this table.

Below are the column description

INDEX_MSTR_ID NUMBER         
DOC_MSTR_ID NUMBER         
PAGE_NO NUMBER         
PAGE_PART NUMBER         
PAGE_CON1 VARCHAR2(4000)

FILE_MODIFIED_DATE DATE           
CREATED_DATE DATE 

Answer 1

This query is always going to result in a full table scan. Your only filter cannot use a B-TREE index, due to the leading wildcard:

where page_con1 like('%sachin%')

If you want to do lots of queries of this nature you need to build a Text index on that column. From its datatype page_con1 appears to hold text fragments rather than full documents so you should use a CTXCAT index. This type of index has the advantage of being transactional, rather than requiring background maintenance. Find out more.

Your query would then look like this:

select doc_mstr_id from index_mstr 
WHERE CATSEARCH(page_con1, 'sachin') > 0;