3.1 派生表的优化
在子查询中，有一类常见的子查询形式，是子查询出现在FROM子句中，这样的子查询，有个学名，叫做“派生表”。这样的且符合SPJ格式要求派生表，可以被优化。

这一节，我们来对比四种数据库，看看各个数据库对于派生表的支持情况。大家可以看到，我们将加入Oracle的对比。

3.1.1 Q1
select * from (select idx as id2 from test2) as temp,test1;

3.1.1.1 ToprowDB/Informix
QUERY: (OPTIMIZATION TIMESTAMP: 08-15-2016 18:22:01)

------

select * from (select idx as id2 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.1.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.1.3 Mysql
mysql> explain select * from (select idx as id2 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 | index | NULL | index1 | 5 | NULL | 4 | Using index |

+----+-------------+------------+-------+---------------+--------+---------+------+------+---------------------------------------+

3 rows in set (0.00 sec)



子查询被上拉，执行了嵌套循环连接。

3.1.1.4 PostgreSQL
postgres=# explain select * from (select idx as id2 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.2 Q2
select * from (select idpk as id2 from test3) as temp,test1;

3.1.2.1 ToprowDB
QUERY: (OPTIMIZATION TIMESTAMP: 08-15-2016 18:22:09)

------

select * from (select idpk as id2 from test3),test1



Estimated Cost: 4

Estimated # of Rows Returned: 1



1) informix.test1: SEQUENTIAL SCAN

2) informix.test3: SEQUENTIAL SCAN

NESTED LOOP JOIN





Query statistics:

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



Table map :

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

Internal name Table name

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

t1 test1

t2 test3



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 10 1 10 00:00.00 2



type rows_prod est_rows time est_cost

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

nljoin 10 1 00:00.00 4



子查询被上拉，执行了嵌套循环连接。

3.1.2.2 Oracle
Execution Plan

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

Plan hash value: 2676247877

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

| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |

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

| 0 | SELECT STATEMENT | | 10 | 610 | 4 (0)| 00:00:01 |

| 1 | MERGE JOIN CARTESIAN| | 10 | 610 | 4 (0)| 00:00:01 |

| 2 | TABLE ACCESS FULL | TEST3 | 2 | 26 | 2 (0)| 00:00:01 |

| 3 | BUFFER SORT | | 5 | 240 | 2 (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.2.3 Mysql
mysql> explain select * from (select idpk as id2 from test3) as temp,test1;

+----+-------------+------------+-------+---------------+---------+---------+------+------+---------------------------------------+

| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |

+----+-------------+------------+-------+---------------+---------+---------+------+------+---------------------------------------+

| 1 | PRIMARY | <derived2> | ALL | NULL | NULL | NULL | NULL | 2 | NULL |

| 1 | PRIMARY | test1 | ALL | NULL | NULL | NULL | NULL | 5 | Using join buffer (Block Nested Loop) |

| 2 | DERIVED | test3 | index | NULL | PRIMARY | 4 | NULL | 2 | Using index |

+----+-------------+------------+-------+---------------+---------+---------+------+------+---------------------------------------+

3 rows in set (0.00 sec)



子查询被上拉，执行了嵌套循环连接。



3.1.2.4 PostgreSQL
postgres=# explain select * from (select idpk as id2 from test3) as temp,test1;

QUERY PLAN

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

Nested Loop (cost=0.00..5815.30 rows=462400 width=96)

-> Seq Scan on test3 (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 行记录)



子查询被物化后上拉，执行了嵌套循环连接。
————————————————
版权声明：本文为CSDN博主「那海蓝蓝」的原创文章，遵循CC 4.0 BY-SA版权协议，转载请附上原文出处链接及本声明。
原文链接：https://blog.csdn.net/fly2nn/article/details/61924799