RE: Semi-deterministic?

Using your test case on an 11.2.0.1 database, where you show 8 executions with the no_merge and scalar caching, I only show 2 executions. Then moving it into a WITH clause, and with materialize as originally alluded to, and what the OP also said he had also done, drops it to 1, at least in my case.

Some years ago, for some folks writing large *ETL* processes against 11.1, it seems like I couldn't count on the WITH materialize approach for all cases (and my preferred solution for them was a join, not a function ;-)). Anyway, with differences between versions, the ways our queries may be rewritten/transformed internally now, or in the future, it seems a bit risky to depend on any one approach to make only one call, so maybe the scalar caching approach makes sure worst case is avoided, though not always the absolute minimum number of calls???

And of course, there's always the question to ask if they should really even be using a function, and if they need to encapsulate some sort of logic for re-use/consistency, could that be done in a view. Not saying or implying that's the case here for the OP, just that it's something commonly seen.

Larry G. Elkins
elkinsl_at_verizon.net
Cell: 214.695.8605

> From: oracle-l-bounce_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Riyaj
> Shamsudeen
> Sent: Wednesday, February 05, 2014 6:21 PM
> Hi Matt
> I reviewed the executions in detail with a test case. I think, in your case, inline view is
> rewritten and merged in to the main query block and that explains the increase in executions. Please
> review 10053 trace output of your code with the inline view ( I know, you probably don't care).
>
> Following is the test case setup (to test it yourself, replace rs. with your username)
>
> REM tables log1 and t1 are created. Package pkg1 is created with an anonymous transaction so that we
> can keep track number of executions of the function call.
> REM function simply inserts a row in to log1 table for every execution.
>
> Even SQL Monitor output for the above SQL is showing one execution of the row source:(Execs column
> below) Sorry for the format, Not sure if it is possible to format in Oracle-l postings.
>
> ======================================================================================================
> ========================================
> | Id | Operation | Name | Rows | Cost | Time | Start | Execs |
> Rows | Activity | Activity Detail |
> | | | | (Estim) | | Active(s) | Active | |
> (Actual) | (%) | (# samples) |
> ======================================================================================================
> ========================================
> | 0 | SELECT STATEMENT | | | | 1 | +0 | 1 |
> 100 | | |
> | 1 | COUNT STOPKEY | | | | 1 | +0 | 1 |
> 100 | | |
> | 2 | NESTED LOOPS | | 1000 | 4 | 1 | +0 | 1 |
> 100 | | |
> | 3 | VIEW | | 1 | 2 | 1 | +0 | 1 |
> 1 | | |
> | 4 | COUNT STOPKEY | | | | 1 | +0 | 1 |
> 1 | | |
> | 5 | FAST DUAL | | 1 | 2 | 1 | +0 | 1 |
> 1 | | |
> | 6 | TABLE ACCESS STORAGE FULL FIRST ROWS | T1 | 1000 | 2 | 1 | +0 | 1 |
> 100 | | |
> ======================================================================================================
> ========================================
>
> However, 10053 trace of the SQL statement shows an interesting rewrite! Essentially, optimizer rewrote
> the inline view and merged with the main query block. That caused 100 execution and so, 100 rows
> inserted into the
> log1 table. I am fine with this explanation.
>
> SELECT /*+ USE_HASH ("DUAL") USE_HASH ("T1") */ "T1"."L1" "L1","TMP"."PKG1"."CALCULATE_N1"(100) "N1"
> FROM "TMP"."T1" "T1","SYS"."DUAL" "DUAL" WHERE ROWNUM<=100
>
> now, let's see if we can force the optimizer not to merge with the rownum and no_merge double
> protection.
>
> truncate table rs.log1;
> select /*+ monitor */ l1, p1.n1
> from rs.t1 ,
> (select /*+ no_merge */ rs.pkg1.calculate_n1(100) n1 from dual where rownum=1) p1 where rownum
> <=100 ;
>
> select count(*) from rs.log1;
> COUNT(*)
> ----------
> 8
>
> Eight is better than 100, But, why 8 executions, when there is a need for just 1 execution?.
> Interesting.
>

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