Index not used in view with WHERE CONTAINS clause

Question

I created a table, and one of the columns is an address. Then I created a view with the WHERE CONTAINS clause that the select state can only be executed at an address containing a specific word.

Then I created the index of the address column in the source table.

He says the index is created.

When i type

select * from myview 

It says

 drg-10599: column is not indexed. 

Any idea why this is not working?

Answer 1

You will need to create an Oracle Text index, not a standard b-tree index. There are several options for creating and maintaining Oracle Text indexes that you really need to read to determine exactly which parameters you want to use.

The simplest possible DDL expression would be

 CREATE INDEX myindex ON table_a(address) INDEXTYPE IS CTXSYS.CONTEXT;