SQL编写知识分享（云和恩墨培训）

1.表连接
内连接：只返回两个表中，满足连接条件的行。
左外连接：返回位于左侧表中的所有行和右侧表中满足连接条件的行。
右外连接：返回位于右侧表中的所有行和左侧表中满足连接条件的行。
全外连接：返回位于两侧表中的所有行。
2.标量子查询
根据子查询出现的位置，我们分为带内视图（出现在FROM子句中）和嵌套子查询（出现在WHERE子句中）。对于出现在WHERE子句中的子查询（嵌
套子查询），若在其内部还与外部查询（父查询）发生了关联，则又称这一类嵌套子查询为关联子查询。
标量子查询特指只返回1行1列的子查询。与其出现的位置无关。标量子查询被执行的次数，取决于其所关联的外层查询中列的唯一值的数量。
3.分页查询
写法1：
select *
from (select rownum rn, t2.*
from (select flag, object_id,
object_name from t1) t2
) t3
where t3.rn<=20 and t3.rn > 10;
写法2：性能较优，取靠后的区间与写法1性能就差不多了，适合查询靠前字段
select *
from (select rownum rn, t2.*
from (select flag, object_id,
object_name from t1) t2
where rownum <= 20
) t3
where t3.rn > 10;
4.执行计划
在Oracle中，对SQL的优化，其本质和
核心就是调整其执行计划。
执行计划的定义：
Oracle 运行一条SQL语句的所有步骤被
称为执行计划。
“步骤”涉及的内容：
1、访问哪个对象
2、用哪种方式访问
3、多个对象之间如何关联
4、“步骤”之间的先后顺序
确定步骤先后顺序的原则：
最右最上‐》平级其次‐》逐层回退‐》只走一次。



最常用的5种方法：
1、EXPLAIN PLAN FOR/集成开发工具中的查看执行计划
特点：
1）、不会真正执行SQL语句。故，执行计划结
果返回快。
2）、无SQL语句的执行结果信息的输出。
3）、无执行统计信息。
4）、有谓词信息。
5）、返回的执行计划可能不是真实的执行计
划。
2、AUTOTRACE开关
特点：
1、需要等SQL语句执行完成。故，执行计划
结果的返回时间的快慢，取决于SQL语句执行时间
的长短。
2、有SQL语句执行结果信息的输出。
3、有执行统计信息。
4、有谓词信息。
3、DBMS_XPLAN.DISPLAY_CURSOR()
常用的两种用法：
1）、需要在SQL中加入“/+ gather_plan_statistics /”
的提示（或设置STATISTICS_LEVEL参数为ALL）
select * from table(dbms_xplan.display_cursor
(‘’,’’,’allstats last’));
必须在SQL执行完成后，在同一个窗口中立即执
行，中间不能有其它SQL执行。
特点：
1）、需要等SQL执行完成
2）、有SQL的执行结果信息输出
3）、有每一步实际处理的行数和逻辑读等信息。
4、SQL的AWR报告
5、10046跟踪事件

5.索引设计
索引的特点：
1、索引与表是分开存放的
2、索引高度低，快速定位记录
3、索引本身有序，可避免排序
4、索引存储有索引列的值，可避免回表
5、索引不存储空值（注：构成索引列的值全空）
2、当表中有1~140行时，只需要一个索引数据块。
此时，根块、分枝块和叶块是同一个数据块。
计算索引的高度与索引条目数量的关系：
1、7000字节 / 50字节 = 140个索引条目
3、当表中有第141行时，索引分裂，变成2层结
构。有一个根块,和2个叶子块。
3、因为每个根块中也可以存储140个条目，所以，
根块下最多可以有140个叶子块。而每个叶子块又
可以存储140个索引条目，所以，2层索引最多可以
存储19600行所对应的索引。
计算索引的高度与索引条目数量的关系：
索引层数
哪些列上适合建索引：
1、经常出现在WHERE子句中。
2、唯一值多。即重复的值越少越好。
3、单列唯一值不多，但多列组合后的唯一值
多，且这些列经常出现在WHERE子句中。
创建复合索引时如何确定列出现的次序？
1、做相等比较的列，尽量放到前边。
2、出现比例高的列，尽量放在前边。