Views in Oracle
Views in Oracle
|
| Release 9.3 |
   |
Generally speaking, a view is a predefined, named query stored in the database. Once created, views can be queried in much the same way that tables can be queried. Views contain rows and columns like tables do. However, views are not tables but representations of data stored in, or calculated from, one or more other tables (business tables) or views. The columns of the view are the items from the select list of the query that defines the view.
You can use views in a number of ways to improve access to your data. Views can provide subsets of rows or columns in a predefined way, codifying common queries in the database, making them available to multiple users and reducing the need for users to construct their own complex queries. Views can join data from multiple base tables or other views and can include aggregation functions to summarize data. Views that return only necessary columns or aggregated data can reduce the volume of data transferred from the database to the client, which can improve performance.
You can grant users access rights (such as SELECT and INSERT) to views in the same way as to tables. Therefore, you can use views to control access to rows or columns in business tables.
This topic covers how views are stored in the Oracle database and how to create a view and register it with ArcSDE.
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, 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 is 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.
sdetable –o create_view –T view_dept_100 –t employees
–c * –w "department = 100" –u gdb –p gdb
ArcSDE 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 has also added the owner name to the table name in the query. If you create your own view with SQL, this is not done automatically.
Notice that the names of the view and its owner are 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 have been selected. (Views exist as schema objects, independent of the data that populates 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 in the "Working with geodatabases using SQL" section of the help.
NOTE: For you to create a view on a table that is owned by another user, the table owner must grant you SELECT privileges 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 grant 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 is 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 and manually registering the view with ArcSDE.
First, create the view 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 is not 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 (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 has 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.
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 does 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):
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.
