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

Tips and technical articles of Oracle DBA

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 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.

@?/ctx/admin/dr0csys

Now connect as CTXSYS user and run following commands.

@?/ctx/admin/dr0inst

@?/ctx/admin/defaults/drdefus.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.

@?/ctx/admin/dr0csys

@?/ctx/admin/defaults/drdefus.sql

grant execute on ctxsys.ctx_ddl to public;

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

Uninstall CTXSYS from Oracle 8i and Oracle 9i:

Execute following script as CTXSYS user.

@?/ctx/admin/dr0drop.sql

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

@?/ctx/admin/dr0dsys.sql

Uninstall CTXSYS from Oracle 10g:

Execute following script as SYS as SYSDBA user.

@?/ctx/admin/catnoctx.sql

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.

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.

SQL>create index scott_emp_ctx1 on scott_doc(author) indextype is ctxsys.context;

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 expert remote dba service provider team. Dbametrix has solid understanding to make SLA as per specification and requirement of client and end users. Dbametrix believes to provide remote services of database administration using SLA. Due to this reason Dbametrix offers 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.