优化嵌套查询的oracle,oracle 查询优化

1 、没有银弹

join 、in、exists等 没有绝对的高效方法，具体问题要具体分析。

2、怎么分析

EXPLAIN PLAN FOR SELECT * FROM PRODUCTS

select * from table(dbms_xplan.display);

1) 查看join 方式：

hash join：散列连接

使用两个表中较小的表(通常是小一点的那个表或数据源)利用连接键(JOIN KEY)在内存中建立散列表，将列数据存储到hash列表中，然后扫描较大的表，同样对JOIN KEY进行HASH后探测散列表，找出与散列表匹配的行

merge join：排序合并连接

不等价关联时，两个表各自排序，然后从各自的排序表中抽取数据，到另一个排序表中做匹配

select emp.username,emp.salary from emp inner join emp2 where emp.salary < emp2.salary

nested loop：嵌套循环连接

循环从一张表中读取数据(驱动表outer table)，然后访问另一张表(被查找表 inner table,通常有索引)。驱动表中的每一行与inner表中的相应记录JOIN。类似一个嵌套的循环。

JOIN的顺序很重要，驱动表的记录集一定要小，返回结果集的响应时间是最快的。

2)表访问方式

通过ROWID访问表

两种方式：1：用户获取到rowid，然后用rowid查询；2：通过索引查询拿到rowid，再查询数据。

索引扫描(index scan)

1：唯一性索引扫描

2：索引全扫描

扫描目标索引所有叶子块的所有索引行，不用扫描

表而是通过索引就可以直接返回所需要的所有数据，要求：select和where 列都存在索引

3：索引快速扫描：与2类似

4 ：索引跳跃式扫：复合索引只指定了一个索引查询条件，Oracle自动优化完成

5：索引范围扫描

当扫描的对象是唯一性索引时，此时目标SQL的where条件一定是范围查询(谓词条件为 BETWEEN、等)；当扫描的对象是非唯一性索引时，对目标SQL的where条件没有限制(可以是等值查询，也可以是范围查询)

全表扫描(Full Table Scan)

目标表的数据量不大时执行效率是非常高的，但全表扫描最大的问题就在于走全表扫描的目标SQL的执行时间会不稳定、不可控，这个执行时间一定会随着目标表数据量的递增而递增。

image.png

3、一些套路

1： inner join 时 ，小表在左边(nested loop时影响很大)

2： 函数 和 类型转换 会使索引失效，如果确实要用函数，可创建函数索引

3： where 条件 顺序影响并不大

4： B-tree索引 is null不会走,is not null会走,位图索引 is null,is not null 都会走索引

5： !=， <> 不会走索引

6：not exists和not in 没有必然的快慢，是hash join和nested loop的比较，要具体分析

7：多个and 查询 列之间如果有关系，建复合索引比每个建单一索引要稍快