Using database views
Release 9.2 Last modified December 3, 2010	Print all topics in : "Tuning an ArcSDE geodatabase"

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:

• General view information
  
• Spatial views
  
• SQL Server views
  
• Oracle views
  
• DB2 views
  
• Informix views
  

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.

Tips

• For information on multiversioned views, see Using multiversioned views.
  
• The Administration Command Reference (Admin_Cmd_Ref) is installed with ArcSDE on Windows systems. It can be found in the Documentation directory under the ArcSDE directory. You can also download a PDF version of the file from the ESRI support site. (Login required)
  

http://support.esri.com/index.cfm?fa=knowledgebase.documentation.listDocs&PID=19

• You can find technical articles specific to views on the ESRI support site.
  

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

• Cross-database views
  
In the multidatabase model, it is possible to create views between tables and ArcSDE feature classes that do not reside in the same database. When you do this, you must correctly qualify the tables involved in your queries and use the –D (database) switch to identify which database contains the data on which to create a view.

In the single spatial database model, you cannot have cross-database views between two feature classes in different databases. However, you can create a cross-database view between a feature class in one database and a table in another.

To create a cross-database view involving a feature class and a table, create a simple ArcSDE view of the feature class, then alter it in the SQL Query Analyzer to query the tables in the other database.

For example, you can create a simple ArcSDE view with the sdetable command



sdetable –o create_view –T taxpars –t taxlots –c taxlots.shape



Then alter this view in Query Analyzer to add an American National Standards Institute (ANSI) join to another table as follows:



ALTER  VIEW taxpars 
AS 
SELECT  tx.shape, p.owner1, p.owneradd, p.ownercity,
 p.ownerstat, p.bldgval, p.landval, p.totalval,
 p.bldgsqft
FROM  taxlots tx
inner join parcelinfodb.dbo.taxlotinfo p on tx.shape = p.shape




• Creating indexed views (SQL Server 2000 and 2005)
  
SQL Server 2000 and 2005 Enterprise Edition allow you to create unique clustered indexes on views and partition views across multiple servers.

Indexed views are stored in the database in the same manner as a table, while nonindexed views store only the SQL statements comprising them. Indexed views are advantageous when your view definition employs a complex join. They are a good choice to use on static data, for they are schema bound to their source. Also, they are stored in the same way as a regular table with a clustered index. Before you try creating indexed views, make sure you are familiar with this topic in the SQL Server Books Online documentation.

Creating an indexed view in SQL Server 2000 involves extra work and has many requirements. These are summarized as follows:


• The view must be created with Schemabinding.
  
• The view must be created with QUOTED_IDENTIFIER on.
  
• The view cannot reference other views.
  
• Tables referenced in views must be two part.
  
• You cannot reference all columns of a table with "*".
  


There are additional rules and conditions. For more information, refer to the topic "Creating an Indexed View" in the SQL Server Books Online documentation.

Since ArcSDE does not create schema-bound views by default, follow this procedure:


1. Create an ArcSDE view with sdetable –o create_view.
   
2. Open SQL Server Query Analyzer and drop the business table view with DROP VIEW <view name>. Before you do this, be sure to copy the view definition.
   
3. Re-create the business table view with the create view statement but be sure to use the WITH SCHEMABINDING option and qualify your view name and table name with the owner.<table or view name> convention.
   
4. Create a unique clustered index on the view with the CREATE UNIQUE CLUSTERED INDEX statement.
   
This is an example that creates an indexed view. This example was done in SQL Server 2000 using a SQL Server authenticated user.

• A view was created with sdetable –o create_view:
  



sdetable –o create_view –T vwWithIndx –t cagis_cent 
–c parcelid,object__id,x_coord,y_coord,book,page,parcel,shp 
–w "parcel>0393" –D parcels




• In Query Analyzer, the view statement was copied, then the business table of the view was dropped:
  

drop view vwWithIndx




• The view was re-created, making sure to qualify the view and table names with the owner name and using the WITH SCHEMABINDING statement:
  



CREATE VIEW vtest.vwWithIndx 
WITH SCHEMABINDING AS SELECT  parcelid, object__id, x_coord, y_coord, book, page, parcel, shp FROM  vtest.cagis_cent WHERE parcel>0393




• A unique clustered index was created on this view:
  

create unique clustered index shpIdxView on vtest.vwWithIndx (shp)

Top of page

Views stored in ArcSDE geodatabases in Oracle

• Querying the Oracle data dictionary for view information
  
When you create views in an Oracle database using SQL, the views are defined in the Oracle data dictionary. The same is true when you create views using sdetable –o create_view. That means you can query the Oracle data dictionary to see how your views are stored.

Oracle maintains a user-accessible view called ALL_VIEWS that describes all views to which the connected user has access rights.



SQL> describe all_views





Name	Null?	Type
OWNER	NOT NULL	VARCHAR2(30)
VIEW_NAME	NOT NULL	VARCHAR2(30)
TEXT_LENGTH		NUMBER
TEXT		LONG
TYPE_TEXT_LENGTH		NUMBER
TYPE_TEXT		VARCHAR2(4000)
OID_TEXT_LENGTH		NUMBER
OID_TEXT		VARCHAR2(4000)
VIEW_TYPE_OWNER		VARCHAR2(30)
VIEW_TYPE		VARCHAR2(30)
SUPERVIEW_NAME		VARCHAR2(30)


Views have owners, names, and text. The TEXT column of ALL_VIEWS contains the text of the query that defines the view. The following examines how a view created by sdetable looks in the Oracle data dictionary.

Suppose you want the manager of Department 100 to see all the columns in the table EMPLOYEES but only the rows for employees in Department 100. This is how the EMPLOYEES table was created:



SQL> create table employees (
  2    objectid    number(38) unique not null,
  3    name        varchar2(32),
  4    department  number not null,
  5    hire_date   date not null);

Table created.



You can use sdetable to create a view with all rows for which the department number is 100. The –w option specifies the WHERE clause of the query stored with the view definition.



Ready: sdetable –o create_view –T view_dept_100 –t employees 
–c * –w "department = 100" –u gdb –p gdb


ArcSDE 9.2 Oracle10g  Build . . .
Attribute        Administration Utility

Successfully created view view_dept_100.



You could subsequently query ALL_VIEWS to see a description of view_dept_100.



SQL> set lines 60
SQL> set pages 10000
SQL> set long 2000
SQL> column text format a60 wrapped;
SQL> select view_name, text_length, text from all_views
  2  where owner = 'GDB' and view_name = 'VIEW_DEPT_100';





VIEW_NAME	TEXT_LENGTH	TEXT
VIEW_DEPT_100	112	SELECT /*+ FIRST_ROWS */ "OBJECTID","NAME","DEPARTMENT","HIRE_DATE", FROM GDB.EMPLOYEES WHERE department = 100


ArcSDE adds the Oracle hint first rows, asking the Oracle optimizer to work toward returning the first rows of the result set as quickly as possible. In this example, ArcSDE also added the owner name to the table name in the query. If you create your own view with SQL, this will not be done for you automatically.

Notice that the names of the view and its owner were converted to uppercase before being stored in ALL_VIEWS. This is done by Oracle, not ArcSDE.

Using this type of view, users can be granted no access to the base table (EMPLOYEES) and SELECT access to the view, VIEW_DEPT_100, that contains only the rows of EMPLOYEES where DEPARTMENT is 100.



SQL> grant select on view_dept_100 to mgr100;

Grant succeeded.

SQL> conn mgr100/mgr100
Connected.
SQL> select * from gdb.view_dept_100;

no rows selected

SQL> select * from gdb.employees;
select * from gdb.employees
                  *
ERROR at line 1:
ORA-00942: table or view does not exist



The first query found the view, but because there are no rows in EMPLOYEES, no rows were selected. (Views exist as schema objects, independent of the data that will populate them.) In the second query, the user MGR100 has no access to the EMPLOYEES table, so it appears to MGR100 that the EMPLOYEES table does not exist.

You would also use views on tables that contain more than one SDO_Geometry or ST_Geometry column so you can register them with ArcSDE. ArcSDE does not support layers with multiple geometry columns, so to register them, you must first create a view that contains only one geometry column. To learn how to do this for Oracle Spatial tables, refer to the Knowledge Base article "Create an Oracle view of an Oracle Spatial layer (containing multiple geometry columns) and register it with ArcSDE". For instructions on doing this with tables containing multiple ST_Geometry columns, see Using spatial views on tables with an ST_Geometry column.



NOTE: For you to create a view on a table that is owned by another user, the table owner would have to grant you SELECT privilege with the GRANT option on the table (or tables) you want to use in the view. In the example above, that means you would either have to own the EMPLOYEES table or the owner of the EMPLOYEES table would have had to have granted you SELECT privileges on the EMPLOYEES table before you could create the view_dept_100 view.





NOTE: If you create a spatial view using the sdetable -o create_view command, the owner of the table must grant you SELECT privileges on the table with the GRANT option. So in the example above, the syntax would be as follows:

SQL> grant select on view_dept_100 to mgr100 with grant option;






• Creating a view with SQL and registering with ArcSDE
  
You can use sdetable to create and register a view, but the same results can be achieved by first using SQL to create the view, then manually registering the view with ArcSDE.

First, create the view, this time for Department 200. Be sure to include the owner name in the table definition so it can be found if this view is accessed by other users.



SQL> create view view_dept_200 as
  2  select * FROM GDB.EMPLOYEES
  3  where department = 200;

View created.

SQL> select view_name, text_length, text from all_views
2	where owner = 'GDB' and view_name = 'VIEW_DEPT_200';





VIEW_NAME	TEXT_LENGTH	TEXT
VIEW_DEPT_200	110	SELECT "OBJECTID","NAME","DEPARTMENT","HIRE_DATE" FROM GDB.EMPLOYEES WHERE department = 200


Notice that the Oracle CREATE VIEW command expands the column selection wildcard character (*) to a list of all the columns in the same order that they appear in the base table. This provides more robust, deterministic output for the view. For example, if a column is added to the base table, the view's schema would not be affected.



SQL> alter table EMPLOYEES add (address varchar2(30));

Table altered.

SQL> desc EMPLOYEES;





Name	Null?	Type
OBJECTID	NOT NULL	NUMBER(38)
NAME		VARCHAR2(32)
DEPARTMENT	NOT NULL	NUMBER
HIRE_DATE	NOT NULL	DATE
ADDRESS		VARCHAR2(30)




SQL> select view_name, text_length, text from all_views
  2  where owner = 'GDB' and view_name = 'VIEW_DEPT_200';





VIEW_NAME	TEXT_LENGTH	TEXT
VIEW_DEPT_200	110	SELECT "OBJECTID","NAME","DEPARTMENT","HIRE_DATE" FROM GDB.EMPLOYEES WHERE department = 200


The view is unchanged by this alteration of its base table.

The base table of a view need not be registered with ArcSDE unless it provides a geometry column (vector), raster, or XML column managed by ArcSDE. In those cases, the base table is already registered with ArcSDE but the view itself must be registered separately.

Registered row ID columns are unique, nonnull integer columns used by ArcSDE to uniquely identify rows in a table or view. Although you can create a view without registering a row ID column, such a view will have limited functionality. A registered row ID column is required for queries and other operations that use ArcSDE log files. ArcGIS also uses the registered row ID column for selections, queries, and other operations. If you use a table or a view with ArcGIS that has no registered row ID column, ArcGIS may create a temporary row ID column for use with the table or view. This operation can slow down the process of connecting to a table or view.

You should register row ID columns for your views as user-maintained row ID columns. Generally, ArcSDE does not support editing through views. Therefore, there is no need for ArcSDE to create an Oracle sequence to generate new row IDs for the view, which is what ArcSDE will do for row ID columns maintained by ArcSDE.


• Creating a view with a join
  
You can create views with joins by including a comma-delimited list of table names as a quoted string after the -t option. The following view returns the employee ID, employee name, and employee salary by joining two tables, EMPLOYEES (owned by the connected user) and SALARY (from the schema PAYROLL):



Ready: sdetable –o create_view –T view_dept_100_salaries –t "employees,payroll.salaries" 
–c 'employees.objectid,employees.name,payroll.salaries.salary' 
–w "department = 100 and employees.objectid = payroll.salaries.employeeid" –u gdb –p gdb



If the tables are not owned by the connected user, the name must be in owner.table format. When using multiple tables, you must qualify the names of each column listed after –c with the name of the table using the format table.column or owner.table.column.

To use tables that are in a geodatabase in a user's schema, specify the user-owned geodatabase with the –D option.

Top of page

Views stored in ArcSDE geodatabases in DB2

• Querying SYSCAT.VIEWS for view information
  
The command sdetable –o create_view creates a view that is defined in the system catalog view of your DB2 database. You can query SYSCAT.VIEWS to see how your views are stored. SYSCAT.VIEWS contains one or more rows for each view created.

SYSCAT.VIEWS catalog view

Field Name	Data Type	Description
VIEWSCHEMA	VARCHAR(128)	Along with VIEWNAME, the qualified name of a view or table that is used to define a materialized query table or staging table
VIEWNAME	VARCHAR(128)	Along with VIEWSCHEMA, the qualified name of a view or table that is used to define a materialized query table or staging table
DEFINER	VARCHAR(128)	Authorization ID of the creator of the view
SEQNO	SMALLINT	Always 1
VIEWCHECK	CHAR(1)	The type of view checking: N = No check option L = Local check option C = Cascaded check option
READONLY	CHAR(1)	Y = Read-only view because of its definition or N = Not read-only view
VALID	CHAR(1)	Y = Valid view or materialized query table definition valid or X = Inoperative view or materialized query table definition; must be re-created
QUALIFIER	VARCHAR(128)	Contains value of the default schema at the time of object definition
FUNC_PATH	VARCHAR(254)	The SQL path of the view creator at the time the view was defined When the view is used in data manipulation statements, this path must be used to resolve function calls in the view. (Use SYSIBM for views created before version 2.)
TEXT	CLOB(64k)	Text of the CREATE VIEW statement


The following is an example of how a view created by sdetable looks in DB2:

Suppose you want the manager of Department 101 to see all the columns in the table EMPLOYEES but only the rows for employees in Department 101. This is how the EMPLOYEES table would be created:



db2 => create table employees(objectid integer, name
varchar(32),department smallint not null, hire_date date not null)

DB20000I  The SQL command completed successfully.



Use sdetable to create a view with all rows in which the department number is 101. The –w option specifies the WHERE clause of the query stored with the view definition.



sdetable –o create_view –T view_dept_101 –t employees 
–c objectid,name,department,hire_date –w "where department = 101" 
–u gdb –p gdb –i 5910

ArcSDE 9.2 DB2 Build. . .
Attribute        Administration Utility
__________________________________
Successfully created view view_dept_101.



The sdetable command defines the view in the DB2 system catalog view, just as if you created the view yourself using the DB2 CREATE VIEW command.



db2 => select viewschema, viewname, text from syscat.views where viewname = 'VIEW_DEPT_101';





VIEWSCHEMA	VIEWNAME	TEXT
WORLD	VIEW_DEPT_101	CREATE VIEW view_dept_101 AS SELECT = objectid, name, department, hire_date FROM WORLD.EMPLOYEES WHERE department = 101


ArcSDE adds the owner name to the table name in the query. If you create your own view with SQL, this will not be done for you automatically.

Notice that the names of the view and its owner were converted to uppercase before being stored in SYSCAT.VIEWS. This is done by DB2, not ArcSDE.

Using this type of view, users can be granted no access to the base table (EMPLOYEES) and SELECT access to the view, VIEW_DEPT_101, that contains only the rows of EMPLOYEES where DEPARTMENT is 101:



db2 => connect to testdb user world using world
   Database Connection Information
 Database server        = DB2/NT 8.2.2
 SQL authorization ID   = WORLD
 Local database alias   = TESTDB

db2 => grant select on view_dept_101 to mgr100
DB20000I  The SQL command completed successfully.

db2 => connect to testdb user mgr100 using mgr100
   Database Connection Information
 Database server        = DB2/NT 8.2.2
 SQL authorization ID   = MGR100
 Local database alias   = TESTDB

db2 => select * from world.view_dept_101
  no record(s) selected.

db2 => select * from world.employees
SQL0551N  "MGR100" does not have the privilege to perform operation "SELECT" on object "WORLD.EMPLOYEES".  SQLSTATE=42501



The first query found the view, but because there are no rows in EMPLOYEES, no rows were selected. Views exist as schema objects, independent of the data that will populate them. In the second query, the user MGR100 has no access to the table EMPLOYEES, so it appears to MGR100 that the table EMPLOYEES does not exist.


• Creating a view with SQL and registering with ArcSDE
  
You can use the sdetable –o create_view command to create and register a view, but using SQL to create the view then manually registering the view with ArcSDE can achieve the same results.

Below, a view is created for Department 201. The owner name is included in the table definition so it can be found if other users access this view.



db2 => create view view_dept_201 as select objectid, name, department, hire_date FROM GDB.EMPLOYEES WHERE department = 201
DB20000I  The SQL command completed successfully.

db2 => select viewschema, viewname, text from syscat.views where viewname = 'VIEW_DEPT_201'





VIEWSCHEMA	VIEWNAME	TEXT
WORLD	VIEW_DEPT_201	CREATE VIEW view_dept_201 AS SELECT objectid, name, department, hire_date FROM GDB.EMPLOYEES WHERE department = 201


Now, if a column is added to the base table, the view's schema would not be affected, as shown below:



db2 => alter table EMPLOYEES add column address varchar(30)
DB20000I  The SQL command completed successfully.

db2 => describe select * from employees

SQLDA Information

 sqldaid : SQLDA     sqldabc: 896  sqln: 20  sqld: 5

 Column Information





sqltype	sqllen	sqlname.data	sqlname.length
497 INTEGER	4	OBJECTID	9
449 VARCHAR	32	NAME	4
500 SMALLINT	2	DEPARTMENT	11
384 DATE	10	HIRE_DATE	9
449 VARCHAR	30	ADDRESS	7




db2 => select viewschema, viewname, text from syscat.views where viewname = 'VIEW_DEPT_201'





VIEWSCHEMA	VIEWNAME	TEXT
WORLD	VIEW_DEPT_2001	CREATE VIEW view_dept_2001 AS SELECT objectid, name, department, hire_date FROM GDB.EMPLOYEES WHERE department = 201


The view is unchanged by this alteration of its base table.

The base table of a view need not be registered with ArcSDE unless it provides a geometry column (vector), raster, or XML column managed by ArcSDE. In those cases, the base table is already registered with ArcSDE, but the view itself must be registered separately.

Registered row ID columns are unique, nonnull integer columns used by ArcSDE to uniquely identify rows in a table or view. Although you can create a view without registering a row ID column, such a view will have limited functionality. A registered row ID column is required for queries and other operations that use ArcSDE log files. ArcGIS also uses the registered row ID column for selections, queries, and other operations. If you use a table or view with ArcGIS that has no registered row ID column, ArcGIS may create a temporary row ID column for use with the table or view. This operation can slow down the process of connecting to a table or view, especially if it is large.

You should register row ID columns for your views as user-maintained row ID columns. Generally, ArcSDE does not support editing through views. Therefore, there is no need for ArcSDE to create a DB2 sequence to generate new row IDs for the view, which is what ArcSDE will do for row ID columns maintained by ArcSDE.

Top of page

Views stored in ArcSDE geodatabases in Informix

• Creating a view with a join
  
You can create views with joins by including a comma-delimited list of table names as a quoted string after the –t option. See the "Views stored in ArcSDE geodatabases in Oracle" section above for details.

Top of page

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