select * from v$version;


drop table t_pred1 purge;
drop table t_pred2 purge;
drop table t_pred3 purge;


create table t_pred1
as
select 
	rownum as id,
        mod( rownum, 5 ) + 1 as n,
        object_name as name
from
    all_objects
where
    rownum <= 100
;



create table t_pred2
as
select
   rownum as id,
   rownum as id2,
   object_name as name
from
   all_objects
where
   rownum <= 20000
;



create table t_pred3
as select * from t_pred2;


create index t_pred2_idx on t_pred2(id, id2);
create index t_pred3_idx on t_pred3(id, id2);


exec dbms_stats.gather_table_stats( user, 't_pred1', method_opt=>'for all columns size 1', cascade=>true);

exec dbms_stats.gather_table_stats( user, 't_pred2', method_opt=>'for all columns size 1', cascade=>true);

exec dbms_stats.gather_table_stats( user, 't_pred3', method_opt=>'for all columns size 1', cascade=>true);



create or replace view v_pred
as
select
   t2.id id2_1,
   t2.id2 id2_2,
   t3.id id3_1,
   t3.id2 id3_2,
   t2.name name2
  ,t3.name name3
from
  t_pred2 t2, t_pred3 t3
where
  t2.id = t3.id
 and t2.id2 = t3.id2


select /*+ gather_plan_statistics cost_based */
     t1.name, v1.name2 , v1.name2
 from t_pred1 t1, v_pred v1
 where
  t1.n = 1 
 and t1.id = v1.id2_1(+);


select * from table(dbms_xplan.display_cursor( null, null, 'all allstats last' ));