ORACLE SQL总结三：DDL语句-CSDN博客

5、DDL语句
5.1 Enable/Disable/Validate/Novalidate 组合特性说明：

是否要求满足约束 Validate Novalidate
已有记录新增/修改记录已有记录新增/修改记录
Enable Yes Yes No Yes
Disable Yes No No No

Validate确保已有数据符合约束；
Novalidate不必考虑已有数据是否符合约束。

除非Novalidate被指定，Enable默认Validate；
除非Validate被指定，Disable默认Novalidate；
Validate和Novalidate对Enable和Disable没有任何默认暗示。

Enable Validate与Enable相同，检查已有记录和新增记录，确保都符合约束；
Enable Novalidate 允许已有记录不必满足约束条件，但新增/修改的记录必须满足；
Disable Validate禁用约束，删除约束上的索引，不允许修改任何被约束的记录；
Disable Novalidate与Disable相同，禁用约束，删除约束上的索引，且允许修改被约束的记录。

详细参考sql references <constraint>

5.2 关于partition表的疑问
Partitioning allows a table, index, or index-organized table to be subdivided into smaller pieces, where each piece of such a database object is called a partition. Each partition has its own name, and may optionally have its own storage characteristics.
partitioning允许表、索引、或索引组织表切分成更小的片，每片称为partition,每个partition有自己的name,并可以有自己的存储特性。

oracle手册上也说partition的优点是可以并行访问分区（因为每个分区表可以放在不同的硬盘上），这样可以提升性能。

那么我的问题来了，如果我只有一块硬盘，那么所有的partition 表就只能放在这一块硬盘上，是不是即使做了partition表也不能提升性能呢？

5.3 索引和索引-组织表

索引的类型：
(1)B-TREE索引，又分为:
            索引-组织表
            反序键索引
            倒序索引
            B-TREE集群索引
(2)Bitmap 索引
(3)Function-based 索引
(4)应用域索引

《oracle concept》关于索引描述得很详细，翻译部分内容
**************
An index is an optional structure, associated with a table or table cluster, that can sometimes speed data access. By creating an index on one or more columns of a table, you gain the ability in some cases to retrieve a small set of randomly distributed rows from the table. Indexes are one of many means of reducing disk I/O.

一个索引是一个可选的于表和表簇有关系的数据结构，它有时可以加快数据访问速度。通过在一个或多个列上产生一个索引，你可以提升访问以随机方式存储的数据的能力，索引是多种减少硬盘I/O操作的方式之一。

If a heap-organized table has no indexes, then the database must perform a full table scan to find a value. For example, without an index, a query of location 2700 in the hr.departments table requires the database to search every row in every table block for this value. This approach does not scale well as data volumes increase.

如果一个heap-organized table(oracle create table 时默认这种方式)，数据库必须执行全表扫描来查找一个数据。例如，如果没有索引，在hr.departments表中查询location=2700，就必须搜索表中每个块(block),这种方式随着数据的增加性能更加糟糕。

For an analogy, suppose an HR manager has a shelf of cardboard boxes. Folders containing employee information are inserted randomly in the boxes. The folder for employee Whalen (ID 200) is 10 folders up from the bottom of box 1, whereas the folder for King (ID 100) is at the bottom of box 3. To locate a folder, the manager looks at every folder in box 1 from bottom to top, and then moves from box to box until the folder is found. To speed access, the manager could create an index that sequentially lists every employee ID with its folder location:

ID 100: Box 3, position 1 (bottom)
ID 101: Box 7, position 8
ID 200: Box 1, position 10
.

假设一种情形，一个HR经理有一个装满卡片箱子的书架，收藏着雇员信息的文件夹被随机地放在盒子里，每个盒子里有10个文件夹，雇员Whalen(ID 200)的文件夹被放在第1个盒子的顶部，雇员King(ID 100)的文件夹被放在第1个盒子的底部，HR经理必须从顶到底地查询每个盒子里的文件夹，并且逐个逐个地查询盒子，直到找到所需的文件夹。为了加快查询速度，经理可以做一张表，表上顺序第列出每个雇员ID和他的文件夹所放的位置。

Bitmap Indexes

位图索引

In a bitmap index, the database stores a bitmap for each index key. In a conventional B-tree index, one index entry points to a single row. In a bitmap index, each index key stores pointers to multiple rows.

在位图索引中，数据库为每个index key都存储了一个位图(bitmap)。在一般的B-tree索引中，一个索引实体(entry)指向一条记录(a single row),但是在位图索引中，每个index key指向多条记录(multiple rows)

Bitmap indexes are primarily designed for data warehousing or environments in which queries reference many columns in an ad hoc fashion. Situations that may call for a bitmap index include:

The indexed columns have low cardinality, that is, the number of distinct values is small compared to the number of table rows.

The indexed table is either read-only or not subject to significant modification by DML statements.

位图索引主要设计用于数据仓库或类似的有随机方式分布的大量的列的数据环境，可以使用位图索引的情形包括：
-索引列有较低的相似度，这就意味着，在表的记录中相同值的数据的数量比较少。
-被索引的表或者只读或者不是DML语句重点照顾的对象

For a data warehouse example, the sh.customer table has a cust_gender column with only two possible values: M and F. Suppose that queries for the number of customers of a particular gender are common. In this case, the customer.cust_gender column would be a candidate for a bitmap index.

举一个数据仓库的例子，sh.customer表有一个cust_gender列，这个列里仅仅包括两个值：M或F，假设没有其他的性别(中性?),在这个例子中 customer.cust_gender列有比较少的相似度，可以作为位图索引。

Each bit in the bitmap corresponds to a possible rowid. If the bit is set, then the row with the corresponding rowid contains the key value. A mapping function converts the bit position to an actual rowid, so the bitmap index provides the same functionality as a B-tree index although it uses a different internal representation.

在位图中每位(bit)对应一个可能的rowid，如果位被指定，该rowid对应的记录(row)也就对应了值，一个映射函数将位的位置转变成一个确切的rowid，这样位图索引就以B-TREE索引不同方式提供了相同的功能。

If the indexed column in a single row is updated, then the database locks the index key entry (for example, M or F) and not the individual bit mapped to the updated row. Because a key points to many rows, DML on indexed data typically locks all of these rows. For this reason, bitmap indexes are not appropriate for many OLTP applications.

如果被索引的列中有一条记录被修改，那么数据库就会锁定索引值(比如M或F)，因为一个索引值对应很多记录，即数据库不会只锁定被修改的记录，连那些与该记录有相同索引值的记录也会一起被锁定。因为这个原因，位图索引不适合于OLTP应用。

Index-Organized Tables
索引组织表

An index-organized table is a table stored in a variation of a B-tree index structure. In a heap-organized table, rows are inserted where they fit. In an index-organized table, rows are stored in an index defined on the primary key for the table. Each index entry in the B-tree also stores the non-key column values. Thus, the index is the data, and the data is the index. Applications manipulate index-organized tables just like heap-organized tables, using SQL statements.

索引组织表(index-organized table)是采用B-TREE索引结构存储方式的表的变种之一，在一个栈组织表(heap-organized table)中，哪里有多余的空间，记录(row)就会被插入到哪里(也就是说记录不以顺序的方式存储，物理上是随机的)。但是在index-organized table中，记录被存储在索引中，并按主键的顺序排列。在B-TREE每个索引实体也存储非主键的列，因此，索引就是表，表也就是索引。但应用操作index-organized table与操作 heap-organized tables没有不同，也使用SQL语句。

For an analogy of an index-organized table, suppose a human resources manager has a book case of cardboard boxes. Each box is labeled with a number—1, 2, 3, 4, and so on—but the boxes do not sit on the shelves in sequential order. Instead, each box contains a pointer to the shelf location of the next box in the sequence.

Folders containing employee records are stored in each box. The folders are sorted by employee ID. Employee King has ID 100, which is the lowest ID, so his folder is at the bottom of box 1. The folder for employee 101 is on top of 100, 102 is on top of 101, and so on until box 1 is full. The next folder in the sequence is at the bottom of box 2.

In this analogy, ordering folders by employee ID makes it possible to search efficiently for folders without having to maintain a separate index. Suppose a user requests the records for employees 107, 120, and 122. Instead of searching an index in one step and retrieving the folders in a separate step, the manager can search the folders in sequential order and retrieve each folder as found.

Index-organized tables provide faster access to table rows by primary key or a valid prefix of the key. The presence of non-key columns of a row in the leaf block avoids an additional data block I/O. For example, the salary of employee 100 is stored in the index row itself. Also, because rows are stored in primary key order, range access by the primary key or prefix involves minimal block I/Os. Another benefit is the avoidance of the space overhead of a separate primary key index.

Index-organized tables are useful when related pieces of data must be stored together or data must be physically stored in a specific order. This type of table is often used for information retrieval, spatial (see "Overview of Oracle Spatial"), and OLAP applications (see "OLAP").

5.4 CASCADE的作用

<ORACLE SQL REFERENCES>的解释是：

Specify CASCADE to disable any integrity constraints that depend on the specified integrity constraint. To disable a primary or unique key that is part of a referential integrity constraint, you must specify this clause.

使用CASCADE用于取消（disable）任何依赖于其他完整性约束的完整性约束，例如，为了取消（disable）与一个外键约束有关系的主键约束或唯一性约束，你必须使用CASCADE。

5.6 Restrictions on Check Constraints check约束的限制

Check constraints are subject to the following restrictions:

check约束有如下限制：

You cannot specify a check constraint for a view. However, you can define the view using the WITH CHECK OPTIONclause, which is equivalent to specifying a check constraint for the view.

你不能为一个视图指定check约束。但是你可以使用WITH CHECK OPTION从句定义一个视图。这等同与你为视图指定check 约束。
The condition of a check constraint can refer to any column in the table, but it cannot refer to columns of other tables.

check约束可以指向一个表的任何列，但是它不能指向其他表的列。
Conditions of check constraints cannot contain the following constructs:

check约束的条件不能包括以下内容
- Subqueries and scalar subquery expressions
  
  子查询和scalar subquery？？如何翻译
- Calls to the functions that are not deterministic (CURRENT_DATE, CURRENT_TIMESTAMP, DBTIMEZONE,LOCALTIMESTAMP, SESSIONTIMEZONE, SYSDATE, SYSTIMESTAMP, UID, USER, and USERENV)
  
  不能调用如下函数CURRENT_DATE,CURRENT_TIMESTAMP, DBTIMEZONE,LOCALTIMESTAMP,SESSIONTIMEZONE,SYSDATE,SYSTIMESTAMP,UID,USER,USERENV
- Calls to user-defined functions
  
  调用用户自定义函数
- Dereferencing of REF columns (for example, using the DEREF function)
  
  ？？如何翻译
- Nested table columns or attributes
  
  嵌套表的列或属性
- The pseudocolumns CURRVAL, NEXTVAL, LEVEL, or ROWNUM
  
  伪列CURRVAL,NEXTVAL,LEVEL,ROWNUM
- Date constants that are not fully specified
  
  没有完全指定（还是定义）的静态数据

转载于:https://my.oschina.net/kursk/blog/67237