如何优化Oracle在where条件中用了自定义函

Oracle的自定义函数，提供了对逻辑的封装能力，便于我们对代码进行管理。然而当这个函数出现在where语句中，它却很可能给我们的SQL语句带来严重的效率问题。因为：

创新互联建站长期为成百上千家客户提供的网站建设服务，团队从业经验10年，关注不同地域、不同群体，并针对不同对象提供差异化的产品和服务；打造开放共赢平台，与合作伙伴共同营造健康的互联网生态环境。为临澧企业提供专业的成都做网站、成都网站制作、成都外贸网站建设，临澧网站改版等技术服务。拥有10余年丰富建站经验和众多成功案例,为您定制开发。

1、Oracle的优化器无法对函数进行优化，只能逐行执行（这就是为什么我们常发现，把函数里面的语句拷出来，就会执行得很快的原因）

2、函数并非标准SQL所包含的东西，因此Oracle在执行函数时，会频繁在SQL上下文和PL/SQL上下文之间切换。当数据量大时，就会增加CPU和内存的消耗，降低语句执行的效率。

因此，自定义函数，就像一把双刃剑一样，摆在了我们面前。对此，我们有以下几种对策：

1、不用函数

2、借用Oracle的缓存机制

3、函数索引

1）不用函数。不用函数确实能解决问题，但如果一段逻辑会被很多个SQL语句用到，这就会给我们的代码管理带来了很大的麻烦。因此这是万不得已的下下策。当然，没必要使用函数的地方，可以尽量避免。

2）借用Oracle的缓存机制。

Oracle对子查询，是会做缓存处理的。因此我们可以把函数写在一个子查询中，如把“my_func(id)”变成“(select my_func(id) from dual)”。这样做可以通过缓存，减少函数被调用的次数，从而提高效率。通常用函数对大数据量进行过滤时，此方法都可大量降低函数被调用的次数（可从Oracle的执行统计看出）

另一种方法，就是对函数强行开启结果缓存。方法是在函数的return类型之后，加上“result_cache”标记。

3）使用函数索引。首先该函数必须被标记为deterministic，即在函数的return类型之后，加上“deterministic”标记。它表示当函数的输入值是确定时，返回结果必定是唯一的。此后，在表的列上新建索引，索引列不是写列名，而是写“my_func(id)”这样即可。

通常地，我推荐先尝试“子查询缓存”这种方法。因为它不仅对函数、表本身没有作任何修改，而且效果也比较明显。我曾经试过对一条很复杂的SQL做了这种处理，结果执行效率提高了60倍之多！如果缓存效果不明显时，可以再尝试下函数索引。不过我发现同样的SQL，我只加函数索引的话，效果并不如“子查询缓存”那样立竿见影。

注意，以上提到的，都只是一些调优的手段而已，并非一定能解决问题。所以可以的话，我们还是要尽量避免把函数放在where语句中。

oracle 索引 优化隐藏器

你在哪看的，是指使用优化器吧。oracle优化器基于两个原则，一个是基于成本（CBO），一个是基于规则（RBO）。如果查询数据量在5%以内，而且有合适的索引，oracle默认是基于CBO，会选择走索引查询，提高查询速度（性能）。

当然，有时候优化器没有这么智能，可能走索引合适的，因为种种原因（比如表的统计信息不准确），没有走索引，这个时候我们可以使用oracle hints（强制索引）让优化器走RBO计划，提高性能

cbo是什么意思？

一、CBO （基于代价的优化方式）

CBO是Cost-Based Optimization的缩写，中文叫做“基于成本的优化。”

Oracle的优化器有两种优化方式,即基于规则的优化方式(Rule-Based Optimization，简称为RBO)和基于代价的优化方式(Cost-Based Optimization，简称为CBO)，在Oracle8及以后的版本,Oracle强烈推荐用CBO的方式。

二、CBO（首席品牌官）

CBO（Chief Brand Officer）首席品牌官，是现代组织（包括企业、政府或其他组织）中设置的专门负责品牌战略管理与运营的高级官员，代表CEO就企业形象、品牌以及文化进行内外部沟通。

三、CBO（国会预算办公室）

CBO是美国 国会预算办公室国会下设的一个专业的、非党派的机构，成立于1975年，没有审批权。

四、CBO （ORACLE提供的一种SQL优化器）

ORACLE提供的一种SQL优化器。

ORACLE 提供了CBO、RBO两种SQL优化器。CBO在ORACLE7 引入，但在ORACLE8i 中才成熟。ORACLE 已经明确声明在ORACLE9i之后的版本中(ORACLE 10G )，RBO将不再支持。

五、CBO（市场流通债券的再证券化）

Collateralised Bond Obligation，即市场流通债券的再证券化，是CDO（担保债务权证）的一个分支。

Oracle数据库该如何着手优化一个SQL

SQL Profile是10g中的新特性，作为自动SQL调整过程的一部分。SQL Profile是一个对象，它包含了可以帮助查询优化器为一个特定的SQL语句找到高效执行计划的信息。这些信息包括执行环境、对象统计和对查询优化器所做评估的修正信息。

它的最大优点之一就是在不修改SQL语句和会话执行环境的情况下影响查询优化器的决定。SQL Profile中包含的并非单个执行计划的信息，SQL Profile不会固定一个SQL语句的执行计划。当表的数据增长或者索引创建、删除，使用同一个SQL Profile的执行计划可能会改变，而存储在SQL Profile中的信息会继续起作用。所以，经过一段很长的时间之后，它的信息有可能会过时，需要重新生成。

oracle的执行计划中表的链接方式有几种？分别适用什么情况？

在日常基于数据库应用的开发过程中，我们经常需要对多个表或者数据源进行关联查询而得出我们需要的结果集。那么Oracle到底存在着哪几种连接方式？优化器内部又是怎样处理这些连接的？哪种连接方式又是适合哪种查询需求的？只有对这些问题有了清晰的理解后，我们才能针对特定的查询需求选择合适的连接方式，开发出健壮的数据库应用程序。选择合适的表连接方法对SQL语句运行的性能有着至关重要的影响。下面我们就Oracle常用的一些连接方法及适用情景做一个简单的介绍。\x0d\x0a3.1嵌套循环连接(nestedloop)\x0d\x0a嵌套循环连接的工作方式是这样的：\x0d\x0a1、Oracle首先选择一张表作为连接的驱动表，这张表也称为外部表（OuterTable）。由驱动表进行驱动连接的表或数据源称为内部表（InnerTable）。\x0d\x0a2、提取驱动表中符合条件的记录，与被驱动表的连接列进行关联查询符合条件的记录。在这个过程中，Oracle首先提取驱动表中符合条件的第一条记录，再与内部表的连接列进行关联查询相应的记录行。在关联查询的过程中，Oracle会持续提取驱动表中其他符合条件的记录与内部表关联查询。这两个过程是并行进行的，因此嵌套循环连接返回前几条记录的速度是非常快的。在这里需要说明的是，由于Oracle最小的IO单位为单个数据块，因此在这个过程中Oracle会首先提取驱动表中符合条件的单个数据块中的所有行，再与内部表进行关联连接查询的，然后提取下一个数据块中的记录持续地循环连接下去。当然，如果单行记录跨越多个数据块的话，就是一次单条记录进行关联查询的。\x0d\x0a3、嵌套循环连接的过程如下所示：\x0d\x0aNestedloop\x0d\x0aOuterloop\x0d\x0aInnerloop\x0d\x0a我们可以看出这里面存在着两个循环，一个是外部循环，提取驱动表中符合条件的每条记录。另外一个是内部循环，根据外循环中提取的每条记录对内部表进行连接查询相应的记录。由于这两个循环是嵌套进行的，故此种连接方法称为嵌套循环连接。\x0d\x0a嵌套循环连接适用于查询的选择性强、约束性高并且仅返回小部分记录的结果集。通常要求驱动表的记录（符合条件的记录，通常通过高效的索引访问）较少，且被驱动表连接列有唯一索引或者选择性强的非唯一索引时，嵌套循环连接的效率是比较高的。\x0d\x0a嵌套循环连接驱动表的选择也是连接中需要着重注意的一点，有一个常见的误区是驱动表要选择小表，其实这是不对的。假如有两张表A、B关联查询，A表有1000000条记录，B表有10000条记录，但是A表过滤出来的记录只有10条，这时候显然用A表当做驱动表是比较合适的。因此驱动表是由过滤条件限制返回记录最少的那张表，而不是根据表的大小来选择的。\x0d\x0a在外连接查询中，如果走嵌套循环连接的话，那么驱动表必然是没有符合条件关联的那张表，也就是后面不加(+)的那张表。这是由于外连接需要提取可能另一张表没符合条件的记录，因此驱动表需要是那张我们要返回所有符合条件记录的表。比如下面这个查询，\x0d\x0a嵌套循环连接返回前几行的记录是非常快的，这是因为使用了嵌套循环后，不需要等到全部循环结束再返回结果集，而是不断地将查询出来的结果集返回。在这种情况下，终端用户将会快速地得到返回的首批记录，且同时等待Oracle内部处理其他记录并返回。如果查询的驱动表的记录数非常多，或者被驱动表的连接列上无索引或索引不是高度可选的情况，嵌套循环连接的效率是非常低的\x0d\x0a--删除原表\x0d\x0adroptablet1;\x0d\x0a\x0d\x0a--建立测试表\x0d\x0acreatetablet1(\x0d\x0af1varchar2(10),\x0d\x0af2varchar2(1000)\x0d\x0a)\x0d\x0atablespaceCTL\x0d\x0apctfree98;\x0d\x0a\x0d\x0a--填充测试内容\x0d\x0ainsertintot1(f1,f2)\x0d\x0aselectrownum,lpad(rownum,700,'0')\x0d\x0afromdba_tablesa,dba_tab_colsb\x0d\x0awherea.owner=b.owner\x0d\x0aandrownumselect/*+ordereduse_hash(t1,t2)*/\x0d\x0at1.f1,t2.f1\x0d\x0afromctl.t1t1,ctl.t2t2\x0d\x0awheret1.f1=t2.f1234;\x0d\x0a\x0d\x0a999rowsselected.\x0d\x0a\x0d\x0aExecutionPlan\x0d\x0a----------------------------------------------------------\x0d\x0a0SELECTSTATEMENTOptimizer=CHOOSE(Cost=5Card=82Bytes=1148\x0d\x0a)\x0d\x0a10HASHJOIN(Cost=5Card=82Bytes=1148)\x0d\x0a21TABLEACCESS(FULL)OF'T1'(Cost=2Card=82Bytes=574)\x0d\x0a31TABLEACCESS(FULL)OF'T2'(Cost=2Card=82Bytes=574)\x0d\x0a\x0d\x0aStatistics\x0d\x0a----------------------------------------------------------\x0d\x0a0recursivecalls\x0d\x0a0dbblockgets\x0d\x0a11113consistentgets\x0d\x0a0physicalreads\x0d\x0a0redosize\x0d\x0a23590bytessentviaSQL*Nettoclient\x0d\x0a1381bytesreceivedviaSQL*Netfromclient\x0d\x0a68SQL*Netroundtripsto/fromclient\x0d\x0a0sorts(memory)\x0d\x0a0sorts(disk)\x0d\x0a999rowsprocessed\x0d\x0a3.3,排序合并连接(mergejoin)\x0d\x0a排序合并连接的方法非常简单。在排序合并连接中是没有驱动表的概念的，两个互相连接的表按连接列的值先排序，排序完后形成的结果集再互相进行合并连接提取符合条件的记录。相比嵌套循环连接，排序合并连接比较适用于返回大数据量的结果。\x0d\x0a排序合并连接在数据表预先排序好的情况下效率是非常高的，也比较适用于非等值连接的情况，比如、=、=)\x0d\x0a3,hash_join_enabled=false;\x0d\x0a4,数据源已排序

SQL 语句优化，该怎么处理

（1）选择最有效率的表名顺序(只在基于规则的优化器中有效)：

ORACLE的解析器按照从右到左的顺序处理FROM子句中的表名，FROM子句中写

在最后的表(基础表 driving table)将被最先处理，在FROM子句中包含多个表的

情况下,你必须选择记录条数最少的表作为基础表。如果有3个以上的表连接查询

, 那就需要选择交叉表(intersection table)作为基础表, 交叉表是指那个被其

他表所引用的表.

（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)例子：

DELETE FROM EMP E WHERE E.ROWID (SELECT MIN(X.ROWID)

FROM EMP X WHERE X.EMP_NO = E.EMP_NO);

（9）用TRUNCATE替代DELETE：

当删除表中的记录时,在通常情况下, 回滚段(rollback segments ) 用来存

放可以被恢复的信息. 如果你没有COMMIT事务,ORACLE会将数据恢复到删除之前

的状态(准确地说是恢复到执行删除命令之前的状况) 而当运用TRUNCATE时, 回

滚段不再存放任何可被恢复的信息.当命令运行后,数据不能被恢复.因此很少的

资源被调用,执行时间也会很短. (译者按: TRUNCATE只在删除全表适

用,TRUNCATE是DDL不是DML)

（10）尽量多使用COMMIT：

只要有可能,在程序中尽量多使用COMMIT, 这样程序的性能得到提高,需求也

会因为COMMIT所释放的资源而减少:

COMMIT所释放的资源:

a. 回滚段上用于恢复数据的信息.

b. 被程序语句获得的锁

c. redo log buffer 中的空间

d. ORACLE为管理上述3种资源中的内部花费

（11）用Where子句替换HAVING子句：

避免使用HAVING子句, HAVING 只会在检索出所有记录之后才对结果集进行

过滤. 这个处理需要排序,总计等操作. 如果能通过WHERE子句限制记录的数目,

那就能减少这方面的开销. (非oracle中)on、where、having这三个都可以加条

件的子句中，on是最先执行，where次之，having最后，因为on是先把不符合条

件的记录过滤后才进行统计，它就可以减少中间运算要处理的数据，按理说应该

速度是最快的，where也应该比having快点的，因为它过滤数据后才进行sum，在

两个表联接时才用on的，所以在一个表的时候，就剩下where跟having比较了。

在这单表查询统计的情况下，如果要过滤的条件没有涉及到要计算字段，那它们

的结果是一样的，只是where可以使用rushmore技术，而having就不能，在速度

上后者要慢如果要涉及到计算的字段，就表示在没计算之前，这个字段的值是不

确定的，根据上篇写的工作流程，where的作用时间是在计算之前就完成的，而

having就是在计算后才起作用的，所以在这种情况下，两者的结果会不同。在多

表联接查询时，on比where更早起作用。系统首先根据各个表之间的联接条件，

把多个表合成一个临时表后，再由where进行过滤，然后再计算，计算完后再由

having进行过滤。由此可见，要想过滤条件起到正确的作用，首先要明白这个条

件应该在什么时候起作用，然后再决定放在那里

（12）减少对表的查询：

在含有子查询的SQL语句中,要特别注意减少对表的查询.例子：

SELECT TAB_NAME FROM TABLES WHERE (TAB_NAME,DB_VER) = ( SELECT

TAB_NAME,DB_VER FROM TAB_COLUMNS WHERE VERSION = 604)

（13）通过内部函数提高SQL效率.：

复杂的SQL往往牺牲了执行效率. 能够掌握上面的运用函数解决问题的方法

在实际工作中是非常有意义的

（14）使用表的别名(Alias)：

当在SQL语句中连接多个表时, 请使用表的别名并把别名前缀于每个Column

上.这样一来,就可以减少解析的时间并减少那些由Column歧义引起的语法错误.

（15）用EXISTS替代IN、用NOT EXISTS替代NOT IN：

在许多基于基础表的查询中,为了满足一个条件,往往需要对另一个表进行联

接.在这种情况下, 使用EXISTS(或NOT EXISTS)通常将提高查询的效率. 在子查

询中,NOT IN子句将执行一个内部的排序和合并. 无论在哪种情况下,NOT IN都是

最低效的 (因为它对子查询中的表执行了一个全表遍历). 为了避免使用NOT IN

,我们可以把它改写成外连接(Outer Joins)或NOT EXISTS.

例子：

（高效）SELECT * FROM EMP (基础表) WHERE EMPNO 0 AND EXISTS (SELECT

‘X' FROM DEPT WHERE DEPT.DEPTNO = EMP.DEPTNO AND LOC = ‘MELB')

(低效)SELECT * FROM EMP (基础表) WHERE EMPNO 0 AND DEPTNO IN(SELECT

DEPTNO FROM DEPT WHERE LOC = ‘MELB')

（16）识别'低效执行'的SQL语句：

虽然目前各种关于SQL优化的图形化工具层出不穷,但是写出自己的SQL工具

来解决问题始终是一个最好的方法：

SELECT EXECUTIONS , DISK_READS, BUFFER_GETS, ROUND((BUFFER_GETS-

DISK_READS)/BUFFER_GETS,2) Hit_radio, ROUND(DISK_READS/EXECUTIONS,2)

Reads_per_run,

SQL_TEXT FROM V$SQLAREA WHERE EXECUTIONS0 AND BUFFER_GETS 0 AND

(BUFFER_GETS-DISK_READS)/BUFFER_GETS 0.8 ORDER BY 4 DESC;

（17）用索引提高效率：

索引是表的一个概念部分,用来提高检索数据的效率，ORACLE使用了一个复

杂的自平衡B-tree结构. 通常,通过索引查询数据比全表扫描要快. 当ORACLE找

出执行查询和Update语句的最佳路径时, ORACLE优化器将使用索引. 同样在联结

多个表时使用索引也可以提高效率. 另一个使用索引的好处是,它提供了主键

(primary key)的唯一性验证.。那些LONG或LONG RAW数据类型, 你可以索引几乎

所有的列. 通常, 在大型表中使用索引特别有效. 当然,你也会发现, 在扫描小

表时,使用索引同样能提高效率. 虽然使用索引能得到查询效率的提高,但是我们

也必须注意到它的代价. 索引需要空间来存储,也需要定期维护, 每当有记录在

表中增减或索引列被修改时, 索引本身也会被修改. 这意味着每条记录的INSERT

, DELETE , UPDATE将为此多付出4 , 5 次的磁盘I/O . 因为索引需要额外的存

储空间和处理,那些不必要的索引反而会使查询反应时间变慢.。定期的重构索引

是有必要的.：

ALTER INDEX INDEXNAME REBUILD TABLESPACENAME

（18）用EXISTS替换DISTINCT：

当提交一个包含一对多表信息(比如部门表和雇员表)的查询时,避免在

SELECT子句中使用DISTINCT. 一般可以考虑用EXIST替换, EXISTS 使查询更为迅

速,因为RDBMS核心模块将在子查询的条件一旦满足后,立刻返回结果. 例子：

(低效): SELECT DISTINCT DEPT_NO,DEPT_NAME FROM DEPT D , EMP E

WHERE D.DEPT_NO = E.DEPT_NO (高效): SELECT DEPT_NO,DEPT_NAME FROM DEPT

D WHERE EXISTS ( SELECT ‘X' FROM EMP E WHERE E.DEPT_NO = D.DEPT_NO);

（19） sql语句用大写的；因为oracle总是先解析sql语句，把小写的字母转换成大写的再执行

（20）在java代码中尽量少用连接符“＋”连接字符串！