当前位置：文档库 › 通过分析SQL语句的执行计划优化SQL(总结)

通过分析SQL语句的执行计划优化SQL(总结)

做DBA快7年了，中间感悟很多。在DBA的日常工作中，调整个别性能较差的SQL语句时一项富有挑战性的工

作。其中的关键在于如何得到SQL语句的执行计划和如何从SQL语句的执行计划中发现问题。总是想将日常

经验的点点滴滴总结一下，但是直到最近才下定决心，总共花了3个周末时间，才将其整理成册，便于自

己日常工作。不好意思独享，所以将其贴出来。

修改日志：

2006.02.20：

根据网友反馈已做部分修改，但pdf文件没有做修改，修改部分在“如何产生执行计划”关于set

autotraceonly的介绍部分

第一章、第2章并不是很重要，是自己的一些想法，关于如何做一个稳定、高效的应用系统的一些想法。

第三章以后都是比较重要的。

附录的内容也是比较重要的。我常用该部分的内容。

前言

本文档主要介绍与SQL调整有关的内容，内容涉及多个方面：SQL语句执行的过程、ORACLE优化器，表之间的关联，如何得到SQL执行计划，如何分析执行计划等内容，从而由浅到深的方式了解SQL优化的过程，使大家逐步步入SQL 调整之门，然后你将发现……。

该文档的不当之处，敬请指出，以便进一步改正。请将其发往我的信箱：xu_yu_jin2000@https://www.wendangku.net/doc/8312274087.html,。

如果引用本文的内容，请著名出处

第1章性能调整综述

第2章有效的应用设计

第3章SQL语句处理的过程

第4章ORACLE的优化器

第5章ORACLE的执行计划

访问路径(方法) -- access path

表之间的连接

如何产生执行计划

如何分析执行计划

如何干预执行计划- - 使用hints提示

具体案例分析

第6章其它注意事项

附录

第1章性能调整综述

Oracle数据库是高度可调的数据库产品。本章描述调整的过程和那些人员应与Oracle服务器的调整有关，

以及与调整相关联的操作系统硬件和软件。本章包括以下方面:

l 谁来调整系统?

l 什么时候调整?

l 建立有效调整的目标

l 在设计和开发时的调整

l 调整产品系统

l 监控产品系统

谁来调整系统:

为了有效地调整系统，若干类人员必须交换信息并牵涉到系统调整中，例如:

l 应用设计人员必须传达应用系统的设计，使得每个人都清楚应用中的数据流动. l 应用开发人员必须传达他们选择的实现策略，使得语句调整的过程中能快速、容易地识别有问题的应用

模块和可疑的SQL语句.

l 数据库管理人员必须仔细地监控系统活动并提供它们的资料，使得异常的系统性能可被快速得识别和纠

正.

l 硬件/软件管理人员必须传达系统的硬件、软件配置并提供它们的资料，使得相关人员能有效地设计和

管理系统。

简而言之，与系统涉及的每个人都在调整过程中起某些作用，当上面提及的那些人员传达了系统的特性并

提供了它们的资料，调整就能相对的容易和更快一些。

不幸的是，事实上的结果是：数据库管理员对调整负有全部或主要的责任。但是，数据库管理员很少有合

适的系统方面的资料，而且，在很多情况下，数据库管理员往往是在实施阶段才介入数据库，这就给调整

工作带来许多负面的影响，因为在设计阶段的缺陷是不能通过DBA的调整而得以解决，而设计阶段的缺陷

往往对数据库性能造成极大的影响。

其实，在真正成熟的开发环境下，开发人员作为纯代码编写人员时，对性能的影响最小，此时大部分的工

作应由应用设计人员完成，而且数据库管理员往往在前期的需求管理阶段就介入，为设计人员提供必要的

技术支持。

调整并不是数据库管理员的专利，相反大部分应该是设计人员和开发人员的工作，这就需要设计人员和开

发人员具体必要的数据库知识，这样才能组成一个高效的团队，然而事实上往往并非如此。

什么时候作调整？

多数人认为当用户感觉性能差时才进行调整，这对调整过程中使用某些最有效的调整策略来说往往是太迟

了。此时，如果你不愿意重新设计应用的话，你只能通过重新分配内存(调整SGA)和调整I/O的办法或多或

少地提高性能。Oracle提供了许多特性，这些特性只有应用到正确地设计的系统中时才能够很大地提高性

能。

应用设计人员需要在设计阶段设置应用的性能期望值。然后在设计和开发期间，应用设计人员应考虑哪些

Oracle 特性可以对系统有好处，并使用这些特性。

通过良好的系统设计，你就可以在应用的生命周期中消除性能调整的代价和挫折。图1-1图1-2说明在应用

的生命周期中调整的相对代价和收益，正如你见到的，最有效的调整时间是在设计阶段。在设计期间的调

整能以最低的代价给你最大的收益。

图1-1在应用生命周期中调整的代价

图1-2 在应用生命周期中调整的收益

当然，即使在设计很好的系统中，也可能有性能降低。但这些性能降低应该是可控的和可以预见的。

调整目标

不管你正在设计或维护系统，你应该建立专门的性能目标，它使你知道何时要作调整。如果你试图胡乱地

改动初始化参数或SQl 语句，你可能会浪费调整系统的时间，而且无什么大的收益。调整你的系统的最有

效方法如下：

l 当设计系统时考虑性能

l 调整操作系统的硬件和软件

l 识别性能瓶颈

l 确定问题的原因

l 采取纠正的动作

当你设计系统时，制定专门的目标；例如，响应时间小于３秒。当应用不能满足此目标时，识别造成变慢

的瓶颈（例如，I/O竞争），确定原因，采取纠正动作。在开发期间，你应测试应用研究，确定在采取应

用之前是否满足设计的性能目标。

当你正在维护生产库系统时，有多种快速有效的方法来识别性能瓶颈。

不管怎样，调整通常是一系列开销。一旦你已确定了瓶颈，你可能要牺牲一些其它方面的指标来达到所要

的结果。例如，如果I/O有问题，你可能需要更多内存或磁盘。如果不可能买，你可能要限制系统的并发

性，来获取所需的性能。然而，如果你已经明确地定义了性能的目标，那用什么来交换高性能的决策就变

的很容易的，因为你已经确定了哪些方面是最重要的，如过我的目标为高性能，可能牺牲一些空间资源。

随着应用的越来越庞大，硬件性能的提高，全面的调整应用逐渐变成代价高昂的行为，在这样情况下，要

取得最大的投入/效率之比，较好的办法是调整应用的关键部分，使其达到比较高的性能，这样从总体上

来说，整个系统的性能也是比较高的。这也就是有名的20/80原则，调整应用的20%(关键部分)，能解决

80%的问题。

在设计和开发系统时作调整

良好设计的系统可以防止在应用生命周期中产生性能问题。系统设计人员和应用开发人员必须了解Oracle

的查询处理机制以便写出高效的SQL语句。“第2章有效的应用设计”讨论了你的系统中各种可用的配置

，以及每种配置更适合哪种类型的应用。“第5章优化器”讨论了Oracle的查询优化器，以及如何写语句

以获取最快的结果。

当设计你的系统时，使用下列优化性能的准则：

l 消除客户机／服务器应用中不必要的网络传输。-- 使用存储过程。

l 使用适合你系统的相应Oracle服务器选件（例如，并行查询或分布式数据库）。l 除非你的应用有特殊的需要，否则使用缺省的Oracle锁。

l 利用数据库记住应用模块，以便你能以每个模块为基础来追踪性能。

l 选择你的数据块的最佳大小。-- 原则上来说大一些的性能较好。

l 分布你的数据，使得一个节点使用的数据本地存贮在该节点中。

调整产品系统

本节描述对应用系统快速、容易地找出性能瓶颈，并决定纠正动作的方法。这种方法依赖于对Oracle服务器体系结构和特性的了解程度。在试图调整你的系统

前，你应熟悉Oracle调整的内容。

为调整你已有的系统，遵从下列步骤：

l 调整操作系统的硬件和软件

l 通过查询V $SESSION_WAIT视图，识别性能的瓶颈，这个动态性能视图列出了造成会话(session)等待的事件。

l 通过分析V $SESSION_WAIT中的数据，决定瓶颈的原因。

l 纠正存在的问题。

监控应用系统

这主要是通过监控oracle的动态视图来完成。

各种有用的动态视图：如v$session_wait, v$session_event等。

第2章有效的应用设计

我们通常将最常用的应用分为2种类型：联机事务处理类型(OLTP)，决策支持系统(DSS)。

联机事务处理(OLTP)

该类型的应用是高吞吐量，插入、更新、删除操作比较多的系统，这些系统以不断增长的大容量数据为特

征，它们提供给成百用户同时存取，典型的OLTP系统是订票系统，银行的业务系统，订单系统。OTLP的主

要目标是可用性、速度、并发性和可恢复性。

当设计这类系统时，必须确保大量的并发用户不能干扰系统的性能。还需要避免使用过量的索引与

cluster 表，因为这些结构会使插入和更新操作变慢。

决策支持(DSS)

该类型的应用将大量信息进行提取形成报告，协助决策者作出正确的判断。典型的情况是：决策支持系统

将OLTP应用收集的大量数据进行查询。典型的应用为客户行为分析系统(超市，保险等)。

决策支持的关键目标是速度、精确性和可用性。

该种类型的设计往往与OLTP设计的理念背道而驰，一般建议使用数据冗余、大量索引、cluster table、

并行查询等。

近年来，该类型的应用逐渐与OLAP、数据仓库紧密的联系在一起，形成的一个新的应用方向。

2楼06-01-12 17:25 [ 大中小]

SunnyXu

一般会员

注册日期: 2004 Nov

来自:

技术贴数:38

精华贴数:1

论坛积分:267

论坛排名:9743

论坛徽章:0

第3章SQL语句处理的过程

在调整之前我们需要了解一些背景知识，只有知道这些背景知识，我们才能更好的去调整sql语句。

本节介绍了SQL语句处理的基本过程，主要包括：

·查询语句处理

·DML语句处理(insert, update, delete)

·DDL 语句处理(create .. , drop .. , alter .. , )

·事务控制(commit, rollback)

SQL 语句的执行过程(SQL Statement Execution)

图3-1 概要的列出了处理和运行一个sql语句的需要各个重要阶段。在某些情况下，Oracle运行sql的过程可能与下面列出的各个阶段的顺序有所不同。如DEFINE阶段可能在FETCH阶段之前，这主要依赖你如何书写代码。

对许多oracle的工具来说，其中某些阶段会自动执行。绝大多数用户不需要关心各个阶段的细节问题，然而，知道执行的各个阶段还是有必要的，这会帮助你写出更高效的SQL语句来，而且还可以让你猜测出性能差的SQL语句主要是由于哪一个阶段造成的，然后我们针对这个具体的阶段，找出解决的办法。

图3-1 SQL语句处理的各个阶段

DML语句的处理

本节给出一个例子来说明在DML语句处理的各个阶段到底发生了什么事情。

假设你使用Pro*C程序来为指定部门的所有职员增加工资。程序已经连到正确的用户，你可以在你的程序

中嵌入如下的SQL语句：

EXEC SQL UPDATE employees

SET salary = 1.10 * salary

WHERE department_id = :var_department_id;

var_department_id是程序变量，里面包含部门号，我们要修改该部门的职员的工资。当这个SQL语句执行

时，使用该变量的值。

每种类型的语句都需要如下阶段：

·第1步: Create a Cursor 创建游标

·第2步: Parse the Statement 分析语句

·第5步: Bind Any Variables 绑定变量

·第7步: Run the Statement 运行语句

·第9步: Close the Cursor 关闭游标

如果使用了并行功能，还会包含下面这个阶段：

·第6步: Parallelize the Statement 并行执行语句

如果是查询语句，则需要以下几个额外的步骤，如图3所示：

·第3步: Describe Results of a Query 描述查询的结果集

·第4步: Define Output of a Query 定义查询的输出数据

·第8步: Fetch Rows of a Query 取查询出来的行

下面具体说一下每一步中都发生了什么事情：.

第1步: 创建游标(Create a Cursor)

由程序接口调用创建一个游标（cursor）。任何SQL语句都会创建它，特别在运行DML语句时，都是自动创

建游标的，不需要开发人员干预。多数应用中，游标的创建是自动的。然而，在预编译程序(pro*c)中游

标的创建，可能是隐含的，也可能显式的创建。在存储过程中也是这样的。

第2步:分析语句(Parse the Statement)

在语法分析期间，SQL语句从用户进程传送到Oracle，SQL语句经语法分析后，SQL语句本身与分析的信息

都被装入到共享SQL区。在该阶段中，可以解决许多类型的错误。

语法分析分别执行下列操作：

l 翻译SQL语句，验证它是合法的语句，即书写正确

l 实现数据字典的查找，以验证是否符合表和列的定义

l 在所要求的对象上获取语法分析锁，使得在语句的语法分析过程中不改变这些对象的定义

l 验证为存取所涉及的模式对象所需的权限是否满足

l 决定此语句最佳的执行计划

l 将它装入共享SQL区

l 对分布的语句来说，把语句的全部或部分路由到包含所涉及数据的远程节点以上任何一步出现错误，都将导致语句报错，中止执行。

只有在共享池中不存在等价SQL语句的情况下，才对SQL语句作语法分析。在这种情况下，数据库内核重新

为该语句分配新的共享SQL区，并对语句进行语法分析。进行语法分析需要耗费较多的资源，所以要尽量

避免进行语法分析，这是优化的技巧之一。

语法分析阶段包含了不管此语句将执行多少次，而只需分析一次的处理要求。Oracle只对每个SQL语句翻

译一次，在以后再次执行该语句时，只要该语句还在共享SQL区中，就可以避免对该语句重新进行语法分

析，也就是此时可以直接使用其对应的执行计划对数据进行存取。这主要是通过绑定变量(bind

variable)实现的，也就是我们常说的共享SQL，后面会给出共享SQL的概念。

虽然语法分析验证了SQL语句的正确性，但语法分析只能识别在SQL语句执行之前所能发现的错误(如书写

错误、权限不足等)。因此，有些错误通过语法分析是抓不到的。例如，在数据转换中的错误或在数据中

的错（如企图在主键中插入重复的值）以及死锁等均是只有在语句执行阶段期间才能遇到和报告的错误或

情况。

查询语句的处理

查询与其它类型的SQL语句不同，因为在成功执行后作为结果将返回数据。其它语句只是简单地返回成功

或失败，而查询则能返回一行或许多行数据。查询的结果均采用表格形式，结果行被一次一行或者批量地

被检索出来。从这里我们可以得知批量的fetch数据可以降低网络开销，所以批量的fetch也是优化的技巧

之一。

有些问题只与查询处理相关，查询不仅仅指SELECT语句，同样也包括在其它SQL语句中的隐含查询。例如

，下面的每个语句都需要把查询作为它执行的一部分：

INSERT INTO table SELECT...

UPDATE table SET x = y WHERE...

DELETE FROM table WHERE...

CREATE table AS SELECT...

具体来说，查询

·要求读一致性

·可能使用回滚段作中间处理

·可能要求SQL语句处理描述、定义和取数据阶段

第3步: 描述查询结果(Describe Results of a Query)

描述阶段只有在查询结果的各个列是未知时才需要；例如，当查询由用户交互地输入需要输出的列名。在

这种情况要用描述阶段来决定查询结果的特征（数据类型，长度和名字）。

[/B]第4步: 定义查询的输出数据(Define Output of a Query) [/B]

在查询的定义阶段，你指定与查询出的列值对应的接收变量的位置、大小和数据类型，这样我们通过接收

变量就可以得到查询结果。如果必要的话，Oracle会自动实现数据类型的转换。这是将接收变量的类型与

对应的列类型相比较决定的。

第5步: 绑定变量(Bind Any Variables)

此时，Oracle知道了SQL语句的意思，但仍没有足够的信息用于执行该语句。Oracle 需要得到在语句中列

出的所有变量的值。在该例中，Oracle需要得到对department_id列进行限定的值。得到这个值的过程就

叫绑定变量(binding variables)

此过程称之为将变量值捆绑进来。程序必须指出可以找到该数值的变量名（该变量被称为捆绑变量，变量

名实质上是一个内存地址，相当于指针）。应用的最终用户可能并没有发觉他们正在指定捆绑变量，因为

Oracle 的程序可能只是简单地指示他们输入新的值，其实这一切都在程序中自动做了。

因为你指定了变量名，在你再次执行之前无须重新捆绑变量。你可以改变绑定变量的值，而Oracle在每次

执行时，仅仅使用内存地址来查找此值。

如果Oracle 需要实现自动数据类型转换的话（除非它们是隐含的或缺省的），你还必须对每个值指定数

据类型和长度。关于这些信息可以参考oracle的相关文档，如Oracle Call Interface Programmer's

Guide

第6步: 并行执行语句(Parallelize the Statement )

ORACLE 可以在SELECTs, INSERTs, UPDATEs, MERGEs, DELETEs语句中执行相应并行查询操作，对于某些

DDL操作，如创建索引、用子查询创建表、在分区表上的操作，也可以执行并行操作。并行化可以导致多

个服务器进程(oracle server processes)为同一个SQL语句工作，使该SQL语句可以快速完成，但是会耗

费更多的资源，所以除非很有必要，否则不要使用并行查询。

第7步: 执行语句(Run the Statement)

到了现在这个时候，Oracle拥有所有需要的信息与资源，因此可以真正运行SQL 语句了。如果该语句为

SELECT查询或INSERT语句，则不需要锁定任何行，因为没有数据需要被改变。然而，如果语句为UPDATE或

DELETE语句，则该语句影响的所有行都被锁定，防止该用户提交或回滚之前，

别的用户对这些数据进行修

改。这保证了数据的一致性。

对于某些语句，你可以指定执行的次数，这称为批处理(array processing)。指定执行N次，则绑定变量

与定义变量被定义为大小为N的数组的开始位置，这种方法可以减少网络开销，也是优化的技巧之一。

第8步: 取出查询的行(Fetch Rows of a Query)

在fetch阶段，行数据被取出来，每个后续的存取操作检索结果集中的下一行数据，直到最后一行被取出

来。上面提到过，批量的fetch是优化的技巧之一。

第9步: 关闭游标(Close the Cursor)

SQL语句处理的最后一个阶段就是关闭游标

DDL语句的处理(DDL Statement Processing)

DDL语句的执行不同与DML语句和查询语句的执行，这是因为DDL语句执行成功后需要对数据字典数据进行

修改。对于DDL语句，语句的分析阶段实际上包括分析、查找数据字典信息和执行。

事务管理语句、会话管理语句、系统管理语句只有分析与执行阶段，为了重新执行该语句，会重新分析与

执行该语句。

事务控制(Control of Transactions)

一般来说，只有使用ORACLE编程接口的应用设计人员才关心操作的类型，并把相关的操作组织在一起，形

成一个事务。一般来说，我门必须定义事务，这样在一个逻辑单元中的所有工作可以同时被提交或回滚，

保证了数据的一致性。一个事务应该由逻辑单元中的所有必须部分组成，不应该多一个，也不应该少一个

。

·在事务开始和结束的这段时间内，所有被引用表中的数据都应该在一致的状态(或可以被回溯到一致的

状态)

·事务应该只包含可以对数据进行一致更改(one consistent change to the data)的SQL语句

例如，在两个帐号之间的转帐(这是一个事务或逻辑工作单元)，应该包含从一个帐号中借钱(由一个SQL完

成)，然后将借的钱存入另一个帐号(由另一个SQL完成)。这2个操作作为一个逻辑单元，应该同时成功或

同时失败。其它不相关的操作，如向一个帐户中存钱，不应该包含在这个转帐事务中。

在设计应用时，除了需要决定哪种类型的操作组成一个事务外，还需要决定使用

BEGIN_DISCRETE_TRANSACTIO存储过程是否对提高小的、非分布式的事务的性能有作用。

3楼06-01-12 17:30 [ 大中小]

SunnyXu

一般会员

注册日期: 2004 Nov

来自:

技术贴数:38

精华贴数:1

论坛积分:267

论坛排名:9743

论坛徽章:0

第4章ORACLE的优化器

优化器有时也被称为查询优化器，这是因为查询是影响数据库性能最主要的部分，不要以为只有SELECT语

句是查询。实际上，带有任何WHERE条件的DML(INSERT、UPDATE、DELETE)语句中都包含查询要求，在后面

的文章中，当说到查询时，不一定只是指SELECT语句，也有可能指DML语句中的查询部分。优化器是所有

关系数据库引擎中的最神秘、最富挑战性的部件之一，从性能的角度看也是最重要的部分，它性能的高低

直接关系到数据库性能的好坏。

我们知道，SQL语句同其它语言(如C语言)的语句不一样，它是非过程化(non-procedural)的语句，即当你

要取数据时，不需要告诉数据库通过何种途径去取数据，如到底是通过索引取数据，还是应该将表中的每

行数据都取出来，然后再通过一一比较的方式取数据(即全表扫描)，这是由数据库的优化器决定的，这就

是非过程化的含义，也就是说，如何取数据是由优化器决定，而不是应用开发者通过编程决定。在处理

SQL的SELECT、UPDATE、INSERT或DELETE语句时，Oracle 必须访问语句所涉及的数据，Oracle的优化器部

分用来决定访问数据的有效路径，使得语句执行所需的I/O和处理时间最小。

为了实现一个查询，内核必须为每个查询定制一个查询策略，或为取出符合条件的数据生成一个执行计划

(execution plan)。典型的，对于同一个查询，可能有几个执行计划都符合要求，都能得到符合条件的数

据。例如，参与连接的表可以有多种不同的连接方法，这取决于连接条件和优化器采用的连接方法。为了

在多个执行计划中选择最优的执行计划，优化器必须使用一些实际的指标来衡量每个执行计划使用的资源

(I/0次数、CPU等)，这些资源也就是我们所说的代价(cost)。如果一个执行计划使用的资源多，我们就说

使用执行计划的代价大。以执行计划的代价大小作为衡量标准，优化器选择代价最小的执行计划作为真正

执行该查询的执行计划，并抛弃其它的执行计划。

在ORACLE的发展过程中，一共开发过2种类型的优化器：基于规则的优化器和基于代价的优化器。这2种优

化器的不同之处关键在于：取得代价的方法与衡量代价的大小不同。现对每种优化器做一下简单的介绍：

基于规则的优化器-- Rule Based (Heuristic) Optimization(简称RBO)：

在ORACLE7之前，主要是使用基于规则的优化器。ORACLE在基于规则的优化器中采用启发式的方法

(Heuristic Approach)或规则(Rules)来生成执行计划。例如，如果一个查询的where 条件(where clause)

包含一个谓词(predicate，其实就是一个判断条件，如”=”, “>”, ”<”等)，而且该谓词上引用的列

上有有效索引，那么优化器将使用索引访问这个表，而不考虑其它因素，如表中数据的多少、表中数据的

易变性、索引的可选择性等。此时数据库中没有关于表与索引数据的统计性描述，如表中有多上行，每行

的可选择性等。优化器也不考虑实例参数，如multi block i/o、可用排序内存的大小等，所以优化器有

时就选择了次优化的计划作为真正的执行计划，导致系统性能不高。

如，对于

select * from emp where deptno = 10;

这个查询来说，如果是使用基于规则的优化器，而且deptno列上有有效的索引，则会通过deptno列上的索

引来访问emp表。在绝大多数情况下，这是比较高效的，但是在一些特殊情况下，使用索引访问也有比较

低效的时候，现举例说明：

1) emp表比较小，该表的数据只存放在几个数据块中。此时使用全表扫描比使用索引访问emp表反而要好

。因为表比较小，极有可能数据全在内存中，所以此时做全表扫描是最快的。而

如果使用索引扫描，需要

先从索引中找到符合条件记录的rowid，然后再一一根据这些rowid从emp中将数据取出来，在这种条件下

，效率就会比全表扫描的效率要差一些。

2) emp表比较大时，而且deptno = 10条件能查询出表中大部分的数据如(50%)。如该表共有4000万行数据

，共放在有500000个数据块中，每个数据块为8k，则该表共有约4G，则这么多的数据不可能全放在内存中

，绝大多数需要放在硬盘上。此时如果该查询通过索引查询，则是你梦魇的开始。

db_file_multiblock_read_count参数的值200。如果采用全表扫描，则需要

500000/db_file_multiblock_read_count=500000/200=2500次I/O。但是如果采用索引扫描，假设deptno

列上的索引都已经cache到内存中，所以可以将访问索引的开销忽略不计。因为要读出4000万x 50% =

2000万数据，假设在读这2000万数据时，有99.9%的命中率，则还是需要20000次I/O,比上面的全表扫描需

要的2500次多多了，所以在这种情况下，用索引扫描反而性能会差很多。在这样的情况下，用全表扫描的

时间是固定的，但是用索引扫描的时间会随着选出数据的增多使查询时间相应的延长。

上面是枯燥的假设数据，现在以具体的实例给予验证：

环境: oracle 817 + linux + 阵列柜，表SWD_BILLDETAIL有3200多万数据；

表的id列、cn列上都有索引

经查看执行计划，发现执行select count(id) from SWD_BILLDETAIL;使用全表扫描，执行完用了大约

1.50分钟(4次执行取平均，每次分别为1.45 1.51

2.00 1.46)。而执行select count(id) from

SWD_BILLDETAIL where cn <'6';却用了2个小时还没有执行完，经分析该语句使用了cn列上的索引，然后

利用查询出的rowid再从表中查询数据。我为什么不使用select count(cn) from SWD_BILLDETAIL where

cn <'6';呢？后面在分析执行路径的索引扫描时时会给出说明。

下面就是基于规则的优化器使用的执行路径与各个路径对应的等级：

RBO Path 1: Single Row by Rowid(等级最高)

RBO Path 2: Single Row by Cluster Join

RBO Path 3: Single Row by Hash Cluster Key with Unique or Primary Key

RBO Path 4: Single Row by Unique or Primary Key

RBO Path 5: Clustered Join

RBO Path 6: Hash Cluster Key

RBO Path 7: Indexed Cluster Key

RBO Path 8: Composite Index

RBO Path 9: Single-Column Indexes

RBO Path 10: Bounded Range Search on Indexed Columns

RBO Path 11: Unbounded Range Search on Indexed Columns

RBO Path 12: Sort Merge Join

RBO Path 13: MAX or MIN of Indexed Column

RBO Path 14: ORDER BY on Indexed Column

RBO Path 15: Full Table Scan(等级最低)

上面的执行路径中，RBO认为越往下执行的代价越大，即等级越低。在RBO生成执行计划时，如果它发现有

等级高的执行路径可用，则肯定会使用等级高的路径，而不管任何其它影响性能的元素，即RBO通过上面

的路径的等级决定执行路径的代价，执行路径的等级越高，则使用该执行路径的代价越小。如上面2个例

子所述，如果使用RBO，则肯定使用索引访问表，也就是选择了比较差的执行计划，这样会给数据库性能

带来很大的负面影响。为了解决这个问题，从ORACLE 7开始oracle引入了基于代价的优化器，下面给出了

介绍。

基于代价的优化器-- Cost Based Optimization(简称CBO)

Oracle把一个代价引擎(Cost Engine)集成到数据库内核中，用来估计每个执行计划需要的代价，该代价

将每个执行计划所耗费的资源进行量化，从而CBO可以根据这个代价选择出最

优的执行计划。一个查询耗

费的资源可以被分成3个基本组成部分：I/O代价、CPU代价、network代价。I/O代价是将数据从磁盘读入

内存所需的代价。访问数据包括将数据文件中数据块的内容读入到SGA的数据高速缓存中，在一般情况下

，该代价是处理一个查询所需要的最主要代价，所以我们在优化时，一个基本原则就是降低查询所产生的

I/O总次数。CPU代价是处理在内存中数据所需要的代价，如一旦数据被读入内存，则我们在识别出我们需

要的数据后，在这些数据上执行排序(sort)或连接(join)操作，这需要耗费CPU资源。

对于需要访问跨节点(即通常说的服务器)数据库上数据的查询来说，存在network 代价，用来量化传输操

作耗费的资源。查询远程表的查询或执行分布式连接的查询会在network代价方面花费比较大。

在使用CBO时，需要有表和索引的统计数据(分析数据)作为基础数据，有了这些数据，CBO才能为各个执行

计划计算出相对准确的代价，从而使CBO选择最佳的执行计划。所以定期的对表、索引进行分析是绝对必

要的，这样才能使统计数据反映数据库中的真实情况。否则就会使CBO选择较差的执行计划，影响数据库

的性能。分析操作不必做的太频繁，一般来说，每星期一次就足够了。切记如果想使用CBO，则必须定期

对表和索引进行分析。

对于分析用的命令，随着数据库版本的升级，用的命令也发生了变换，在oracle 8i以前，主要是用

ANALYZE命令。在ORACLE 8I以后，又引入了DBMS_STATS存储包来进行分析。幸运的是从ORACLE 10G以后，

分析工作变成自动的了，这减轻的DBA的负担，不过在一些特殊情况下，还需

要一些手工分析。

如果采用了CBO优化器，而没有对表和索引进行分析，没有统计数据，则ORACLE使用缺省的统计数据(至少

在ORACLE 9I中是这样)，这可以从oracle的文档上找到。使用的缺省值肯定与系统的实际统计值不一致，

这可能会导致优化器选择错误的执行计划，影响数据库的性能。

要注意的是：虽然CBO的功能随着ORACLE新版本的推出，功能越来越强，但它不是能包治百病的神药，否

则就不再需要DBA了，那我就惨了！！！实际上任何一个语句，随着硬件环境与应用数据的不同，该语句

的执行计划可能需要随之发生变化，这样才能取得最好的性能。所以有时候不在具体的环境下而进行SQL

性能调整是徒劳的。

在ORACLE8I推出的时候，ORACLE极力建议大家使用CBO，说CBO有种种好处，但是在那是ORACLE开发的应用

系统还是使用基于规则的优化器，从这件事上我们可以得出这样的结论：1) 如果团队的数据库水平很高

而且都熟悉应用数据的特点，RBO也可以取得很好的性能。2）CBO不是很稳定，但是一个比较有前途的优

化器，Oracle极力建议大家用是为了让大家尽快发现它的BUG，以便进一步改善，但是ORACLE为了对自己

开发的应用系统负责，他们还是使用了比较熟悉而且成熟的RBO。从这个事情上给我们的启发就是：我们

在以后的开发中，应该尽量采用我们熟悉并且成熟的技术，而不要一味的采用新技术，一味采用新技术并

不一定能开发出好的产品。幸运的是从ORACLE 10G后，CBO已经足够的强大与智能，大家可以放心的使用

该技术，因为ORACLE 10G后，Oracle自己开发的应用系统也使用CBO优化器了。而且ORACLE规定，从

ORACLE 10G开始，开始废弃RBO优化器。这句话并不是指在ORACLE 10G 中不能使用RBO，而是从ORACLE 10G

开始开始，不再为RBO的BUG提供修补服务。

在上面的第2个例子中，如果采用CBO优化器，它就会考虑emp表的行数，deptno 列的统计数据，发现对该

列做查询会查询出过多的数据，并且考虑db_file_multiblock_read_count参数的设置，发现用全表扫描

的代价比用索引扫描的代价要小，从而使用全表扫描从而取得良好的执行性能。

判断当前数据库使用何种优化器：

主要是由optimizer_mode初始化参数决定的。该参数可能的取值为：first_rows_[1 | 10 | 100 | 1000]

| first_rows | all_rows | choose | rule。具体解释如下：

RULE为使用RBO优化器。

CHOOSE则是根据实际情况，如果数据字典中包含被引用的表的统计数据，即引用的对象已经被分析，则就

使用CBO优化器，否则为RBO优化器。

ALL_ROWS为CBO优化器使用的第一种具体的优化方法，是以数据的吞吐量为主要目标，以便可以使用最少

的资源完成语句。

FIRST_ROWS为优化器使用的第二种具体的优化方法，是以数据的响应时间为主要目标，以便快速查询出开

始的几行数据。

FIRST_ROWS_[1 | 10 | 100 | 1000] 为优化器使用的第三种具体的优化方法，让优化器选择一个能够把

响应时间减到最小的查询执行计划，以迅速产生查询结果的前n 行。该参数为ORACLE 9I新引入的。

从ORACLE V7以来，optimizer_mode参数的缺省设置应是"choose"，即如果对已分析的表查询的话选择CBO

，否则选择RBO。在此种设置中，如果采用了CBO，则缺省为CBO中的all_rows 模式。

2021年SQL语句大全实例

SQL语句实例欧阳光明(2021.03. 07) 表操作例1对于表的教学管理数据库中的表STUDENTS ,可以定义如下： CREATE TABLE STUDENTS (SNO NUMERIC (6, 0) NOT NULL SNAME CHAR (8) NOT NULL AGE NUMERIC(3,0) SEX CHAR(2) BPLACE CHAR(20) PRIMARY KEY(SNO)) 例2对于表的教学管理数据库中的表ENROLLS ,可以定义如下：CREATE TABLE ENROLLS (SNO NUMERIC(6,0) NOT NULL CNO CHAR(4) NOT NULL GRADE INT PRIMARY KEY(SNO,CNO) FOREIGN KEY(SNO) REFERENCES STUDENTS(SNO) FOREIGN KEY(CNO) REFERENCES COURSES(CNO)

*欧阳光明*创编2021.03.07 CHECK ((GRADE IS NULL) OR (GRADE BETWEEN 0 AND 100))) 例3根据表的STUDENTS表，建立一个只包含学号.姓名.年龄的女学生表。 CREATE TABLE GIRL AS SELECT SNO, SNAME, AGE FROM STUDENTS WHERE SEX=女?; 例4删除教师表TEACHER。 DROP TABLE TEACHER 例5在教师表中增加住址列。 ALTER TABLE TEACHERS ADD (ADDR CHAR(50)) 例6把STUDENTS表中的BPLACE列删除，并且把引用 BPLACE列的所有视图和约束也一起删除。 ALTER TABLE STUDENTS DROP BPLACE CASCADE 例7补充定义ENROLLS表的主关键字。 ALTER TABLE ENROLLS ADD PRIMARY KEY (SNO,CNO);

在程序开发中怎样写SQL语句可以提高数据库的性能

1、首先要搞明白什么叫执行计划? 执行计划是数据库根据SQL语句和相关表的统计信息作出的一个查询方案，这个方案是由查询优化器自动分析产生的，比如一条SQL语句如果用来从一个10万条记录的表中查1条记录，那查询优化器会选择“索引查找”方式，如果该表进行了归档，当前只剩下5000条记录了，那查询优化器就会改变方案，采用“全表扫描”方式。可见，执行计划并不是固定的，它是“个性化的”。产生一个正确的“执行计划”有两点很重要： (1) SQL语句是否清晰地告诉查询优化器它想干什么? (2) 查询优化器得到的数据库统计信息是否是最新的、正确的? 2、统一SQL语句的写法对于以下两句SQL语句，程序员认为是相同的，数据库查询优化器认为是不同的。 select * from dual Select * From dual 其实就是大小写不同，查询分析器就认为是两句不同的SQL语句，必须进行两次解析。生成2个执行计划。所以作为程序员，应该保证相同的查询语句在任何地方都一致，多一个空格都不行! 3、不要把SQL语句写得太复杂我经常看到，从数据库中捕捉到的一条SQL语句打印出来有2张A4纸这么长。一般来说这么复杂的语句通常都是有问题的。我拿着这2页长的SQL语句去请教原作者，结果他说时间太长，他一时也看不懂了。可想而知，连原作者都有可能看糊涂的SQL语句，数据库也一样会看糊涂。一般，将一个Select语句的结果作为子集，然后从该子集中再进行查询，这种一层嵌套语句还是比较常见的，但是根据经验，超过3层嵌套，查询优化器就很容易给出错误的执行计划。因为它被绕晕了。像这种类似人工智能的东西，终究比人的分辨力要差些，如果人都看晕了，我可以保证数据库也会晕的。另外，执行计划是可以被重用的，越简单的SQL语句被重用的可能性越高。而复杂的SQL语句只要有一个字符发生变化就必须重新解析，

SQL学习总结

SQL学习 2．1 SQL 简介当面对一个陌生的数据库是，通常需要一种方式与它进行交换，以完成用户所需要的各种工作，这个时候，就要用到SQL语言了，由于SQL 语言的标准化，所以大多数关系型数据库系统都支持SQL语言，它已经发展成多种平台进行交互操作的底层会话语言。 2．2 SQL 使用入门 2．2．1 SQL分类 SQL 语句主要可以划分为以下3个类别 1）、DDL (Data Definition Languages)语句：数据定义语言，这些语句定义了不同的数据段、数据库、表、列、索引等数据库对象的定义。常用的语句关键字主要包括create、drop、alter等。 2）、DML(Data Manipulation Languagr)语句：数据操作语句，用于添加、删除、更新和查询数据库记录，并检查数据完整性。常用的语句关键字主要包括insert、delete、update和select等。 3）、DCL(Data Control Language)语句：数据控制语句，用于控制不同数据段直接的许可和访问级别的语句。这些语句定义了数据库、表、字段、用户的访问权限和安全级别、主要的语句关键字包括grant、revoke等。 2．2．2 DDL 语句 DDL是数据定义语言的缩写，是对数据库内部的对象进行创建（create）、删除（drop）、修改（alter）的操作语言，它和DML语句的最大区别是DML只是对表内部数据的操作，而不涉及表定义，结构的修改，更不会涉及其它的对象，DDL语句更多地被数据库管理员（DBA）所使用，一般开发人员很少使用。下面通过一些例子来介绍MySQL中常用的DDL语句的使用方法。 1、创建数据库因为所有的数据都存储在数据库中，因此需要学习的第一个命令是创建数据库，语法如下：

精典的SQL语句

精典的SQL语句ffice ffice" /> 1. 行列转换--普通假设有张学生成绩表(CJ)如下 Name Subject Result 张三语文80 张三数学90 张三物理85 李四语文85 李四数学92 李四物理82 想变成姓名语文数学物理张三80 90 85 李四85 92 82 declare @sql var char(4000) set @sql = 'select Name' select @sql = @sql + ',sum(case Subject when '''+Subject+''' then Res ult end) ['+Subject+']' from (select distinct Subject from CJ) as a select @sql = @sql+' from test group by name' exec(@sql) 2. 行列转换--合并有表A, id pid 1 1 1 2 1 3 2 1 2 2 3 1 如何化成表B BR>id pid 1 1,2,3 2 1,2 3 1 创建一个合并的函数 create function fmerg(@id int) returns var char(8000) as begin declare @str var char(8000) set @str=''

select @str=@str+','+cast(pid as var char) from 表A where id=@id se t @str=right(@str,len(@str)-1) return(@str) End go --调用自定义函数得到结果 select distinct id,dbo.fmerg(id) from 表A 3. 如何取得一个数据表的所有列名方法如下：先从SYSTEMOBJECT系统表中取得数据表的SYSTEMID,然后再SYSCOL UMN表中取得该数据表的所有列名。 SQL语句如下： declare @objid int,@objname char(40) set @objname = 'tablename' select @objid = id from sysobjects where id = object_id(@objname) select 'Column_name' = name from syscolumns where id = @objid order b y colid 是不是太简单了？呵呵不过经常用阿. 4. 通过SQL语句来更改用户的密码修改别人的,需要sysadmin role EXEC sp_password NULL, 'newpassword', 'User' 如果帐号为SA执行EXEC sp_password NULL, 'newpassword', sa 5. 怎么判断出一个表的哪些字段不允许为空？ select COLUMN_NAME from INFORMATION_SCHEMA.COLUMNS where IS_NULLABLE= 'NO' and TABLE_NAME=tablename 6. 如何在数据库里找到含有相同字段的表？ a. 查已知列名的情况 SELECT https://www.wendangku.net/doc/8312274087.html, as TableName,https://www.wendangku.net/doc/8312274087.html, as columnname From syscolumns a INNER JOIN sysobjects b ON a.id=b.id AND b.type='U' AND https://www.wendangku.net/doc/8312274087.html,='你的字段名字' b. 未知列名查所有在不同表出现过的列名 Select https://www.wendangku.net/doc/8312274087.html, As tablename,https://www.wendangku.net/doc/8312274087.html, As columnname From syscolumns s1, sysobjects o Where s1.id = o.id And o.type = 'U' And Exists ( Select 1 From syscolumns s2 Where https://www.wendangku.net/doc/8312274087.html, = https://www.wendangku.net/doc/8312274087.html, And s1.id <> s2.id ) 7. 查询第xxx行数据假设id是主键： select *

数据库_经典SQL语句大全

一、基础 1、说明：创建数据库 CREATE DATABASE database-name 2、说明：删除数据库 drop database dbname 3、说明：备份sql server --- 创建备份数据的 device USE master EXEC sp_addumpdevice 'disk', 'testBack', 'c:\mssql7backup\MyNwind_1. dat' --- 开始备份 BACKUP DATABASE pubs TO testBack 4、说明：创建新表 create table tabname(col1 type1 [not null] [primary key],col2 type2 [not null],..) 根据已有的表创建新表： A：create table tab_new like tab_old (使用旧表创建新表) B：create table tab_new as select col1,col2… from tab_old definition only 5、说明：删除新表 drop table tabname 6、说明：增加一个列 Alter table tabname add column col type 注：列增加后将不能删除。DB2中列加上后数据类型也不能改变，唯一能改变的是增加varchar类型的长度。 7、说明：添加主键：Alter table tabname add primary key(col) 说明：删除主键： Alter table tabname drop primary key(col) 8、说明：创建索引：create [unique] index idxname on tabname(col….)删除索引：drop index idxname 注：索引是不可更改的，想更改必须删除重新建。 9、说明：创建视图：create view viewname as select statement 删除视图：drop view viewname 10、说明：几个简单的基本的sql语句选择：select * from table1 where 范围插入：insert into table1(field1,field2) values(value1,value2) 删除：delete from table1 where 范围更新：update table1 set field1=value1 where 范围查找：select * from table1 where field1 like ’%value1%’ ---like的语法很精妙，查资料! 排序：select * from table1 order by field1,field2 [desc] 总数：select count as totalcount from table1 求和：select sum(field1) as sumvalue from table1

sql语句(mysql优化)绝对经典

sql语句（mysql优化）绝对经典误区1：count(1)和count(primary_key) 优于count(*) 很多人为了统计记录条数，就使用count(1) 和count(primary_key) 而不是count(*) ，他们认为这样性能更好，其实这是一个误区。对于有些场景，这样做可能性能会更差，应为数据库对count(*) 计数操作做了一些特别的优化。误区2：count(column) 和count(*) 是一样的这个误区甚至在很多的资深工程师或者是DBA 中都普遍存在，很多人都会认为这是理所当然的。实际上，count(column) 和count(*) 是一个完全不一样的操作，所代表的意义也完全不一样。count(column) 是表示结果集中有多少个column字段不为空的记录，count(*) 是表示整个结果集有多少条记录误区3：select a,b from … 比select a,b,c from …可以让数据库访问更少的数据量这个误区主要存在于大量的开发人员中，主要原因是对数据库的存储原理不是太了解。实际上，大多数关系型数据库都是按照行(row)的方式存储，而数据存取操作都是以一个固定大小的IO单元(被称作block 或者page)为单位，一般为4KB，8KB… 大多数时候，每个IO单元中存储了多行，每行都是存储了该行的所有字段(lob等特殊类型字段除外)。所以，我们是取一个字段还是多个字段，实际上数据库在表中需要访问的数据量其实是一样的。当然，也有例外情况，那就是我们的这个查询在索引中就可以完成，也就是说当只取a,b两个字段的时候，不需要回表，而c这个字段不在使用的索引中，需要回表取得其数据。在这样的情况下，二者的IO量会有较大差异。(覆盖索引) 误区4：order by 一定需要排序操作我们知道索引数据实际上是有序的，如果我们的需要的数据和某个索引的顺序一致，而且我们的查询又通过这个索引来执行，那么数据库一般会省略排序操作，而直接将数据返回，因为数据库知道数据已经满足我们的排序需求了。实际上，利用索引来优化有排序需求的SQL，是一个非常重要的优化手段。延伸阅读：MySQL ORDER BY 的实现分析，MySQL 中GROUP BY 基本实现原理以及MySQL DISTINCT 的基本实现原理。（order by null）

SQL语句--Group By总结

1 SQL语句--Group By总结 1. Group By 语句简介： Group By语句从英文的字面意义上理解就是“根据(by)一定的规则进行分组(Group)”。它的作用是通过一定的规则将一个数据集划分成若干个小的区域，然后针对若干个小区域进行数据处理。 P.S. 这里真是体会到了一个好的命名的力量，Group By从字面是直接去理解是非常好理解的。恩，以后在命名的环节一定要加把劲:)。话题扯远了。 2. Group By 的使用：上面已经给出了对Group By语句的理解。基于这个理解和SQL Server 2000的联机帮助，下面对Group By语句的各种典型使用进行依次列举说明。 2.1 Group By [Expressions]: 这个恐怕是Group By语句最常见的用法了，Group By + [分组字段](可以有多个)。在执行了这个操作以后，数据集将根据分组字段的值将一个数据集划分成各个不同的小组。比如有如下数据集，其中水果名称(FruitName)和出产国家(ProductPlace)为联合主键： FruitName ProductPlace Price Apple China $1.1 Apple Japan $2.1 Apple USA $2.5 Orange China $0.8 Banana China

$3.1 Peach USA $3.0 如果我们想知道每个国家有多少种水果，那么我们可以通过如下SQL语句来完成： SELECTCOUNT(*)AS水果种类,ProductPlaceAS出产国 FROMT_TEST_FRUITINFO GROUPBYProductPlace 这个SQL语句就是使用了Group By + 分组字段的方式，那么这句SQL语句就可以解释成“我按照出产国家(ProductPlace)将数据集进行分组，然后分别按照各个组来统计各自的记录数量。”很好理解对吧。这里值得注意的是结果集中有两个返回字段，一个是ProductPlace(出产国), 一个是水果种类。如果我们这里水果种类不是用Count(*)，而是类似如下写法的话： SELECTFruitName,ProductPlaceFROMT_TEST_FRUITINFOGROUPBYProductPlace 那么SQL在执行此语句的时候会报如下的类似错误：选择列表中的列'T_TEST_FRUITINFO.FruitName'无效，因为该列没有包含在聚合函数或GROUPBY子句中。这就是我们需要注意的一点，如果在返回集字段中，这些字段要么就要包含在Group By语句的后面，作为分组的依据；要么就要被包含在聚合函数中。我们可以将Group By操作想象成如下的一个过程，首先系统根据SELECT 语句得到一个结果集，如最开始的那个水果、出产国家、单价的一个详细表。然后根据分组字段，将具有相同分组字段的记录归并成了一条记录。这个时候剩下的那些不存在于Group By语句后面作为分组依据的字段就有可能出现多个值，但是目前一种分组情况只有一条记录，一个数据格是无法放入多个数值的，所以这里就需要通过一定的处理将这些多值的列转化成单值，然后将其放在对应的数据格中，那么完成这个步骤的就是聚合函数。这就是为什么这些函数叫聚合函数(aggregate functions)了。 2.2 Group By All [expressions] ： Group By All + 分组字段, 这个和前面提到的Group By [Expressions]的形式多了一个关键字ALL。这个关键字只有在使用了where语句的，且where条件筛选掉了一些组的情况才可以看出效果。在SQL Server 2000的联机帮助中，对于Group By All是这样进行描述的：

SQL查询语句大全集锦(超经典)

SQL查询语句大全集锦 MYSQL查询语句大全集锦一、简单查询简单的Transact-SQL查询只包括选择列表、FROM子句和WHERE子句。它们分别说明所查询列、查询的表或视图、以及搜索条件等。例如，下面的语句查询testtable表中姓名为“张三”的nickname字段和email字段。复制内容到剪贴板代码:SELECT `nickname`,`email`FROM `testtable`WHERE `name`='张三' (一) 选择列表选择列表(select_list)指出所查询列，它可以是一组列名列表、星号、表达式、变量(包括局部变量和全局变量)等构成。 1、选择所有列例如，下面语句显示testtable表中所有列的数据：复制内容到剪贴板代码:SELECT * FROM testtable 2、选择部分列并指定它们的显示次序查询结果集合中数据的排列顺序与选择列表中所指定的列名排列顺序相同。例如：复制内容到剪贴板代码:SELECT nickname,email FROM testtable 3、更改列标题在选择列表中，可重新指定列标题。定义格式为：列标题=列名列名列标题如果指定的列标题不是标准的标识符格式时，应使用引号定界符，例如，下列语句使用汉字显示列标题：

复制内容到剪贴板代码:SELECT 昵称=nickname,电子邮件=email FROM testtable 4、删除重复行 SELECT语句中使用ALL或DISTINCT选项来显示表中符合条件的所有行或删除其中重复的数据行，默认为ALL。使用DISTINCT选项时，对于所有重复的数据行在SELECT返回的结果集合中只保留一行。 5、限制返回的行数使用TOP n [PERCENT]选项限制返回的数据行数，TOP n说明返回n行，而TOP n PERCENT时，说明n是表示一百分数，指定返回的行数等于总行数的百分之几。例如：复制内容到剪贴板代码:SELECT TOP 2 * FROM `testtable` 复制内容到剪贴板代码:SELECT TOP 20 PERCENT * FROM `testtable` (二) FROM子句 FROM子句指定SELECT语句查询及与查询相关的表或视图。在FROM子句中最多可指定256个表或视图，它们之间用逗号分隔。在FROM子句同时指定多个表或视图时，如果选择列表中存在同名列，这时应使用对象名限定这些列所属的表或视图。例如在usertable和citytable表中同时存在cityid列，在查询两个表中的cityid时应使用下面语句格式加以限定：复制内容到剪贴板代码:SELECT `username`,citytable.cityid FROM `usertable`,`citytable` WHERE usertable.cityid=citytable.cityid在FROM子句中可用以下两种格式为表或视图指定别名：复制内容到剪贴板代码:表名 as 别名表名别名例如上面语句可用表的别名格式表示为：复制内容到剪贴板

2020年(Oracle管理)如何优化SQL语句以提高Oracle执行效率

（Oracle管理）如何优化SQL语句以提高Oracle执行效率

（1）选择最有效率的表名顺序(只在基于规则的优化器中有效)： Oracle的解析器按照从右到左的顺序处理FROM子句中的表名，FROM子句中写在最后的表(基础表drivingtable)将被最先处理，在FROM子句中包含多个表的情况下,你必须选择记录条数最少的表作为基础表。如果有3个以上的表连接查询,那就需要选择交叉表(intersectiontable)作为基础表,交叉表是指那个被其他表所引用的表。（2）WHERE子句中的连接顺序： Oracle采用自下而上的顺序解析WHERE子句,根据这个原理,表之间的连接必须写在其他WHERE条件之前,那些可以过滤掉最大数量记录的条件必须写在WHERE子句的末尾。（3）SELECT子句中避免使用‘*’： Oracle在解析的过程中,会将‘*’依次转换成所有的列名,这个工作是通过查询数据字典完成的,这意味着将耗费更多的时间。（4）减少访问数据库的次数： Oracle在内部执行了许多工作:解析SQL语句,估算索引的利用率,绑定变量,读数据块等。（5）在SQL*Plus,SQL*Forms和Pro*C中重新设置ARRAYSIZE参数,可以增加每次数据库访问的检索数据量,建议值为200。（6）使用DECODE函数来减少处理时间：使用DECODE函数可以避免重复扫描相同记录或重复连接相同的表。（7）整合简单,无关联的数据库访问：如果你有几个简单的数据库查询语句,你可以把它们整合到一个查询中(即使它们之间没有关系)。（8）删除重复记录：最高效的删除重复记录方法(因为使用了ROWID)例子：DELETEFROMEMPEWHEREE.ROWID>(SELECTMIN(X.ROWID)

SQL语句从大到小排序

根据下面三个关系模式完成下面习题：答案已设为白色需要就全选设为黑色学生表student 第一章课件：编写基本的sql语句。 1．查询所有学生情况。 3．查询所有学生的姓名，性别以及年龄。 5．查询所有学生10年后的年龄。 7．查询所有课程（列名用中文显示）。 9．查看竟有那些学生选课（重复学号显示一次）。 11．显示课程表的边结构。第二章课件：约束和排序数据。 01．查询计算机系的所有学生的姓名和年龄。 02．查询体育课的学分。 03．查询年龄小于18的学生。 04．查询年龄大于20的学生。 05．查询年龄介于18和20之间的学生（包括18和20）。 06．查询年龄不在18和20之间的学生。 07．查询年龄为18，20，22的学生。 08．查询年龄不是18，20，22的学生。 09．查询所有姓张的学生。 10．查询所有没有先行课的课程。 11．查询有先行课的课程。 12．在计算机系中找，姓张的男生。 13．在计算机系中找，姓张的或者姓李的男生并且按照年龄从大到小排序。 14．查询所有学生信息，显示结果先按系从大到小排序，再按年龄排序。第三章课件：多表查询 1．查询每个学生（学号）选了哪门课（课程）得了多少分 2．查询每个学生（姓名）选了哪门课（课程号）得了多少分 3．查询每个学生（姓名）选了哪门课（课程名）得了多少分 4．查询一下王林选可哪门课得了多少分。 5．查询每个学生的成绩类别（优、良还是及格）。 6．查询哪个学生没有选课（用外查询）。 7．查询哪门课没有人选（用外查询）。第四章课件：组函数

1．查询一下所有课程的平均分，最高分，最低分和总分数。 2．查询一下有多少个学生参加选课。 3．查询一下计算机系有多少人过20岁。 4．统计一下计算机系的男生多少人。 5．查询一下每个学生考试的最高分和最低分。 6．查询每门课（课程号）的最高分和最底分。 7．查询每门课（课程名）的最高分和最底分。 8．查询计算机系中男生多少人，女生多少人。 9，查询人数在三百人以上的系。 10．查询选修人数在三人（包括三人）的课程（课程名）。 11．查询各科考试成绩最低的同学。 12．查询考试成绩小于所选课程平均分的人。（有能力的同学选做）第五章课件：子查询 1．查询所有比王林大的同学信息。 2．查询和王林同在一个系的所有学生信息。 3．查询一下谁的成绩（所有成绩）最低。 4．查询一下每门课成绩最底的同学（要姓名，和成绩）。 5．查询一下哪个学生没有选课（用子查询）。 6．查询一下哪门课没有人选（用子查询）。 7．查询一下和王林一个系，但是比他年龄大的同学。第六章课件：ddl语句 1．创建以上四个表，要求每个表必须有主键，表和表之间必须有外间关联。 3．写出insert语句，给表添加以上数据。 5．提交所有操作。 7．将王林的年龄设置为空。 9．将张大民调到计算机系。 11．将体育课的学分设置成和管理学学分一样（update 中带有子查询）。 13．回滚所有操作。 9．某公司印了一批充值卡，卡的密码是随机生成的，现在出现这个问题：卡里面的“o和0”（哦和零）“i和1”（哎和一），用户反映说看不清楚，公司决定，把存储在数据

常用经典SQL语句大全完整版教学文案

常用经典S Q L语句大全完整版

常用经典SQL语句大全完整版--详解+实例下列语句部分是Mssql语句，不可以在access中使用。 SQL分类： DDL—数据定义语言(CREATE，ALTER，DROP，DECLARE) DML—数据操纵语言(SELECT，DELETE，UPDATE，INSERT) DCL—数据控制语言(GRANT，REVOKE，COMMIT，ROLLBACK) 首先,简要介绍基础语句： 1、说明：创建数据库 CREATE DATABASE database-name 2、说明：删除数据库 drop database dbname 3、说明：备份sql server --- 创建备份数据的 device USE master EXEC sp_addumpdevice ’disk’, ’testBack’, ’c:\mssql7backup\MyNwind_1.dat’--- 开始备份

BACKUP DATABASE pubs TO testBack 4、说明：创建新表 create table tabname(col1 type1 [not null] [primary key],col2 type2 [not null],..) 根据已有的表创建新表： A：create table tab_new like tab_old (使用旧表创建新表) B：create table tab_new as select col1,col2… from tab_old definition only 5、说明：删除新表：drop table tabname 6、说明：增加一个列：Alter table tabname add column col type 注：列增加后将不能删除。DB2中列加上后数据类型也不能改变，唯一能改变的是增加varchar类型的长度。 7、说明：添加主键：Alter table tabname add primary key(col) 说明：删除主键：Alter table tabname drop primary key(col) 8、说明：创建索引：create [unique] index idxname on tabname(col….)

如何优化sql语句

如何优化sql语句.txt心态决定状态，心胸决定格局，眼界决定境界。当你的眼泪忍不住要流出来的时候，睁大眼睛，千万别眨眼，你会看到世界由清晰到模糊的全过程。2010-01-18 honglove (高级程序员) 1、查询时不返回不需要的行、列业务代码要根据实际情况尽量减少对表的访问行数，最小化结果集，在查询时，不要过多地使用通配符如：select * from table1语句，要用到几列就选择几列，如：select col1,col2 from table1;在可能的情况下尽量限制结果集行数如：select top 100 col1,col2,col3 from talbe2,因为某些情况下用户是不需要那么多的数据的。 2、合理使用EXISTS, NOT EXISTS字句如下所示： SELECT SUM(T1.C1) FROM T1 WHERE ((SELECT COUNT(*) FROM T2 WHERE T2.C2=T1.C2)>0) SELECT SUM(T1.C1) FROM T1 WHERE EXISTS(SELECT * FROM T2 WHERE T2.C2=T1.C2) 两种产生相同的结果，但是后者的效率显然要高过于前者。银行后者不会产生大量锁定的表扫描或是索引扫描。经常需要些一个T_SQLL语句比较一个父结果集和子结果集，从而找到是否存在在父结果集中有而在子结果集中乜嘢的记录，如： SELECT _a.hdr_key FROM hdr_tb1 a -----------tb1 a 表示tb1用别名a代替 WHERE NOT EXISTS (SELECT * FROM dt1_tb1 b WHERE a.hdr_key = b.hdr_key) SELECT _a.hdr_key FROM hdr_tb1 a -----------tb1 a 表示tb1用别名a代替 LEFT JION dt1_tb1 b ON a.hdr_key = b.hdr_key WHERE b.hdr_key IS NULL SELECT hdr_key FROM hdr_tb1

基础：如何编写优化的sql语句

一.SQL的优化器执行分析在ORACLE RDBMS SERVER软件的内部，对于SQL语句的执行有一个优化器（OPTIMIZER）对SQL语句的执行进行优化。在我们使用后面介绍的工具对SQL的执行路径进行查看的时候，系统显示出来的是由优化器给出的执行路径的解释方案，如果对优化器的解释方案不了解的话，就无法针对出现的问题进行SQL语句的调整。现把ORACLE8提供的优化器的执行解释方案公布如下。这部分内容的详细解释可以参照oracle的文档。这里给出的是快速参考。实际上，这些操作的含义往往名字上就可以表现出来，不用查手册。 1.如何看SQL解释方案 Execution Plan ---------------------------------------------------------- 1SELECT STATEMENT Optimizer=CHOOSE (Cost=94 Card=1) 2SORT (AGGREGATE) 3COUNT (STOPKEY) 4INDEX (FULL SCAN) OF 'PK_TBI_TM' (UNIQUE) (Cost=94 Car d=27164) （图1）图1为ORACLE对语句“select count(*) from tbi_tm where rownum<10”给出的一个执行的解释方案，那我们该如何看这个方案呢？我们在看这个执行方案的时候要遵循一个原则“由里到外，由高到低”，同时“由里到外”不能违反“由高到低”的原则。因此上述的语句的执行步骤顺序为：4、3、2；假设存在一个步骤5的话，而且它的层次同3一致，那么，上述语句的执行步骤顺序就会改为：4、3、5、2。在图1中还给出了执行了何种操作（例如：INDEX（FULL SCAN），COUNT (STOPKEY)等，具体的说明在下面进行解释），同时在最后还给出了执

50个经典sql语句总结

一个项目涉及到的50个Sql语句(整理版) --1.学生表 Student(S,Sname,Sage,Ssex) --S 学生编号,Sname 学生姓名,Sage 出生年月,Ssex 学生性别--2.课程表 Course(C,Cname,T) --C --课程编号,Cname 课程名称,T 教师编号 --3.教师表 Teacher(T,Tname) --T 教师编号,Tname 教师姓名 --4.成绩表 SC(S,C,score) --S 学生编号,C 课程编号,score 分数 */ --创建测试数据 create table Student(S varchar(10),Sname nvarchar(10),Sage datetime,Ssex nvarchar(10)) insert into Student values('01' , N'赵雷' , '1990-01-01' , N'男') insert into Student values('02' , N'钱电' , '1990-12-21' , N'男') insert into Student values('03' , N'孙风' , '1990-05-20' , N'男') insert into Student values('04' , N'李云' , '1990-08-06' , N'男') insert into Student values('05' , N'周梅' , '1991-12-01' , N'女') insert into Student values('06' , N'吴兰' , '1992-03-01' , N'女') insert into Student values('07' , N'郑竹' , '1989-07-01' , N'女') insert into Student values('08' , N'王菊' , '1990-01-20' , N'女') create table Course(C varchar(10),Cname nvarchar(10),T varchar(10)) insert into Course values('01' , N'语文' , '02') insert into Course values('02' , N'数学' , '01') insert into Course values('03' , N'英语' , '03') create table Teacher(T varchar(10),Tname nvarchar(10)) insert into Teacher values('01' , N'张三') insert into Teacher values('02' , N'李四') insert into Teacher values('03' , N'王五') create table SC(S varchar(10),C varchar(10),score decimal(18,1)) insert into SC values('01' , '01' , 80) insert into SC values('01' , '02' , 90) insert into SC values('01' , '03' , 99) insert into SC values('02' , '01' , 70) insert into SC values('02' , '02' , 60) insert into SC values('02' , '03' , 80) insert into SC values('03' , '01' , 80) insert into SC values('03' , '02' , 80) insert into SC values('03' , '03' , 80) insert into SC values('04' , '01' , 50) insert into SC values('04' , '02' , 30) insert into SC values('04' , '03' , 20) insert into SC values('05' , '01' , 76) insert into SC values('05' , '02' , 87)

数据库经典SQL语句大全

数据库经典SQL语句大全篇一：经典SQL语句大全下列语句部分是Mssql语句，不可以在access中使用。 SQL分类： DDL—数据定义语言(CREATE，ALTER，DROP，DECLARE) DML—数据操纵语言(SELECT，DELETE，UPDATE，INSERT) DCL—数据控制语言(GRANT，REVOKE，COMMIT，ROLLBACK) 首先，简要介绍基础语句： 1、说明：创建数据库 CREATE DATABASE database-name 2、说明：删除数据库 drop database dbname 3、说明：备份sql server --- 创建备份数据的 device USE master EXEC sp_addumpdevice 'disk'，'testBack'， 'c:mssql7backupMyNwind_1.dat' --- 开始备份 BACKUP DATABASE pubs TO testBack 4、说明：创建新表

create table tabname(col1 type1 [not null] [primary key]，col2 type2 [not null]，..) 根据已有的表创建新表： A：create table tab_new like tab_old (使用旧表创建新表) B：create table tab_new as select col1，col2? from tab_old definition only 5、说明：删除新表： tabname 6、说明：增加一个列：Alter table tabname add column col type 注：列增加后将不能删除。DB2中列加上后数据类型也不能改变，唯一能改变的是增加varchar类型的长度。 7、说明：添加主键：Alter table tabname add primary key(col) 说明：删除主键：Alter table tabname drop primary key(col) 8、说明：创建索引：create [unique] index idxname on tabname(col?.) 删除索引：drop index idxname 注：索引是不可更改的，想更改必须删除重新建。