oracle数据库查询原理及优化.doc

张静懿

5页

1次

2025-04-27

免费下载

Oracle

语句提高查询效率的方法

：

.. where column in(select * from ... where ...);

：

... where exists (select 'X' from ...where ...);

第二种格式要远比第一种格式的效率高。在

Oracle

中可以几乎将所有的

操作符子查询改

写为使用

EXISTS

的子查询

使用

EXISTS

，

Oracle

系统会首先检查主查询，然后运行子查询直到它找到第一个匹配项，

这就节省了时间

Oracle

系统在执行

子查询时，首先执行子查询，并将获得的结果列表存放在在一个加了

索引的临时表中

避免使用

having

字句

避免使用

HAVING

子句

, HAVING

只会在检索出所有记录之后才对结果集进行过滤

这个处

理需要排序

总计等操作

如果能通过

WHERE

子句限制记录的数目

那就能减少这方面的开

销。

SQL Select

语句完整的执行顺序：

、

from

子句组装来自不同数据源的数据；

、

where

子句基于指定的条件对记录行进行筛选；

、

group by

子句将数据划分为多个分组；

、使用聚集函数进行计算；

、使用

having

子句筛选分组；

、计算所有的表达式；

、使用

order by

对结果集进行排序。

例：

update tablename set columnName=colunName+1,columnNameBa group by

在

select

语句中可以使用

group by

子句将行划分成较小的组，然后，使用聚组函数返回每

一个组的汇总信息，另外，可以使用

having

子句限制返回的结果集。

group by

子句可以将

查询结果分组，并返回行的汇总信息

Oracle

按照

group by

子句中指定的表达式的值分组查

询结果。在带有

group by

子句的查询语句中，在

select

列表中指定的列要么是

group by

子

句中指定的列，要么包含聚组函数

select max(sal),job emp group by job;(

注意

max(sal),job

的

job

并非一定要出现，但有意义

)

查询语句的

select

和

group by ,having

子句是聚组函数唯一

出现的地方，在

where

子句中不能使用聚组函数。

select deptno,sum(sal) from emp where

sal>1200 group by deptno having sum(sal)>8500 order by deptno;

当在

gropu by

子句中使用

having

子句时，查询结果中只返回满足

having

条件的组。在一个

sql

语句中可以有

where

子句和

having

子句。

having

与

where

子句类似，均用于设置限定条

件

where

子句的作用是在对查询结果进行分组前，将不符合

where

条件的行去掉，即在分组之

前过滤数据，条件中不能包含聚组函数，使用

where

条件显示特定的行。

having

子句的作用是筛选满足条件的组，即在分组之后过滤数据，条件中经常包含聚组函

数，使用

having

条件显示特定的组，也可以使用多个分组标准进行分组。

查询每个部门的每种职位的雇员数

select deptno,job,count(*) from emp group by deptno,job;

Oracle SQL

性能优化技巧

选用适合的

ORACLE

优化器

ORACLE

的优化器共有

种

、

RULE (

基于规则

) b

、

COST (

基于成本

) c

、

CHOOSE (

选择性

)

设置缺省的优化器，可以通过对

init.ora

文件中

OPTIMIZER_MODE

参数的各种声明，

如

RULE

，

COST

，

CHOOSE

，

ALL_ROWS

，

FIRST_ROWS

。你当然也在

SQL

句级或是会

话

(session)

级对其进行覆盖。

为了使用基于成本的优化器

(CBO

，

Cost-Based Optimizer)

，

你必须经常运行

analyze

命

令，以增加数据库中的对象统计信息

(object statistics)

的准确性。

如果数据库的优化器模式设置为选择性

(CHOOSE)

，那么实际的优化器模式将和是否运

行过

analyze

命令有关。如果

table

已经被

analyze

过，

优化器模式将自动成为

CBO

，

反之，

数据库将采用

RULE

形式的优化器。

在缺省情况下，

ORACLE

采用

CHOOSE

优化器，为了避免那些不必要的全表扫描

(full table

scan)

，你必须尽量避免使用

CHOOSE

优化器，而直接采用基于规则或者基于成本的优化

器。

访问

Table

的方式

ORACLE

采用两种访问表中记录的方式：

、

全表扫描

全表扫描就是顺序地访问表中每条记录。

ORACLE

采用一次读入多个数据块

(database

block)

的方式优化全表扫描。

、

通过

ROWID

访问表

你可以采用基于

ROWID

的访问方式情况，提高访问表的效率，

ROWID

包含了表中

记录的物理位置信息。

ORACLE

采用索引

(INDEX)

实现了数据和存放数据的物理位置

(ROWID)

之间的联系。通常索引提供了快速访问

ROWID

的方法，因此那些基于索引列的

查询就可以得到性能上的提高。

共享

SQL

语句

为了不重复解析相同的

SQL

语句，在第一次解析之后，

ORACLE

将

SQL

语句存放在内

存中。这块位于系统全局区域

SGA(system global area)

的共享池

(shared buffer pool)

中的内存

可以被所有的数据库用户共享。因此，当你执行一个

SQL

语句

(

有时被称为一个游标

)

时，

如果它和之前的执行过的语句完全相同，

ORACLE

就能很快获得已经被解析的语句以及最

好的执行路径。

ORACLE

的这个功能大大地提高了

SQL

的执行性能并节省了内存的使用。

Oracle

语句提高查询效率的方法

：

.. where column in(select * from ... where ...);

：

... where exists (select 'X' from ...where ...);

第二种格式要远比第一种格式的效率高。在

Oracle

中可以几乎将所有的

操作符子查询改

写为使用

EXISTS

的子查询

使用

EXISTS

，

Oracle

系统会首先检查主查询，然后运行子查询直到它找到第一个匹配项，

这就节省了时间

Oracle

系统在执行

子查询时，首先执行子查询，并将获得的结果列表存放在在一个加了

索引的临时表中

避免使用

having

字句

避免使用

HAVING

子句

, HAVING

只会在检索出所有记录之后才对结果集进行过滤

这个处

理需要排序

总计等操作

如果能通过

WHERE

子句限制记录的数目

那就能减少这方面的开

销。

SQL Select

语句完整的执行顺序：

、

from

子句组装来自不同数据源的数据；

、

where

子句基于指定的条件对记录行进行筛选；

、

group by

子句将数据划分为多个分组；

、使用聚集函数进行计算；

、使用

having

子句筛选分组；

、计算所有的表达式；

、使用

order by

对结果集进行排序。

例：

update tablename set columnName=colunName+1,columnNameBa group by

在

select

语句中可以使用

group by

子句将行划分成较小的组，然后，使用聚组函数返回每

一个组的汇总信息，另外，可以使用

having

子句限制返回的结果集。

group by

子句可以将

查询结果分组，并返回行的汇总信息

Oracle

按照

group by

子句中指定的表达式的值分组查

询结果。在带有

group by

子句的查询语句中，在

select

列表中指定的列要么是

group by

子

句中指定的列，要么包含聚组函数

select max(sal),job emp group by job;(

注意

max(sal),job

的

job

并非一定要出现，但有意义

)

查询语句的

select

和

group by ,having

子句是聚组函数唯一

出现的地方，在

where

子句中不能使用聚组函数。

select deptno,sum(sal) from emp where

sal>1200 group by deptno having sum(sal)>8500 order by deptno;

当在

gropu by

子句中使用

having

子句时，查询结果中只返回满足

having

条件的组。在一个

sql

语句中可以有

where

子句和

having

子句。

having

与

where

子句类似，均用于设置限定条

件

where

子句的作用是在对查询结果进行分组前，将不符合

where

条件的行去掉，即在分组之

前过滤数据，条件中不能包含聚组函数，使用

where

条件显示特定的行。

having

子句的作用是筛选满足条件的组，即在分组之后过滤数据，条件中经常包含聚组函

数，使用

having

条件显示特定的组，也可以使用多个分组标准进行分组。

查询每个部门的每种职位的雇员数

select deptno,job,count(*) from emp group by deptno,job;

Oracle SQL

性能优化技巧

选用适合的

ORACLE

优化器

ORACLE

的优化器共有

种

、

RULE (

基于规则

) b

、

COST (

基于成本

) c

、

CHOOSE (

选择性

)

设置缺省的优化器，可以通过对

init.ora

文件中

OPTIMIZER_MODE

参数的各种声明，

如

RULE

，

COST

，

CHOOSE

，

ALL_ROWS

，

FIRST_ROWS

。你当然也在

SQL

句级或是会

话

(session)

级对其进行覆盖。

为了使用基于成本的优化器

(CBO

，

Cost-Based Optimizer)

，

你必须经常运行

analyze

命

令，以增加数据库中的对象统计信息

(object statistics)

的准确性。

如果数据库的优化器模式设置为选择性

(CHOOSE)

，那么实际的优化器模式将和是否运

行过

analyze

命令有关。如果

table

已经被

analyze

过，

优化器模式将自动成为

CBO

，

反之，

数据库将采用

RULE

形式的优化器。

在缺省情况下，

ORACLE

采用

CHOOSE

优化器，为了避免那些不必要的全表扫描

(full table

scan)

，你必须尽量避免使用

CHOOSE

优化器，而直接采用基于规则或者基于成本的优化

器。

访问

Table

的方式

ORACLE

采用两种访问表中记录的方式：

、

全表扫描

全表扫描就是顺序地访问表中每条记录。

ORACLE

采用一次读入多个数据块

(database

block)

的方式优化全表扫描。

、

通过

ROWID

访问表

你可以采用基于

ROWID

的访问方式情况，提高访问表的效率，

ROWID

包含了表中

记录的物理位置信息。

ORACLE

采用索引

(INDEX)

实现了数据和存放数据的物理位置

(ROWID)

之间的联系。通常索引提供了快速访问

ROWID

的方法，因此那些基于索引列的

查询就可以得到性能上的提高。

共享

SQL

语句

为了不重复解析相同的

SQL

语句，在第一次解析之后，

ORACLE

将

SQL

语句存放在内

存中。这块位于系统全局区域

SGA(system global area)

的共享池

(shared buffer pool)

中的内存

可以被所有的数据库用户共享。因此，当你执行一个

SQL

语句

(

有时被称为一个游标

)

时，

如果它和之前的执行过的语句完全相同，

ORACLE

就能很快获得已经被解析的语句以及最

好的执行路径。

ORACLE

的这个功能大大地提高了

SQL

的执行性能并节省了内存的使用。

of 5

免费下载

数据库

关注

评论