Reputation: 11
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
Upvotes: 1
Views: 122
Reputation: 146349
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;
Upvotes: 3