2012-10-11 11gR2 concepts page 97-128

Updatable Join Views -- 可更改的连接视图
An updatable join view, also called a modifiable join view, involves two or more
base tables or views and permits DML operations. An updatable view contains
multiple tables in the top-level FROM clause of the SELECT statement and is not
restricted by the WITH READ ONLY clause.
To be inherently updatable, a view must meet several criteria. For example, a general
rule is that an INSERT, UPDATE, or DELETE operation on a join view can modify only
one base table at a time. The following query of the USER_UPDATABLE_COLUMNS data
dictionary view shows that the view created in Example 4–7 is updatable:
SQL> SELECT TABLE_NAME, COLUMN_NAME, UPDATABLE
2 FROM USER_UPDATABLE_COLUMNS
3 WHERE TABLE_NAME = 'STAFF_DEPT_10_30';
TABLE_NAME COLUMN_NAME UPD
------------------------------ ------------------------------ ---
STAFF_DEPT_10_30 EMPLOYEE_ID YES
STAFF_DEPT_10_30 LAST_NAME YES
STAFF_DEPT_10_30 JOB_ID YES
STAFF_DEPT_10_30 DEPARTMENT_ID YES

All updatable columns of a join view must map to columns of a key-preserved table. A
key-preserved table in a join query is a table in which each row of the underlying
table appears at most one time in the output of the query. In Example 4–7,
department_id is the primary key of the departments table, so each row from the
employees table appears at most once in the result set, making the employees table
key-preserved. The departments table is not key-preserved because each of its rows
may appear many times in the result set.

Object Views -- 对象视图
Just as a view is a virtual table, an object view is a virtual object table. Each row in the
view is an object, which is an instance of an object type. An object type is a
user-defined data type.
You can retrieve, update, insert, and delete relational data as if it was stored as an
object type. You can also define views with columns that are object data types, such as
objects, REFs, and collections (nested tables and VARRAYs).
Like relational views, object views can present only the data that you want users to
see. For example, an object view could present data about IT programmers but omit
sensitive data about salaries. The following example creates an employee_type
object and then the view it_prog_view based on this object:
CREATE TYPE employee_type AS OBJECT
(
employee_id NUMBER (6),
last_name VARCHAR2 (25),
job_id VARCHAR2 (10)
);
/
CREATE VIEW it_prog_view OF employee_type
WITH OBJECT IDENTIFIER (employee_id) AS
SELECT e.employee_id, e.last_name, e.job_id
FROM employees e
WHERE job_id = 'IT_PROG';
Object views are useful in prototyping or transitioning to object-oriented applications
because the data in the view can be taken from relational tables and accessed as if the
table were defined as an object table. You can run object-oriented applications without
converting existing tables to a different physical structure.

创建一种类型，再用这种类型去创建视图。

Overview of Materialized Views -- 物化视图
Materialized views are query results that have been stored or "materialized" in
advance as schema objects. The FROM clause of the query can name tables, views, and
materialized views. Collectively these objects are called master tables (a replication
term) or detail tables (a data warehousing term).

Materialized views are used to summarize, compute, replicate, and distribute data.
They are suitable in various computing environments, such as the following:
-- In data warehouses, you can use materialized views to compute and store data
generated from aggregate functions such as sums and averages.
-- In materialized view replication, the view contains a complete or partial copy of a
table from a single point in time. Materialized views replicate data at distributed
sites and synchronize updates performed at several sites. This form of replication
is suitable for environments such as field sales when databases are not always
connected to the network.
-- In mobile computing environments, you can use materialized views to download
a data subset from central servers to mobile clients, with periodic refreshes from
the central servers and propagation of updates by clients to the central servers.

In a replication environment, a materialized view shares data with a table in a different
database, called a master database. The table associated with the materialized view at
the master site is the master table. Figure 4–7 illustrates a materialized view in one
database based on a master table in another database. Updates to the master table
replicate to the materialized view database.

 

Refresh Methods for Materialized Views
The database maintains data in materialized views by refreshing them after changes to
their master tables. The refresh method can be incremental, known as fast refresh, or a
complete refresh.

For materialized views that use the fast refresh method, a materialized view log or
direct loader log keeps a record of changes to the master tables. -- 使用快速刷新方法需要有物化视图日志。

 

Query Rewrite  --  查询重写
Query rewrite is an optimization technique that transforms a user request written in
terms of master tables into a semantically equivalent request that includes
materialized views. When base tables contain large amounts of data, computing an
aggregate or join is expensive and time-consuming. Because materialized views
contain precomputed aggregates and joins, query rewrite can quickly answer queries
using materialized views.

 

Unique Constraints  -- 唯一约束
Unless a NOT NULL constraint is also defined, a null always satisfies a unique key
constraint. Thus, columns with both unique key constraints and NOT NULL constraints
are typical. This combination forces the user to enter values in the unique key and
eliminates the possibility that new row data conflicts with existing row data.

这里要注意，NULL值也被认为是唯一的。所以一般设置唯一约束都会一起把NOT NULL(非空)约束一起设置了。

 

Foreign Key Constraints

注意这里，最下面那行，空值也能被插入。

 

Parent Key Modifications and Foreign Keys
When a parent key is modified, referential integrity constraints can specify the
following actions to be performed on dependent rows in a child table:
 -- No action on deletion or update
In the normal case, users cannot modify referenced key values if the results would
violate referential integrity. For example, if employees.department_id is a
foreign key to departments, and if employees belong to a particular department,
then an attempt to delete the row for this department violates the constraint.
 -- Cascading deletions 级联删除
A deletion cascades (DELETE CASCADE) when rows containing referenced key
values are deleted, causing all rows in child tables with dependent foreign key
values to also be deleted. For example, the deletion of a row in departments
causes rows for all employees in this department to be deleted.
 -- Deletions that set null 删除并置外键为空
A deletion sets null (DELETE SET NULL) when rows containing referenced key
values are deleted, causing all rows in child tables with dependent foreign key
values to set those values to null. For example, the deletion of a department row
sets the department_id column value to null for employees in this department.

 

Indexes and Foreign Keys
As a rule, foreign keys should be indexed. The only exception is when the matching
unique or primary key is never updated or deleted. Indexing the foreign keys in child
tables provides the following benefits:
 -- Prevents a full table lock on the child table. Instead, the database acquires a row
lock on the index.
 -- Removes the need for a full table scan of the child table. As an illustration,
assume that a user removes the record for department 10 from the departments
table. If employees.department_id is not indexed, then the database must
scan employees to see if any employees exist in department 10.

 

Checks for Modified and Existing Data

 关于这里，可以看我这里相应的实验： http://blog.csdn.net/empoli/article/details/8045241

 

Deferrable Constraints

Deferrable Constraints
A deferrable constraint permits a transaction to use the SET CONSTRAINT clause to
defer checking of this constraint until a COMMIT statement is issued. If you make
changes to the database that might violate the constraint, then this setting effectively
lets you disable the constraint until all the changes are complete.
You can set the default behavior for when the database checks the deferrable
constraint. You can specify either of the following attributes:
 -- INITIALLY IMMEDIATE
The database checks the constraint immediately after each statement executes. If
the constraint is violated, then the database rolls back the statement.
 -- INITIALLY DEFERRED
The database checks the constraint when a COMMIT is issued. If the constraint is
violated, then the database rolls back the transaction.

这个实验也从这里找： http://blog.csdn.net/empoli/article/details/8045241

 

Contents of the Data Dictionary

Views with the Prefix ALL_
Views with the prefix ALL_ refer to the user's overall perspective of the database.
These views return information about schema objects to which the user has access
through public or explicit grants of privileges and roles, in addition to schema objects
that the user owns.

ALL_ 实际上就是能看到你有权限操作的对象。

For example, the following query returns information about all the objects to which
you have access:
SELECT OWNER, OBJECT_NAME, OBJECT_TYPE
FROM ALL_OBJECTS
ORDER BY OWNER, OBJECT_NAME;
Because the ALL_ views obey the current set of enabled roles, query results depend on
which roles are enabled, as shown in the following example:
SQL> SET ROLE ALL;
Role set.
SQL> SELECT COUNT(*) FROM ALL_OBJECTS;
COUNT(*)
----------
68295
SQL> SET ROLE NONE;
Role set.
SQL> SELECT COUNT(*) FROM ALL_OBJECTS;
COUNT(*)
----------
53771
Application developers should be cognizant of the effect of roles when using ALL_
views in a stored procedure, where roles are not enabled by default.

开发人员注意，这里角色没有显示的赋予的话，在存储过程的调用中会有差异。

Contents of the Dynamic Performance Views
When you use the Database Configuration Assistant (DBCA) to create a database,
Oracle automatically creates the data dictionary. Oracle Database automatically runs
the catalog.sql script, which contains definitions of the views and public
synonyms for the dynamic performance views. You must run catalog.sql to create
these views and synonyms.

手工建库的话，用Oracle自带的CATALOG.SQL可以创建视图的定义和动态性能视图的公共同义词。

 

Database Object Metadata -- 数据库对象的元数据
The DBMS_METADATA package provides interfaces for extracting complete definitions
of database objects. The definitions can be expressed either as XML or as SQL DDL.
Two styles of interface are provided: a flexible, sophisticated interface for
programmatic control, and a simplified interface for ad hoc querying.