correlated subquery: number of executions

From: D'Hooge Freek <Freek.DHooge_at_uptime.be>
Date: Tue, 10 Feb 2009 18:08:55 +0100
Message-ID: <AF9ECF804B2F294BB43BC5063302658F2619CF86FE_at_ws03-exch07.iconos.be>

Hi,

I have following execution plan from an 10.2.0.4 database:

sys_at_TRIDION> explain plan for
  2 update tridion_cm_email.distributionlists dc   3 set total_contacts =

  4      ( select /*+ CARDINALITY (T 500000) */
  5               count(t.contact_id) as total_contacts
  6        from tridion_cm_email.t_contacts t
  7        group by distlist_id
  8        having t.distlist_id = dc.id
  9      );

Explained.

sys_at_TRIDION> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT

---

Plan hash value: 1025259390

---

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

---

| 0 | UPDATE STATEMENT | | 1524 | 12192 | 12 (0)| 00:00:01 |
| 1 | UPDATE | DISTRIBUTIONLISTS | | | | |
| 2 | TABLE ACCESS FULL | DISTRIBUTIONLISTS | 1524 | 12192 | 12 (0)| 00:00:01 |

|*  3 |   FILTER                |                   |       |       |            |          |


|   4 |    HASH GROUP BY        |                   |   500K|    12M|    61  (89)| 00:00:01 |

|   5 |     INDEX FAST FULL SCAN| IX_T_CONTACTS_01  |   500K|    12M|     7   (0)| 00:00:01 |

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

Predicate Information (identified by operation id):

---

   3 - filter("DISTLIST_ID"=:B1)

17 rows selected.

Now I'm wondering if the subquery will be executed 1 time per record in the distributionlists table (the ID column is a unique key, so subquery caching is not relevant here) or if oracle will cache the result from step 4 and then execute the filter per row in the distributionlists table?

Kind regards,

Freek D'Hooge
Uptime
Oracle Database Administrator
email: freek.dhooge_at_uptime.be
tel +32(0)3 451 23 82
http://www.uptime.be

--
http://www.freelists.org/webpage/oracle-l

Received on Tue Feb 10 2009 - 11:08:55 CST