|
|
Lesson 14-15
[php]
Lession14 Creating Views
* CREATE [OR REPLACE] [FORCE|NOFORCE] VIEW view [(alias[, alias]...)] AS subquery [WITH CHECK OPTION [CONSTRAINT constraint]] [WITH READ ONLY]
FORCE creates the view regardless of whether the base tables exist or not. 创建时若基表不存在，则提示：警告: 创建的视图带有编译错误，但可以创建，以后基表建立后，该视图可以直接使用；
NOFORCE creates the view only if the base tables exist. This is the default. 缺省
WITH CHECK OPTION specifies that only rows accessible to the view may be inserted or updated. 不能插入或修改不符合建立视图条件的表记录
constraint is the name assigned to the CHECK OPTION constraint.
WITH READ ONLY ensures that no DML operations can be performed on this view. 限制任何对view的DML操作
* CeateViewAsSelect 注意7和8i的一个区别是：8i以前的版本不允许使用order by (而目前OCP考试是以7为准的)
* 对视图进行DML操作的限制
不能删除记录：存在组函数、GROUP BY子句、DISTINCT
不能修改记录：列上使用了函数、有ROWNUM类的伪列
不能插入记录：表中的NOT NULL列没有在视图中选取
* You can query USER_UPDATABLE_COLUMNS to see whether the columns in a join view are updatable.
* Any UPDATE, INSERT, or DELETE statement on a join view can modify only one underlying base table. If at least one column in the subquery join has a unique index, then it may be possible to modify one base table in a join view.
* Only the creator or a user with the DROP ANY VIEW privilege can remove a view.
-----------------------------------------------
Lession15 Creating Indexes
* Reference the column that has an index in the predicate WHERE clause without modifying the indexed column with a function or expression.
* The fastest way to access any particular row is by referencing its ROWID.
* Rule-based optimization is when the Oracle7 Server decides when it is appropriate to use an index based on its internal rules. The Server identifies the columns that are indexed and the index types.
* The cost-based optimization method uses statistics about tables along with information about available indexes to select an execution plan or the SQL statements.
* An index does not have to be in the same tablespace as the associated table. Furthermore, performance of queries that use an index can be improved by storing an index and its table in different tablespaces located on different disk drives because both the index and the table data can be retrieved in parallel.
* Primary keys can be a combination of up to 16 column values.
* The order of the columns in a concatenated index is important. The Server reads the columns left to right when choosing the efficiency for he index look up.
* To create an index in your schema, you must have CREATE INDEX privileges.
* When to Create a Index
The column is used frequently in the WHERE clause or in a join condition.
The column contains a wide range of values.
The column contains a large number of null values.
Two or more columns are frequently used together in a WHERE clause or join condition.
The table is large and most queries are expected to retrieve less than 2–4% of the rows.
* When to Not Create a Index
The table is small.
The columns are not often used as a condition in the query.
Most queries are expected to retrieve more than 2–4% of the rows.
The table is updated frequently.
* To optimize joins, you can create an index on the FOREIGN KEY column, which will speed up the search to match rows to the PRIMARY KEY column.
* The optimizer does not use an index if the WHERE clause contains the IS NULL expression.
* In order to drop an index, you must be the owner of the index or have the DROP ANY INDEX privilege.
* If drop a table, indexes and constraints would be automatically dropped, but views and sequences remain.
* USER_INDEXES / USER_IND_COLUMNS
? B+ tree的原理? The Oracle7 Server uses a balanced B*tree index structure. This is a binary,
self-balancing search structure to equalize access times to any row. It is an efficient
method of ensuring that access to any specified value will take approximately the
same time whether the row is at the beginning, middle, or end of the table.
Each index that the Oracle7 Server builds consists of a number of pages (or branches)
of storage arranged in a tree. Each page (or branch) holds a series of key values and
pointers to pages (or branches) lower in the structure until eventually the key values
indicate the location of the data itself. The location identifier at the database level is
called a ROWID.
[/php] |
|
楼主: easyfree
IP卡
狗仔卡
楼主
显身卡
发表于 2001-11-22 15:30