Thursday, 6 December 2012

Oracle text and domain indexes.

We all know that normal indexes can't be used for statements such as "select * from table where col like '%abc%'". Reason for this can be found here.

Now if you have a very big table and with statements like above you can't use index, as a result you will have to go for full table scan which you really don't want. So problem to this situation is domain indexes. We will show you how to create it and we will show how index can be used with predicates such as '%pattern%'. But to create domain indexes you need to have environment in place. If you don't have please create ctxsys user from here before going forward.

We will create a sample test table. Then we will create a domain index.

SQL> create table test as select object_name,object_type from dba_objects;

Table created.

Execute the following block so as to create a preference. This preference will be used while creating the index. We have many more attributes available, but for current scenario we can use this.

SQL> begin
  2  ctx_ddl.create_preference('SUBSTRING_PREF', 'BASIC_WORDLIST');
  3  ctx_ddl.set_attribute('SUBSTRING_PREF', 'SUBSTRING_INDEX','TRUE');
  4  end;
  5  /

PL/SQL procedure successfully completed.

Now create a domain index as shown.

SQL> create index test_idx on test(object_name)
  2  indextype is ctxsys.context parameters ('wordlist SUBSTRING_PREF MEMORY 50M');
Index created.

From the plan we can clearly see that index is being used for the predicate '%EXEC%'. If we haven't created the domain index it would have gone for full table scan. 

SQL> explain plan for select * from test where contains( object_name,'%EXEC%') > 0;


SQL> select * from table(dbms_xplan.display);

| Id  | Operation                   | Name     | Rows  | Bytes | Cost (%CPU)| Time     |
|   0 | SELECT STATEMENT            |          |     9 |   801 |     1   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| TEST     |     9 |   801 |     1   (0)| 00:00:01 |
|*  2 |   DOMAIN INDEX              | TEST_IDX |       |       |     1   (0)| 00:00:01 |

Predicate Information (identified by operation id):

   2 - access("CTXSYS"."CONTAINS"("OBJECT_NAME",'%EXEC%')>0)


back to top