oracle concept 11g--2 Tables and Table Clusters

Schema Object Types

对象类型包括：
1.索引
Indexes are schema objects that contains an entry for each indexed row of the table or table cluster and provide direct, fast access to rows.
索引是一个提供直接快速访问表或者表集群的对象类型
2.分区
Partitions are pieces of large tables and indexes. Each partition has its own name and may optionally have its own storage characteristics.
分区是一个大表或者索引的片段，每个分区有自己的分区名字，并且拥有自己的存储属性
3.视图
Views are customized presentations of data in one or more tables or other views. You can think of them as stored queries. Views do not actually contain data.
视图是一种定制的某个或者多个表的数据展示，视图并不包含真正的数据
4.序列
序列是被定义的被多个用户共享的一个整数，序列可以被用作主键
5.维度
维度这个概念不太清楚
6.同义词
A synonym is an alias for another schema object. Because a synonym is simply an alias, it requires no storage other than its definition in the data dictionary.
同义词只是另一个数据对象的别名，它并不占用存储，它只存放在数据字典中
7.PL/SQL和包
PL/SQL is the Oracle procedural extension of SQL.
PL/SQL知识oracle的扩展SQL
Other types of objects are also stored in the database and can be created and manipulated with SQL statements but are not contained in a schema. These objects include database users, roles, contexts, and directory objects.
还有其他的数据库对象，它们并没有包含在schema中，这些对象包括：用户，角色，上下文，目录等对象

Schema Object Storage

Some schema objects store data in logical storage structures called segments. For example, a nonpartitioned heap-organized table or an index creates a segment. Other schema objects, such as views and sequences, consist of metadata only.
一些schema对象存储数据在逻辑上叫做段。例如堆栈表，索引等。其他schema例如视图或者序列仅仅由元数据组成
A segment cannot span multiple tablespaces .
一个段不能跨越多个表空间

If the definition of object A references object B, then A is a dependent object with respect to B and B is a referenced object with respect to A.
有些对象之间是相互依赖的，如果一个对象的定义中引用了其他对象，那么此对象被称为依赖对象（dependent object），此对象所引用的对象被称为 引用对象（referenced object）
When a referenced object changes in a way that might affect a dependent object, the dependent object is marked invalid. For example, if a user drops a table, no view based on the dropped table is usable.
oracle自动跟踪依赖对象和引用对象，当发现引用对象改变会影响依赖对象，依赖对象会变为无效的状态。
An invalid dependent object must be recompiled against the new definition of a referenced object before the dependent object is usable. Recompilation occurs automatically when the invalid dependent object is referenced.
无效的依赖对象必须被重新编译当引用对象被重新定义状态的时候
关于这个测试参考-- dependent object&referenced object--test01
---------------------------------------------------------------------------------------------------------------------
The SYS schema stores the base tables and views for the data dictionary .
SYS用户是当数据库创建的时候自动创建的一个管理用户，这个用户能够完成数据库所有的操作。SYS用户存储基本和数据字典的视图
Tables in the SYS schema are manipulated only by the database and must never be modified by any user.
不允许任何用户修改SYS schema中的对象
----------------------------------------------------------------------------------------------------------------------
Oracle Database tables fall into the following basic categories:
表分为俩种类型：
1.Relational tables
2.Object tables
关系型表分为以下类型：
1. heap-organized table
A heap-organized table does not store rows in any particular order.
堆栈表存储无序的行
2. index-organized table
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.
索引组织表根据主键进行排序，索引组织表增强应用的性能并且有效的利用磁盘空间
3. external table
-------------------------------------------------------------------------------------------------
表又分为永久表和临时表，临时表只是存在于事物或者会话当中
虚拟列
列类型：
1.字符数据类型
2.数字数据类型
3.日期数据类型
4.rowid数据类型
5.Format Models and Data Types
6.raw类型
7.LOB类型
8.PL/SQL类型
9.BOOLEAN类型
10.reference类型
11.composite类型
12.用户定义类型
字符数据类型：
最常用的字符数据类型是varchar2
In contrast to VARCHAR2, CHAR stores fixed-length character strings.
varchar2是变长的，char是定长的
The database uses blanks to pad the value to the specified length.
数据库使用空白去填充char字符类型的剩余空间
NCHAR&NVARCHAR2

Numeric Data Types

Oracle Database does not store leading and trailing zeros.
oracle不会存储前导零和尾部零
The NUMBER data type is recommended for most cases in which you must store numeric data.
如果是数字类型，oracle建议使用number类型

Rowid Data Types

Every row stored in the database has an address. Oracle Database uses a ROWID data type to store the address ( rowid ) of every row in the database.
在数据库中的每行都有一个地址，oracle用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.
外键rowid存储在外键表中
Oracle Database uses rowids internally for the construction of indexes.
oracle内部使用rowid来构建索引结构
A B-tree index, which is the most common type, contains an ordered list of keys divided into ranges.
B-tree索引按照范围进行索引键排序
Each key is associated with a rowid that points to the associated row's address for fast access.
每个索引键使用rowid关联到指定的行地址来进行fast access（fast access的原理）
rowid作用：
Rowids are the fastest means of accessing particular rows.

Rowids provide the ability to see how a table is organized.
Rowids are unique identifiers for rows in a given table.

Values of the ROWID pseudocolumn are strings representing the address of each row
rowid代表了每行的地址

The database stores constraints in the data dictionary .
约束存储在数据库中的数据字典里
Oracle Database temporary tables hold data that exists only for the duration of a transaction or session
oracle数据库的临时表处理数据只能在事物或者session当中
Data in a temporary table is private to the session, which means that each session can only see and modify its own data.
数据在临时表中是私有的，意味着只能看到自己的临时数据

Table Storage

Oracle Database uses a data segment in a tablespace to hold table data. As explained in "User Segments" , a segment contains extents made up of data blocks .
oracle在表空间中使用段来处理表数据，段包含区，区由块组成

Table Organization

By default, a table is organized as a heap , which means that the database places rows where they fit best rather than in a user-specified order.
默认的表是以堆栈组成，这意味着数据库处理行是以最合适的位置放置，而不是用户指定的顺序
Thus, a heap-organized table is an unordered collection of rows.
所以堆栈表中的行不是按照顺序排列
As users add rows, the database places the rows in the first available free space in the data segment.
当用户在表中添加行的时候，数据库把行放置在第一个适合的空闲空间在数据段中
Rows are not guaranteed to be retrieved in the order in which they were inserted.
行插入并不能保证按照顺序插入

表按照create table语句的顺序来存储列，但是有例外：oracle总是存储long类型在列的最后

Row Storage

The database stores rows in data blocks. Each row of a table containing data for less than 256 columns is contained in one or more row pieces
数据库在数据块中存储行，表中每行可以包含少于256个列，存储在一个或多个row pieces中
If possible, Oracle Database stores each row as one row piece. However, if all of the row data cannot be inserted into a single data block, or if an update to an existing row causes the row to outgrow its data block, then the database stores the row using multiple row pieces
oracle尽可能的存储每行在一个行片中，然而如果一个数据块不能存储所有的行数据，或者更新之后的行数据不能存储在原有数据块中，则就会存储一个行在多个行片中
every row in a heap-organized table has a rowid unique to this table that corresponds to the physical address of a row piece. For table clusters, rows in different tables that are in the same data block can have the same rowid.
每个在堆栈表中的行有一个唯一的rowid对应一个行片的物理地址，对于集群表来说行在相同的数据块不同的表中共享相同的rowid
Oracle Database uses rowids internally for the construction of indexes. For example, each key in a B-tree index is associated with a rowid that points to the address of the associated row for fast access (see "B-Tree Indexes"). Physical rowids provide the fastest possible access to a table row, enabling the database to retrieve a row in as little as a single I/O.
oracle使用内部的结构构建索引，B-tree索引关联一个rowid指向关联的行进行fast access。物理rowid能提供最快的访问一个表中的行，允许数据库返回在一个I/O中返回查询行

Table Compression

表压缩技术，目前我暂时应用的不多，暂略过。。

Overview of Table Clusters

You can consider clustering tables when they are primarily queried (but not modified) and records from the tables are frequently queried together or joined.
如果这些表经常用于一起查询或者join连接，则建议考虑使用表集群，而如果这些表经常用于修改，则不建议使用表集群
表集群的优点：
Disk I/O is reduced for joins of clustered tables.

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.

表集群特性不适合以下种类的表：
The tables are frequently updated.

The tables frequently require a full table scan.
The tables require truncating.

表集群的在数据库中的逻辑形式：





哈希簇相关知识点，目前我暂时用的不多，先略过。。。

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/30291162/viewspace-1703218/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/30291162/viewspace-1703218/