ORACLE表连接方式的分析与优化-tony
数据仓库环境ORACLE表连接方式的分析与优化a摘要:针对在数据仓库环境下,由于超大数据量的处理而产生的效率问题，本文深入分析了ORACLE表的几种连接方式、特点、适用范围,以及对于如何使用和优化做了详细的探讨。
关键字：数据仓库 ORACLE 表连接一引言数据仓库技术是目前已知的比较成熟和被广泛采用的解决方案，用于整和电信运营企业内部所有分散的原始业务数据，并通过便捷有效的数据访问手段，可以支持企业内部不同部门，不同需求，不同层次的用户随时获得自己所需的信息。
数据仓库系统需要能够及时地追踪和分析大量的历史数据，并能够及时做出分析和预测，因此实时性是一个非常重要的指标。
ORACLE由于可靠性、高性能等方面的特点，在电信行业大部分的数据仓库系统中担当了后台数据库的角色。
由于电信行业的特点，处理的数据量十分庞大，处理的时间长。
尤其是对于大表之间的关联操作，有的大表的记录数达到数亿条，处理时间更是漫长，这成为影响数据库运行效率的主要因素。
因此，对于数据库的性能优化相当重要。
性能优化是个很大的课题，需要综合考虑，从服务器、磁盘、网络、ORACLE实例、ORACLE SQL等多方面着手。
本文着重分析ORACLE SQL优化中对于系统性能影响极大的表连接方式、特点、适用范围，并对如何使用和优化做了详细的探讨。
·二表的连接表的连接是指在一个SQL语句中通过表与表之间的关联，从一个或多个表检索出相关的数据。
连接是通过SQL语句中FROM从句的多个表名，以及WHERE从句里定义的表之间的连接条件来实现的。
如果一个SQL语句的关联表超过两个，那么连接的顺序如何呢？ORACLE首先连接其中的两个表，产生一个结果集；然后将产生的结果集与下一个表再进行关联；继续这个过程，直到所有的表都连接完成；最后产生所需的数据。
下面都以两个表的连接为例create table user_info(user_name char(10),user_id char(10));create table dev_info(dev_no char(10),user_id char(10),dev_type char(10));说明和分析表的各种连接方式。
ORACLE 从6的版本开始，优化器使用4种不同的表的连接方式：嵌套循环连接（NESTED LOOP JOIN）群集连接 (CLUSTER JOIN)排序合并连接（SORT MERGE JOIN）笛卡尔连接 (CARTESIAN JOIN)ORACLE 7.3中，新增加了哈希连接(HASH JOIN)。
在ORACLE 8中，新增加了索引连接(INDEX JOIN)。
这六种连接方式都有其独特的技术特点，在一定的条件下，可以充分发挥高效的性能。
但是也都有其局限性，如果使用不当，不仅不能提高效率，反而会严重影响系统的性能。
因此，深入地探讨连接方式的内部运行机制对于性能优化是必要的。
1 嵌套循环连接嵌套循环连接的内部处理的流程：1）Oracle 优化器根据基于规则RBO或基于成本CBO的原则，选择两个表中的一个作为驱动表，并指定其为外部表。
2）Oracle 优化器再将另外一个表指定为内部表。
3）Oracle从外部表中读取第一行,然后和内部表中的数据逐一进行对比，所有匹配的记录放在结果集中。
4）Oracle读取外部表中的第二行，再和内部表中的数据逐一进行对比，所有匹配的记录添加到结果集中。
5）重复上述步骤，直到外部表中的所有纪录全部处理完。
6）最后产生满足要求的结果集。
通过查询SQL语句的执行计划可以看出哪个表是外部表，哪个为内部表。
如 select er_name,b.dev_nofrom user_info a, dev_info bwhere er_id = er_id;的执行计划:SELECT STATEMENT Optimizer=CHOOSENESTED LOOPSTABLE ACCESS (FULL) OF 'USER_INFO'TABLE ACCESS (FULL) OF 'DEV_INFO'使用嵌套循环连接是一种从结果集中提取第一批记录最快速的方法。
在驱动行源表(就是正在查找的记录)较小、或者内部行源表已连接的列有惟一的索引或高度可选的非惟一索引时, 嵌套循环连接效果是比较理想的。
嵌套循环连接比其他连接方法有优势,它可以快速地从结果集中提取第一批记录,而不用等待整个结果集完全确定下来。
这样,在理想情况下,终端用户就可以通过查询屏幕查看第一批记录,而在同时读取其他记录。
不管如何定义连接的条件或者模式,任何两行记录源可以使用嵌套循环连接,所以嵌套循环连接是非常灵活的。
然而,如果内部行源表(读取的第二张表)已连接的列上不包含索引,或者索引不是高度可选时, 嵌套循环连接效率是很低的。
如果驱动表的记录非常庞大时,其他的连接方法可能更加有效。
可以通过在SQL 语句中添加HINTS,强制ORACLE 优化器产生嵌套循环连接的执行计划。
select /*+ use_nl(a b) */ er_name,b.dev_nofrom user_info a, dev_info bwhere er_id = er_id; 2 群集连接（CLUSTER JOIN ）群集连接实际上是嵌套循环连接的一种特例。
如果所连接的两张源表是群集中的表，即两张表属于同一个段（SEGMENT ）,，那么ORACLE 能够使用群集连接。
处理的过程是：ORACLE 从第一张行源表中读取第一行，然后在第二张行源表中使用CLUSTER 索引查找能够匹配到的纪录；继续上面的步骤处理行源表中的第二行，直到所有的记录全部处理完。
群集连接的效率极高，因为两个参加连接的行源表实际上处于同一个物理块上。
但是，群集连接也有其限制，没有群集的两个表不可能用群集连接。
所以，群集连接实际上很少使用。
3 排序合并连接（SORT MERGE JOIN ）排序合并连接内部处理的流程：1) 优化器判断第一个源表是否已经排序，如果已经排序，则到第3步，否则 上面的表是外部表，即驱动表 下面的表是内部表到第2步。
2)第一个源表排序3)优化器判断第二个源表是否已经排序，如果已经排序，则到第5步，否则到第4步。
4)第二个源表排序5)已经排过序的两个源表进行合并操作，并生成最终的结果集。
在缺乏数据的选择性或者可用的索引时,或者两个源表都过于庞大(所选的数据超过表记录数的5%)时,排序合并连接将比嵌套循环连更加高效。
排列合并连接需要比较大的临时内存块,以用于排序，这将导致在临时表空间占用更多的内存和磁盘I/O。
select er_name,b.dev_nofrom user_info a, dev_info bwhere er_id > er_id;Plan--------------------------------------------------SELECT STATEMENT Optimizer=CHOOSE (Cost=7 Card=336 Bytes=16128)MERGE JOIN (Cost=7 Card=336 Bytes=16128)SORT (JOIN) (Cost=4 Card=82 Bytes=1968)TABLE ACCESS (FULL) OF 'USER_INFO' (Cost=2 Card=82 Bytes=1968)SORT (JOIN) (Cost=4 Card=82 Bytes=1968)TABLE ACCESS (FULL) OF 'DEV_INFO' (Cost=2 Card=82 Bytes=1968) 可以通过在SQL语句中添加HINTS,强制ORACLE优化器产生排序合并连接的执行计划。
select /*+ use_merge(a b) */ er_name,b.dev_nofrom user_info a, dev_info bwhere er_id > er_id;排序合并连接是基于RBO的。
4 笛卡尔连接(CARTESIAN JOIN)笛卡尔连接是指在sql语句中没有写出表连接的条件，优化器把第一个表的每一条记录和第二个表的所有纪录相连接。
如果第一个表的纪录数为m, 第二个表的纪录数为m,则会产生m*n条纪录数。
下面的查询，未指名连接条件，就会产生笛卡尔连接。
select er_name,b.dev_nofrom user_info a ,dev_info b;由于笛卡尔连接会导致性能很差的SQL,因此一般也很少用到。
5 哈希连接当内存能够提供足够的空间时,哈希(HASH)连接是Oracle优化器通常的选择。
哈希连接中，优化器根据统计信息，首先选择两个表中的小表，在内存中建立这张表的基于连接键的哈希表；优化器再扫描表连接中的大表，将大表中的数据与哈希表进行比较，如果有相关联的数据，则将数据添加到结果集中。
当表连接中的小表能够完全cache到可用内存的时候，哈希连接的效果最佳。
哈希连接的成本只是两个表从硬盘读入到内存的成本。
但是，如果哈希表过大而不能全部cache到可用内存时，优化器将会把哈希表分成多个分区，再将分区逐一cache到内存中。
当表的分区超过了可用内存时，分区的部分数据就会临时地写到磁盘上的临时表空间上。
因此，分区的数据写磁盘时，比较大的区间（EXTENT）会提高I/O性能。
ORACLE推荐的临时表空间的区间是1MB。
临时表空间的区间大小由UNIFORM SIZE指定。
当哈希表构建完成后，进行下面的处理：1）第二个大表进行扫描2）如果大表不能完全cache到可用内存的时候，大表同样会分成很多分区3）大表的第一个分区cache到内存4）对大表第一个分区的数据进行扫描，并与哈希表进行比较，如果有匹配的纪录，添加到结果集里面5）与第一个分区一样，其它的分区也类似处理。
6）所有的分区处理完后，ORACLE对产生的结果集进行归并，汇总，产生最终的结果。
当哈希表过大或可用内存有限，哈希表不能完全CACHE到内存。
随着满足连接条件的结果集的增加，可用内存会随之下降，这时已经CACHE到内存的数据可能会重新写回到硬盘去。
如果出现这种情况，系统的性能就会下降。
当连接的两个表是用等值连接并且表的数据量比较大时，优化器才可能采用哈希连接。
哈希连接是基于CBO的。
只有在数据库初始化参数HASH_JOIN_ENABLED设为True,并且为参数PGA_AGGREGATE_TARGET设置了一个足够大的值的时候,Oracle才会使用哈希边连接。
HASH_AREA_SIZE是向下兼容的参数,但在Oracle9i之前的版本中应当使用HASH_AREA_SIZE。
Oracle 表连接优化
优化SQL的另一种思维5 ORACLE的执行计划之表之间的连接优化SQL的另一种思维系列文章是作者用了快一年的时间完成的SQL优化文章，它的原理实际上是通过分析SQL语句的执行计划来实现对整体SQL进行优化的，这是多年项目经验的总结，由于考虑到目前国内的Oracle部署还基本维持在Oracle 8 和Oracle 9的水平上，所以里面的模拟环境都是在Oracle 8.3下进行的，希望可以针对性更强一些。
本文是系列连载的第五章：ORACLE的执行计划中表之间的连接篇。
《优化SQL的另一种思维》目录一、性能调整综述二、有效的应用设计三、SQL语句处理的过程四、ORACLE的优化器五、ORACLE的执行计划背景知识访问路径(方法) -- access path表之间的连接如何产生执行计划如何分析执行计划如何干预执行计划 - - 使用hints提示具体测试实例具体案例分析六、其它注意事项七、整体实例分析客户端程序优化SQL步骤tkprof程序整体实例解说【IT专家网独家】Join是一种试图将两个表结合在一起的谓词，一次只能连接2个表，表连接也可以被称为表关联。
在后面的叙述中，我们将会使用”row source”来代替”表”，因为使用row source更严谨一些，并且将参与连接的2个row source分别称为row source1和row source 2。
Join过程的各个步骤经常是串行操作，即使相关的row source 可以被并行访问，即可以并行的读取做join连接的两个row source的数据，但是在将表中符合限制条件的数据读入到内存形成row source后，join的其它步骤一般是串行的。
有多种方法可以将2个表连接起来，当然每种方法都有自己的优缺点，每种连接类型只有在特定的条件下才会发挥出其最大优势。
row source(表)之间的连接顺序对于查询的效率有非常大的影响。
通过首先存取特定的表，即将该表作为驱动表，这样可以先应用某些限制条件，从而得到一个较小的row source，使连接的效率较高，这也就是我们常说的要先执行限制条件的原因。
Oracle+表连接方式(内连接-外连接-自连接)+详解
Oracle 表之间的连接分为三种：1. 内连接(自然连接)2. 外连接（1）左外连接(左边的表不加限制)（2）右外连接(右边的表不加限制)（3）全外连接(左右两表都不加限制)3. 自连接（同一张表内的连接）SQL的标准语法：select table1.column,table2.column from table1 [inner | left | right | full ] join table2 on table1.column1 = table2.column2;inner join 表示内连接；left join表示左外连接；right join表示右外连接；full join表示完全外连接；on子句用于指定连接条件。
注意：如果使用from子句指定内、外连接，则必须要使用on子句指定连接条件；如果使用（+）操作符指定外连接，则必须使用where子句指定连接条件。
一．内连接（Inner Join/Join）1.1 Inner JoinInner join逻辑运算符返回满足第一个（顶端）输入与第二个（底端）输入联接的每一行。
这个和用select查询多表是一样的效果，所以内连接用的很少。
还有一点要说明的就是Join 默认就是inner join。
所以我们在写内连接的时候可以省略inner 这个关键字。
1.2 下面举例来说明内连接：1.2.1 先创建2张测试表并插入数据：SQL> select * from dave;ID NAME---------- ----------1 dave2 bl1 bl2 daveSQL> select * from bl;ID NAME---------- ----------1 dave2 bl1.2.3 用内链接进行查询：SQL> Select a.id,, from dave a inner join bl b on a.id=b.id; -- 标准写法ID NAME NAME---------- ---------- ----------1 dave dave2 bl bl1 bl dave2 dave blSQL> Select a.id,, from dave a join bl b on a.id=b.id; -- 这里省略了inner 关键字ID NAME NAME---------- ---------- ----------1 dave dave2 bl bl1 bl dave2 dave blSQL> Select a.id,, from dave a,bl b where a.id=b.id; -- select 多表查询ID NAME NAME---------- ---------- ----------1 dave dave2 bl bl1 bl dave2 dave bl从这三个SQL 的结果我们也可以看出，他们的作用是一样的。
Oracle 表三种连接方式使用介绍(sql优化)
1. NESTED LOOP对于被连接的数据子集较小的情况，nested loop连接是个较好的选择。
nested loop就是扫描一个表，每读到一条记录，就根据索引去另一个表里面查找，没有索引一般就不会是nested loops。
一般在nested loop中，驱动表满足条件结果集不大，被驱动表的连接字段要有索引，这样就走nstedloop。
如果驱动表返回记录太多，就不适合nested loops了。
如果连接字段没有索引，则适合走hash join，因为不需要索引。
可用ordered提示来改变CBO默认的驱动表，可用USE_NL(table_name1 table_name2)提示来强制使用nested loop。
要点如下：1）对于被连接的数据子集较小的情况，嵌套循环连接是个较好的选择2）使用USE_NL(table_name1 table_name2)可是强制CBO 执行嵌套循环连接3）Nested loop一般用在连接的表中有索引，并且索引选择性较好的时候4）OIN的顺序很重要，驱动表的记录集一定要小，返回结果集的响应时间是最快的。
5）Nested loops 工作方式是从一张表中读取数据，访问另一张表（通常是索引）来做匹配，nested loops适用的场合是当一个关联表比较小的时候，效率会更高。
2. HASH JOINhash join是CBO 做大数据集连接时的常用方式。
优化器扫描小表（数据源），利用连接键（也就是根据连接字段计算hash 值）在内存中建立hash表，然后扫描大表，每读到一条记录就探测hash表一次，找出与hash表匹配的行。
当小表可以全部放入内存中，其成本接近全表扫描两个表的成本之和。
如果表很大不能完全放入内存，这时优化器会将它分割成若干不同的分区，不能放入内存的部分就把该分区写入磁盘的临时段，此时要有较大的临时段从而尽量提高I/O 的性能。
临时段中的分区都需要换进内存做hash join。
oracle 表关联方式
Oracle 表关联方式一、概述在 Oracle 数据库中，表关联是一种非常重要的操作，用于将多个表中的数据进行关联查询。
通过表关联，可以根据两个或多个表之间的关系，获取到更加丰富和准确的查询结果。
Oracle 提供了多种表关联方式，包括内连接、外连接和交叉连接等。
本文将详细介绍这些表关联方式的使用方法和特点。
二、内连接内连接是最常用的一种表关联方式，它通过匹配两个表之间的共同字段，将满足条件的行组合在一起。
内连接的语法如下：SELECT列名FROM表1INNER JOIN表2ON表1.列名 = 表2.列名;1. 等值连接等值连接是内连接的一种常见形式，它通过两个表之间的共同字段进行相等条件的匹配。
例如，我们有两个表employees和departments，它们通过department_id字段进行关联。
我们可以使用等值连接查询出每个员工所在的部门：SELECT e.employee_id, e.first_name, d.department_nameFROM employees eINNER JOIN departments dON e.department_id = d.department_id;2. 非等值连接非等值连接是内连接的另一种形式，它通过使用不等于操作符（如<, >, <=, >=）进行条件匹配。
非等值连接常用于查询满足某种范围条件的数据。
例如，我们可以使用非等值连接查询出每个员工的工资等级：SELECT e.employee_id, e.first_name, j.grade_levelFROM employees eINNER JOIN job_grades jON e.salary BETWEEN j.lowest_sal AND j.highest_sal;三、外连接外连接用于查询两个表之间的关联数据，并且包括未匹配的行。
外连接分为左外连接、右外连接和全外连接三种形式。
Oracle表三种连接方式使用介绍（sql优化）
Oracle表三种连接⽅式使⽤介绍（sql优化）1. NESTED LOOP对于被连接的数据⼦集较⼩的情况，nested loop连接是个较好的选择。
nested loop就是扫描⼀个表，每读到⼀条记录，就根据索引去另⼀个表⾥⾯查找，没有索引⼀般就不会是 nested loops。
⼀般在nested loop中，驱动表满⾜条件结果集不⼤，被驱动表的连接字段要有索引，这样就⾛nstedloop。
如果驱动表返回记录太多，就不适合nested loops了。
如果连接字段没有索引，则适合⾛hash join，因为不需要索引。
可⽤ordered提⽰来改变CBO默认的驱动表，可⽤USE_NL(table_name1 table_name2)提⽰来强制使⽤nested loop。
要点如下：1）对于被连接的数据⼦集较⼩的情况，嵌套循环连接是个较好的选择2）使⽤USE_NL(table_name1 table_name2)可是强制CBO 执⾏嵌套循环连接3）Nested loop⼀般⽤在连接的表中有索引，并且索引选择性较好的时候4）OIN的顺序很重要，驱动表的记录集⼀定要⼩，返回结果集的响应时间是最快的。
5）Nested loops ⼯作⽅式是从⼀张表中读取数据，访问另⼀张表（通常是索引）来做匹配，nested loops适⽤的场合是当⼀个关联表⽐较⼩的时候，效率会更⾼。
2. HASH JOINhash join是CBO 做⼤数据集连接时的常⽤⽅式。
优化器扫描⼩表（数据源），利⽤连接键（也就是根据连接字段计算hash 值）在内存中建⽴hash表，然后扫描⼤表，每读到⼀条记录就探测hash表⼀次，找出与hash表匹配的⾏。
当⼩表可以全部放⼊内存中，其成本接近全表扫描两个表的成本之和。
如果表很⼤不能完全放⼊内存，这时优化器会将它分割成若⼲不同的分区，不能放⼊内存的部分就把该分区写⼊磁盘的临时段，此时要有较⼤的临时段从⽽尽量提⾼I/O 的性能。
Oracle中表的连接及其调整
Oracle中表的连接及其调整只有对这些问题有了清晰的理解后，我们才能针对特定的查询需求选择合适的连接方式，开发出健壮的数据库应用程序。
选择合适的表连接方法对SQL语句运行的性能有着至关重要的影响。
下面我们就Oracle常用的一些连接方法及适用情景做一个简单的介绍。
常用的表连接方式：a.嵌套循环连接（Nested Loop）b.排序合并连接(Sort Merge)c. 哈希连接（Hash join）一、嵌套循环连接（Nested Loop）嵌套循环连接的工作方式是这样的：1、Oracle首先选择一张表作为连接的驱动表，这张表也称为外部表（Outer Table）。
由驱动表进行驱动连接的表或数据源称为内部表（Inner Table）。
2、提取驱动表中符合条件的记录，与被驱动表的连接列进行关联查询符合条件的记录。
在这个过程中，Oracle首先提取驱动表中符合条件的第一条记录，再与内部表的连接列进行关联查询相应的记录行。
在关联查询的过程中，Oracle 会持续提取驱动表中其他符合条件的记录与内部表关联查询。
这两个过程是并行进行的，因此嵌套循环连接返回前几条记录的速度是非常快的。
在这里需要说明的是，由于Oracle最小的IO单位为单个数据块，因此在这个过程中Oracle会首先提取驱动表中符合条件的单个数据块中的所有行，再与内部表进行关联连接查询的，然后提取下一个数据块中的记录持续地循环连接下去。
当然，如果单行记录跨越多个数据块的话，就是一次单条记录进行关联查询的。
3、嵌套循环连接的过程如下所示：我们可以看出这里面存在着两个循环，一个是外部循环，提取驱动表中符合条件的每条记录。
另外一个是内部循环，根据外循环中提取的每条记录对内部表进行连接查询相应的记录。
由于这两个循环是嵌套进行的，故此种连接方法称为嵌套循环连接。
嵌套循环连接适用于查询的选择性强、约束性高并且仅返回小部分记录的结果集。
通常要求驱动表的记录（符合条件的记录，通常通过高效的索引访问）较少，且被驱动表连接列有唯一索引或者选择性强的非唯一索引时，嵌套循环连接的效率是比较高的。
oracle多表连接查询性能优化
原始SQL语句如下：select *from (select c.product_name,a.prd_inst_name, prd_state,a.accept_date, basic_state,a.evolution_pty_id,decode(a.if_prepay, 1, '是', '否') prepay,a.install_addr,a.install_date,a.service_nbr,a.ofr_id, ofr_name,d.cust_name,rownum numfrom crm_tb_prd_prd_inst a,crm_tb_prd_prd c,crm_tb_pty_cust d,m_prd_inst_stas m,m_prd_inst_base_state m1,m_prdmark_inst_base_state m2,ext_crm_tb_prd_prd_inst extwhere a.prd_id = c.product_idand m.gwm_fid(+) = a.prd_inst_stas_idand m1.basic_state(+) = a.basic_stateand m2.ofr_id(+) = a.ofr_idand a.prd_inst_id(+) = ext.prd_inst_idand d.cust_id(+) = a.own_cust_idand ext.building_fid = 693253and rownum <= 10)where num > 0;数据库中，a、c、d 三张表中数据十万数量级，每查询一次，时间约为75s。
查询表结构，发现a表关联的m表的外键ofr_id ，m1表的外键basic_state，d表的外键own_cust_id均无索引，为其添加索引。
索引添加后，执行速度提升为53s左右，效果仍不明显。
oracle 表连接语法-概述说明以及解释
oracle 表连接语法-概述说明以及解释1.引言1.1 概述在Oracle数据库中，表连接是一种关联查询数据的方法。
通过使用表连接，我们可以在多个数据库表之间建立关系，从而实现更复杂和灵活的数据查询和分析操作。
表连接的概念是基于关系型数据库原理的，它允许我们根据共享的列值将不同的表关联起来。
这样，我们就可以在不同的表中检索和比较数据，以获取更全面和准确的结果。
在进行表连接操作时，我们需要指定连接条件，即两个表之间相关联的列。
这些连接条件可以是等值连接、范围连接、非等值连接等等，具体的选择要根据实际需求和查询要求来确定。
内连接是最常用的表连接类型之一。
它只检索那些在所有相关的表中都存在的行，即仅返回匹配的行。
内连接会排除掉那些没有匹配的行，因此结果集中只包含满足连接条件的数据。
另一种常见的表连接类型是外连接。
外连接可以分为左外连接、右外连接和全外连接三种。
左外连接表示返回左边表中的所有记录，不论是否满足连接条件；右外连接则是返回右边表中的所有记录；而全外连接则是返回两个表的所有记录。
最后，我们还可以进行自连接操作。
自连接是指将一个表与其自身进行连接，从而实现不同记录之间的比较。
自连接可以解决一些特殊的业务需求，例如将一个表中的数据与同一表中的其他行进行匹配和比较。
通过熟悉和掌握表连接的语法和使用方法，我们可以更加灵活和高效地查询和分析数据库中的数据。
在实际的应用场景中，表连接操作在数据仓库建设、业务分析和报表生成等领域发挥着重要的作用。
在本文中，我们将详细介绍表连接的概念、语法和常见的应用场景。
同时，我们还将讨论一些注意事项和技巧，以提高表连接查询的效率和准确性。
最后，我们会展望一下表连接在未来的发展趋势，为读者带来更多的思考和探索的机会。
1.2 文章结构在本文中，我们将重点讨论Oracle 数据库中的表连接语法。
本文共分为三大部分，即引言、正文和结论。
引言部分提供了关于文章主题的概述、文章的结构和写作目的。
oracle优化方案
Oracle优化方案引言在使用Oracle数据库时，为了提高性能和效率，我们需要对数据库进行优化。
本文将介绍一些常用的Oracle优化方案，包括索引优化、SQL优化、统计信息优化等。
索引优化索引是提高数据库查询性能的重要手段之一。
以下是一些优化索引的方法：1. 创建合适的索引在设计表结构时，应该通过分析和了解业务需求，选择合适的列创建索引。
常见的索引类型包括B树索引、位图索引等。
2. 考虑联合索引联合索引是指多个列组合在一起创建的索引。
在某些情况下，使用联合索引可以提高查询速度。
但是需要注意，联合索引过多或列顺序不当会导致索引失效。
3. 避免使用过多的索引虽然索引可以提高查询性能，但是过多的索引会增加写操作的开销，并且会占用更多的存储空间。
因此，应该根据实际情况，避免创建过多的索引。
4. 定期维护索引定期检查和维护索引是保证索引效率的重要步骤。
可以通过重建索引、收集统计信息等方法来优化索引性能。
SQL优化SQL查询是Oracle数据库最常用的操作之一。
以下是一些优化SQL查询的方法：1. 减少不必要的查询避免在查询中使用不必要的表，减少不必要的连接操作，可以大幅提高查询性能。
2. 使用正确的查询方式在编写SQL查询时，应该选择合适的查询方式。
常用的查询方式有嵌套查询、子查询、联接等。
根据实际情况选择最合适的查询方式，可以减少数据量和运算量，提高查询速度。
3. 优化WHERE条件在查询语句中，应该尽量避免使用通配符查询（如%）、使用函数在WHERE条件中进行运算，因为这样会导致全表扫描，影响查询性能。
4. 使用分页查询当查询结果集较大时，可以考虑使用分页查询。
通过limit和offset来限制查询结果的返回范围，可以减少数据的传输和处理，提高查询效率。
统计信息优化Oracle数据库中的统计信息对查询优化器的决策至关重要。
以下是一些优化统计信息的方法：1. 收集统计信息及时收集和更新表的统计信息是保证数据库查询性能稳定的重要步骤。
oracle多表关联的优化问题
ORACLE多表查询优化ORACLE多表查询优化这里提供的就是执行性能的优化,而不就是后台数据库优化器资料:参考数据库开发性能方面的各种问题,收集了一些优化方案统计如下(当然,象索引等优化方案太过简单就不列入了,嘿嘿):执行路径:ORACLE的这个功能大大地提高了SQL的执行性能并节省了内存的使用:我们发现,单表数据的统计比多表统计的速度完全就是两个概念、单表统计可能只要0、02秒,但就是2张表联合统计就可能要几十表了、这就是因为ORACLE 只对简单的表提供高速缓冲(cache buffering) ,这个功能并不适用于多表连接查询、、数据库管理员必须在init、ora中为这个区域设置合适的参数,当这个内存区域越大,就可以保留更多的语句,当然被共享的可能性也就越大了、当您向ORACLE提交一个SQL语句,ORACLE会首先在这块内存中查找相同的语句、这里需要注明的就是,ORACLE对两者采取的就是一种严格匹配,要达成共享,SQL 语句必须完全相同(包括空格,换行等)、共享的语句必须满足三个条件:A、字符级的比较:当前被执行的语句与共享池中的语句必须完全相同、例如:SELECT * FROM EMP;与下列每一个都不同SELECT * from EMP;Select * From Emp;SELECT * FROM EMP;B、两个语句所指的对象必须完全相同:用户对象名如何访问Jack sal_limit private synonymWork_city public synonymPlant_detail public synonymJillsal_limit privatesynonymWork_city public synonymPlant_detail table owner考虑一下下列SQL语句能否在这两个用户之间共享、SQL 能否共享原因select max(sal_cap) from sal_limit; 不能每个用户都有一个private synonym - sal_limit , 它们就是不同的对象select count(*) from work_city where sdesc like 'NEW%'; 能两个用户访问相同的对象public synonym - work_cityselect a、sdesc,b、location from work_city a , plant_detail b where a、city_id = b、city_id 不能用户jack 通过private synonym访问plant_detail 而jill 就是表的所有者,对象不同、C、两个SQL语句中必须使用相同的名字的绑定变量(bind variables)例如:第一组的两个SQL语句就是相同的(可以共享),而第二组中的两个语句就是不同的(即使在运行时,赋于不同的绑定变量相同的值)a、select pin , name from people where pin = :blk1、pin;select pin , name from people where pin = :blk1、pin;b、select pin , name from people where pin = :blk1、ot_ind;select pin , name from people where pin = :blk1、ov_ind;重点关注1:选择最有效率的表名顺序(只在基于规则的优化器中有效)重点关注ORACLE的解析器按照从右到左的顺序处理FROM子句中的表名,因此FROM子句中写在最后的表(基础表 driving table)将被最先处理、在FROM子句中包含多个表的情况下,您必须选择记录条数最少的表作为基础表、当ORACLE处理多个表时, 会运用排序及合并的方式连接它们、首先,扫描第一个表(FROM子句中最后的那个表)并对记录进行派序,然后扫描第二个表(FROM子句中最后第二个表),最后将所有从第二个表中检索出的记录与第一个表中合适记录进行合并、例如: 表 TAB1 16,384 条记录表 TAB2 1 条记录选择TAB2作为基础表 (最好的方法)select count(*) from tab1,tab2 执行时间0、96秒选择TAB2作为基础表 (不佳的方法)select count(*) from tab2,tab1 执行时间26、09秒如果有3个以上的表连接查询, 那就需要选择交叉表(intersection table)作为基础表, 交叉表就是指那个被其她表所引用的表、例如: EMP表描述了LOCATION表与CATEGORY表的交集、SELECT *FROM LOCATION L ,CATEGORY C,EMP EWHERE E、EMP_NO BETWEEN 1000 AND 2000AND E、CAT_NO = C、CAT_NOAND E、LOCN = L、LOCN将比下列SQL更有效率SELECT *FROM EMP E ,LOCATION L ,CATEGORY CWHERE E、CAT_NO = C、CAT_NOAND E、LOCN = L、LOCNAND E、EMP_NO BETWEEN 1000 AND 2000重点关注2:WHERE子句中的连接顺序.重点关注ORACLE采用自下而上的顺序解析WHERE子句,根据这个原理,表之间的连接必须写在其她WHERE条件之前, 那些可以过滤掉最大数量记录的条件必须写在WHERE 子句的末尾、例如:(低效,执行时间156、3秒)SELECT …FROM EMP EWHERE SAL >; 50000AND JOB = ‘MANAGER’AND 25 < (SELECT COUNT(*) FROM EMPWHERE MGR=E、EMPNO);(高效,执行时间10、6秒)SELECT …FROM EMP EWHERE 25 < (SELECT COUNT(*) FROM EMPWHERE MGR=E、EMPNO)AND SAL >; 50000AND JOB = ‘MANAGER’;重点关注3:SELECT子句中避免使用‘ * ‘ .重点关注当您想在SELECT子句中列出所有的COLUMN时,使用动态SQL列引用‘*’ 就是一个方便的方法、不幸的就是,这就是一个非常低效的方法、实际上,ORACLE在解析的过程中, 会将’*’ 依次转换成所有的列名, 这个工作就是通过查询数据字典完成的, 这意味着将耗费更多的时间、7、减少访问数据库的次数当执行每条SQL语句时, ORACLE在内部执行了许多工作: 解析SQL语句, 估算索引的利用率, 绑定变量 , 读数据块等等、由此可见, 减少访问数据库的次数 , 就能实际上减少ORACLE的工作量、例如,以下有三种方法可以检索出雇员号等于0342或0291的职员、方法1 (最低效)SELECT EMP_NAME , SALARY , GRADEFROM EMPWHERE EMP_NO = 342;SELECT EMP_NAME , SALARY , GRADEFROM EMPWHERE EMP_NO = 291;方法2 (次低效)DECLARECURSOR C1 (E_NO NUMBER) ISSELECT EMP_NAME,SALARY,GRADEFROM EMPWHERE EMP_NO = E_NO;BEGINOPEN C1(342);FETCH C1 INTO …,、、,、、 ;OPEN C1(291);FETCH C1 INTO …,、、,、、 ;CLOSE C1;END;方法3 (高效)SELECT A、EMP_NAME , A、SALARY , A、GRADE,B、EMP_NAME , B、SALARY , B、GRADEFROM EMP A,EMP BWHERE A、EMP_NO = 342AND B、EMP_NO = 291;注意:在SQL*Plus , SQL*Forms与Pro*C中重新设置ARRAYSIZE参数, 可以增加每次数据库访问的检索数据量 ,建议值为200、重点关注4:使用DECODE函数来减少处理时间、重点关注使用DECODE函数可以避免重复扫描相同记录或重复连接相同的表、例如:SELECT COUNT(*),SUM(SAL)FROM EMPWHERE DEPT_NO = 0020AND ENAME LIKE ‘SMITH%’;SELECT COUNT(*),SUM(SAL)FROM EMPWHERE DEPT_NO = 0030AND ENAME LIKE ‘SMITH%’;您可以用DECODE函数高效地得到相同结果SELECT COUNT(DECODE(DEPT_NO,0020,’X’,NULL)) D0020_COUNT,COUNT(DECODE(DEPT_NO,0030,’X’,NULL))D0030_COUNT,SUM(DECODE(DEPT_NO,0020,SAL,NULL)) D0020_SAL,SUM(DECODE(DEPT_NO,0030,SAL,NULL)) D0030_SALFROM EMP WHERE ENAME LIKE ‘SMITH%’;类似的,DECODE函数也可以运用于GROUP BY 与ORDER BY子句中、重点关注5: 删除重复记录、重点关注最高效的删除重复记录方法 ( 因为使用了ROWID)DELETE FROM EMP EWHERE E、ROWID >; (SELECT MIN(X、ROWID)FROM EMP XWHERE X、EMP_NO = E、EMP_NO); 重点关注6: 用TRUNCATE替代DELETE、重点关注当删除表中的记录时,在通常情况下, 回滚段(rollback segments ) 用来存放可以被恢复的信息、如果您没有COMMIT事务,ORACLE会将数据恢复到删除之前的状态(准确地说就是恢复到执行删除命令之前的状况)而当运用TRUNCATE时, 回滚段不再存放任何可被恢复的信息、当命令运行后,数据不能被恢复、因此很少的资源被调用,执行时间也会很短、(译者按: TRUNCATE只在删除全表适用,TRUNCATE就是DDL不就是DML)重点关注7: 尽量多使用COMMIT、重点关注只要有可能,在程序中尽量多使用COMMIT, 这样程序的性能得到提高,需求也会因为COMMIT所释放的资源而减少:COMMIT所释放的资源:a、回滚段上用于恢复数据的信息、b、被程序语句获得的锁c、 redo log buffer 中的空间d、ORACLE为管理上述3种资源中的内部花费(译者按: 在使用COMMIT时必须要注意到事务的完整性,现实中效率与事务完整性往往就是鱼与熊掌不可得兼)重点关注8:减少对表的查询、重点关注在含有子查询的SQL语句中,要特别注意减少对表的查询、例如:低效SELECT TAB_NAMEFROM TABLESWHERE TAB_NAME = ( SELECT TAB_NAMEFROM TAB_COLUMNSWHER E VERSION = 604)AND DB_VER= ( SELECT DB_VERFROMTAB_COLUMNSWHERE VERSION = 604)高效SELECT TAB_NAMEFROM TABLESWHERE (TAB_NAME,DB_VER)= ( SELECT TAB_NAME,DB_VER)FROM TAB_COLUMNSWHERE VERSION = 604)Update 多个Column 例子:低效:UPDATE EMPSET EMP_CAT = (SELECT MAX(CATEGORY) FROMEMP_CATEGORIES),SAL_RANGE = (SELECT MAX(SAL_RANGE) FROM EMP_CATEGORIES)WHERE EMP_DEPT = 0020;高效:UPDATE EMPSET (EMP_CAT, SAL_RANGE)= (SELECT MAX(CATEGORY) , MAX(SAL_RANGE)FROM EMP_CATEGORIES)WHERE EMP_DEPT = 0020;重点关注9:用EXISTS替代IN、重点关注在许多基于基础表的查询中,为了满足一个条件,往往需要对另一个表进行联接、在这种情况下, 使用EXISTS(或NOT EXISTS)通常将提高查询的效率、低效:SELECT *FROM EMP (基础表)WHERE EMPNO >; 0AND DEPTNO IN (SELECT DEPTNOFROM DEPTWHERE LOC = ‘MELB’)高效:SELECT *FROM EMP (基础表)WHERE EMPNO >; 0AND EXISTS (SELECT ‘X’FROM DEPTWHERE DEPT、DEPTNO = EMP、DEPTNOAND LOC = ‘MELB’)(译者按: 相对来说,用NOT EXISTS替换NOT IN 将更显著地提高效率,下一节中将指出)重点关注10:用NOT EXISTS替代NOT IN 、重点关注在子查询中,NOT IN子句将执行一个内部的排序与合并、无论在哪种情况下,NOT IN都就是最低效的 (因为它对子查询中的表执行了一个全表遍历)、为了避免使用NOT IN ,我们可以把它改写成外连接(Outer Joins)或NOT EXISTS、例如:SELECT …FROM EMPWHERE DEPT_NO NOT IN (SELECT DEPT_NOFROM DEPTWHEREDEPT_CAT=’A’);为了提高效率、改写为:(方法一: 高效)SELECT …、FROM EMP A,DEPT BWHERE A、DEPT_NO = B、DEPT(+)AND B、DEPT_NO IS NULLAND B、DEPT_CAT(+) = ‘A’(方法二: 最高效)SELECT …、FROM EMP EWHERE NOT EXIST S (SELECT ‘X’FROM DEPT DWHERE D、DEPT_NO = E、DEPT_NOAND DEPT_CAT = ‘A’);当然,最高效率的方法就是有表关联、直接两表关系对联的速度就是最快的!重点关注11:识别’低效执行’的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_TEXTFROM V$SQLAREAWHERE EXECUTIONS>;0AND BUFFER_GETS >; 0AND (BUFFER_GETS-DISK_READS)/BUFFER_GETS < 0、8ORDER BY 4 DESC;(译者按: 虽然目前各种关于SQL优化的图形化工具层出不穷,但就是写出自己的SQL工具来解决问题始终就是一个最好的方法)。