Menu
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?
373
Introduction to SQL Server indexed view To create an indexed view, you use the following steps: First, create a view that uses the WITH SCHEMABINDING option which binds the view to the schema of the underlying tables. Second, create a unique clustered index on the view. This materializes the view.
Indexes can only be created on views which have the same owner as the referenced table or tables. This is also called an intact ownership-chain between the view and the table(s). Typically, when table and view reside within the same schema, the same schema-owner applies to all objects within the schema.
The reason the non-clustered index is not used is because it is more efficient to select the single row using the unique primary key clustered index. You can't get any faster than that to select all columns for a single row (barring a hash index on an in-memory table).
You should always add an index on any field to be used in a WHERE clause (whether for SELECT, UPDATE, or DELETE). The type of index depends on the type of data in the field and whether you need each row to have a unique value.
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;
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
