How can a node in an execution plan have smaller costs than its child?

Question

I always thought that a node in an execution plan can only be executed after its children have executed, and thus the total cost of a node has to be greater or equal than the cost of the child nodes. However, this is not always the case, like in the following example:

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")

What is the right way to read the difference in costs between line 6 and 7?

Answer 1

Maybe try another tool (TOAD / SQL*Plus / etc) to see if the result is the same (ie to determine if it's a client-display or server issue)?

Are all your tables ANALYZEd?

Answer 2

You didn't provide the SQL, but you probably have scalar subqueries within the overall query - EXPLAIN PLAN outputs the cost of a single execution of the subquery in this case, but doesn't know how many times it will execute.

[Edit] I figured if I looked I would find a Jonathan Lewis reference that explains this better - see http://jonathanlewis.wordpress.com/2007/10/12/scalar-subqueries/