The table has 9M rows:
SQL> with function f return int is
2 begin
3 for r in (select value from v$mystat natural join v$statname where name like 'IM scan rows') loop
4 dbms_output.put_line(r.value);
5 return r.value;
6 end loop;
7 end;
8 select f() from t_inmemory where rownum<=1
9 ;
10 /
F()
----------
0
1 row selected.
SQL> /
F()
----------
491436
1 row selected.
SQL> /
F()
----------
982872
1 row selected.
DDL and Plan
create table t_inmemory inmemory as with gen as (select 0 id from dual connect by level<=3e3) select 0 n from gen,gen; SQL_ID cpgrrfv9h6m52, child number 0 ------------------------------------- with function f return int is begin for r in (select value from v$mystat natural join v$statname where name like 'IM scan rows') loop dbms_output.put_line(r.value); return r.value; end loop; end; select f() from t_inmemory where rownum<=1 Plan hash value: 3697881339 ---------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Cost (%CPU)| Time | ---------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | | 3 (100)| | |* 1 | COUNT STOPKEY | | | | | | 2 | TABLE ACCESS INMEMORY FULL| T_INMEMORY | 1 | 3 (0)| 00:00:01 | ---------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 1 - filter(ROWNUM<=1)
[collapse]