drop table T1 purge;
create table T1 tablespace USERS as
select 
    rownum id,
    case when rownum<4e4 then mod(rownum,500) else 999 end col1
from   ( select 1 just_a_column
         from DUAL
         connect by level <= 5e5
       )
/


drop table T2 purge;
create table T2 tablespace USERS as
select 
    rownum id,
    case when rownum<8e5 then mod(rownum,500) else 999 end col1
from   ( select 1 just_a_column
         from DUAL
         connect by level <= 1e6
       )
/


alter system flush shared_pool;

drop table Q purge;

create table Q as
   select /*+ GATHER_PLAN_STATISTICS */
       T1.ID    id1
     , T2.ID    id2
     , T1.COL1  val
   from
       T1,
       T2
   where
       T1.COL1=150
     and T1.COL1=T2.COL1
   /

select * from table(dbms_xplan.display_cursor(null,null,'ALLSTATS LAST  +PEEKED_BINDS +PARALLEL +PARTITION +COST +BYTES'));

exec dbms_stats.gather_table_stats(user,'T1', method_opt=>'for all columns size 1');
exec dbms_stats.gather_table_stats(user,'T2', method_opt=>'for all columns size 1');


exec dbms_stats.delete_table_stats('SYS','T1');
exec dbms_stats.delete_table_stats('SYS','T2');

exec dbms_stats.gather_table_stats(user,'T1', method_opt=>'for all columns size SKEWONLY');
exec dbms_stats.gather_table_stats(user,'T2', method_opt=>'for all columns size SKEWONLY');


@stats_col SYS T1 % % % %
@stats_col SYS T2 % % % %

@hist_cross_freq SYS T1 COL1 SYS T2 COL2


