What type of index should be used in Oracle -

i have large table of 7 column in oracle 11g. total size of table more 3gb , total row in table 1876823. query using select doc_mstr_id index_mstr page_con1 like('%sachin%') taking minute. please me optimize query proper indexing table. please let me know if partitioned required table.

below 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  

this query going result in full table scan. filter cannot use b-tree index, due leading wildcard:

where page_con1 like('%sachin%') 

if want lots of queries of nature need build text index on column. datatype page_con1 appears hold text fragments rather full documents should use ctxcat index. type of index has advantage of being transactional, rather requiring background maintenance. find out more.

your query this:

select doc_mstr_id index_mstr  catsearch(page_con1, 'sachin') > 0;