Unlike ordinary tables in a relational database, a view is not part of the physical schema. It is a dynamic, virtual table computed from data in the database. Changing the data in a table alters the data shown in the view.
Views can provide advantages over tables:
- You can use a view to make a subset of data available to certain users.
- A view can join and simplify multiple tables into a single virtual table.
- Views can aggregate data (using a sum, average, or other function) to calculate and present data.
Mapping Existing Views to Feature Classes
AutoCAD Map 3D toolset automatically displays as classes existing database views that are defined in their native databases. If the view includes geometry, it is displayed as a feature class. The following restrictions apply:
- You cannot create or modify the view definition within AutoCAD Map 3D toolset.
- In the Data Table, you can edit data in views only if you have permissions to do so and only if the data store supports editing of view-based data.
- The ability to insert, update, and delete data in the view depends on how the view is defined in its native data store.
- For existing Oracle schemas, geometry that is included in a view needs a separate entry in the user_sdo_geom_metadata table. This allows AutoCAD Map 3D toolset to determine the correct spatial context to use when for that view when displaying it as a feature class.
If you plan to use a database view with AutoCAD Map 3D toolset, keep in mind the following points :
- Your native view must contain a primary key.
- Your native view must use a spatial index.
In the Schema Editor, the columns defined for the view appear as properties, but you cannot edit them. However, you can use the Schema Editor to create feature classes and properties that mimic database views.
For example, although you have an Oracle table, Rivers, with 20 properties, you may want certain people to see only six of those properties. You can use the Schema Editor to create a new feature class based on the existing Rivers table, and add the six properties you want to expose.
Schema Editor allows mapping directly into the physical database objects (tables or views).
Accessing Views from Native Schema
In addition to mapping feature classes to existing views in a FDO-enabled datastore, you can “reverse-engineer” views in native, existing, non-FDO-enabled datastores into feature classes.
To do this, the following must be true:
- The primary key or unique index columns must be exposed in the view.
If the view contains a join, columns that identify each row uniquely must also be exposed.
If a class has no primary key, you can still expose it in AutoCAD Map 3D toolset, but it will be read only.
- For Oracle data stores, if there is geometry in the view, there must be an entry in user_sdo_geom_metadata for that geometry. This will provide the spatial context and coordinate system information.
- For Oracle non-simple views, you can specify which columns to use for a primary key for the resulting feature class. Provide this information with the view, as shown in this example:
alter view <viewname> add constraint <constraintname> primary key (columnnames) disable novalidate;