How to resolve full table scan with "like" operator in where condition?

This article is specially for remote dba who are working on 24*7 running large databases. Full table scan is always degrading performance of Oracle database. For troubleshooting of this issues are very important for remote dba. Because When we are using "like" operator in where condition in query. Oracle skips index scanning and performs full table scan. If you are using hint of index in query will also bypass index object. Full table scanning may be decrease performance of query when large table involved. Normal btree index doesn't accessed by Oracle when "like" operator used in where condition with "%" search string.

Oracle ConText Cartridge introduced in Oracle8.1.5 and it was called as interMedia Text. CTXSYS schema provides strong search, retrieve and accessing capabilities for text stored in Oracle database. ConText also provides advance linguistic processing of English language text search and extraction.

How to install CTXSYS schema in Oracle database Version Oracle 8i,Oracle 9i and Oracle 10g:

Install CTXSYS in Oracle 10.1.0.1 and Oracle 10.2.0.1:

How to uninstall Oracle ConText (CTXSYS schema) from Oracle 8i,Oracle 9i and Oracle 10g:

Uninstall CTXSYS from Oracle 10g:

How to use context index for eliminating full table scan with "like" operator in where condition ?

Oracle ConText: Install and de-install of Oracle ConText (CTXSYS) with help of ConText Index eliminating Full table scan. View large

Oracle text can perform linguistic analysis on documents as well as search text using a various search patterns like keyword searching, Boolean searching, matching pattern searching,HTML and XML searching. It support CHAR, VARCHAR, VARCHAR2, LONG, LONG RAW, BLOB, CLOB, BFILE, Date, number datatype columns.

Context index is very helpful to eliminating full table scan with "like" operator search string. Here we can see how can use it in our example. Now create context index for your column of table as follows. Now use context index for your table object for accessing "like" clause in where condition in select query.

Now use context index for your table object for accessing "like" clause in where condition in select query and check execution path for domain index scanning.

Special Tip: While using context index for search pattern don't use LIKE operator in where clause but instead of "like", use CONTAINS operator for searching criteria.

Dbametrix is expert remote dba services provider team. Dbametrix has solid understanding to make SLA as per specification and requirements of client and end users. Dbametrix believes to provide remote services of database administration using transparent SLA.We offer SLA based cost effective remote dba plans. Client of Dbametrix can able to put trust on company because Dbametrix delivers cost effective remote dba plan using Service Level Agreement SLA and response time matrix.