Oracle的子查询优化之一

子查询优化是查询优化器的重要内容。查询优化器对子查询优化的支持程度是衡量查询优化器优劣的重要指标。

在《数据库查询优化器的艺术：原理解析与SQL性能优化》一书中，介绍并对比了Pg、MySQL的优化器技术，其中包括子查询的优化技术，现在，我们来对比一下Pg、MySQL和Oracle的子查询的优化技术。总的看，Oracle的子查询优化技术还是最好的，这点可以从表一、表二看出。

 

 

子查询优化技术	Oracle	PostgreSQL	MySQL
子查询合并	支持	不支持	不支持
子查询展开(派生表)	支持	支持	支持
聚集子查询消除	支持	不支持	不支持

表一 子查询优化技术对比表

 

常见子查询		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 Data                                                                   

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

/*+                                                                            

BEGIN_OUTLINE_DATA                                                             

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_DATA                                                               

*/                                                                             

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]     

 

待续......