ToprowDB Dynamic Server 查询优化技术---子查询优化--02-3
1.2 ANY/SOME/ALL类型的子查询
我们进行如下的子查询，分别用S6—S11表示：
S6: SELECT * FROM t3 WHERE b3 >= ANY (SELECT b1 FROM t1);
S7: SELECT * FROM t3 WHERE b3 >= ANY (SELECT a1 FROM t1);
S8: SELECT * FROM t3 WHERE b3 <= SOME (SELECT a1 FROM t1);
S9: SELECT * FROM t3 WHERE b3 = SOME (SELECT a1 FROM t1);
S10: SELECT * FROM t3 WHERE b3 <= ALL (SELECT a1 FROM t1);
S11: SELECT * FROM t3 WHERE b3 <= ALL (SELECT a1 FROM t1 WHERE a3=a1);
1.2.1 ToprowDB
1.2.1.1 S6语句
查看查询执行计划，子查询被优化。
SELECT * FROM t3 WHERE EXISTS (SELECT b1 FROM t1 WHERE a3=a1)
Estimated Cost: 4
Estimated # of Rows Returned: 1
1) informix.t3: SEQUENTIAL SCAN
2) informix.t1: INDEX PATH
(1) Index Name: informix. 102_7
Index Keys: a1 (Serial, fragments: ALL)
Lower Index Filter: informix.t3.a3 = informix.t1.a1
NESTED LOOP JOIN //子查询被消除
Query statistics:
-----------------
Table map :
----------------------------
Internal name Table name
----------------------------
t1 t3
t2 t1
type table rows_prod est_rows rows_scan time est_cost
-------------------------------------------------------------------
scan t1 6 1 6 00:00.00 2
type table rows_prod est_rows rows_scan time est_cost
-------------------------------------------------------------------
scan t2 3 1 3 00:00.00 0
type rows_prod est_rows time est_cost
-------------------------------------------------
nljoin 3 1 00:00.01 4
1.2.1.2 S7语句
查看查询执行计划，子查询被优化。
SELECT * FROM t3 WHERE EXISTS (SELECT a1 FROM t1 WHERE a3=a1)
Estimated Cost: 3
Estimated # of Rows Returned: 1
1) informix.t3: SEQUENTIAL SCAN
2) informix.t1: INDEX PATH
(1) Index Name: informix. 102_7
Index Keys: a1 (Key-Only) (Serial, fragments: ALL)
Lower Index Filter: informix.t3.a3 = informix.t1.a1
NESTED LOOP JOIN //子查询被消除
Query statistics:
-----------------
Table map :
----------------------------
Internal name Table name
----------------------------
t1 t3
t2 t1
type table rows_prod est_rows rows_scan time est_cost
-------------------------------------------------------------------
scan t1 6 1 6 00:00.00 2
type table rows_prod est_rows rows_scan time est_cost
-------------------------------------------------------------------
scan t2 3 1 3 00:00.01 0
type rows_prod est_rows time est_cost
-------------------------------------------------
nljoin 3 1 00:00.01 3
1.2.1.3 S8语句
查看查询执行计划，子查询被优化。
SELECT * FROM t3 WHERE EXISTS (SELECT id1 FROM t1 WHERE a3=a1)
Estimated Cost: 4
Estimated # of Rows Returned: 1
1) informix.t3: SEQUENTIAL SCAN
2) informix.t1: INDEX PATH
(1) Index Name: informix. 102_7
Index Keys: a1 (Serial, fragments: ALL)
Lower Index Filter: informix.t3.a3 = informix.t1.a1
NESTED LOOP JOIN //子查询被消除
Query statistics:
-----------------
Table map :
----------------------------
Internal name Table name
----------------------------
t1 t3
t2 t1
type table rows_prod est_rows rows_scan time est_cost
-------------------------------------------------------------------
scan t1 6 1 6 00:00.00 2
type table rows_prod est_rows rows_scan time est_cost
-------------------------------------------------------------------
scan t2 3 1 3 00:00.00 0
type rows_prod est_rows time est_cost
-------------------------------------------------
nljoin 3 1 00:00.00 4
1.2.1.4 S9语句
查看查询执行计划，子查询没有被优化。
SELECT * FROM t3 WHERE b3 = SOME (SELECT a1 FROM t1)
Estimated Cost: 4
Estimated # of Rows Returned: 1
1) informix.t3: SEQUENTIAL SCAN
Filters: informix.t3.b3 = ANY <subquery>
Subquery:
---------
Estimated Cost: 2
Estimated # of Rows Returned: 1
1) informix.t1: SEQUENTIAL SCAN
Query statistics:
-----------------
Table map :
----------------------------
Internal name Table name
----------------------------
t1 t3
type table rows_prod est_rows rows_scan time est_cost
-------------------------------------------------------------------
scan t1 6 1 6 00:00.02 4
Subquery statistics:
--------------------
Table map :
----------------------------
Internal name Table name
----------------------------
t1 t1
type table rows_prod est_rows rows_scan time est_cost
-------------------------------------------------------------------
scan t1 3 1 3 00:00.00 2
1.2.1.5 S10语句
查看查询执行计划，子查询没有被优化。
SELECT * FROM t3 WHERE EXISTS (SELECT b1 FROM t1 WHERE a1>2)
Estimated Cost: 2
Estimated # of Rows Returned: 1
1) informix.t3: SEQUENTIAL SCAN
Filters: EXISTS <subquery>
Subquery:
---------
Estimated Cost: 2
Estimated # of Rows Returned: 1
1) informix.t1: INDEX PATH
(1) Index Name: informix. 102_7
Index Keys: a1 (Serial, fragments: ALL)
Lower Index Filter: informix.t1.a1 > 2
Query statistics:
-----------------
Table map :
----------------------------
Internal name Table name
----------------------------
t1 t3
type table rows_prod est_rows rows_scan time est_cost
-------------------------------------------------------------------
scan t1 6 1 6 00:00.01 2
Subquery statistics:
--------------------
Table map :
----------------------------
Internal name Table name
----------------------------
t1 t1
type table rows_prod est_rows rows_scan time est_cost
-------------------------------------------------------------------
scan t1 1 1 1 00:00.00 2
1.2.1.6 S11语句
查看查询执行计划，子查询被优化（半连接优化）。
SELECT * FROM t3 WHERE EXISTS (SELECT a1 FROM t1 WHERE b3=b1)
Estimated Cost: 4
Estimated # of Rows Returned: 1
1) informix.t3: SEQUENTIAL SCAN
2) informix.t1: SEQUENTIAL SCAN (First Row)
Filters: informix.t3.b3 = informix.t1.b1
NESTED LOOP JOIN (Semi Join)
Query statistics:
-----------------
Table map :
----------------------------
Internal name Table name
----------------------------
t1 t3
t2 t1
type table rows_prod est_rows rows_scan time est_cost
-------------------------------------------------------------------
scan t1 6 1 6 00:00.00 2
type table rows_prod est_rows rows_scan time est_cost
-------------------------------------------------------------------
scan t2 1 1 3 00:00.00 2
type rows_prod est_rows time est_cost
-------------------------------------------------
nljoin 6 1 00:00.00 4
————————————————
版权声明：本文为CSDN博主「那海蓝蓝」的原创文章，遵循CC 4.0 BY-SA版权协议，转载请附上原文出处链接及本声明。
原文链接：https://blog.csdn.net/fly2nn/article/details/61924794
相关文章