PARTII-Oracle数据访问-SQL

7. SQL

7.1. SQL简介

SQL(发音为"sequel")是一种基于集合的、高级的声明式计算机语言,所有程序和用户都使用它来访问Oracle数据库中的数据。尽管一些Oracle工具和应用程序掩盖了SQL的使用,但所有数据库操作都是使用SQL执行的。任何其他数据访问方法都绕过了Oracle数据库内置的安全性,可能会危及数据的安全性和完整性。

SQL提供了一个与关系数据库(如Oracle数据库)的接口。SQL将以下任务统一在一个一致的语言中:

  • 创建、替换、修改和删除对象
  • 插入、更新和删除表行
  • 查询数据
  • 控制对数据库及其对象的访问
  • 保证数据库的一致性和完整性

SQL可以交互式使用,这意味着可以手动将语句输入到程序中。SQL语句也可以嵌入到使用其他语言(如C或Java)编写的程序中。

7.1.1. SQL数据访问

计算机语言主要分为两大类:一类是声明式语言,它们是非过程性的,描述应该做什么,另一类是过程性语言,如C++和Java,描述应该如何去做。SQL在某种程度上是声明式的,因为用户指定他们想要的结果,而不是如何得到它。SQL语言编译器执行生成过程的工作,以导航数据库并执行所需的任务。

SQL使您能够在逻辑层面上处理数据。只有在您想要操作数据时,才需要关心实现细节。例如,以下语句查询姓氏以K开头的员工记录:

SELECT last_name, first_name 
  FROM hr.employees 
 WHERE last_name LIKE 'K%' 
 ORDER BY last_name, first_name;

数据库在单一步骤中检索满足WHERE条件(也称为谓词)的所有行。这些行可以作为一个单元传递给用户、另一个SQL语句或应用程序。您不需要逐行处理,也不需要知道这些行是如何物理存储或检索的。

所有SQL语句都使用优化器,这是Oracle数据库的一部分,它确定访问指定数据的最有效方式。Oracle数据库还支持您可以使用的技巧,以使优化器更好地执行其工作。

7.1.2. SQL标准

Oracle致力于遵循业界公认的标准,并积极参与SQL标准委员会的工作。公认的行业委员会包括美国国家标准协会(ANSI)和国际标准化组织(ISO)。ANSI和ISO/IEC都已接受SQL作为关系数据库的标准语言。

最新的SQL标准于2003年7月被采纳,通常被称为SQL:2003。SQL标准的一部分,第14部分,SQL/XML(ISO/IEC 9075-14)在2006年被修订,通常被称为SQL/XML:2006。

Oracle SQL包含许多对ANSI/ISO标准SQL语言的扩展,Oracle数据库工具和应用程序提供了额外的语句。工具SQL*Plus、SQL Developer和Oracle Enterprise Manager使您能够对Oracle数据库运行任何ANSI/ISO标准SQL语句以及这些工具提供的任何额外语句或函数。

7.2. SQL语句概述

在Oracle数据库中对信息执行的所有操作都是通过SQL语句来运行的。SQL语句是包含标识符、参数、变量、名称、数据类型和SQL保留字的计算机程序或指令。

注意:SQL保留字在SQL中有特殊含义,不应用于其他任何目的。例如,“SELECT”和“UPDATE”是保留字,不应用作表名。

SQL语句必须相当于一个完整的SQL句子,例如:SELECT last_name, department_id FROM employees
Oracle数据库只执行完整的SQL语句。以下片段会生成错误,提示需要更多的文本:SELECT last_name;

7.2.1. 数据定义语言(DDL)

数据定义语言(DDL)语句用于定义、结构性更改和删除模式对象。例如,DDL语句允许您:

  • 创建、修改和删除模式对象和其他数据库结构,包括数据库本身和数据库用户。大多数DDL语句以关键字CREATE、ALTER或DROP开头。
  • 删除模式对象中的所有数据,而不移除这些对象的结构(TRUNCATE)。

注意:与DELETE不同,TRUNCATE不生成撤销数据,这使得它比DELETE更快。此外,TRUNCATE不会触发删除触发器。

  • 授予和撤销权限和角色(GRANT, REVOKE):这些DDL语句允许数据库管理员控制用户对数据库对象(如表、视图、序列等)的访问权限,以及用户可以扮演的角色。
  • 开启和关闭审计选项(AUDIT, NOAUDIT):这些语句用于启用或禁用对数据库操作的审计跟踪,以便记录和监控数据库的安全和使用情况。
  • 向数据字典添加注释(COMMENT):这个语句允许用户为数据库对象添加描述性文本,这些文本存储在数据字典中,有助于理解对象的用途和功能。

DDL允许您更改对象的属性,而无需更改访问该对象的应用程序。例如,您可以在人力资源应用程序访问的表中添加一列,而无需重写应用程序。您还可以在数据库用户在数据库中执行工作时使用DDL来更改对象的结构。

在Oracle数据库中,这种能力特别重要,因为它支持在线DDL操作,这意味着您可以在不中断数据库服务的情况下对数据库结构进行更改。例如,使用ALTER TABLE语句可以添加、删除或修改列,或者使用ALTER INDEX来重建索引,而不会妨碍用户对数据库的正常访问。这种灵活性对于维护大型、活跃的数据库系统至关重要。

示例7-1使用DDL语句创建了plants表,然后使用DML插入两行数据。接着,示例使用DDL来更改表结构,授予和撤销用户对此表的权限,最后删除该表。

Example 71 DDL Statements 
CREATE TABLE plants ( plant_id NUMBER PRIMARY KEY, common_name VARCHAR2(15) );
INSERT INTO plants VALUES (1, 'African Violet'); # DML statement 
INSERT INTO plants VALUES (2, 'Amaryllis'); # DML statement
ALTER TABLE plants ADD ( latin_name VARCHAR2(40) );
GRANT SELECT ON plants TO scott;
REVOKE SELECT ON plants FROM scott; 
DROP TABLE plants;

在数据库执行DDL语句之前,以及之后执行COMMIT或ROLLBACK之前,会发生一个隐式的COMMIT。在示例7-1中,两个INSERT语句后面跟着一个ALTER TABLE语句,因此数据库会提交这两个INSERT语句。如果ALTER TABLE语句成功,则数据库会提交这个语句;否则,数据库会回滚这个语句。无论哪种情况,两个INSERT语句已经被提交。

7.2.2. 数据操作语言(DML)

数据操作语言(DML)语句用于查询或操作现有模式对象中的数据。与DDL语句允许您更改数据库结构不同,DML语句允许您查询或更改内容。例如,ALTER TABLE更改表的结构,而INSERT则向表中添加一行或多行数据。
DML语句是最常用的SQL语句,使您能够:

  • 从一到多个表或视图中检索或提取数据(SELECT)。
  • 通过指定列值列表或使用子查询选择和操作现有数据,将新行数据添加到表或视图中(INSERT)。
  • 更改表或视图中现有行的列值(UPDATE)。
  • 条件性地更新或插入行到表或视图中(MERGE)。
  • 从表或视图中删除行(DELETE)。
  • 查看SQL语句的执行计划(EXPLAIN PLAN)。参见第7-22页的“Oracle数据库如何处理DML”。
  • 锁定表或视图,暂时限制其他用户的访问(LOCK TABLE)。

以下示例使用DML查询employees表。示例使用DML向employees表插入一行,更新这行,然后删除它:

SELECT * FROM employees;
INSERT INTO employees (employee_id, last_name, email, job_id, hire_date, salary) 
VALUES (1234, 'Mascis', 'JMASCIS', 'IT_PROG', '14-FEB-2011', 9000);
UPDATE employees SET salary=9100 WHERE employee_id=1234; 
DELETE FROM employees WHERE employee_id=1234;

将一组DML语句构成一个逻辑工作单元称为事务。例如,转账事务可能涉及三个独立的操作:减少储蓄账户余额、增加支票账户余额以及在账户历史表中记录转账。与DDL语句不同,DML语句不会隐式提交当前事务。

7.2.2.1. Select

查询是从表或视图中检索数据的操作。SELECT是唯一可以用来查询数据的SQL语句。执行SELECT语句检索出的数据集被称为结果集。

表7-1显示了SELECT语句中两个必需的关键字和两个通常可以在SELECT语句中找到的关键字。该表还将SELECT语句的功能与关键字相关联。

7.2.2.2. Join

连接(join)是一种查询,它结合了两个或更多表、视图或物化视图中的行。示例7-2连接了employees和departments表(FROM子句),选择只满足特定条件的行(WHERE子句),并使用投影来从两个列中检索数据(SELECT)。SQL语句后的示例输出如下。

Example 72 Sample Join 
SELECT email, department_name 
  FROM employees 
  JOIN departments 
   ON employees.department_id = departments.department_id 
 WHERE employee_id IN (100,103) 
 ORDER BY email;
EMAIL                     DEPARTMENT_NAME
------------------------- -----------------------------
AHUNOLD                    IT 
Executive                  SKING 

图7-1以图形方式表示了示例7-2中显示的连接操作中的投影和选择操作。

大多数连接至少有一个连接条件,这个条件要么在FROM子句中,要么在WHERE子句中,用于比较来自不同表的两个列。数据库将行对组合起来,每对行包含来自每个表的一行,对于这些行,连接条件满足。评估结果为TRUE。优化器根据连接条件、索引以及表的任何可用统计信息来确定数据库连接表的顺序。连接类型包括以下:

  • 内连接(Inner joins)
    内连接是两个或更多表的连接,它只返回满足连接条件的行。例如,如果连接条件是employees.department_id = departments.department_id,那么不满足此条件的行不会被返回。

  • 外连接(Outer joins)
    外连接返回所有满足连接条件的行,并且还返回一个表中的行,即使另一个表中没有行满足条件。例如,employeesdepartments的左外连接检索employees表中的所有行,即使在departments中没有匹配项。右外连接则检索departments中的所有行,即使在employees中没有匹配项。

  • 笛卡尔积(Cartesian products)
    如果在连接查询中的两个表没有连接条件,那么数据库将返回它们的笛卡尔积。一个表的每一行与另一个表的每一行结合。例如,如果employees表有107行,departments表有27行,那么笛卡尔积将包含107*27行。笛卡尔积很少有用。

7.2.2.3. 子查询以及隐式查询

子查询是嵌套在另一个SQL语句中的SELECT语句。当您需要执行多个查询来解决一个问题时,子查询非常有用。

语句的每个查询部分称为查询块。在示例7-3中,括号中的子查询是内部查询块。内部SELECT语句检索位置ID为1800的部门的ID。这些部门ID由外部查询块需要,后者检索由子查询提供的部门ID的员工名称。

Example 73 Subquery 
SELECT first_name, last_name 
  FROM employees 
 WHERE department_id IN (SELECT department_id FROM departments WHERE location_id = 1800);

SQL语句的结构并不强制数据库首先执行内部查询。例如,数据库可以将整个查询重写为employees和departments的连接,这样子查询就永远不会单独执行。作为另一个例子,虚拟专用数据库(VPD)功能可以使用WHERE子句限制对employees的查询,这样数据库决定首先查询employees,然后获取department IDs。优化器确定检索请求行的最佳步骤顺序。

隐式查询是DML语句的一个组成部分,它在不使用子查询的情况下检索数据。没有明确包含SELECT语句的UPDATE、DELETE或MERGE语句使用隐式查询来检索要修改的行。例如,以下语句包含了对Baer记录的隐式查询:

UPDATE employees
   SET salary = salary*1.1 
 WHERE last_name = 'Baer';

唯一不一定包含查询组件的DML语句是带有VALUES子句的INSERT语句。例如,INSERT INTO TABLE mytable VALUES (1)语句在插入一行之前不检索行。

7.2.3. 事务控制语句

事务控制语句管理DML语句所做的更改,并将DML语句分组到事务中。这些语句使您能够:

  • 使事务中的更改永久化(COMMIT)。
  • 撤销自事务开始以来(ROLLBACK)或自保存点以来(ROLLBACK TO SAVEPOINT)的更改。保存点是事务上下文中用户声明的中间标记。
    注意:ROLLBACK命令结束一个事务,但ROLLBACK TO SAVEPOINT不会。
  • 设置一个可以回滚的点(SAVEPOINT)。为事务设置属性(SET TRANSACTION)。
  • 指定是在每个DML语句之后检查可延迟的完整性约束,还是在事务提交时检查(SET CONSTRAINT)。

以下示例启动了一个名为“Update salaries”的事务。示例中创建了一个保存点,更新了员工的薪水,然后将事务回滚到保存点。示例随后将薪水更新为不同的值并提交。

SET TRANSACTION NAME 'Update salaries'; 
SAVEPOINT before_salary_update; 
UPDATE employees SET salary=9100 WHERE employee_id=1234 # DML 
ROLLBACK TO SAVEPOINT before_salary_update; 
UPDATE employees SET salary=9200 WHERE employee_id=1234 # DML 
COMMIT COMMENT 'Updated salaries';

7.2.4. 会话控制语句

会话控制语句动态管理用户会话的属性。如第15-4页“Connections and Sessions”中所解释的,会话是数据库实例内存中的一个逻辑实体,代表当前用户登录到数据库的状态。会话从用户通过数据库认证开始,持续到用户断开连接或退出数据库应用程序为止。

会话控制语句使您能够:

  • 通过执行特殊功能来更改当前会话,例如启用和禁用SQL跟踪(ALTER SESSION)。
  • 为当前会话启用和禁用角色,角色是一组权限(SET ROLE)。

以下示例为会话启用SQL跟踪,然后启用当前会话中授予的所有角色,除了dw_manager:

ALTER SESSION SET SQL_TRACE = TRUE;
SET ROLE ALL EXCEPT dw_manager;

会话控制语句不会隐式提交当前事务。

7.2.5. 系统控制语句

系统控制语句更改数据库实例的属性。唯一的系统控制语句是ALTER SYSTEM。它使您能够更改诸如共享服务器的最小数量、终止会话以及执行其他系统级任务等设置。

以下是系统控制语句的示例:

  • ALTER SYSTEM SWITCH LOGFILE; 切换日志文件。
  • ALTER SYSTEM KILL SESSION '39, 23'; 终止指定的会话。

ALTER SYSTEM语句不会隐式提交当前事务。

7.2.6. 嵌入式SQL语句

嵌入式SQL语句将DDL、DML和事务控制语句嵌入到过程化语言程序中。它们与Oracle预编译器一起使用。嵌入式SQL是将SQL集成到您的程序化语言应用程序中的一种方法。另一种方法是使用过程化API,如开放数据库连接(ODBC)或Java数据库连接(JDBC)。

嵌入式SQL语句使您能够:

  • 定义、分配和释放游标(DECLARE CURSOR, OPEN, CLOSE)。
  • 指定数据库并连接到它(DECLARE DATABASE, CONNECT)。
  • 分配变量名(DECLARE STATEMENT)。
  • 初始化描述符(DESCRIBE)。
  • 指定如何处理错误和警告条件(WHENEVER)。
  • 解析并运行SQL语句(PREPARE, EXECUTE, EXECUTE IMMEDIATE)。
  • 从数据库检索数据(FETCH)。

7.3. 优化器概述

要理解Oracle数据库如何处理SQL语句,有必要了解数据库中被称为优化器(也称为查询优化器或基于成本的优化器)的部分。所有SQL语句都使用优化器来确定访问指定数据的最有效方式。

7.3.1. 优化器用途

为了执行DML语句,Oracle数据库可能需要执行许多步骤。每个步骤要么从数据库中物理地检索数据行,要么为发出该语句的用户准备这些数据行。
处理DML语句通常有多种可能的方式。例如,访问表或索引的顺序可能会有所不同。数据库用来执行语句的步骤极大地影响着该语句的执行速度。优化器生成描述可能执行方法的执行计划。
优化器通过考虑包括查询条件、可用访问路径、为系统收集的统计信息和提示在内的几个信息源,来确定哪个执行计划最为高效。对于Oracle处理的任何SQL语句,优化器执行以下操作:

  • 表达式和条件的评估:对SQL语句中使用的表达式和条件进行计算和检查,以确定它们的结果。
  • 完整性约束的检查:检查数据的完整性约束,以了解更多关于数据的信息,并基于这些元数据进行优化。
  • 语句转换:对SQL语句进行转换,以找到更有效的执行方式。
  • 优化器目标的选择:确定优化器在生成执行计划时的目标,如速度、资源使用等。
  • 访问路径的选择:选择访问数据的最佳路径,比如全表扫描、索引扫描等。
  • 连接顺序的选择:确定表连接操作的最佳顺序,以提高查询效率。

优化器生成处理查询的大多数可能方式,并为生成的执行计划中的每个步骤分配一个成本。选择成本最低的计划作为要执行的查询计划。

注意:您可以在不执行计划的情况下获取SQL语句的执行计划。只有数据库实际用来执行查询的执行计划才正确地被称为查询计划。

您可以通过设置优化器目标和为优化器收集代表性统计信息来影响优化器的选择。例如,您可以将优化器目标设置为以下之一:

  • 总吞吐量
    ALL_ROWS提示指示优化器尽可能快地将结果的最后一行传递给客户端应用程序。
  • 初始响应时间
    FIRST_ROWS提示指示优化器尽可能快地将第一行传递给客户端。

这些提示可以帮助优化器根据应用程序的需求选择不同的优化策略。例如,如果您的应用程序需要快速获取所有结果的最后一行,而不是立即显示结果,那么ALL_ROWS可能是合适的选择。相反,如果您的应用程序需要快速显示第一条记录以提高用户体验,那么FIRST_ROWS可能是更好的选择。通过这些设置,您可以调整数据库的行为,以满足特定的性能要求。

典型的最终用户交互式应用程序将从初始响应时间优化中受益,而批量模式的非交互式应用程序将从总吞吐量优化中受益。

7.3.2. 优化器的组件

优化器包含三个主要组件,如图7-2所示。

优化器的输入是已解析的查询(参见第7-16页的“SQL解析”)。优化器执行以下操作:

  1. 优化器接收已解析的查询,并根据可用的访问路径和提示生成一组潜在的计划。
  2. 优化器根据数据字典中的统计信息估计每个计划的成本。成本是一个估计值,与使用特定计划执行语句所需的预期资源使用成正比。
  3. 优化器比较计划的成本,并选择成本最低的计划,即查询计划,传递给行源生成器(参见第7-19页的“SQL行源生成”)。

7.3.2.1. 查询转换器(Query Transformer)

查询转换器(Query Transformer)的作用是确定是否有助于改变查询的形式,以便优化器能够生成更好的执行计划。查询转换器的输入是已解析的查询,它由一组查询块表示。

7.3.2.2. 估计器(Estimator)

估计器确定给定执行计划的总体成本。估计器生成三种不同类型的度量来实现这一目标:

  • 选择性:这种度量表示行集的行分数。选择性与查询谓词相关,例如 last_name='Smith',或谓词的组合。
  • 基数:这种度量表示行集中的行数。
  • 成本:这种度量表示工作单位或使用的资源。查询优化器使用磁盘I/O、CPU使用和内存使用作为工作单位。

如果存在统计信息,估计器会使用它们来计算这些度量。统计信息提高了度量准确性的程度。

7.3.2.3. 计划生成器(Plan Generator)

计划生成器尝试为提交的查询制定不同的计划,并选择成本最低的计划。优化器为每个嵌套子查询和未合并视图生成子计划,这些由单独的查询块表示。计划生成器通过尝试不同的访问路径、连接方法和连接顺序,为查询块探索各种计划。

优化器自动管理计划,并确保只使用经过验证的计划。SQL计划管理(SPM)通过仅在新计划被验证为性能优于当前计划后才使用它,从而允许控制计划的演变。诸如EXPLAIN PLAN语句这样的诊断工具使您能够查看优化器选择的执行计划。EXPLAIN PLAN显示了如果在当前会话中立即执行特定SQL查询的查询计划。其他诊断工具包括Oracle Enterprise Manager和SQL*Plus的AUTOTRACE命令。第7-20页的示例7-6显示了启用AUTOTRACE时查询的执行计划。

7.3.3. 访问路径

访问路径是数据从数据库中检索的方式。例如,使用索引的查询与不使用索引的查询有不同的访问路径。通常情况下,索引访问路径最适合检索表中一小部分行。全表扫描对于访问表的大部分数据更为高效。数据库可以使用几种不同的访问路径从表中检索数据。以下是代表性的列表:

  • 全表扫描(Full table scans)
    这种扫描读取表中的所有行,并过滤掉不符合选择条件的行。数据库顺序扫描段中的所有数据块,包括高于高水位线(high water mark)的数据块,高水位线用于分隔已使用和未使用的空间(参见第12-27页的“段空间和高水位线”)。

  • 行ID扫描(Rowid scans)
    行的行ID指定了包含该行的数据文件和数据块以及该行在块中的位置。数据库首先从语句的WHERE子句或通过索引扫描获取选定行的行ID,然后根据其行ID定位每个选定行。

  • 索引扫描(Index scans)
    这种扫描搜索索引以获取SQL语句访问的索引列值(参见第3-6页的“索引扫描”)。如果语句只访问索引的列,则Oracle数据库直接从索引中读取索引列值。

  • 簇扫描(Cluster scans)
    簇扫描用于从存储在索引表簇中的表中检索数据,其中所有具有相同簇键值的行都存储在相同的数据块中(参见第2-23页的“索引簇概览”)。数据库首先通过扫描簇索引获取选定行的行ID。Oracle数据库根据这个行ID定位行。

  • 哈希扫描(Hash scans)
    哈希扫描用于定位存储在哈希簇中的行,其中所有具有相同哈希值的行都存储在相同的数据块中(参见第2-25页的“哈希簇概览”)。数据库首先通过对语句指定的簇键值应用哈希函数来获取哈希值。然后,Oracle数据库扫描包含具有此哈希值的行的数据块。

优化器根据语句可用的访问路径和使用每种访问路径或路径组合的估计成本来选择访问路径。

7.3.4. 优化器统计信息

优化器统计信息是描述数据库及其对象细节的数据集合。这些统计信息提供了数据存储和分布的统计正确图像,优化器在评估访问路径时可以使用这些信息。

优化器统计信息包括以下几类:

  1. 表统计信息:包括表中的行数、块数和平均行长度。
  2. 列统计信息:包括列中不同值的数量、空值数量以及数据的分布情况。
  3. 索引统计信息:包括索引的叶子块数和索引级别。
  4. 系统统计信息:包括CPU和I/O的性能及利用率。

Oracle数据库会自动收集所有数据库对象的优化器统计信息,并将这些统计信息作为自动维护任务来维护。您也可以使用DBMS_STATS包手动收集统计信息。这个PL/SQL包可以修改、查看、导出、导入和删除统计信息。

优化器统计信息是为了查询优化的目的而创建的,它们存储在数据字典中。这些统计信息不应该与通过动态性能视图可见的性能统计信息混淆。

7.3.5. 优化器Hints

提示是在SQL语句中的注释,它作为对优化器的指令。有时,对特定应用程序的数据有更多了解的应用程序设计者可以选择更有效的执行SQL语句的方法。应用程序设计者可以在SQL语句中使用提示来指定应如何运行语句。

例如,假设您的交互式应用程序运行一个返回50行的查询。这个应用程序最初只获取查询的前25行以呈现给最终用户。您希望优化器生成一个计划,尽快获取前25条记录,以便用户不必被迫等待。您可以使用提示将此指令传递给优化器,如示例7-4中的SELECT语句和AUTOTRACE输出所示。

Example 74 Execution Plan for SELECT with FIRST_ROWS Hint 
SELECT /*+ FIRST_ROWS(25) */ employee_id, department_id FROM hr.employees WHERE department_id > 50;
-----------------------------------------------------------------------
| Id | Operation                    | Name              | Rows | Bytes
-----------------------------------------------------------------------
| 0 | SELECT STATEMENT              |                   | 26   | 182 
| 1 |  TABLE ACCESS BY INDEX ROWID  | EMPLOYEES         | 26   | 182 
|*2 |   INDEX RANGE SCAN            | EMP_DEPARTMENT_IX |      | 
------------------------------------------------------------------------

示例7-4中的执行计划显示,优化器选择了employees表的department_id列上的索引来查找部门ID超过50的员工的前25行。优化器使用从索引检索到的行ID来从employees表中检索记录并将其返回给客户端。通常,第一条记录的检索几乎是即时的。
示例7-5展示了相同的语句,但没有使用优化器提示。

Example 75 Execution Plan for SELECT with No Hint 
SELECT employee_id, department_id FROM hr.employees WHERE department_id > 50;
-----------------------------------------------------------------------
| Id | Operation             | Name               | Rows | Bytes | Cos
-----------------------------------------------------------------------
| 0 | SELECT STATEMENT       |                    | 50   | 350   | 
|* 1 | VIEW                  | index$_join$_001   |
|* 2 |  HASH JOIN            |                    |              |
|* 3 |   INDEX RANGE SCAN    | EMP_DEPARTMENT_IX  | 50   | 350   | 
|  4 |   INDEX FAST FULL SCAN| EMP_EMP_ID_PK      | 50   | 350   |

示例7-5中的执行计划通过连接两个索引来尽快返回所请求的记录。与示例7-4不同,示例7-5中的优化器选择对EMP_DEPARTMENT_IX进行范围扫描,以找到所有部门ID超过50的行,并将这些行放入哈希表中。然后,优化器选择读取EMP_EMP_ID_PK索引。对于此索引中的每行,它探测哈希表以找到部门ID。

在这种情况下,数据库无法在EMP_DEPARTMENT_IX的索引范围扫描完成之前将第一行返回给客户端。因此,这个生成的计划返回第一条记录的时间会更长。与示例7-4中通过索引行ID访问表的计划不同,示例7-5中的计划使用多块I/O,从而实现大量读取。这些读取使得整个结果集的最后一行能够更快地返回。

7.4. SQL处理概述

本节解释了Oracle数据库如何处理SQL语句。具体来说,本节解释了数据库处理DDL语句以创建对象、DML修改数据和查询检索数据的方式。

7.4.1. SQL处理阶段

图7-3描述了SQL处理的一般阶段:解析、优化、行源生成和执行。根据语句的不同,数据库可能会省略这些步骤中的一些。

7.4.1.1. SQL解析

如图7-3所示,SQL处理的第一阶段是解析。这一阶段涉及将SQL语句的各个部分分离成可以被其他程序处理的数据结构。数据库在应用程序的指示下解析语句,这意味着只有应用程序,而不是数据库本身,可以减少解析的次数。

当应用程序发出SQL语句时,应用程序会对数据库进行解析调用,以准备语句执行。解析调用会打开或创建一个游标,这是一个用于会话特定的私有SQL区域的句柄,它包含一个已解析的SQL语句和其他处理信息。游标和私有SQL区域位于PGA(程序全局区域)中。

在解析调用期间,数据库执行以下检查:

  1. 语法检查:确保SQL语句符合SQL语法规则。
  2. 语义检查:确保SQL语句中的引用(如表名、列名)在数据库中存在,并且用户有足够的权限进行操作。
  3. 共享池检查:查看SQL语句是否已经在共享池(Shared Pool)中存在,如果是,则可能不需要重新解析。

上述检查是为了在语句执行前识别可能出现的错误。有些错误无法通过解析来捕捉。例如,数据库可能只有在语句执行期间才会遇到死锁或数据转换错误(参见第9-16页的“锁和死锁”)。

  1. 语法检查

Oracle数据库必须检查每个SQL语句的语法有效性。违反良好格式化SQL语法规则的语句将无法通过检查。例如,以下语句因为关键字FROM被错误地拼写为FORM而失败:

SQL> SELECT * FORM employees;
SELECT * FORM employees *
ERROR at line 1: ORA-00923: FROM keyword not found where expected
  1. 语义检查

语句的语义是它的含义。因此,语义检查确定一个语句是否有意义,例如,语句中的对象和列是否存在。一个语法正确的语句可能会在语义检查中失败,如下例所示,尝试查询一个不存在的表:

SQL> SELECT * FROM nonexistent_table; SELECT * FROM nonexistent_table *
ERROR at line 1: ORA-00942: table or view does not exist
  1. 共享池检查

在解析过程中,数据库执行共享池检查,以确定是否可以跳过资源密集型的语句处理步骤。为此,数据库使用哈希算法为每个SQL语句生成一个哈希值。语句哈希值是V$SQL.SQL_ID中显示的SQL ID。

当用户提交一个SQL语句时,数据库会在共享SQL区域搜索是否存在具有相同哈希值的已解析语句。SQL语句的哈希值与以下值不同:

  • 语句的内存地址
    Oracle数据库使用SQL ID在查找表中执行键控读取。通过这种方式,数据库获得了语句的可能内存地址。

  • 语句的执行计划的哈希值
    一个SQL语句在共享池中可以有多个计划。每个计划都有不同的哈希值。如果同一个SQL ID有多个计划哈希值,那么数据库就知道这个SQL ID存在多个计划。

解析操作根据提交的语句类型和哈希检查的结果分为以下几类:

  • 硬解析
    如果Oracle数据库无法重用现有代码,则必须构建应用程序代码的新可执行版本。这个操作被称为硬解析,或库缓存未命中。数据库总是对DDL进行硬解析。在硬解析期间,数据库会多次访问库缓存和数据字典缓存,以检查数据字典。当数据库访问这些区域时,它会在所需对象上使用称为锁(latch)的序列化装置,以确保它们的定义不会改变(参见第9-25页的“锁”)。锁争用会增加语句执行时间并降低并发性。

  • 软解析
    软解析是任何不是硬解析的解析。如果提交的语句与共享池中可重用的SQL语句相同,那么Oracle数据库会重用现有代码。这种代码重用也称为库缓存命中。软解析在执行的工作量上可能有所不同。例如,配置会话共享SQL区域有时可以减少软解析中的锁定(latching)量,使它们变得更“软”。通常情况下,软解析比硬解析更可取,因为数据库跳过了优化和行源生成步骤,直接进行执行。

图7-4是专用服务器架构中UPDATE语句的共享池检查的简化表示。

如果检查确定共享池中的一个语句具有相同的哈希值,那么数据库会执行语义和环境检查,以确定这些语句是否具有相同的含义。仅仅语法相同是不够的。例如,假设两个不同的用户登录到数据库并发出以下SQL语句:

CREATE TABLE my_table ( some_col INTEGER ); 
SELECT * FROM my_table;

两个用户的SELECT语句在语法上是相同的,但是有两个不同的模式对象被命名为my_table。这种语义差异意味着第二个语句不能重用第一个语句的代码。

即使两个语句在语义上是相同的,环境差异也可能导致硬解析。在这种情况下,环境是可能影响执行计划生成的会话设置的总和,如工作区大小或优化器设置。考虑由单个用户执行的以下一系列SQL语句:

ALTER SYSTEM FLUSH SHARED_POOL; 
SELECT * FROM my_table;
ALTER SESSION SET OPTIMIZER_MODE=FIRST_ROWS; 
SELECT * FROM my_table;
ALTER SESSION SET SQL_TRACE=TRUE; 
SELECT * FROM my_table;

在前面的例子中,相同的SELECT语句在三个不同的优化器环境中执行。因此,数据库为这些语句创建了三个独立的共享SQL区域,并强制对每个语句进行硬解析。

7.4.1.2. SQL优化

如第7-10页的“优化器概述”中所解释的,查询优化是选择执行SQL语句的最有效方法的过程。数据库根据收集到的实际被访问数据的统计信息来优化查询。优化器使用行数、数据集大小和其他因素来生成可能的执行计划,为每个计划分配一个数字成本。数据库使用成本最低的计划。

数据库必须至少对每个独特的DML语句执行一次硬解析,并在这次解析期间进行优化。除非DDL包含需要优化的DML组件,如子查询,否则DDL从不进行优化。

7.4.1.3. SQL行源生成

行源生成器是接收优化器提供的最优执行计划并生成可供数据库其余部分使用的迭代计划(称为查询计划)的软件。迭代计划是由SQL虚拟机执行的二进制程序,它生成结果集。

查询计划采取一系列步骤的形式。每个步骤返回一个行集。这个集合中的行要么被下一个步骤使用,要么在最后一个步骤中返回给发出SQL语句的应用程序。

行源是执行计划中的一个步骤返回的行集以及可以迭代处理这些行的控制结构。行源可以是表、视图或连接或分组操作的结果。

行源生成器生成一个行源树,它是一个行源的集合。行源树显示以下信息:

  • 语句引用的表的顺序
  • 语句中提到的每个表的访问方法
  • 受语句中连接操作影响的表的连接方法
  • 如过滤、排序或聚合等数据操作

示例7-6显示了启用AUTOTRACE时SELECT语句的执行计划。该语句为所有以字母A开头的姓氏的员工选择姓氏、职位标题和部门名称。这个语句的执行计划是行源生成器的输出。

7.4.1.4. SQL执行

在执行过程中,SQL引擎会执行行源生成器产生的树中的每个行源。这一步是DML处理中唯一强制性的步骤。

图7-5是一个执行树,也称为解析树,它显示了行源从一个步骤到另一个步骤的流动。通常,执行中步骤的顺序与计划中的顺序相反,因此您需要从下往上阅读计划。在“操作”列中的初始空格表示层次关系。例如,如果一个操作的名称前面有两个空格,那么这个操作是一个前面有一个空格的操作的子操作。前面有一个空格的操作是SELECT语句本身的子操作。

在图7-5中,树的每个节点充当行源,这意味着执行计划的每个步骤要么从数据库检索行,要么接受一个或多个行源的行作为输入。SQL引擎执行每个行源如下:

  1. 由黑色框表示的步骤:物理地从数据库中的对象检索数据。这些步骤是访问路径,或者是从数据库检索数据的技术。

    • 第6步使用全表扫描来检索departments表的所有行。
    • 第5步使用全表扫描来检索jobs表的所有行。
    • 第4步扫描emp_name_ix索引,按顺序查找每个以字母A开头的键,并检索相应的行ID(参见第3-7页的“索引范围扫描”)。例如,与Atkinson对应的行ID是AAAPzRAAFAAAABSAAe。
    • 第3步从employees表中检索第4步返回的行ID的行。例如,数据库使用行ID AAAPzRAAFAAAABSAAe 来检索Atkinson的行。
  2. 由透明框表示的步骤:对行源进行操作。

    • 第2步执行哈希连接,接受来自第3步和第5步的行源,将第5步行源中的每一行与第3步中的相应行连接,并把结果行返回给第1步。这些步骤共同构成了执行计划的执行树,显示了从数据库中检索和处理数据以生成最终结果集的过程。例如,员工Atkinson的行与工作名称Stock Clerk相关联。
    • 第1步执行另一个哈希连接,接受来自第2步和第6步的行源,将第6步源中的每一行与其在第2步中的相应行进行连接,并将结果返回给客户端。例如,员工Atkinson的行与名为Shipping的部门相关联。

在某些执行计划中,步骤是迭代的,在其他计划中则是顺序的。示例7-6中显示的计划是迭代的,因为SQL引擎从索引到表再到客户端,然后重复这些步骤。

在执行过程中,如果数据不在内存中,数据库会将数据从磁盘读入内存。数据库还获取确保数据完整性所需的任何锁和闩,并记录SQL执行期间所做的任何更改。处理SQL语句的最后阶段是关闭游标。

7.4.2. Oracle数据库如何处理DML语句

大多数DML语句都包含一个查询组件。在查询中,执行游标将查询结果放入称为结果集的行集合中。

结果集的行可以逐行或分组提取。在提取阶段,数据库选择行,并根据查询请求对行进行排序。每次连续提取都会检索结果的下一行,直到最后一行被提取。

通常情况下,数据库无法在最后一行被提取之前确切确定查询要检索的行数。Oracle数据库根据提取调用来检索数据,因此数据库读取的行数越多,它执行的工作就越多。对于某些查询,数据库尽快返回第一行,而对于其他查询,则在返回第一行之前创建整个结果集。

7.4.2.1. 读一致性

通常,查询通过使用Oracle数据库的读取一致性机制来检索数据。这种机制使用撤消数据来显示数据的过去版本,保证查询读取的所有数据块都与时间点一致。

以读取一致性为例,假设一个查询必须在全表扫描中读取100个数据块。查询处理前10个块时,不同会话中的DML修改了第75块。当第一个会话到达第75块时,它意识到变化并使用撤消数据检索旧的、未修改的数据版本,并在内存中构建第75块的非当前版本。

这种机制确保了事务的隔离性,使得即使在并发环境下,每个查询也能够看到在查询开始时数据的一致视图,即使其他会话在查询执行期间对数据进行了修改。这种一致性视图是通过使用撤消数据来实现的,撤消数据记录了数据的先前状态,以便在需要时可以回滚到这些状态。

7.4.2.2. 数据变化

必须更改数据的DML语句使用读取一致性机制仅检索在开始修改时符合搜索条件的数据。之后,这些语句按照数据块当前状态检索数据,并进行所需的修改。数据库必须执行与修改数据相关的其他操作,如生成重做(redo)和撤消(undo)数据。

7.4.3. Oracle数据库如何处理DDL语句

Oracle数据库处理DDL的方式与DML不同。例如,当您创建表时,数据库不会优化CREATE TABLE语句。相反,Oracle数据库解析DDL语句并执行命令。

数据库以不同的方式处理DDL,因为它是定义数据字典中对象的手段。通常,Oracle数据库必须解析并执行许多递归SQL语句来执行DDL命令。假设您如下创建一个表:

CREATE TABLE mytable (mycolumn INTEGER);

通常,数据库将运行数十条递归语句来执行上述语句。递归SQL将执行以下操作:

  • 在执行CREATE TABLE语句之前发出COMMIT
  • 验证用户权限是否足以创建表
  • 确定表应该位于哪个表空间
  • 确保表空间配额没有超出
  • 确保模式中没有对象具有相同的名字
  • 将定义表的行插入数据字典
  • 如果DDL语句成功,则发出COMMIT;如果没有,则发出ROLLBACK
posted @   脆皮老弟  阅读(25)  评论(0编辑  收藏  举报
相关博文:
阅读排行:
· 微软正式发布.NET 10 Preview 1:开启下一代开发框架新篇章
· 没有源码,如何修改代码逻辑?
· PowerShell开发游戏 · 打蜜蜂
· 在鹅厂做java开发是什么体验
· WPF到Web的无缝过渡:英雄联盟客户端的OpenSilver迁移实战
点击右上角即可分享
微信分享提示