Indexes on spatial data can reduce the cost of evaluating relationships between geometries. For example, suppose that you are considering changing the boundaries of your sales regions and want to determine the impact on existing customers. To determine which customers are located within a proposed sales region, you could use the ST_Within method to compare a point representing each customer address to a polygon representing the sales region. Without any index, the database server must test every address point in the Customer table against the sales region polygon to determine if it should be returned in the result, which could be expensive if the Customer table is large, and inefficient if the sales region is small. An index including the address point of each customer may help to return results faster. If a predicate can be added to the query relating the sales region to the states which it overlaps, results might be obtained even faster using an index that includes both the state code and the address point.
There is no special procedure required when creating an index on spatial data (for example, CREATE INDEX statement, Create Index wizard, and so on). However, when creating indexes on spatial data, it is recommended that you do not include more than one spatial column in the index, and that you position the spatial column last in the index definition.
Also, in order to include a spatial column in an index, the column must have a SRID constraint. See Using a SRID as column constraint.
Spatial queries may benefit from a clustered index, but other uses of the table need to be considered before deciding to use a clustered index. You should consider, and test, the types of queries that are likely to be performed to see whether performance improves with clustered indexes.
While you can create text indexes on a spatial column, they offer no advantage over regular indexes; regular indexes are recommended instead.
Discuss this page in DocCommentXchange.
|Copyright © 2010, iAnywhere Solutions, Inc. - SQL Anywhere 12.0.0|