2012-10-9 11gR2 concepts page 33-64

很久没有这么专心的看文档了，异常珍惜这样的时光，要是能够再泡上一壶龙井，人生快哉啊！

今天的摘要如下:

Overview of Tables
-- A heap-organized table does not store rows in any particular order. The CREATE
TABLE statement creates a heap-organized table by default.
-- An index-organized table orders rows according to the primary key values. For
some applications, index-organized tables enhance performance and use disk
space more efficiently.
-- An external table is a read-only table whose metadata is stored in the database but
whose data in stored outside the database.

表分为三类，堆表，索引组织表与外部表。

Columns and Rows
A table can contain a virtual column, which unlike a nonvirtual column does not
consume disk space. The database derives the values in a virtual column on demand
by computing a set of user-specified expressions or functions. For example, the virtual
column income could be a function of the salary and commission_pct columns.

如果我没有记错的话，VRTUAL COLUMN（虚拟列）是11G的新特性。

Oracle Data Types -- Oracle数据库中的类型
VARCHAR2 and CHAR Data Types
-- For each row, Oracle Database stores each value in the column as a variable-length
field unless a value exceeds the maximum length, in which case the database returns
an error. For example, in a single-byte character set, if you enter 10 characters for the
last_name column value in a row, then the column in the row piece stores only 10
characters (10 bytes), not 25. Using VARCHAR2 reduces space consumption.
-- In contrast to VARCHAR2, CHAR stores fixed-length character strings. When you create
a table with a CHAR column, the column requires a string length. The default is 1 byte.
The database uses blanks to pad the value to the specified length.

简单来讲，就是VARCHAR2是变长，CHAR是定长的。

NUMBER Data Type -- 数字类型
You specify a fixed-point number in the form NUMBER(p,s), where p and s refer to
the following characteristics:
-- Precision
The precision specifies the total number of digits. If a precision is not specified,
then the column stores the values exactly as provided by the application without
any rounding.
-- Scale
The scale specifies the number of digits from the decimal point to the least
significant digit. Positive scale counts digits to the right of the decimal point up to
and including the least significant digit. Negative scale counts digits to the left of
the decimal point up to but not including the least significant digit. If you specify a
precision without a scale, as in NUMBER(6), then the scale is 0.
-- the salary column is type NUMBER(8,2), so the precision is 8 and
the scale is 2. Thus, the database stores a salary of 100,000 as 100000.00.

具体实验，参考：http://blog.csdn.net/empoli/article/details/8045241

DATE Data Type -- 日期类型
The database stores dates internally as numbers. Dates are stored in fixed-length fields
of 7 bytes each, corresponding to century, year, month, day, hour, minute, and second.
-- RR is similar to YY (the last two digits of the year), but the century of the return value
varies according to the specified two-digit year and the last two digits of the current
year. Assume that in 1999 the database displays 01-JAN-09. If the date format uses
RR, then 09 specifies 2009, whereas if the format uses YY, then 09 specifies 1909. You
can change the default date format at both the instance and the session level..

Rowid Data Types -- ROWID
-- Physical rowids store the addresses of rows in heap-organized tables, table
clusters, and table and index partitions. 物理ROWID存在于堆表，簇表等。
-- Logical rowids store the addresses of rows in index-organized tables. 逻辑ROWID适用于索引组织表。
-- Foreign rowids are identifiers in foreign tables, such as DB2 tables accessed
through a gateway. They are not standard Oracle Database rowids. FOREIGN ROWID适用于非ORALCE的数据库。
-- A data type called the universal rowid, or UROWID, supports all kinds of rowids. UROWID 支持所有类型的ROWID。

External Table Access Drivers -- 外部表的驱动
-- ORACLE_LOADER enables read-only access to external files using SQL*Loader. You
cannot create, update, or append to an external file using the ORACLE_LOADER driver.

ORACLE_LOADER适用于外部表

-- The ORACLE_DATAPUMP driver enables you to unload external data. This operation
involves reading data from the database and inserting the data into an external table,
represented by one or more external files. After external files are created, the database
cannot update or append data to them. The driver also enables you to load external
data, which involves reading an external table and loading its data into a database.

ORACLE_DATAPUMP与ORACLE_LOADER的区别是ORACLE_DATAPUMP可以从数据库中的表“读进”外部表，反之亦可。

Table Compression -- 压缩表
Basic and OLTP Table Compression
-- Basic table compression
This type of compression compresses data inserted by direct path load only and
supports limited data types and SQL operations.

BASIC TABLE COMPRESSION 只对直接路径加载的数据压缩。
-- OLTP table compression
This type of compression is intended for OLTP applications and compresses data
manipulated by any SQL operation.

Compression Declaration You can declare compression at the tablespace, table, partition,
or subpartition level. If specified at the tablespace level, then all tables created in this
tablespace are compressed by default.
The following statement applies OLTP compression to the orders table: -- 压缩表的方式
-- ALTER TABLE oe.orders COMPRESS FOR OLTP;

The following example of a partial CREATE TABLE statement specifies OLTP
compression for one partition and basic compression for the other partition:
-- CREATE TABLE sales (
prod_id NUMBER NOT NULL,
cust_id NUMBER NOT NULL, ... )
PCTFREE 5 NOLOGGING NOCOMPRESS
PARTITION BY RANGE (time_id)
( partition sales_2008 VALUES LESS THAN(TO_DATE(...)) COMPRESS BASIC,
partition sales_2009 VALUES LESS THAN (MAXVALUE) COMPRESS FOR OLTP );

Storage of Null Values 关于空值NULL的存储
Nulls are stored in the database if they fall between columns with data values. In these
cases, they require 1 byte to store the length of the column (zero). Trailing nulls in a
row require no storage because a new row header signals that the remaining columns
in the previous row are null. For example, if the last three columns of a table are null,
then no data is stored for these columns.

这段话的意思是说一行的最后几个字段如果是空值的话，它们是不占存储空间的，因为下一新行的行头会有标识。

Overview of Table Clusters -- 簇表
A table cluster is a group of tables that share common columns and store related data
in the same blocks. When tables are clustered, a single data block can contain rows
from multiple tables. For example, a block can store rows from both the employees
and departments tables rather than from only a single table.

Because table
clusters store related rows of different tables in the same data blocks, properly used
table clusters offer the following benefits over nonclustered tables: -- 使用簇表的好处
-- Disk I/O is reduced for joins of clustered tables. 减少表之间连接所需的IO
-- Access time improves for joins of clustered tables.访问时间减少
-- Less storage is required to store related table and index data because the cluster 更少的存储空间的占用
key value is not stored repeatedly for each row.
Typically, clustering tables is not appropriate in the following situations: 簇表不适用于以下的情形
-- The tables are frequently updated. 表经常被更新
-- The tables frequently require a full table scan. 表经常需要做全表扫描
-- The tables require truncating. 表需要做些TRUNCATE操作

Overview of Indexed Clusters -- 索引簇
An indexed cluster is a table cluster that uses an index to locate data. The cluster
index is a B-tree index on the cluster key. A cluster index must be created before any
rows can be inserted into clustered tables. -- 在簇表能够被INSERT值之前，要先保证索引簇先创建好。

 

Assume that you create the cluster employees_departments_cluster with the -- 创建过程如下
cluster key department_id, as shown in Example 2–8. Because the HASHKEYS clause
is not specified, this cluster is an indexed cluster. Afterward, you create an index
named idx_emp_dept_cluster on this cluster key.
Example 2–8 Indexed Cluster
CREATE CLUSTER employees_departments_cluster
(department_id NUMBER(4))
SIZE 512;
CREATE INDEX idx_emp_dept_cluster ON CLUSTER employees_departments_cluster;
You then create the employees and departments tables in the cluster, specifying the
department_id column as the cluster key, as follows (the ellipses mark the place
where the column specification goes):
CREATE TABLE employees ( ... )
CLUSTER employees_departments_cluster (department_id);
CREATE TABLE departments ( ... )
CLUSTER employees_departments_cluster (department_id);
Finally, you add rows to the employees and departments tables. The database
physically stores all rows for each department from the employees and departments tables in the same data blocks. The database stores the rows in a heap and locates them with the index.
看下面这个图就明白了

 

 

Overview of Hash Clusters 哈希簇和簇表类似
A hash cluster is like an indexed cluster, except the index key is replaced with a hash
function. No separate cluster index exists. In a hash cluster, the data is the index.
With an indexed table or indexed cluster, Oracle Database locates table rows using key
values stored in a separate index. To find or store a row in an indexed table or table
cluster, the database must perform at least two I/Os: -- 正常通过索引找到表值需要两步
-- One or more I/Os to find or store the key value in the index 先找到索引中的键值
-- Another I/O to read or write the row in the table or table cluster 通过键值找到具体的表中的值

Hashing is an optional way of storing table data to improve the performance of data
retrieval. Hash clusters may be beneficial when the following conditions are met: -- 哈希簇适用于以下情况
-- A table is queried much more often than modified. -- 表查询多于更改
-- The hash key column is queried frequently with equality conditions, for example,
WHERE department_id=20. For such queries, the cluster key value is hashed. WHERE条件是被HASH的值
The hash key value points directly to the disk area that stores the rows.
-- You can reasonably guess the number of hash keys and the size of the data stored
with each key value.

Hash Cluster Creation -- 哈希簇的创建
The cluster key, like the key of an indexed cluster, is a single column or composite key
shared by the tables in the cluster. The hash key values are actual or possible values
inserted into the cluster key column. For example, if the cluster key is
department_id, then hash key values could be 10, 20, 30, and so on.

To create a hash cluster, you use the same CREATE CLUSTER statement as for an
indexed cluster, with the addition of a hash key. The number of hash values for the
cluster depends on the hash key. In Example 2–9, the number of departments that are
likely to exist is 100, so HASHKEYS is set to 100. -- 这里部门数为100，所以把HASH KEY的值设为100 。

CREATE CLUSTER employees_departments_cluster
(department_id NUMBER(4))
SIZE 8192 HASHKEYS 100;

After you create employees_departments_cluster, you can create the
employees and departments tables in the cluster.

 

Hash Cluster Storage -- 哈希簇的存储空间的计算
Oracle Database allocates space for a hash cluster differently from an indexed cluster.
In Example 2–9, HASHKEYS specifies the number of departments likely to exist,
whereas SIZE specifies the size of the data associated with each department. The
database computes a storage space value based on the following formula:
HASHKEYS * SIZE / database_block_size
Thus, if the block size is 4096 bytes in Example 2–9, then the database allocates at least
200 blocks to the hash cluster.

To illustrate the retrieval issues, assume that block 100 in Figure 2–6 is completely full
with rows for department 20. A user inserts a new department with department_id
43 into the departments table. The number of departments exceeds the HASHKEYS
value, so the database hashes department_id 43 to hash value 77, which is the same
hash value used for department_id 20. Hashing multiple input values to the same
output value is called a hash collision. -- 哈希冲突 有时觉得不翻译更好

Overview of Indexes  -- 索引
In general, consider creating an index on a column in any of the following situations:
-- The indexed columns are queried frequently and return a small percentage of the
total number of rows in the table.
-- A referential integrity constraint exists on the indexed column or columns. The
index is a means to avoid a full table lock that would otherwise be required if you
update the parent table primary key, merge into the parent table, or delete from
the parent table.
-- A unique key constraint will be placed on the table and you want to manually
specify the index and all index options.

Indexes have the following properties: 索引的两类属性
-- Usability  可用性
Indexes are usable (default) or unusable. An unusable index is not maintained by
DML operations and is ignored by the optimizer. An unusable index can improve
the performance of bulk loads. Instead of dropping an index and later re-creating
it, you can make the index unusable and then rebuild it. Unusable indexes and
index partitions do not consume space. When you make a usable index unusable,
the database drops its index segment.
-- Visibility  可见性
Indexes are visible (default) or invisible. An invisible index is maintained by DML
operations and is not used by default by the optimizer. Making an index invisible
is an alternative to making it unusable or dropping it. Invisible indexes are
especially useful for testing the removal of an index before dropping it or using
indexes temporarily without affecting the overall application.

Composite Indexes -- 复合索引
Composite indexes can speed retrieval of data for SELECT statements in which the
WHERE clause references all or the leading portion of the columns in the composite
index. Therefore, the order of the columns used in the definition is important. In
general, the most commonly accessed columns go first.

For example, suppose an application frequently queries the last_name, job_id, and
salary columns in the employees table. Also assume that last_name has high
cardinality, which means that the number of distinct values is large compared to the
number of table rows. You create an index with the following column order:
CREATE INDEX employees_ix
ON employees (last_name, job_id, salary);
Queries that access all three columns, only the last_name column, or only the
last_name and job_id columns use this index. In this example, queries that do not
access the last_name column do not use the index. -- 要注意如果使用的是复合索引，查询条件要有第一个被索引的字段，否则不会使用此索引。

Types of Indexes  -- 索引种类
B-tree indexes  --  B树索引
These indexes are the standard index type. They are excellent for primary key and
highly-selective indexes. Used as concatenated indexes, B-tree indexes can retrieve
data sorted by the indexed columns. B-tree indexes have the following subtypes:
– Index-organized tables  -- 索引组织表 表既是索引，索引既是表
An index-organized table differs from a heap-organized because the data is
itself the index. See "Overview of Index-Organized Tables" on page 3-20.
– Reverse key indexes -- 反键索引
In this type of index, the bytes of the index key are reversed, for example, 103
is stored as 301. The reversal of bytes spreads out inserts into the index over
many blocks. See "Reverse Key Indexes" on page 3-11.
– Descending indexes -- 逆序索引
This type of index stores data on a particular column or columns in
descending order. See "Ascending and Descending Indexes" on page 3-11.
– B-tree cluster indexes -- B树索引簇
This type of index is used to index a table cluster key. Instead of pointing to a
row, the key points to the block that contains rows related to the cluster key.

 -- Bitmap and bitmap join indexes 位图索引
In a bitmap index, an index entry uses a bitmap to point to multiple rows. In
contrast, a B-tree index entry points to a single row. A bitmap join index is a
bitmap index for the join of two or more tables. See "Bitmap Indexes" on page 3-13.
 -- Function-based indexes 函数索引
This type of index includes columns that are either transformed by a function,
such as the UPPER function, or included in an expression. B-tree or bitmap indexes
can be function-based. See "Function-Based Indexes" on page 3-17.
 -- Application domain indexes 不翻译了
This type of index is created by a user for data in an application-specific domain.
The physical index need not use a traditional index structure and can be stored
either in the Oracle database as tables or externally as a file.

 

B树索引

 

Index Scans

In an index scan, the database retrieves a row by traversing the index, using the
indexed column values specified by the statement. If the database scans the index for a
value, then it will find this value in n I/Os where n is the height of the B-tree index.
This is the basic principle behind Oracle Database indexes.

索引的高度越高，在查找值时消耗的IO也许会更多。

 

Full Index Scan  -- 索引全扫描

In a full index scan, the database reads the entire index in order. A full
index scan is available if a predicate (WHERE clause) in the SQL statement references a
column in the index, and in some circumstances when no predicate is specified. A full
scan can eliminate sorting because the data is ordered by index key.

 

Fast Full Index Scan -- 索引快速全扫描

A fast full index scan is a full index scan in which the database
reads the index blocks in no particular order. The database accesses the data in the
index itself, without accessing the table.
Fast full index scans are an alternative to a full table scan when the index contains all
the columns that are needed for the query, and at least one column in the index key
has the NOT NULL constraint.

 

Index Range  -- 索引范围扫描

Scan An index range scan is an ordered scan of an index that has the
following characteristics:
 -- One or more leading columns of an index are specified in conditions. A condition
specifies a combination of one or more expressions and logical (Boolean) operators
and returns a value of TRUE, FALSE, or UNKNOWN.
 -- 0, 1, or more values are possible for an index key.

 

Index Unique Scan -- 索引唯一扫

In contrast to an index range scan, an index unique scan must have
either 0 or 1 rowid associated with an index key. The database performs a unique scan
when a predicate references all of the columns in a UNIQUE index key using an
equality operator. An index unique scan stops processing as soon as it finds the first
record because no second record is possible.

 

Index Skip Scan -- 索引跳跃扫描

An index skip scan uses logical subindexes of a composite index. The
database "skips" through a single index as if it were searching separate indexes. Skip
scanning is beneficial if there are few distinct values in the leading column of a
composite index and many distinct values in the nonleading key of the index.