CODEWITHSUNDEEP
oracle-databaseviewindexing
Christopher
Christopher

Reputation: 691

Index is not being used on a view with WHERE CONTAINS clause

I created a table and one of the columns is address. I then created a view with a WHERE CONTAINS clause that states select can only be performed on address that contain a specific word.

I then created an index of the address column on the original table.

It says index created.

When I type 

select * from myview

It says 

drg-10599: column is not indexed.

Any idea why this isn't working?

Upvotes: 5

Views: 8163

Answers (1)

Justin Cave
Justin Cave

Reputation: 231671

You would need to create an Oracle Text index, not a standard b-tree index. There are quite a few options for creating and maintaining Oracle Text indexes that you should really read through in order to figure out exactly what options you want to use.

The simplest possible DDL statement would be

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

Upvotes: 10

Related Questions