Rumah >pangkalan data >tutorial mysql >2 Tables and Table Clusters读书笔记

2 Tables and Table Clusters读书笔记

WBOYasal: 2016-06-07 14:51:161101semak imbas

文章出处 Home / Database / Oracle Database Online Documentation 11g Release 2 (11.2) / Database Administration http://docs.oracle.com/cd/E11882_01/server.112/e40540/tablecls.htm#CNCPT010 额外的话对于这些文档，由于比较零散，章节不太合理是

文章出处

Home / Database / Oracle Database Online Documentation 11g Release 2 (11.2) / Database Administration
http://docs.oracle.com/cd/E11882_01/server.112/e40540/tablecls.htm#CNCPT010

额外的话

对于这些文档，由于比较零散，章节不太合理是正常的，当然开头除外，这篇在官方文档是第二篇，但是个人认为应该先看完逻辑存储结构和物理存储结构还有内存结构和进程结构再看会比较好，所以下一篇将取逻辑存储结构来做读书笔记

表和簇表(table clusters)

介绍schema对象
表概述
簇表(table clusters)概述

schema对象

schema是schema对象的逻辑容器，schema对象的例子有表或者索引，schema对象可以使用sql来创建或者操纵
数据库用户拥有一个密码和多种数据库权限，每个用户拥有一个schema，这个schema和用户同名，schema包含了用户的数据

schema对象类型

表（table）：最重要的schema对象，oracle中的表是行存储的
索引（index）：包含了表或簇表的索引行的entry的schema对象
分区（partition）：是表或者索引的的piece，每个分区有着它自己的名字，也可以有他们自己存储特性
视图（view）：一种个性化的定制展现数据的手段，可以任务视图存储的只是查询，视图不会存储要查询的数据
序列（sequence）：是用户创建的schema对象，可以为多个用户提供共享的产生整数的机制
维度（dimension）：在同一个表中，列组成的集合作为列集合，列集合之间形成列集合对，一个维度就是描述列集合对之间的父子关系。维度常常用来对数据进行分类
同义词（synonym）：schema对象的别名，不存储实际的数据
PL/SQL子程序或者包：PL/SQL是oracle对于sql的过程化扩展。其子程序被命名为PL/SQL块，可以使用一组参数来调用。包是一组相关联的子程序
其他：还有一些其他的对象，例如数据库用户，角色，上下文，目录对象，这些都不包含在一个schema里，它们通过sql来操纵，存储在数据库中

schema对象存储

有一些schema对象存储在一种逻辑存储结构——段（segment），例如非分区的堆组织表或者索引。其它的一些schema对象，例如视图或者序列，只是由元数据组成。
schema对象逻辑存储在表空间（tablespace）中，然而，schema和表空间并没有关系，一个表空间可以存储各种schema对象，而它们可以来自不同的schema，而schema的对象可以存在于不同的表空间中。每个对象的数据将存储在一个或多个数据文件（data file）中

schema对象依赖（dependencies）

有一些schema对象引用了其它对象，这就是schema对象依赖。例如，一个视图由其它表或者其它视图创建而来；PL/SQL子程序调用其它子程序。如果定义了一个对象A引用了对象B，对象A是B的依赖对象（dependent object），B是A的引用对象（referenced object）
oracle有对象依赖自动管理机制，确保了依赖对象始终是最新的，当一个依赖对象被创建，数据库将会跟踪依赖对象和引用对象的依赖。当引用对象改变时，依赖对象将会被标记成invalid。例如，用户删除了一个表，这个表的视图将不可用
在改变引用对象后，依赖对象会变成invalid，必须被重新编译。（官方文档有例子http://docs.oracle.com/cd/E11882_01/server.112/e40540/tablecls.htm#CNCPT1859）

SYS和SYSTEM schema

所有的oracle数据库都有一个administrative帐户，这个账户拥有非常高的权限，是专门设计给有dba权限的人来执行任务的，这些任务可以是开启关闭数据库，管理内存和存储，创建和管理数据库用户等等
SYS schema随着数据库的创建而创建，它拥有几乎所有权限，它存储了数据字典（data dictionary）的基表和视图。SYS schema 中的表只由数据库来操纵，不能被用户修改
SYSTEM schema随着数据库的创建而创建，存储了额外的表和视图（administrative information和oracle工具的信息）

Sample Schemas

是一组示例schema，它们互相都有联系，配合oracle文档的一些讲解
下面给出其中一个例子

表概述

oracle将表分为基本的两类：关系表和对象表
关系表有如下组织特性：
- 堆组织表（默认）
- 索引组织表（按组件排好序）
- 外部表（元数据在数据库里，但是数据在外部）
- 表可以是永久表也可以是临时表
  - 永久表，数据将永久保存在数据库中
  - 临时表，往往只会在事务或者会话期间存在，一旦事物或会话结束，数据将无法查看
接下来会讨论如下话题
- 行和列
- 例子：创建表和修改表
- oracle数据类型
- 完整性约束
- 对象表
- 临时表
- 外部表
- 表存储
- 表压缩

行和列

表由表名和列的集合组成，一个列标识了实体的一个属性。
通常情况下，在创建表时给定了一个列的列名，数据类型，长度。
表可以包含伪列（virtual column），伪列并不占据磁盘空间，通常是一些表达式的计算

例子

见官方文档即可http://docs.oracle.com/cd/E11882_01/server.112/e40540/tablecls.htm#CNCPT88807

oracle数据类型

每个列都有数据类型，这样才能制定它的存储格式，约束，和值的范围。
oracle提供了集中常用的built-in数据类型，最常见的如下：
- 字符数据类型
- 数字数据类型
- 时间数据类型
- rowid数据类型
- Format Models and Data Types
- 还有一些重要的built-in数据类型，包括raw,LOBs，还有collections。pl/sql有常量和变量的数据类型，包括BOOLEAN, reference types, composite types (records), 还有 user-defined types

字符数据类型

字符数据类型将字符存储为字符串，常用的字符数据类型有VARCHAR2
和字符有关的字节在schema中编码，常常叫做character set和code page。例如7-bit ASCII, EBCDIC, 还有Unicode UTF-8
关于字符数据类型的长度语义，可以分为两种——字节和字符。字节语义，将字符串看做是一串字节序列（默认方式）；字符语义，将字符串看做一串字符的序列。从技术上讲，一个字符是数据库字符集的一个代码点（code point）。
VARCHAR2和CHAR
- varchar2存储可变长度的字符串常量。字符串常量应该被单引号包围。
- char是定长的字符串常量
- Oracle Database compares VARCHAR2 values using nonpadded comparison semantics and compares CHAR values using blank-padded comparison semantics.（意思是说，varchar2不会填充，而char在没有填满制定的字符会用空格符填充）
NCHAR和NVARCHAR2
- 都是存储unicode字符集的字符的
- 特征和char，varchar2类似

数字数据类型

oracle数字数据类型存储整型数，浮点数，0，无穷大和无穷小，或者没有定义的数，被称之为“不是数字”或者NAN
数字数据类型都是可变长度的，每个值都是采用科学计数法，其中一个字节被用来存储指数。数据库会使用至多20个字节来存储小数部分（小数中的有效数字，数据库不会存储前导0或者尾端的0）
NUMBER
- 这是一个最常用的数字存储类型
- 可以指定小数点的位数，NUMBER(p,s)
  - p是精度，指定了数字的总数，如果没有指定，将会字节存储应用给出的数字（without any rounding）
  - s是数值范围，指定了小数点后的数字个数
  - 例子：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.
- 浮点数——BINARY_FLOAT和BINARY_DOUBLE
  - BINARY_FLOAT和BINARY_DOUBLE使用二进制精度，在执行算术操作时比number要更快

时间数据类型

基本的数据类型是DATE和TIMESTAMP，oracle还为时间戳提供了时区支持
DATE
- DATE存储日期和时间，在其内部，存储的是数字。每个date被存储为7个字节，支持世纪，年，月，日，时分秒的表示
- 默认格式是DD-MON-RR，例如01-JAN-11
- 默认时间格式24-hour format—HH:MI:SS，默认为24-hour format—HH:MI:SS
- 你可以在实例级别或者会话级别改变日期格式
- 当只有时间没有日期时，日期将被设为本月的第一天
TIMESTAMP
- date的扩展，可以存储分数秒，可以用来存储精确的时间
- TIMESTAMP WITH TIME ZONE 和 TIMESTAMP WITH LOCAL TIME ZONE制定了时区

ROWID数据类型

表中的每一行在数据库中都有一个地址，rowid就是地址，它分为
- 物理rowid：存储了堆组织表，表簇和表，还有索引分区的行的地址
- 逻辑rowid ：存储了索引组织表的行的地址
- Foreign rowid：是foreign tables的标志（Foreign rowids are identifiers in foreign tables, such as DB2 tables accessed through a gateway. They are not standard Oracle Database rowids.）
- universal rowid或者UROWID支持所有类型的rowid
rowid的使用：主要用于索引，例如b树索引，有一系列key划分成几个部分，，每个key都和rowid有关，这样就提供了快速访问数据的方法了
也可以在创建表时就定义一个rowid数据类型的列，例如，可以用来存储那些违反完整性约束的行（这个地方不太懂，原文是You can also create tables with columns defined using the ROWID data type. For example, you can define an exception table with a column of data type ROWID to store the rowids of rows that violate integrity constraints. Columns defined using the ROWID data type behave like other table columns: values can be updated, and so on）。
rowid伪列
- 每个表都有，但却不是存储在表中，无法对rowid伪列进行插入，更新删除操作，就像sql函数没有了参数一样

Format Models and Data Types

format models是用来描述数字或者时间格式的模型
TO_CHAR和TO_DATE

完整性约束

完整性约束是用来限制一列或多列数据的规则，这些规则防止非法的数据进入表中，也可以防止有依赖的数据被删除
如果完整性约束是enabled的，那么数据库会检查更新或者插入的数据。如果完整性约束被disabled，那么将无法防止非法数据的更新和插入
可以在创建表或者之后添加约束，约束可以临时disabled。数据库把完整性约束存储在数据字典中

object tables

在oracle中object type是用户自定义的类型，包括了名字，属性，还有方法。

<code class="language-sql hljs "><span class="hljs-operator"><span class="hljs-keyword">CREATE</span> TYPE department_typ <span class="hljs-keyword">AS</span> OBJECT
   ( d_name     VARCHAR2(<span class="hljs-number">100</span>),
     d_address  VARCHAR2(<span class="hljs-number">200</span>) );</span>
/
<span class="hljs-operator"><span class="hljs-keyword">CREATE</span> <span class="hljs-keyword">TABLE</span> departments_obj_t <span class="hljs-keyword">OF</span> department_typ;</span>
<span class="hljs-operator"><span class="hljs-keyword">INSERT</span> <span class="hljs-keyword">INTO</span> departments_obj_t 
  <span class="hljs-keyword">VALUES</span> (<span class="hljs-string">'hr'</span>, <span class="hljs-string">'10 Main St, Sometown, CA'</span>);</span></code>

临时表

临时表的数据只存在于会话或者事务中，并且会话的临时数据只对该会话可用，也就是具有私有性
使用CREATE GLOBAL TEMPORARY TABLE可以创建临时表，利用ON COMMIT子句可以指临时表的数据是transaction-specific（默认）还是session-specific的
不像其它数据库，oracle临时表是静态定义的，长期存在数据字典中
由于是静态定义，所以可以创建索引（一样是临时的），同样也可以创建视图或者触发器
临时段的分配：只有当数据第一次插入才会分配，在transaction-specific的临时表中，会在事务结束时释放临时段，在session-specific的临时表中，会在会话结束时释放临时段

外部表

外部表获取外部数据源的数据，使得这些外部数据就像在数据库。
对于flat files的访问是非常有用的
对于需要进行etl工作的数据仓库环境，外部表是非常有用的
创建外部表意味着要将其元数据放入数据字典，通常使用CREATE TABLE … ORGANIZATION EXTERNAL语句
外部表访问驱动（External Table Access Drivers）：是一种API，数据库利用这个驱动来访问外部表数据
oracle提供了两样驱动——ORACLE_LOADER（默认）和ORACLE_DATAPUMP驱动，来访问外部表
ORACLE_LOADER使用SQL * Loader工具来只读访问外部表
ORACLE_DATAPUMP可以unload外部表数据，这个操作包括了读取数据库数据并把数据插入到外部表的过程，当这个外部表创建完成，就无法更新和添加数据了；同样地，也允许你load外部表的数据

表存储

oracle使用表空间中的数据段来存储表的数据，一个数据段包括了由数据块组成的区（在逻辑存储结果一节中会更详细介绍）
表组织
- 一般情况下，表都是以堆形式组织的，这意味着数据行不是按顺序的，而是在物理结构上如何最适合就如何放
- 表中可以存放伪列（不占空间），伪列通常是一些表达式或者函数，可以为伪劣创建索引，收集其统计信息，创建完整性约束
行存储
- 数据库将行存储在数据块中，表中每行，只要不超过256列（属性），都包含了一个或多个row pieces
- 如果可能的话，oracle会将每行存储在一个row piece上。然而，如果一行数据无法存储在一个数据块中，或者更新后行的数据量增大，这时就会使用多个row pieces
- 在table cluster中，会存储多一个cluster key
rowids和row pieces
- rowid 是一个10字节物理地址，这个地址是行的物理地址。每行都有一个唯一的rowid指向它的row piece
- 在table cluster中，不同表中的行，如果在同一个数据块的话，可以有相同的rowid
- oracle数据库使用rowid来构建索引
null值的存储
- A null is the absence of a value in a column. Nulls indicate missing, unknown, or inapplicable data.（这段不好翻译，还是直接给出）
- 如果空值位于有数据值的列之间时，需要1byte空间来存储该列的长度
- 如果null位于一行的尾部，则不需要存储空间，因为下一行的行头会标记上一行的尾部剩余列的值为空
表压缩
- 数据库使用表压缩降低表的存储空间，压缩减少了在内存中database buffer cache的使用空间，在某些情况下还可以提高查询速度。表压缩对于数据库应用程序是透明的
- Basic and Advanced Row 压缩
 - basic table压缩：这个方法用于大量数据的操作，不会压缩利用conventional DML修改的数据。需要使用direct path loads的方法，ALTER TABLE … MOVE 操作，或者联机表重定义来达到basic压缩
 - advanced row 压缩:被设计用于OLTP应用，压缩被SQL操作的数据
 - 在这两类压缩下，数据库按照 row-major 格式来存储压缩了的行。每行的所有列将存放在一起
 - 重复的值会被一个在数据块头的短引用所替代。因此，需要重新创建非压缩的数据的信息将被存放在数据块中（很拗口，我目前也不太懂，这个地方原文是这样的：For basic and advanced row compression, the database stores compressed rows in row-major format. All columns of one row are stored together, followed by all columns of the next row, and so on (see Figure 12-7). Duplicate values are replaced with a short reference to a symbol table stored at the beginning of the block. Thus, information needed to re-create the uncompressed data is stored in the data block itself.）
 - 我们也可以人为设置压缩
```
<code class="language-sql hljs ">ALTER TABLE oe.orders COMPRESS FOR OLTP;

CREATE TABLE sales (
 prod_id NUMBER NOT NULL,
 cust_id NUMBER NOT NULL, ... )
 PCTFREE 5 NOLOGGING NOCOMPRESS
 PARTITION BY RANGE (time_id)
 ( partition sales_2010 VALUES LESS THAN(TO_DATE(...)) COMPRESS BASIC,
 partition sales_2011 VALUES LESS THAN (MAXVALUE) COMPRESS FOR OLTP ); </code>
```
Hybrid Columnar 压缩
- 在一组行中同样的列存储在一起，数据块不在按row-major格式存储了，而是利用一种行和圆柱（Columnar）方法的结合
- Hybrid Columnar 压缩的类型
  - Warehouse 压缩
  - Online archival 压缩
  - 这两类也需要使用direct path loads, ALTER TABLE … MOVE操作或者online table redefinition
- Hybrid Columnar 压缩优化了在Exadata存储设备中的数据仓库和决策支持应用。其它储存系统也能支持这种压缩，但提供不了想Exadata那样高效的查询
- 压缩单元（Compression Units）

table clusters概述

是一组表共享列，并且存放在同一个数据块中。假设有两个表employees 和 departments，它们相同的列是department_id，那么department_id在这两个表是共享的，而这两个表相应的数据都会存在同一个数据块中
cluster key：相同的列，例如department_id（当然如果有多列相同也是可以的）
cluster key value：相同列的值
相比于一班的表，有如下好处：
- 在做join操作时，可以减少IO
- 在做join操作时，可以加快访问时间
- 用于存放表和索引的空间减少了
不推荐的情况：
- 经常更新的表
- 经常需要全表扫描的表
- 需要truncate的表

indexed Clusters

是使用了索引的table cluster，B树索引建在cluster key上，需要在行被插入前表前创建（是这样吗？原文：A cluster index must be created before any rows can be inserted into clustered tables.）

<code class="language-sql hljs "><span class="hljs-operator"><span class="hljs-keyword">CREATE</span> CLUSTER employees_departments_cluster
   (department_id <span class="hljs-keyword">NUMBER</span>(<span class="hljs-number">4</span>))
<span class="hljs-keyword">SIZE</span> <span class="hljs-number">512</span>;</span>

<span class="hljs-operator"><span class="hljs-keyword">CREATE</span> INDEX idx_emp_dept_cluster <span class="hljs-keyword">ON</span> CLUSTER employees_departments_cluster;</span>

<span class="hljs-operator"><span class="hljs-keyword">CREATE</span> <span class="hljs-keyword">TABLE</span> employees ( ... )
   CLUSTER employees_departments_cluster (department_id);</span>

<span class="hljs-operator"><span class="hljs-keyword">CREATE</span> <span class="hljs-keyword">TABLE</span> departments ( ... )
   CLUSTER employees_departments_cluster (department_id);</span>
</code>

这里写图片描述

hash clusters概述

像index cluster一样，之时index key用hash函数代替了，作为hash key
推荐使用场景：
- 查询大大多于修改
- hash key常常用于等值查询

代码例子：

<code class="language-sql hljs "><span class="hljs-operator"><span class="hljs-keyword">CREATE</span> CLUSTER employees_departments_cluster
   (department_id <span class="hljs-keyword">NUMBER</span>(<span class="hljs-number">4</span>))
<span class="hljs-keyword">SIZE</span> <span class="hljs-number">8192</span> HASHKEYS <span class="hljs-number">100</span>;</span>

<span class="hljs-operator"><span class="hljs-keyword">SELECT</span> *
<span class="hljs-keyword">FROM</span>   employees
<span class="hljs-keyword">WHERE</span>  department_id = :p_id;</span>

<span class="hljs-operator"><span class="hljs-keyword">SELECT</span> * 
<span class="hljs-keyword">FROM</span>   departments 
<span class="hljs-keyword">WHERE</span>  department_id = :p_id;</span>

<span class="hljs-operator"><span class="hljs-keyword">SELECT</span> * 
<span class="hljs-keyword">FROM</span>   employees e, departments d 
<span class="hljs-keyword">WHERE</span>  e.department_id = d.department_id
<span class="hljs-keyword">AND</span>    d.department_id = :p_id;</span></code>