Re: correlated subquery: number of executions

  • From: "jaromir nemec" <jaromir@xxxxxxxxxxxx>
  • To: <Freek.DHooge@xxxxxxxxx>, <oracle-l@xxxxxxxxxxxxx>
  • Date: Wed, 11 Feb 2009 00:38:43 +0100

Hello Freek,


sys@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)
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?
I'd expect due to the filter that the query is performed on the per row basis. But why not run it with gather_plan_statistics hint and show the display_cursor ALLSTATS statistics. This should uniquely answert the question. 

Regards,

Jaromir D.B. Nemec


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

Other related posts:

  1. Home
  2. oracle-l
  3. Archive
  4. 02-2009