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 and providing dba services. 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 - Step By Step Guide:

Install CTXSYS in Oracle 8.1.5 , Oracle 8.1.6, Oracle 8.1.7, Oracle 9.0.1, Oracle 9.2.1:

Connect as internal in sql*plus and run following scripts.

SQL>@?/ctx/admin/dr0csys
Now connect as CTXSYS user and run following commands.
SQL>@?/ctx/admin/dr0inst
SQL>@?/ctx/admin/defaults/drdefus.sql
SQL>grant execute on ctxsys.ctx_ddl to public;

Install CTXSYS in Oracle 10.1.0.1 and Oracle 10.2.0.1:

Connect as SYS as SYSDBA in sql*plus and run following scripts.

SQL>@?/ctx/admin/dr0csys
SQL>@?/ctx/admin/defaults/drdefus.sql
SQL>grant execute on ctxsys.ctx_ddl to public;

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

Uninstall CTXSYS from Oracle 8i and Oracle 9i:

Execute following script as CTXSYS user.

SQL>@?/ctx/admin/dr0drop.sql

Now execute following script as sys or internal user for verification of the un-installation of Context (CTXSYS).

SQL>@?/ctx/admin/dr0dsys.sql

Uninstall CTXSYS from Oracle 10g:

Execute following script as SYS as SYSDBA user.

SQL>@?/ctx/admin/catnoctx.sql

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

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.

SQL>select book_refer,book_title from scott_doc where CONTAINS (author,'G%');

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 the expert Oracle DBA team to resolve every performance bottleneck using excellent database dba services and Remote Database support to very critical and large Oracle databases. Dbametrix has expertise to solve every performance problem of Oracle database server very quickly. During Remote DBA Services work, team provides proactive analysis regularly to update client for future performance forecasting.

Dbametrix is world wide leader in remote dba support. Expert remote DBA team of Dbametrix is offering high quality professional Oracle DBA support with strong response time to fulfill your SLA. Contact our sales department for more information.