《Oracle SQL高级编程 》笔记

 

一 SQL核心

group by子句执行from和where子句后得到的经过筛选后的结果进行聚合。

select列表里面的任何非聚合字段都必须包含在group by表达式中。

group by 和 having的字句顺序可以互换，谁先谁后无关紧要。本质上说having子句是在group by执行后筛选汇总。

 

select子句问题，当使用另外一个select语句产生一列值时，这个查询必须只能返回一行一列的值（该用法叫标量子查询）。

尽量避免使用标量子查询，因为它在结果集中每一行都要执行一遍。

distinct子句，用来在其他子句执行完毕后从结果集中去除重复的行。

 

order by，oracle中order by必须在其他所有子句都执行完之后执行。需要排序的数据量大小影响性能，数据量指结果中的字节数

可以用行数乘以每一行的字节数来估算。较小的排序可以在内存中实现，大的动用临时磁盘空间。

排序是查询过程中开销相当大的一个处理步骤。

 

二 SQL执行

SGA共享池，执行过的每一句SQL在共享池里面都有解析后的内容，存储的这些语句的地方称为库高速缓存（library cache）。

Oracle所使用的系统参数也存放这里，叫数据字典高速缓存（dictionary cache）。

最高效使用共享池，语句需要共享，如果每个sql是唯一的，就无法共享。

硬解析和软解析。

相同sql需要严格一致，大小写，有注释没有注释，参数等等。可以通过select ×　from　v$sql查询。

SGA缓冲区缓存，在数据库块从硬盘读取出来后或者写入硬盘之前，用于存放数据库块。从缓冲区读数据块叫逻辑读取，必须从硬盘读取则称为物理读取。

查询转换，查询转换器可能为优化修改查询语句。可以使用NO_QUERY_TRANSFOERMATION提示禁止。

 

三 联结方法

查询多张表，优化器决定怎么连接效率最高。分为：嵌套循环联结、散列联结、排序-合并联结、笛卡尔联结。

每个联结方法分为两个分支：访问的第一张表叫驱动表，第二张表叫内层表或者被驱动表。优化器来确定哪张表作为驱动表。

预估大小最小（就块、数据行及字节）的表通常作为驱动表。

嵌套循环联结：外层循环就是一个只使用where子句中的属于驱动表的条件对它进行的查询。然后获取的行就会逐个进入到内层循环中。

如果内层循环检查也匹配，就会传递到查询计划的下一步（如果没有更多步骤就直接放入最终结果集中）。

嵌套循环联结优点是内存使用非常少，因为数据行集一次只加工一行，但是大数据集处理时间较长。

排序-合并联结：先排序后合并，排序的开销非常大。适用于where条件是非等式的查询如where column1 between t2.column2 and t2.column1

散列联结：只用于相等联结。

 

四 SQL执行计划

举例：在命令窗口执行，这些只是预期的执行计划，不实际执行查询。

>explain plan for select * from esb_capability_interface;

>select * from table(dbms_xplan.display);

可以查看：

PLAN_TABLE_OUTPUT

--------------------------------------------------------------------------------

Plan hash value: 2910271166

--------------------------------------------------------------------------------

| Id  | Operation         | Name                     | Rows  | Bytes | Cost (%CP

--------------------------------------------------------------------------------

|   0 | SELECT STATEMENT  |                          |    93 |  4557 |     3   (

|   1 |  TABLE ACCESS FULL| ESB_CAPABILITY_INTERFACE |    93 |  4557 |     3   (

--------------------------------------------------------------------------------

输出包括：1.sql引用的每一张表

          2.访问每张表的方法

 3.每一对需要联结的数据源所用的联结方法

 4.按次序列出的所有需要完成的运算

 5.计划中个步骤的谓语信息列表

 6.对于每个运算，估计出改步骤所需要的操作的数据行数和字节数

 7.每个运算，计算出成本值

 8.如果适用，所访问的分区信息

 9.如果适用，并行执行的相关信息

 

五 索引

典型的基于索引的访问路径通常包含3步：

（1）遍历索引树并将SQL语句中的谓语应用到索引列后收集叶子块的行编号

（2）使用行编号从数据库块中获取数据行

（3）在所获取的数据行上应用其余的谓语来得出最终结果

索引列的选择：

（1）如果sql访问某一列上面使用了等式或者范围谓语

等等

IS NULL谓语不能使用单列索引。通过使用另一个虚拟列来创建多列索引，就可以在IS NULL子句中启用索引。

如select × from t1 where n1 is null，如果create index t1_n1 on t1(n1)单列索引，查询将全表扫描。

这时create index t1_n1 on t1(n1,0) 加入一个虚拟0值，查询时优化器会使用索引。

索引结构类型:

B-树索引 倒置的树形结构，查询索引时会遍历树。树结构过高影响性能。

位图索引 使用位图表示列值的行编号。不适用有大量更新的列或者具有较多DML操作（ 数据操纵语言insert、update、delete，大量DML操作容易引起锁定问题）的表。

         适用于大多数对具有较少唯一值的列进行只读运算的数据仓库表。如要表需要定期更新，需要先删除位图索引，加载数据后在创建位图索引。

索引组织表（index organized tables, IOTs） 表本身被组织成一个索引，所有列存储在索引树自身上。常规表中每一行都有一个行编号，而在IOTs中数据行存储

在索引结构中，没有物理行编号。适用于（1）数据行长度较短的表 （2）大多使用主键进行访问的表

可以通过organization index建立IOT Sales_iot，IOT是一种能够有效减少数据行而且需要进行大量DML和Select活动的表中额外索引特殊结构。

 

索引分区

局部索引：局部分区索引使用local关键字来建立，分区边界与表相同。与每个表分区相联结的有一个索引分区。因为维护操作可以在独立分区进行，表的可用性更好，

对于索引分区的维护仅需要锁定相应的表分区而不是整张表。分区数过多（几千）影响性能。

全局索引：用GLOBAL来创建。索引的分区边界和表的分区边界不一定要匹配，表和索引分区键也可以不一样。

散列分区和范围分区

 

如果一个谓语在索引列上应用了函数，优化器不会选用该列上的索引。如to_char(id)='100'，就不会选用id列上的索引。

create index t1_index on t1(to_char(id))

函数必须是确定性函数，即每次执行必须返回一致的值。

 

六 Select之外内容

如果需要删除整张表或者分区内的所有数据行，使用TRUNCATE效率更高。

缺点：是DDL命令，有一次隐式提交（一旦截断就提交了，没法恢复）|只能整张表截断|不能闪回表截断之前的状态

merge 表存在就更新，否则插入。9i版本引入。

merge into table_name using (subquery) on (subquery.column=table.column)

when matched then update ...

when not matched then insert ...