drop table t1 purge;
drop table t2 purge;
drop function f1;
set echo on;
create table t1 as select level a, level b from dual connect by level<=100;
create table t2 as select level a, level b from dual connect by level<=100;
create function f1 return int as
   res int;
begin
   select count(*) into res from t2;
   return res;
end;
/
alter system flush shared_pool;

alter table t1 result_cache (mode force);
set feed on sql_id;
select count(*) cnt from t1 where f1>0;
def rc_sqlid = &_sql_id
col result_cache for a15;
select sql_id,child_number,plan_hash_value,invalidations,optimizer_env_hash_value,result_cache from v$sql s where sql_id='&rc_sqlid';
col PLAN_TABLE_OUTPUT for a120;
select t.* from v$sql s,table(dbms_xplan.display_cursor('&rc_sqlid',child_number)) t
where s.sql_id='&rc_sqlid';
col object_name new_val cache_id;
select object_name from v$sql_plan p where p.sql_id='&rc_sqlid' and operation='RESULT CACHE';
col name for a40;
col cache_id for a10;
col cache_key for a10;
select o.id,o.name, 
       d.*,
       do.cache_id, do.cache_key,do.object_no
from  v$result_cache_objects o
     ,v$result_cache_dependency d
     ,v$result_cache_objects do
where o.cache_id='&cache_id'
  and d.result_id=o.id
  and do.id = d.depend_id;
set echo off;

SQL> create table t1 as select level a, level b from dual connect by level<=100;

Table created.

SQL> create table t2 as select level a, level b from dual connect by level<=100;

Table created.

SQL> create function f1 return int as
  2     res int;
  3  begin
  4     select count(*) into res from t2;
  5     return res;
  6  end;
  7  /

Function created.

SQL> alter table t1 result_cache (mode force);

Table altered.

SQL> set feed on sql_id;
SQL> select count(*) cnt from t1 where f1>0;

       CNT
----------
       100

1 row selected.

SQL_ID: a9nyksby4dfqm
SQL> def rc_sqlid = &_sql_id
SQL> col result_cache for a15;
SQL> select sql_id,child_number,plan_hash_value,invalidations,optimizer_env_hash_value,result_cache from v$sql s where sql_id='&rc_sqlid';

SQL_ID        CHILD_NUMBER PLAN_HASH_VALUE INVALIDATIONS OPTIMIZER_ENV_HASH_VALUE RESULT_CACHE
------------- ------------ --------------- ------------- ------------------------ ---------------
a9nyksby4dfqm            0      4294799605             0               1605398594 Y

1 row selected.

SQL_ID: bf2z9mfkdwnb1
SQL> col PLAN_TABLE_OUTPUT for a120;
SQL> select t.* from v$sql s,table(dbms_xplan.display_cursor('&rc_sqlid',child_number)) t
  2  where s.sql_id='&rc_sqlid';

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------
SQL_ID  a9nyksby4dfqm, child number 0
-------------------------------------
select count(*) cnt from t1 where f1>0

Plan hash value: 4294799605

-------------------------------------------------------------------------------------------
| Id  | Operation            | Name                       | Rows  | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT     |                            |       |     3 (100)|          |
|   1 |  RESULT CACHE        | 0d95aym0kvw37casnbgjdnqzfh |     1 |            |          |
|   2 |   SORT AGGREGATE     |                            |     1 |            |          |
|*  3 |    FILTER            |                            |       |            |          |
|   4 |     TABLE ACCESS FULL| T1                         |   100 |     3   (0)| 00:00:01 |
-------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   3 - filter("F1"()>0)

Result Cache Information (identified by operation id):
------------------------------------------------------

   1 -

SQL> col object_name new_val cache_id;
SQL> select object_name from v$sql_plan p where p.sql_id='&rc_sqlid' and operation='RESULT CACHE';

OBJECT_NAME
------------------------------
0d95aym0kvw37casnbgjdnqzfh

1 row selected.

SQL> col name for a40;
SQL> col cache_id for a10;
SQL> col cache_key for a10;
SQL> select o.id,o.name,
  2         d.*,
  3         do.cache_id, do.cache_key,do.object_no
  4  from  v$result_cache_objects o
  5       ,v$result_cache_dependency d
  6       ,v$result_cache_objects do
  7  where o.cache_id='&cache_id'
  8    and d.result_id=o.id
  9    and do.id = d.depend_id;

        ID NAME                                      RESULT_ID  DEPEND_ID  OBJECT_NO     CON_ID CACHE_ID   CACHE_KEY   OBJECT_NO
---------- ---------------------------------------- ---------- ---------- ---------- ---------- ---------- ---------- ----------
        64 select count(*) cnt from t1 where f1>0           64         66      79027          3 XTENDER.T2 XTENDER.T2      79027
        64 select count(*) cnt from t1 where f1>0           64         95      79026          3 XTENDER.T1 XTENDER.T1      79026
        64 select count(*) cnt from t1 where f1>0           64         71      78995          3 XTENDER.F1 XTENDER.F1      78995

3 rows selected.

SQL> select * from t;

        ID DES
---------- ---
         1 aaa
         2 bbb
         3 eee
         4 xxx

SQL> create or replace view tv as select * from t;

View created.

SQL> select rowid from tv;

ROWID
------------------
AAATNbAAMAAARRDAAA
AAATNbAAMAAARRDAAB
AAATNbAAMAAARRDAAC
AAATNbAAMAAARRDAAD

SQL> create or replace view tv as select * from t, dual;

View created.

SQL> select rowid from tv;
select rowid from tv
                  *
ERROR at line 1:
ORA-01445: cannot select ROWID from, or sample, a join view without a key-preserved table

SQL> create or replace view tv2 as select t.* from t join t t2 on t.id=t2.id;

View created.

SQL> select rowid from tv2;
select rowid from tv2
                  *
ERROR at line 1:
ORA-01445: cannot select ROWID from, or sample, a join view without a key-preserved table

SQL> alter table t add constraint t_pk primary key (id);

Table altered.

SQL> select rowid from tv2;

ROWID
------------------
AAATNbAAMAAARRDAAA
AAATNbAAMAAARRDAAB
AAATNbAAMAAARRDAAC
AAATNbAAMAAARRDAAD

create table operators(c_operator,c_range) as (
                   select 'Moldova-fix' c_operator,'810373' c_range from dual union all
                   select 'Moldova-Mobile Moldcell' operator,'81037376' range from dual union all
                   select 'Moldova-Mobile Moldcell' operator,'81037378' range from dual union all
                   select 'Moldova-Mobile Moldcell' operator,'81037379' range from dual union all
                   select 'Moldova-Mobile Orange' operator,'81037360' range from dual union all
                   select 'Moldova-Mobile Orange' operator,'810373610' range from dual union all
                   select 'Moldova-Mobile Orange' operator,'810373611' range from dual union all
                   select 'Moldova-Mobile Orange' operator,'810373620' range from dual union all
                   select 'Moldova-Mobile Orange' operator,'810373621' range from dual union all
                   select 'Moldova-Mobile Orange' operator,'81037368' range from dual union all
                   select 'Moldova-Mobile Orange' operator,'81037369' range from dual
                  )
/
alter table operators
 add constraint uq_operators 
 unique(c_range) 
 using index(create index uq_operators on operators(c_range,c_operator))
/

with phones as (
                   select '81037361100' phone from dual union all
                   select '81037378987' phone from dual union all
                   select '81037368223' phone from dual union all
                   select '81037399999' phone from dual union all
                   select 'XYZ' phone from dual
)
select/*+ use_nl(a) */ *
from phones p
    ,lateral(
       select/*+ no_decorrelate */ *
       from (
         select
            c_operator
         from operators o
         where o.c_range<=p.phone
           and p.phone like o.c_range||'%'
         order by o.c_range desc
       )
       where rownum=1
     )(+) a;

Plan hash value: 3028188581

---------------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                       | Name            | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time   | A-Rows |   A-Time   | Buffers |
---------------------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                |                 |      1 |        |       |    15 (100)|          |      5 |00:00:00.01 |       5 |
|   1 |  NESTED LOOPS OUTER             |                 |      1 |      5 |   100 |    15   (0)| 00:00:01 |      5 |00:00:00.01 |       5 |
|   2 |   VIEW                          |                 |      1 |      5 |    35 |    10   (0)| 00:00:01 |      5 |00:00:00.01 |       0 |
|   3 |    UNION-ALL                    |                 |      1 |        |       |            |          |      5 |00:00:00.01 |       0 |
|   4 |     FAST DUAL                   |                 |      1 |      1 |       |     2   (0)| 00:00:01 |      1 |00:00:00.01 |       0 |
|   5 |     FAST DUAL                   |                 |      1 |      1 |       |     2   (0)| 00:00:01 |      1 |00:00:00.01 |       0 |
|   6 |     FAST DUAL                   |                 |      1 |      1 |       |     2   (0)| 00:00:01 |      1 |00:00:00.01 |       0 |
|   7 |     FAST DUAL                   |                 |      1 |      1 |       |     2   (0)| 00:00:01 |      1 |00:00:00.01 |       0 |
|   8 |     FAST DUAL                   |                 |      1 |      1 |       |     2   (0)| 00:00:01 |      1 |00:00:00.01 |       0 |
|   9 |   VIEW                          | VW_LAT_5E5D704D |      5 |      1 |    13 |     1   (0)| 00:00:01 |      4 |00:00:00.01 |       5 |
|* 10 |    COUNT STOPKEY                |                 |      5 |        |       |            |          |      4 |00:00:00.01 |       5 |
|  11 |     VIEW                        |                 |      5 |      1 |    13 |     1   (0)| 00:00:01 |      4 |00:00:00.01 |       5 |
|* 12 |      INDEX RANGE SCAN DESCENDING| UQ_OPERATORS    |      5 |      1 |    31 |     1   (0)| 00:00:01 |      4 |00:00:00.01 |       5 |
---------------------------------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

  10 - filter(ROWNUM=1)
  12 - access("O"."C_RANGE"<="P"."PHONE")
       filter("P"."PHONE" LIKE "O"."C_RANGE"||'%')


40 rows selected.

with points(ID,X,Y,CNT,DIST) as (
select  1, 1, 1,  5, 61.5629421 from dual union all
select  2, 2, 2, 10, 42.9905342 from dual union all
select  3, 3, 1, 10, 42.3402527 from dual union all
select  4, 3, 2, 13, 33.9007161 from dual union all
select  5, 3, 3, 15, 32.0060445 from dual union all
select  6, 3, 4, 12, 36.5312328 from dual union all
select  7, 4, 1, 10, 40.4182704 from dual union all
select  8, 4, 2, 14, 31.1038271 from dual union all
select  9, 4, 3, 16, 28.2066774 from dual union all
select 10, 4, 4, 14, 32.1841901 from dual union all
select 11, 4, 5, 10, 42.4182704 from dual union all
select 12, 5, 2, 13,  35.300723 from dual union all
select 13, 5, 3, 15, 31.6497533 from dual union all
select 14, 5, 4, 13,  34.916584 from dual union all
select 15, 5, 5, 10, 44.6276236 from dual union all
select 16, 6, 3, 11, 41.6318623 from dual union all
select 17, 6, 4, 10, 44.2841669 from dual 
)
select
    id,x,y,cnt,
                 cardinality( 
                    cast( collect(SYS.KU$_OBJNUM(id))over(order by x range between 2 preceding and 2 following) as sys.ku$_objnumset) 
                    multiset intersect
                    cast( collect(SYS.KU$_OBJNUM(id))over(order by y range between 2 preceding and 2 following) as sys.ku$_objnumset) 
                 )
     as tst
from points
order by id;

ORA-03113: end-of-file on communication channel