Oracle 19c Concepts(02)：Tables and Table Clusters

CREATE TABLE test_table ( col1 INTEGER, col2 INTEGER );CREATE OR REPLACE PROCEDURE test_proc
AS
BEGINFOR x IN ( SELECT col1, col2 FROM test_table )LOOP-- process dataNULL;END LOOP;
END;
/

下面的查询显示过程 test_proc 的状态是有效的：

SQL> SELECT OBJECT_NAME, STATUS FROM USER_OBJECTS WHERE OBJECT_NAME = 'TEST_PROC';OBJECT_NAME STATUS
----------- -------
TEST_PROC   VALID

下面的查询显示过程 test_proc 的状态是有效的：

SQL> ALTER TABLE test_table ADD col3 NUMBER;Table altered.SQL> SELECT OBJECT_NAME, STATUS FROM USER_OBJECTS WHERE OBJECT_NAME = 'TEST_PROC';OBJECT_NAME STATUS
----------- -------
TEST_PROC   VALID

但是， 更改 col1 列的数据类型后， test_proc 过程变得无效，因为该过程依赖于该列。

SQL> ALTER TABLE test_table MODIFY col1 VARCHAR2(20);Table altered.SQL> SELECT OBJECT_NAME, STATUS FROM USER_OBJECTS WHERE OBJECT_NAME = 'TEST_PROC';OBJECT_NAME STATUS
----------- -------
TEST_PROC   INVALID

运行或重新编译该过程使它重新有效，如下面的示例中所示：

SQL> EXECUTE test_procPL/SQL procedure successfully completed.SQL> SELECT OBJECT_NAME, STATUS FROM USER_OBJECTS WHERE OBJECT_NAME = 'TEST_PROC';OBJECT_NAME STATUS
----------- -------
TEST_PROC   VALID

CREATE TABLE products ( prod_id INT, count INT INVISIBLE );
ALTER TABLE products MODIFY ( count VISIBLE );

CREATE TABLE employees( employee_id    NUMBER(6), first_name     VARCHAR2(20), last_name      VARCHAR2(25)CONSTRAINT     emp_last_name_nn  NOT NULL, email          VARCHAR2(25)CONSTRAINT     emp_email_nn  NOT NULL, phone_number   VARCHAR2(20), hire_date      DATECONSTRAINT     emp_hire_date_nn  NOT NULL, job_id         VARCHAR2(10)CONSTRAINT     emp_job_nn  NOT NULL, salary         NUMBER(8,2), commission_pct NUMBER(2,2), manager_id     NUMBER(6), department_id  NUMBER(4), CONSTRAINT     emp_salary_minCHECK (salary > 0), CONSTRAINT     emp_email_ukUNIQUE (email)) ;

ALTER TABLE employees
ADD ( CONSTRAINT     emp_emp_id_pkPRIMARY KEY (employee_id), CONSTRAINT     emp_dept_fkFOREIGN KEY (department_id)REFERENCES departments, CONSTRAINT     emp_job_fkFOREIGN KEY (job_id)REFERENCES jobs (job_id), CONSTRAINT     emp_manager_fkFOREIGN KEY (manager_id)REFERENCES employees) ;

EMPLOYEE_ID FIRST_NAME  LAST_NAME      SALARY COMMISSION_PCT DEPARTMENT_ID
----------- ----------- ------------- ------- -------------- -------------100 Steven      King            24000                           90101 Neena       Kochhar         17000                           90102 Lex         De Haan         17000                           90103 Alexander   Hunold           9000                           60107 Diana       Lorentz          4200                           60149 Eleni       Zlotkey         10500             .2            80174 Ellen       Abel            11000             .3            80178 Kimberely   Grant            7000            .15

前面的输出说明了表、列和行的如下重要特征：

• 表中的行描述一名雇员的全部属性： 名称、 薪金、 部门等。 例如，输出的第一行显示名为 Steven King 的雇员记录。

• 列描述该雇员的一个属性。 在本例中， employee_id 列是主键，其含义是指每一位员工由其雇员 id 唯一地标识，保证任何两个雇员都不具有相同的雇员 id。

• 非键列可以包含具有相同值的行。在本例中，雇员 101、102 的薪金相同，都是 17000。

• 外键列会引用同一个表或另一个表中的主键或唯一键。 在本例中，department_id 值 90 对应于 departments 表中的 department_id 列等于 90 的行。

• 域是行和列的交叉点。 它只可以包含一个值。 例如，雇员 104 的部门 ID 域包含值 60。

• 域可以没有值。 在这种情况下，该域包含空值。 雇员 100 的 commission_pct 列的值为空，而雇员 149 的相应值是.2。一个列通常允许为空，除非已对该列定义了 NOT NULL 或主键完整性约束，在这种情况下如果该列无值，是不可以插入任何行的。

SQL> SELECT ROWID FROM employees WHERE employee_id = 100;ROWID
------------------
AAAPecAAFAAAABSAAA

SQL> SELECT last_name employee, TO_CHAR(salary, '$99,990.99') AS "SALARY"2  FROM   employees3  WHERE  department_id = 80 AND last_name = 'Russell';EMPLOYEE                  SALARY
------------------------- -----------
Russell                    $14,000.00

下面的示例更新一个雇佣日期，使用 TO_DATE 函数与格式掩码 'YYYY MM DD' 将字符串'1998 05 20'转换为日期值。

SQL> UPDATE employees2  SET hire_date = TO_DATE('1998 05 20','YYYY MM DD')3  WHERE last_name = 'Hunold';

50,Shipping,121,1500
120,Treasury,,1700
70,Public Relations,204,2700
30,Purchasing,114,1700
130,Corporate Tax,,1700
10,Administration,200,1700
110,Accounting,205,1700

列的顺序对于表中的所有行都是相同的。通常，数据库按它们被列在 CREATE TABLE 语句中的列顺序来存储列，但这个顺序是不保证的。例如，如果表中有 LONG 类型的列，那么数据库总是将其存储到行中的最后一列。此外，如果向表中添加一个新列，则新列将被存储为最后一列。

与常规列不同，表可以包含虚拟列，它不占用磁盘上的空间。数据库通过计算一组用户指定的表达式或函数，在虚拟列中按需派生一个值。您可以对虚拟列创建索引、 收集统计数据、并创建完整性约束。因此，虚拟列非常像非虚拟列。

ALTER TABLE oe.orders ROW STORE COMPRESS ADVANCED;

CREATE TABLE sales (prod_id     NUMBER     NOT NULL,cust_id     NUMBER     NOT NULL, ... )PCTFREE 5 NOLOGGING NOCOMPRESSPARTITION BY RANGE (time_id)( partition sales_2013 VALUES LESS THAN(TO_DATE(...)) ROW STORE COMPRESS BASIC,partition sales_2014 VALUES LESS THAN (MAXVALUE) ROW STORE COMPRESS ADVANCED );

CREATE CLUSTER employees_departments_cluster(department_id NUMBER(4))
SIZE 512;CREATE INDEX idx_emp_dept_cluster ON CLUSTER employees_departments_cluster;

因为未指定 HASHKEYS 子句，所以 employees_departments_cluster 是一个索引化表簇。前面的示例是在簇键department_id上创建一个名为 idx_emp_dept_cluster 的索引

CREATE TABLE employees ( ... )CLUSTER employees_departments_cluster (department_id);CREATE TABLE departments ( ... )CLUSTER employees_departments_cluster (department_id);

将行添加到 employees 表和 departments 表中。数据库在物理上将雇员表和部门表中每个部门的所有行都存储在相同的数据块中。数据库以堆的形式存储行，并使用索引定位他们。

图 2-5 显示了包含 employees 表和 departments 表所构成的 employees_departments_cluster 表簇。数据库将部门 20 的雇员所在的行存储在一起，部门 110 的行也存储在一起，依次类推。如果表未被聚簇，那么数据库将不保证相关的行被存储在一起。

B 树簇索引把簇键值与数据所在块的数据库块地址 (DBA) 关联起来。例如，键 20 的索引条目显示包含部门 20 的雇员数据所在块的地址：

20,AADAAAA9d

与非聚簇表上的索引类似，簇索引被单独管理，簇索引也可以与表簇存在于不同的表空间中。

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

创建聚簇 employees_departments_cluster 后，可以在该簇中创建 employees 表和 departments 表。然后可以将数据如同索引化簇一样装载到哈希簇中。

SELECT *
FROM   employees
WHERE  department_id = :p_id;SELECT * 
FROM   departments 
WHERE  department_id = :p_id;SELECT * 
FROM   employees e, departments d 
WHERE  e.department_id = d.department_id
AND    d.department_id = :p_id;

如果某个用户查询 department_id = 20 的雇员，那么数据库可能将此值哈希到 bucket 77。如果某个用户查询 department_id = 10 的雇员，那么数据库可能将此值哈希到 bucket 15。数据库使用内部生成的哈希值来定位包含请求的部门的雇员行所在的数据块。

下图将哈希簇段显示为一行水平排列的块。如图所示，查询只需单个 I/O就可以检索到数据。

哈希簇的一个限制是在非索引化簇键上的范围扫描不可用。假设在创建哈希簇时没有为哈希簇创建单独的索引。对部门 id 在 20 和 100 之间的查询不能使用哈希算法，因为它不能哈希 20 和 100 之间的每个可能的值。因为没有索引存在，数据库必须执行完全扫描。

HASHKEYS * SIZE / database_block_size

因此，如果哈希簇创建示例中的块大小是 4096 字节，那么数据库至少要为该哈希簇分配 200 个数据块。

Oracle 数据库并不限制您可以向哈希簇中插入的哈希键值的数目。例如，即使 HASHKEYS 是 100，但这不会阻止你向部门表中插入 200 个不同的部门。但是，当（输入的） 哈希键值的数目超过哈希键（HASHKEYS 的设定） 数目时，哈希簇检索效率会降低。

为说明检索问题，假定图 2-6 中的块 100 内全是部门 20 的行。用户向部门表中插入一个新的部门，其 department_id 为 43。部门的数量超过 HASHKEYS 的值，因此数据库对 department_id 43 进行哈希处理得到哈希值 77，这与 department_id 20 的哈希值相同。多个输入的值被哈希为相同的输出值称为哈希冲突。

当用户为部门 43 往簇中插入行时，数据库不能将这些行存储在块 100 中，因为它已经满了。数据库将块 100 链接到一个新的溢出块，比如说块 200，并将插入的行存储在新块中。这两个块 100 和 200 现在都可以存储部门 20 或 43 的数据。如图 2-7 ，查询部门 20 或 43 现在需要两个 i/o 才能检索到数据： 块 100 和其相关联的块 200。您可以通过重新创建具有不同 HASHKEYS 值的表簇来解决这个问题。

CREATE TABLE lineitem ( orderkey      NUMBER        , shipdate      DATE          ,receiptdate   DATE          ,destination   VARCHAR2(50)  ,quantity      NUMBER        );

这张 lineitem 表包含四个数据块，一个块里包含两条数据。表2-3 展示了表里的八条数据。

您可以使用 CREATE MATERIALIZED ZONEMAP 语句在 lineitem 表上创建一个区域映射。每个区域包含2个块，存储着 orderkey、shipdate和receiptdate列的最小和最大值。表 2-4 显示了区域映射。

当您执行以下查询时，数据库可以读取区域映射，然后只扫描block 1和block 2，因为日期1-3-2014 介于最小和最大日期之间：

SELECT * FROM lineitem WHERE shipdate = '1-3-2014';

CREATE TABLE sales
(prod_id     NOT NULL NUMBER
,  cust_id     NOT NULL NUMBER
,  amount_sold NUMBER(10,2) ...
)
CLUSTERING BY LINEAR ORDER (cust_id, prod_id)YES ON LOAD YES ON DATA MOVEMENTWITH MATERIALIZED ZONEMAP;

同时限定cust_id和prod_id列或前缀cust_id的查询将使I/O缩减。仅限定prod_id的查询不会发生显著的I/O减少，因为prod_id是BY LINEAR ORDER子句的后缀。下面的示例展示了数据库如何在表扫描期间减少I/O。

SELECT * FROM sales WHERE cust_id = 100;

因为sales表是一个 BY LINEAR ORDER 的聚簇，数据库必须只读取包含cust_id值为100的区域。

SELECT * FROM sales WHERE cust_id = 100 AND prod_id = 2300;

因为sales表是一个 BY LINEAR ORDER 的聚簇，数据库必须只读取包含cust_id值100和prod_id值2300的区域。

CREATE TABLE sales
(prod_id NUMBER NOT NULL
,  cust_id NUMBER NOT NULL
,  amount_sold NUMBER(10,2) ...
)
CLUSTERING salesJOIN products ON (sales.prod_id = products.prod_id)JOIN customers ON (sales.cust_id = customers.cust_id)BY INTERLEAVED ORDER((  products.prod_category,  products.prod_subcategory),(  customers.cust_state_province,  customers.cust_city))
WITH MATERIALIZED ZONEMAP;

假设应用程序查询联接中的sales、products和customers表。查询指定谓词 customers.prod_category 和 customers_cust_state_province，如下:

SELECT cust_city, prod_sub_category, SUM(amount_sold)
FROM   sales, products, customers
WHERE  sales.prod_id = products.prod_id 
AND    sales.cust_id = customers.cust_id
AND    customers.prod_category = 'Boys' 
AND    customers.cust_state_province = 'England - Norfolk' 
GROUP BY cust_city, prod_sub_category;

在前面的查询中，prod_category 和 cust_state_province 列是 CREATE TABLE 示例中显示的聚簇定义的一部分。在扫描 sales 表的过程中，数据库可以查询区域映射，只访问该区域中的 rowids。

100,Steven,King,SKING,515.123.4567,17-JUN-03,AD_PRES,31944,150,90
101,Neena,Kochhar,NKOCHHAR,515.123.4568,21-SEP-05,AD_VP,17000,100,90 
102,Lex,De Haan,LDEHAAN,515.123.4569,13-JAN-01,AD_VP,17000,100,90

您可以创建一个外部表，把这个文本文件复制到外部表定义中指定的位置，并使用 SQL 查询该文本文件中的记录。类似地，可以使用外部表对 JSON 文档或 LOB 进行只读访问。

在数据仓库环境中，外部表对于执行常见的提取、转换和加载（ETL）任务非常有用。例如，外部表使得数据加载阶段和数据转换阶段对接，这种技术消除了为进一步处理数据库中的数据而在数据库内存放中间数据的需要。

从Oracle数据库12c第2版(12.2)开始，您可以在虚拟或非虚拟列上对外部表进行分区。因此，您可以利用 partition pruning 和 partition-wise joins 所提供的性能改进。例如，可以使用分区外部表来分析存储在 Hadoop 分布式文件系统(HDFS)或NoSQL数据库上的大量非关系数据。

CREATE TYPE department_typ AS OBJECT( d_name     VARCHAR2(100),d_address  VARCHAR2(200) );
/

下面的示例 CREATE TABLE 语句创建一个名为 departments_obj_t 的对象表，其对象类型为 department_typ ，然后向表中插入一行。此表的属性（列）派生自该对象类型的定义。

CREATE TABLE departments_obj_t OF department_typ;
INSERT INTO departments_obj_t VALUES ('hr', '10 Main St, Sometown, CA');

与关系表列相似，对象表只能包含同种类型的行，即具有同种声明类型的对象实例。默认情况下，对象表中的每个行对象都有一个相关联的逻辑对象标识符 (OID)，以便在该对象表中唯一地标识该对象。对象表的 OID 列是一个隐藏列。