Oracle 2005 年出了一个 30 多页的小册子,《Query Optimization in Oracle Database10g Release 2》,介绍了常见的优化器技术。
我是做 SQL 执行的,优化部分只了解皮毛,从没有系统学习过。本系列逐个学习和介绍,自我提升,也帮助他人。
谓词推导(Transitive predicate generation)听上去高大上,实际并不复杂。所谓谓词,简单理解成 一个个的 WHERE 条件好了。
从
WHERE A.C1 = B.C1 AND A.C1 = 1231
可以推导出
WHERE A.C1 = B.C1 AND A.C1 = 1231 AND B.C1 = 1231
还可以继续推导出
WHERE A.C1 = 1231 AND B.C1 = 1231
这看上去是废话,但在工程实现中非常有用,它在 B 表上增加了一个过滤条件,可以让 B 表的扫描具备更多可能性。
看下面一个更具体的例子加深理解:
SELECT COUNT(DISTINCT O_ORDERKEY) FROM ORDER, LINEITEM WHERE O_ORDERKEY = L_ORDERKEY
AND O_ORDERDATE = L_SHIPDATE
AND O_ORDERDATE BETWEEN '1-JAN-2002' AND '31-JAN-2002'
可以推导出
SELECT COUNT(DISTINCT O_ORDERKEY) FROM ORDER, LINEITEM WHERE O_ORDERKEY = L_ORDERKEY
AND O_ORDERDATE = L_SHIPDATE
AND O_ORDERDATE BETWEEN '1-JAN-2002' AND '31-JAN-2002'
AND L_SHIPDATE BETWEEN '1-JAN-2002' AND '31-JAN-2002'
如果 LINEITEM 表在 L_SHIPDATE 上按照月份创建了 RANGE 分区,那么新增的谓词可以让 LINEITEM 表的扫描范围从全表扫描缩小到一个月的数据扫描,极大提升性能。
进一步地,可以干掉 O_ORDERDATE = L_SHIPDATE
,得到 SQL 如下:
SELECT COUNT(DISTINCT O_ORDERKEY) FROM ORDER, LINEITEM WHERE O_ORDERKEY = L_ORDERKEY
AND O_ORDERDATE BETWEEN '1-JAN-2002' AND '31-JAN-2002'
AND L_SHIPDATE BETWEEN '1-JAN-2002' AND '31-JAN-2002'
NOTE: 一般来说,优化器会竭尽全力把所有能推导出来的谓词都推出来,基表上的谓词都下压到基表,增加基表的过滤性。
上面都是等值推导,不等式也可以推,例如:
WHERE A.C1 = B.C1 AND A.C1 > 1231
可以推导出
WHERE A.C1 = B.C1 AND A.C1 > 1231 AND B.C1 > 1231
(不过,不能继续推导,把 A.C1 = B.C1
干掉哦)