1, 测试数据如下:
SQL> select * from t1;
a | b | c
- ---+----+---
- 1 | 10 | 1
- 2 | 20 | 2
- 3 | 30 | 3
- 4 | 40 | 4
- 5 | 50 | 5
- 6 | 60 | 6
- (6 rows)
- SQL> select * from t2;
a | b | d
- ---+----+---
- 1 | 10 | 1
- 2 | 20 | 2
- 3 | 30 | 3
- (3 rows)
2, 解析示例 SQL 如下 :
- select *
- from (
- select * from t1 where c>= 2
- ) t1 left join (
- select * from t2 where b <30
) t2 on t1.a = t2.a
- and t2.d> 1
- where t1.b <50
- ;
3,Oracle 数据库查看执行结果及执行计划:
- SQL> select *
- from (
- select * from t1 where c>= 2
- ) t1 left join (
- select * from t2 where b <30
) t2 on t1.a = t2.a
- and t2.d> 1
- where t1.b <50
- ;
A B C A B D
- ---------- ---------- ---------- ---------- ---------- ----------
- 2 20 2 2 20 2
- 3 30 3
- 4 40 4
- Execution Plan
- ----------------------------------------------------------
- Plan hash value: 1823443478
- ---------------------------------------------------------------------------
- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
- ---------------------------------------------------------------------------
- | 0 | SELECT STATEMENT | | 3 | 234 | 7 (15)| 00:00:01 |
- |* 1 | HASH JOIN OUTER | | 3 | 234 | 7 (15)| 00:00:01 |
- |* 2 | TABLE ACCESS FULL| T1 | 3 | 117 | 3 (0)| 00:00:01 |
- |* 3 | TABLE ACCESS FULL| T2 | 1 | 39 | 3 (0)| 00:00:01 |
- ---------------------------------------------------------------------------
- Predicate Information (identified by operation id):
- ---------------------------------------------------
- 1 - access("T1"."A"="T2"."A"(+))
- 2 - filter("T1"."B"<50 AND "C">=2)
- 3 - filter("T2"."D"(+)>1 AND "B"(+)<30)
- Note
- -----
- - dynamic sampling used for this statement (level=2)
- Statistics
- ----------------------------------------------------------
0 recursive calls
- 0 db block gets
- 7 consistent gets
0 physical reads
0 redo size
926 bytes sent via SQL*Net to client
523 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
- 0 sorts (memory)
- 0 sorts (disk)
- 3 rows processed
4,PGSQL 数据库查看执行结果及执行计划:
- postgres=# select *
- postgres-# from (
- postgres(# select * from t1 where c>= 2
- postgres(# ) t1 left join (
- postgres(# select * from t2 where b <30
- postgres(# ) t2 on t1.a = t2.a
- postgres-# and t2.d> 1
- postgres-# where t1.b <50
- postgres-# ;
a | b | c | a | b | d
- ---+----+---+---+----+---
- 2 | 20 | 2 | 2 | 20 | 2
- 3 | 30 | 3 | | |
- 4 | 40 | 4 | | |
- (3 rows)
- postgres=# explain analyze select *
- postgres-# from (
- postgres(# select * from t1 where c>= 2
- postgres(# ) t1 left join (
- postgres(# select * from t2 where b <30
- postgres(# ) t2 on t1.a = t2.a
- postgres-# and t2.d> 1
- postgres-# where t1.b <50
- postgres-# ;
- QUERY PLAN
- ------------------------------------------------------------------------------------------------------------
- Hash Left Join (cost=37.04..85.88 rows=197 width=24) (actual time=0.020..0.027 rows=3 loops=1)
- Hash Cond: ("outer".a = "inner".a)
- -> Seq Scan on t1 (cost=0.00..36.55 rows=197 width=12) (actual time=0.005..0.008 rows=3 loops=1)
- Filter: ((c>= 2) AND (b <50))
- -> Hash (cost=36.55..36.55 rows=197 width=12) (actual time=0.006..0.006 rows=1 loops=1)
- -> Seq Scan on t2 (cost=0.00..36.55 rows=197 width=12) (actual time=0.002..0.003 rows=1 loops=1)
- Filter: ((b <30) AND (d> 1))
- Total runtime: 0.052 ms
- (8 rows)
5,MySQL 数据库查看执行结果及执行计划:
- mysql> select *
- -> from (
- -> select * from t1 where c>= 2
- -> ) t1 left join (
- -> select * from t2 where b <30
-> ) t2 on t1.a = t2.a
-> and t2.d> 1
-> where t1.b <50
- -> ;
- +---+----+---+------+------+------+
- | a | b | c | a | b | d |
- +---+----+---+------+------+------+
- | 2 | 20 | 2 | 2 | 20 | 2 |
- | 3 | 30 | 3 | NULL | NULL | NULL |
- | 4 | 40 | 4 | NULL | NULL | NULL |
- +---+----+---+------+------+------+
- 3 rows in set (0.05 sec)
- mysql> explain select *
- -> from (
- -> select * from t1 where c>= 2
- -> ) t1 left join (
- -> select * from t2 where b <30
-> ) t2 on t1.a = t2.a
-> and t2.d> 1
-> where t1.b <50
- -> ;
- +----+-------------+------------+------+---------------+-------------+---------+------+------+-------------+
- | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
- +----+-------------+------------+------+---------------+-------------+---------+------+------+-------------+
- | 1 | PRIMARY | <derived2> | ALL | NULL | NULL | NULL | NULL | 6 | Using where |
- | 1 | PRIMARY | <derived3> | ref | <auto_key0> | <auto_key0> | 8 | t1.a | 1 | Using where |
- | 3 | DERIVED | t2 | ALL | NULL | NULL | NULL | NULL | 3 | Using where |
- | 2 | DERIVED | t1 | ALL | NULL | NULL | NULL | NULL | 6 | Using where |
- +----+-------------+------------+------+---------------+-------------+---------+------+------+-------------+
- 4 rows in set (0.00 sec)
6, 针对以上 SQL 执行计划的分析:
1) 全表扫描左表 T1, 同时根据 T1 表子查询条件 "C">=2 和 where 过滤条件 "T1"."B"<50 联合过滤, 即 filter("T1"."B"<50 AND "C">=2), 计算结果临时表记为 tmp1;
2) 全表扫描右表 T2, 同时根据 T2 表子查询条件 "B"(+)<30 和 on 子句 "T2"."D"(+)>1 联合过滤, 即 filter("T2"."D"(+)>1 AND "B"(+)<30), 计算结果临时表记为 tmp2;
3) 左表 T1 及右表 T2 处理后临时表 tmp1 和 tmp2 通过 access("T1"."A"="T2"."A"(+)) 连接条件进行 Hash Left Join 操作, 左临时表结果集全量返回, 右表不匹配行置为 null, 返回结果临时表记为 tmp3;
4) 返回结果集.
7, 一些更为复杂得 SQL 如下, 有兴趣自行研究:
1) 测试数据
- create table tmp1 as
- select a,b,c,a as e from t1;
- create table tmp2 as
- select a,b,d,a as e from t2;
2) 示例 SQL
- select *
- from (
- select * from tmp1 where c>= 1
- ) t1 left join (
- select * from tmp2 where b <30
) t2 on t1.a = t2.a
- and t2.d> 1 and t1.e>= 2
- where t1.b <50
- ;
- select *
- from (
- select * from tmp1 where c>= 1
- ) t1 left join (
- select * from tmp2 where b <30
) t2 on t1.a = t2.a
- and t2.d> 1 and t1.e>= 2
- where t1.b < 50 and t2.e <= 3
- ;
来源: http://www.bubuko.com/infodetail-2689270.html