3.1.3 Q3
select * from (select age as age2 from test2) as temp,test1;
3.1.3.1 ToprowDB
QUERY: (OPTIMIZATION TIMESTAMP: 08-15-2016 18:22:17)
------
select * from (select age as age2 from test2),test1
Estimated Cost: 4
Estimated # of Rows Returned: 1
1) informix.test1: SEQUENTIAL SCAN
2) informix.test2: SEQUENTIAL SCAN
NESTED LOOP JOIN
Query statistics:
-----------------
Table map :
----------------------------
Internal name Table name
----------------------------
t1 test1
t2 test2
type table rows_prod est_rows rows_scan time est_cost
-------------------------------------------------------------------
scan t1 5 1 5 00:00.00 2
type table rows_prod est_rows rows_scan time est_cost
-------------------------------------------------------------------
scan t2 20 1 20 00:00.00 2
type rows_prod est_rows time est_cost
-------------------------------------------------
nljoin 20 1 00:00.00 4
子查询被上拉，执行了嵌套循环连接。
3.1.3.2 Oracle
Execution Plan
----------------------------------------------------------
Plan hash value: 1251974749
------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 20 | 1220 | 5 (0)| 00:00:01 |
| 1 | MERGE JOIN CARTESIAN| | 20 | 1220 | 5 (0)| 00:00:01 |
| 2 | TABLE ACCESS FULL | TEST2 | 4 | 52 | 2 (0)| 00:00:01 |
| 3 | BUFFER SORT | | 5 | 240 | 3 (0)| 00:00:01 |
| 4 | TABLE ACCESS FULL | TEST1 | 5 | 240 | 1 (0)| 00:00:01 |
------------------------------------------------------------------------------
Note
-----
- dynamic sampling used for this statement (level=2)
子查询被上拉，执行了sort merge连接。
3.1.3.3 Mysql
mysql> explain select * from (select age as age2 from test2) as temp,test1;
+----+-------------+------------+------+---------------+------+---------+------+------+---------------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+------------+------+---------------+------+---------+------+------+---------------------------------------+
| 1 | PRIMARY | <derived2> | ALL | NULL | NULL | NULL | NULL | 4 | NULL |
| 1 | PRIMARY | test1 | ALL | NULL | NULL | NULL | NULL | 5 | Using join buffer (Block Nested Loop) |
| 2 | DERIVED | test2 | ALL | NULL | NULL | NULL | NULL | 4 | NULL |
+----+-------------+------------+------+---------------+------+---------+------+------+---------------------------------------+
3 rows in set (0.00 sec)
子查询被上拉，执行了嵌套循环连接。
3.1.3.4 PostgreSQL
postgres=# explain select * from (select age as age2 from test2) as temp,test1;
QUERY PLAN
---------------------------------------------------------------------
Nested Loop (cost=0.00..5815.30 rows=462400 width=96)
-> Seq Scan on test2 (cost=0.00..16.80 rows=680 width=4)
-> Materialize (cost=0.00..20.20 rows=680 width=92)
-> Seq Scan on test1 (cost=0.00..16.80 rows=680 width=92)
(4 行记录)
子查询物化后被上拉，执行了嵌套循环连接。
3.1.4 小结
我们把原始的SQL语句复制一份，目的是查阅方便。
S1: select * from (select idx as id2 from test2) as temp,test1;
S2: select * from (select idpk as id2 from test3) as temp,test1;
S3: select * from (select age as age2 from test2) as temp,test1;
然后对比如下:
分析：
q 从整体上看，派生表是非相关的
q 四个数据库采用的优化技术都是上拉子查询，说明四个数据库基本相似
q 三条子查询SQL语句的优化结果在各自的数据库中一致，即和子查询目标列是否有索引/没有直接关系
q Oracle 可以对特定的非 SPJ 类型的视图、子查询进行优化，但此处没有举例子
————————————————
版权声明：本文为CSDN博主「那海蓝蓝」的原创文章，遵循CC 4.0 BY-SA版权协议，转载请附上原文出处链接及本声明。
原文链接：https://blog.csdn.net/fly2nn/article/details/61924801
