| How to resolve full table scan with "like" operator in where condition? |
|
| Tips and technical articles of Oracle DBA |
| 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%'); |
|
| 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. |
|
