ORACLE 理论概念

oracle表连接

http://space.itpub.net/26511146/viewspace-745511

oracle表之间的内部连接可分为三种方式：

（1）nested loop join--嵌套连接

（2）sort merge join--排序合并连接

（3）hash join--哈希连接

下面分别介绍这三种内部连接方式及优缺点：

（1）nested loop join

oracle优化器根据CBO或者RBO选择驱动表作为外部表，从外部表的关联列上的第一行开始，逐步与内部表关联列的每一行进行对比，将匹配的结果放入结果集；继续外部表的第二行。。直到最后一行，产生所有结果集。

基于CBO的优化器会选择合适的表作为驱动表。同时也可以用RBO手动添加hints，进行指定：/*+ use_nl(a,b) */

条件：内部表的关联列上存在索引，且两个表的数据较少时。

优点：可以先返回一部分数据，而无需等待全部集合。

（2）sort merge join

oracle优化器选择一个源表作为外部表，对关联列进行排序；然后对内部表的关联列进行排序，最后进行合并操作，产生结果集。

可以手动添加hints：/*+ use_merge(a,b) */来指定连接方式。

条件：无索引时，或者两个表的数据较大。

缺点：由于涉及排序，所以对PGA要求较高，当PGA不够时，会用到临时表空间。

（3）hash join

oracle优化器选择外部表，将关联列的数据全部cache到内存，形成hash表；然后再将内部表的关联列cache到内存，比较后产生结果集。

条件：用于等值连接且两个表数据量较大时。

缺点：对PGA要求较高。

下面对比这几种连接方式：

oracle表的连接类型，在sql写法上分为以下几种：

（1）inner join--内连接

（2）left (outer) join--左外连接

（3）right (outer) join--右外连接

下面对这几种连接方式做出说明：

（1）inner join

写法：select * from A inner join B on A.col=B.col;

等价于：select * from A,B where A.col=B.col;

（2）left outer join

写法：select * from A left join B on A.col=B.col;

（3）right outer jon

写法：select * from A right join B on A.col=B.col;

总结：

oracle优化是一个系统工程，对于sql的优化，可以参考的内容包括查看sql的执行计划（set autotrace trace only），通过执行计划查看表连接的方式，同时确定索引的使用情况。对于索引的有效性，可以查看视图（V$object_usage）。

oracle每天晚上十点（默认），开始执行job，来收集对象（表、索引）的统计信息，可以通过查看dba_tables或者dba_indexes视图来查看统计信息的收集情况。可以查看表的平均行长，块的大小以及最后分析时间来确定。

一个sql语句是否应该使用索引，可以做一个sql access advisor来确定。

总之，多表之间的关联，容易造成sql执行时间过长，此时，分析表间的关联方式，以及执行计划情况，是一个不错的研究出发点。

oracle--sql内部处理机制

http://space.itpub.net/?uid-26511146-action-viewspace-itemid-753781

1：连接到oracle服务器

方式：

（1）thin：即瘦客户端。对于thin方式，多见于现在很多的B/S结构，用于jdbc连接。

（2）oci：通过客户端连接，包括sqlplus和工具（pl/sql developer），和EM。

这两种连接的方式，最主要的区别就是需不需要安装oracle客户端。

2：sql处理机制

处理步骤：

（1）parsing--解析：包括语法校验、语义校验、权限校验等，生成解析树；

其实我们发出select或者DML操作时，都是由oracle的服务器进程（server process）来完成的。

（2）optimizer--优化：基于CBO的优化模式下，oracle会在成百上千种可能的执行计划中，选择一个最优的执行计划。CBO在这一步的主要工作有sql转换，确定访问路径，确定表与表的关联方法以及表间的关联次序。最后，生成最有效的执行计划，所谓最有效，oracle考虑的因素主要是IO和CPU的消耗。

CBO在一步的工作，就好象是汽车上的导航，比如我从西单到大兴，会有很多种走法，导航经过对距离和时间的计算，会产生出最优的路线，然后推荐出来。试想一下，假如今天的天气非常不好，雾霾+京开高速严重堵车。可是导航不知道啊，这时，它还告诉我让我走京开。这种走法就不行了吧？。。

CBO也一样，它要想准确，严重依赖于表和索引的统计信息，如果统计信息缺失或不准确，CBO给出的执行计划也一样不准确。。

在10g以及11g中，oracle默认都是在周一到周五晚上10点，周六日全天，通过job的形式进行统计信息收集。对于不同业务系统，我们可以根据情况进行定期收集，通过dbms-stats包就可以做到。

（2.1）bind--绑定：在OLTP系统中，很多时候都会用到绑定变量代替直接变量。绑定变量的好处是可以固定执行计划，将sql缓存到shared pool中的library cache中。当有相同的sql执行时，oracle就不会再在上千种执行计划中计算了，而是直接用到library中缓存过的执行计划，这将大大减少时间。

说到固定执行计划，问题自然就出来了：绑定变量一定好么？答案是否定的。。原因是绑定变量会固定执行计划，如果随着数据不断的被更形，数据发生了极大的扭曲，那么，以前适合走索引的列，现在也许适合走全表扫描了。。这时再用以前的执行计划，是不是就不够合理了？

其实oracle早就想到这一点了，oracle根据绑定窥视技术，采用了相同的执行计划，但是，根据统计信息，oracle认识到之前的执行计划可能已经不适合了，所以它采用了一种叫做自适应游标的技术，产生了此语句的子执行计划。可以通过查看v$sql中的is_bind_sensitive值，此值如果为Y，则表示绑定变量会根据不同值，产生了新的执行计划。

（3）execute--执行：有了执行计划，oracle就可以按照计划去查找数据了。如果在data buffer中，则从中找oracle块，如果不在，则走IO，去文件中查找。

（4）fetch--取操作：如果是select语句，则server process将结果返回给用户；如果是DML，则会返回用户更新完毕的提示。

3：CBO

刚才已经说过CBO了，它的优点就是总是查找最优的执行计划，通常比RBO的效率要好。但是，它的缺点也很明显，就是严重依赖于统计信息。当缺乏统计信息时，oracle也不是不能完成查询，CBO会采用动态采样，辅助完成查询。

4：访问路径+表连接方式+表连接次序

访问路径：

（1）full table scan（FTS）全表扫描；

（2）table access by rowid：当通过索引时，会根据索引中的rowid去查找表中数据。

（3）index unique scan：通过唯一性约束索引或者主键的唯一索引返回一条结果时，就通过这种方式访问索引；

（4）index range scan：范围扫描，当发出>、<、between等操作时，采用这种方式访问索引；

（5）index fast full scan：select filing_no from T where filing_no like '2012%';类似于这种语句，filing_no是索引列，同时至查找filing_no时，只需快速扫描索引就可以了。。

表连接方式：

nested-loop，sort-merge join,hash join，我之前的博客有写。

http://space.itpub.net/26511146/viewspace-745511

5：执行计划

获取执行计划的方法：

（1）explain plan：需要创建plan_table表，通过DBCA已自动运行脚本了。

（2）autotrace：sqlplus中执行的命令，可以查看执行计划可统计信息。

（3）sqltrace+tkprof：跟踪session，将结格式化后，可以看到执行计划和统计信息。

（4）工具：sql/plus developer中“F5”，或者EM中查看。

查看执行计划方法：先访问缩进量最深的；如果存在缩进量相同的，就从上边的开始查看。

6：浅谈“慢”

其实慢这个说法，不够标准。也就是说，我们需要知道慢在哪里，什么时候开始慢。。。

这里有一些处理“慢”的方便方法：

（1）识别问题语句：可以通过v$sql，或者AWR中top sql或者通过10046事件来查找有问题的sql；

（2）定位低效资源：通过查看执行计划，看看是否有低效的全表扫描；是否存在索引，如果存在，是否使用了索引；是否使用了正确的表连接方法和次序；是否有在后面的选择操作等等。

（3）调优sql：找到了问题所在，可以运行sql access advisor来查看索引的建议。通过创建索引，或者使用hints来优化sql。

（4）比较性能

通过ADDM、sql tuning advisor等工具来比较一下前后的性能差异。如果达到了预期，则调优结束。。

浅析oracle函数

http://space.itpub.net/?uid-26511146-action-viewspace-itemid-753981

oracle的自带函数有很多，我发现在平时的工作中，有些查询本来可以通过oracle自带函数来实现，但是由于不知道这些函数，往往导致很复杂或者很麻烦的书写。。下面列出一些经常用到的函数，供参考。

（1）聚集函数

也成组函数，包括sum，avg，max，min，count等。当用到count时，需要注意空值的运用。比如：

select count(*) from T;

select count(tel) from T;

select count(nvl(tel,0)) from T;

这三种写法，第一种和第三种的结果是一样的，而第二种，则当tel列有空值时，是不会计入count的，所以结果肯定不准确。

（2）decode与case

decode函数与简单case函数的表达结果基本相同，就是将某些特定的列值转换为需要的值或表达式。比如：

select decode(job_id,10,1.1*salary,20,1.2*salary,30,1.3*salary,salary) as 奖金 from T;

select case job_id when 10 then 1.1*salary

when 20 then 1.2*salary

when 30 then 1.3*salary

else salary

end as 奖金

from T;

这两个函数都是将特定值转换为需要的列或表达式。

而复杂case函数有些不同，可以完成更复杂的查询：

select case

when salary<500 then 'D级'

when salary<600 and salary>=500 then 'C级'

when salary<700 and salary>=600 then 'B级'

when salary<800 and salary>=700 then 'A级'

else ‘特级’

end 薪水级别,count(*)

from T

group by

case

when salary<500 then 'D级'

when salary<600 and salary>=500 then 'C级'

when salary<700 and salary>=600 then 'B级'

when salary<800 and salary>=700 then 'A级'

else ‘特级’

end;

（3）nvl与trim

nvl是将列为空值时，转换为其他值代替。例如，查询薪水列，当薪水为空时，用0代替：

select nvl(salary,0) from T;

trim函数就是去除空格的：

select trim(col) from T;

（4）trunc与round

trunc函数是直接截取小数点后的位数，而round是四舍五入保留几位小数。例如：

select trunc(level,1) from T;--将level列直接截取保留1位，值为0.98就变为0.9；

select round(level,1) from T;--将level四舍五入保留1位，值为0.98就变为1；

注意，trunc默认是截至到整数部分，即trunc(col,0)，当trunc(col,-1)时，会把个位数直接截取为0，比如trunc(321,-1)的结果为320.也可以trunc(日期)，返回number类型的日期。如trunc(sysdate)相当于只保留sysydate的整数，时分秒截去。

（5）rollup

rollup是完成聚集后进行的小计或者总计，通常用于报表。例如：

select depart_id,count(*)

from su_examiner_manage

where deaprt_id>=209

group by depart_id;

结果如下：

20933512167032176714725

可以看出，结果除了对depart_id列进行聚集，还进行了总计。rollup（a,b,c）就是先对abc进行汇总，再对ab进行小计，对a进行小计，最后进行总计。一共产生n+1个结果集。

（6）substr

substr是对字符串进行截取，例如：

select substr(col,0,2) from T；--代表从col列第一位开始，向后截取两位，相当于返回两位的字符串。

（7）add_months与months_between

add_months函数是将date类型的列值增加或者减少几个月。

select add_months(sysdate,-2) from dual;--将当前日期往前推2个月，就是2012-12-06 13：57：28。

months_between是将两个date类型的值作减法，返回两个值之间相差的月份,类型为number。

select months_between(sysdate,add_months(sysdate,-2)) from dual;--结果为2.

当然还有很多其他的函数，我们也可以在数据库中自定义函数（function）来使用。

总之，我们在写sql时，可以多运用这些函数，方便查询。