Using a text index to query a view

When querying a view, you can specify a text index on the underlying base table to improve response time. For example, suppose you create a text index on the Employees.Address column called EmployeeAddressTxtIdx. Then, you create a view on the Employees table called MyEmployeesView. Using a statement similar to the following, you can query the view using the text index on the underlying table. 

SELECT COUNT(*) FROM MyEmployeesView WHERE CONTAINS( EmployeeAddressTxtIdx, 'Avenue' );

Searching a view using a text index on the underlying base table is restricted as follows:

  • The view cannot contain a TOP, FIRST, DISTINCT, GROUP BY, ORDER BY, UNION, INTERSECT, EXCEPT clause, or window function.
  • A CONTAINS query can refer to a base table inside a view, but not to a base table inside a view inside another view.
Copyright © 2008, iAnywhere Solutions, Inc. - SQL Anywhere 11.0.0