Show Navigation | Hide Navigation
You are here:
Geodatabases and ArcSDE > Administering ArcSDE geodatabases > Tuning an ArcSDE geodatabase

Using database views

Release 9.2
Last modified December 3, 2010
E-mail This Topic Printable Version Give Us Feedback

Print all topics in : "Tuning an ArcSDE geodatabase"

Related Topics

NOTE: ArcSDE Enterprise only

Essentially, views are stored queries that select data from specified tables. The difference between a view and a regular query executed by a user or client application is that views are stored in the database, and the underlying tables can be indexed to improve performance.

Since views are stored in the database, view functionality varies from one database management system (DBMS) product to another. These differences are described in the DBMS-specific view information section at the bottom of this topic.

To navigate to different sections of this topic, use these links:

With ArcSDE, it is possible to define views against a single table, between two feature classes, or between a feature class and a table, or you can create more complex views containing subqueries or spanning databases. ArcSDE views are created with the sdetable –o create_view command. A spatial ArcSDE view includes the spatial column of a feature class. This view will appear as a feature class to ArcSDE clients.

NOTE: When you create a view on a versioned dataset, you will only see the business table, not the edits in the delta tables!

Views can be created using the sdetable administration command. The syntax of sdetable –o create_view is

sdetable –o create_view  
–T <view_name> 
–t <table1,table2...tablen>     
–c <table_col1,table_col2...table_coln
[–a <view_col1,view_col2...view_coln>] 
[–w <"where_clause">]
[–i <service>] 
[–s <server_name>] 
[–D <database>]
–u <DB_User_name> [–p <DB_User_password>] [–N] [–q]

NOTE: You must have CREATE VIEW permissions to execute this command.

You must list the columns (–c) you want in the view as well as a WHERE clause (–w) within the sdetable command. To make a spatial view, you need to be sure to specify the spatial column with the –c option column list.

To remove a view, you can use the sdetable –o delete command.

Each DBMS product also provides ways to create database views, usually by issuing SQL commands. Since this is DBMS specific, consult the documentation provided with your DBMS product. Views created outside ArcSDE need to be registered with the geodatabase before they can be used by an ArcGIS client.

Specific permissions are needed to allow a user to create a view; for example, the user must be granted CREATE VIEW privileges in Oracle or SQL Server databases. Also, with some DBMSs, if you create a view on a feature class or table for which you are not the owner, you will not be able to grant other users rights to the view unless the owner of the underlying tables has granted you permission to grant privileges to other users.


Top of page

Spatial views

Spatial views contain a single spatial column. You can create spatial views in the same way you create other views. The table containing the spatial column must be the same table that provides the row ID column for the view. Spatial views cannot be edited through an ArcSDE client.

A spatial view can only reference one table that contains a spatial column. If you want to include attribute information from another table containing a spatial column, you must first create a nonspatial view of that second table. You can then create your spatial view using one table with a spatial column and attribute information from the nonspatial view. The following is an example of this:

To create a spatial view containing the site address and shape columns from the ADDRESS feature class and the site address and owner name from the PARCEL feature class, you could first create a simple view of the PARCEL business table:

sdetable –o create_view –T view_parcel_owner –t parcel 
–c owner,site_address –u manager –p fl

Next, create a spatial view using attributes from the ADDRESS feature class and the simple view, VIEW_PARCEL_OWNER

sdetable –o create_view –T view_adrs_owner –t address,view_parcel_owner 
–c address.objectid,address.site_address,address.shape, view_parcel_owner.owner,view_parcel_owner.site_address 
–w "address.site_address = view_parcel_owner.site_address" –u manager –p fl

Spatial views should only be created on nonversioned data; otherwise, they may return inaccurate results. The software does not prevent you from creating a spatial view on a multiversioned table, so you must enforce this practice as part of your workflow.

To create views that can be used through ArcGIS Desktop applications, you should use the sdetable –o create_view command to create the view, being sure to include the ObjectID field from the feature class in the view's fields. Be sure to include the business table's spatial column to make the view spatial.

When you create a spatial ArcSDE view on a dataset stored in the ArcSDE compressed binary type or Open Geospatial Consortium well-known binary (OGCWKB) representation (available in geodatabases stored in Oracle or SQL Server), three views are created: one each for the business table, the feature table, and the spatial index table. Creating a nonspatial ArcSDE view only creates a single view.

For details on how to use the sdetable command, consult the ArcSDE Administration Command Reference.

Some users create registered views with sdetable –o create_view and alter the view definition using SQL. This is done to avoid complex sdetable –o create_view command line syntax (especially the –c option) or to include a more complex or extended view definition, such as one that uses connections to external databases. Although altering a view can solve some problems, it is also possible to create a view that does not work well with ArcSDE or ArcSDE clients, such as ArcGIS Desktop.

When you create a view using ArcSDE tools or by other means, you must take into consideration the appropriateness of the new view. You must determine, for example, whether the specified columns actually exist in the specified tables and if there are indexes on the columns to speed query execution. If you choose to replace views created by ArcSDE by altering the view afterward, you are taking responsibility for not only the query's correctness and efficiency but also its relevance and suitability for use with ArcSDE clients. For example, creating one-to-many views involving feature classes results in duplicate ObjectIDs. This leads to unexpected behavior in applications such as ArcMap and ArcCatalog, because the ObjectID is supposed to contain unique values. See About ObjectID fields for a definition of ObjectIDs.

Top of page

DBMS-specific view information

The following provides some information on view behavior specific to particular DBMSs used with ArcSDE:

Views stored in ArcSDE geodatabases in SQL Server

Top of page

Views stored in ArcSDE geodatabases in Oracle

Top of page

Views stored in ArcSDE geodatabases in DB2

Top of page

Views stored in ArcSDE geodatabases in Informix

Top of page

Please visit the Feedback page to comment or give suggestions on ArcGIS Desktop Help.
Copyright © Environmental Systems Research Institute, Inc.