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.
[sourcecode language=”sql”]
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)
[/sourcecode]