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;

 

然后对比如下:

SQL语句	ToprowDB	Oracle	MySQL	PostgreSQL
S1	上拉优化（nested loop）	上拉优化（sort merge）	上拉优化（nested loop）	上拉优化（子查询先被物化）
S2	同上	同上	同上	同上
S3	同上	同上	同上	同上

分析：

q  从整体上看，派生表是非相关的

q  四个数据库采用的优化技术都是上拉子查询，说明四个数据库基本相似

q  三条子查询SQL语句的优化结果在各自的数据库中一致，即和子查询目标列是否有索引/唯一没有直接关系

q  Oracle 可以对特定的非 SPJ 类型的视图、子查询进行优化，但此处没有举例子