执行计划中的节点如何比其子节点具有更小的成本？

Question

我一直认为执行计划中的节点只有在其子节点执行完毕后才能执行，因此节点的总成本必须大于或等于子节点的成本。 然而，情况并非总是如此，如以下示例所示：

Plan hash value: 2810258729

------------------------------------------------------------------------------------------------- ------------------------
| Id  | Operation                                | Name                         | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------------------------- ------------------------
|   0 | SELECT STATEMENT                         |                              |    10 |  1170 |  3871   (1)| 00:00:47 |
|*  1 |  COUNT STOPKEY                           |                              |       |       |            |          |
|   2 |   VIEW                                   |                              |    10 |  1170 |  3871   (1)| 00:00:47 |
|   3 |    VIEW                                  | V_TOP_GENRE                  |    10 |  1170 |  3871   (1)| 00:00:47 |
|   4 |     WINDOW SORT                          |                              |    10 |   890 |  3871   (1)| 00:00:47 |
|   5 |      MERGE JOIN                          |                              |    10 |   890 |  3871   (1)| 00:00:47 |
|   6 |       VIEW                               |                              |   345 | 10350 |  3867   (1)| 00:00:47 |
|   7 |        SORT GROUP BY                     |                              |   345 | 16560 |   133K  (1)| 00:26:41 |
|*  8 |         HASH JOIN                        |                              |  9627 |   451K|   133K  (1)| 00:26:41 |
|   9 |          VIEW                            |                              |  9627 |   366K|   133K  (1)| 00:26:41 |
|  10 |           SORT UNIQUE                    |                              |  9627 |   611K|   133K (51)| 00:26:41 |
|  11 |            UNION-ALL                     |                              |       |       |            |          |
|* 12 |             HASH JOIN                    |                              |  6639 |   421K| 66681   (1)| 00:13:21 |
|  13 |              INDEX FAST FULL SCAN        | T_CREATIVE_SELECTED_ADV_CREA | 28973 |   169K|     9   (0)| 00:00:01 |
|  14 |              NESTED LOOPS                |                              | 22243 |  1281K| 66671   (1)| 00:13:21 |
|  15 |               TABLE ACCESS BY INDEX ROWID| REPORT_FILTER_TIMERANGE      |     1 |    24 |     1   (0)| 00:00:01 |
|* 16 |                INDEX UNIQUE SCAN         | SYS_C0053942                 |     1 |       |     1   (0)| 00:00:01 |
|* 17 |               TABLE ACCESS FULL          | INSERTION_TV_RADIO           | 22243 |   760K| 66670   (1)| 00:13:21 |
|* 18 |             HASH JOIN                    |                              |  2988 |   189K| 66697   (1)| 00:13:21 |
|  19 |              INDEX FAST FULL SCAN        | T_CREATIVE_SELECTED_ADV_CREA | 28973 |   169K|     9   (0)| 00:00:01 |
|  20 |              NESTED LOOPS                |                              | 10010 |   576K| 66688   (1)| 00:13:21 |
|  21 |               TABLE ACCESS BY INDEX ROWID| REPORT_FILTER_TIMERANGE      |     1 |    24 |     1   (0)| 00:00:01 |
|* 22 |                INDEX UNIQUE SCAN         | SYS_C0053942                 |     1 |       |     1   (0)| 00:00:01 |
|* 23 |               TABLE ACCESS FULL          | INSERTION_TV_RADIO           | 10010 |   342K| 66687   (1)| 00:13:21 |
|  24 |          TABLE ACCESS FULL               | ASSIGNMENT_BROADCAST_GENRE   | 25135 |   220K|    20   (0)| 00:00:01 |
|* 25 |       SORT JOIN                          |                              |   345 | 10005 |     4  (25)| 00:00:01 |
|  26 |        TABLE ACCESS FULL                 | GENRE                        |   345 | 10005 |     3   (0)| 00:00:01 |
------------------------------------------------------------------------------------------------- ------------------------


Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter(ROWNUM<=10)
   8 - access("A"."BROADCAST_ID"="C"."BROADCAST_ID")
  12 - access("AD"."CREATIVE_ID"="A"."CREATIVE_ID")
  16 - access("B"."RANGE_NAME"='current')
  17 - filter("A"."BROADCAST_BEFORE_ID"<>(-1) AND "A"."INS_DATE">="B"."START_DATE" AND
              "A"."INS_DATE"<="B"."END_DATE")
  18 - access("AD"."CREATIVE_ID"="A"."CREATIVE_ID")
  22 - access("B"."RANGE_NAME"='current')
  23 - filter("A"."BROADCAST_AFTER_ID"<>(-1) AND "A"."BROADCAST_BEFORE_ID"<>(-1) AND
              "A"."BROADCAST_BEFORE_ID"<>"A"."BROADCAST_AFTER_ID" AND "A"."INS_DATE">="B"."START_ DATE" AND "A"."INS_DATE"<="B"."END_DATE")
  25 - access("TA"."GENRE_ID"="G"."GENRE_ID")
       filter("TA"."GENRE_ID"="G"."GENRE_ID")

读取第 6 行和第 7 行之间成本差异的正确方法是什么？

Answer 1

也许尝试另一个工具（TOAD / SQL*Plus / 等）来查看结果是否相同（即确定是客户端显示问题还是服务器问题）？

您的所有表格都已分析吗？

Answer 2

您没有提供 SQL，但在整个查询中可能有标量子查询 - 在这种情况下，EXPLAIN PLAN 输出子查询的单次执行成本，但不知道它将执行多少次。

[编辑]
我想如果我看一下，我会找到一个乔纳森·刘易斯的参考资料，可以更好地解释这一点 - 请参阅 http://jonathanlewis.wordpress.com/2007/10/12/scalar-subqueries/