ToprowDB Dynamic Server 查询优化技术---子查询优化--03-3

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