子查询优化是查询优化器的重要内容。查询优化器对子查询优化的支持程度是衡量查询优化器优劣的重要指标。
在《数据库查询优化器的艺术:原理解析与SQL性能优化》一书中,介绍并对比了Pg、MySQL的优化器技术,其中包括子查询的优化技术,现在,我们来对比一下Pg、MySQL和Oracle的子查询的优化技术。总的看,Oracle的子查询优化技术还是最好的,这点可以从表一、表二看出。
表一 子查询优化技术对比表
常见子查询 |
| Oracle | PostgreSQL | MySQL |
IN类型 |
| 支持 | 支持 | 支持 |
NOT IN类型 |
| 支持 | 不支持 | 支持 |
ALL类型 | “>ALL” | 支持 | 不支持 | 用MAX优化 |
“=ALL” | 用“EXISTS strategy”方式优化 | |||
“<ALL” | 用MIN操作优化 | |||
ANY类型 | “>ANY” | 支持 | 支持 | 用MIN优化 |
“=ALL” | 用“EXISTS strategy”方式优化 | |||
“<ALL” | 用MAX操作优化 | |||
SOME类型 | “>SOME” | 支持 | 支持 | 用MIN优化 |
“=SOME” | 用“EXISTS strategy”方式优化 | |||
“<SOME” | 用MAX操作优化 | |||
EXISTS类型 |
| 支持 | 支持 | 不支持 |
NOT EXISTS类型 |
| 支持 | 支持 | 不支持 |
语句格式说明 | <key> <op> <supquery> |
表二 常规形式的子查询的优化技术对比表
Oracle具体事例:
一 子查询合并、聚集子查询消除,具体事例,可以参考《Oracle高性能SQL引擎剖析:SQL优化与调优机制详解》
如:http://book.51cto.com/art/201302/381136.htm
二 子查询展开
SELECT * FROM t1, (SELECT * FROM t2 WHERE t2.a2 >10) v_t2 WHERE t1.a1<10 AND v_t2.a2<20;
Oracle的执行计划:MERGE JOIN CARTESIAN表明嵌套层次消除,子查询被上拉。
Plan hash value: 2804794538
----------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 39 | 702 | 11 (0)| 00:00:01 |
| 1 | MERGE JOIN CARTESIAN | | 39 | 702 | 11 (0)| 00:00:01 | 嵌套层次消除,子查询被上拉
| 2 | TABLE ACCESS BY INDEX ROWID | T2 | 4 | 36 | 3 (0)| 00:00:01 |
|* 3 | INDEX RANGE SCAN | SYS_C0010114 | 4 | | 2 (0)| 00:00:01 |
| 4 | BUFFER SORT | | 9 | 81 | 8 (0)| 00:00:01 |
| 5 | TABLE ACCESS BY INDEX ROWID| T1 | 9 | 81 | 2 (0)| 00:00:01 |
|* 6 | INDEX RANGE SCAN | SYS_C0010126 | 9 | | 1 (0)| 00:00:01 |
----------------------------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
1 - SEL$F5BB74E1
2 - SEL$F5BB74E1 / T2@SEL$2
3 - SEL$F5BB74E1 / T2@SEL$2
5 - SEL$F5BB74E1 / T1@SEL$1
6 - SEL$F5BB74E1 / T1@SEL$1
Outline Da
-------------
/*+
BEGIN_OUTLINE_DA
USE_MERGE_CARTESIAN(@"SEL$F5BB74E1" "T1"@"SEL$1")
LEADING(@"SEL$F5BB74E1" "T2"@"SEL$2" "T1"@"SEL$1")
INDEX_RS_ASC(@"SEL$F5BB74E1" "T1"@"SEL$1" ("T1"."A1"))
INDEX_RS_ASC(@"SEL$F5BB74E1" "T2"@"SEL$2" ("T2"."A2"))
OUTLINE(@"SEL$2")
OUTLINE(@"SEL$1")
MERGE(@"SEL$2")
OUTLINE_LEAF(@"SEL$F5BB74E1")
ALL_ROWS
DB_VERSION('11.1.0.6')
OPTIMIZER_FEATURES_ENABLE('11.1.0.6')
IGNORE_OPTIM_EMBEDDED_HINTS
END_OUTLINE_DA
*/
Predicate Information (identified by operation id):
---------------------------------------------------
3 - access("T2"."A2">10 AND "T2"."A2"<20)
6 - access("T1"."A1"<10)
Column Projection Information (identified by operation id):
-----------------------------------------------------------
1 - (#keys=0) "T2"."ID2"[NUMBER,22], "T2"."A2"[NUMBER,22], "T2"."B2"[NUMBER,22],
"T1"."ID1"[NUMBER,22], "T1"."A1"[NUMBER,22], "T1"."B1"[NUMBER,22]
2 - "T2"."ID2"[NUMBER,22], "T2"."A2"[NUMBER,22], "T2"."B2"[NUMBER,22]
3 - "T2".ROWID[ROWID,10], "T2"."A2"[NUMBER,22]
4 - (#keys=0) "T1"."ID1"[NUMBER,22], "T1"."A1"[NUMBER,22], "T1"."B1"[NUMBER,22]
5 - "T1"."ID1"[NUMBER,22], "T1"."A1"[NUMBER,22], "T1"."B1"[NUMBER,22]
6 - "T1".ROWID[ROWID,10], "T1"."A1"[NUMBER,22]
待续......