set timing off feed off

create table XT_NULLS_TEST(a not null, b not null,c)
   as 
      select
            level a
         ,  mod(level,100) b
         ,  lpad(1,100,1)  c 
      from dual 
      connect by level<=1e5;

create index IX_NULLS_TEST on XT_NULLS_TEST(a);

exec dbms_stats.gather_table_stats('','XT_NULLS_TEST');


------------------- Main test with statistics: ----------------------------------------------
-- Variable with null:
var v_null number;

exec xt_runstats.init(p_latches => false);

-- INDEX RANGE SCAN:
select/*+ INDEX(XT_NULLS_TEST IX_NULLS_TEST)       */ count(*) cnt from XT_NULLS_TEST where a > :v_null;
exec xt_runstats.snap('IRS');

-- FULL TABLE SCAN:
select/*+ FULL(XT_NULLS_TEST)                      */ count(*) cnt from XT_NULLS_TEST where a > :v_null;
exec xt_runstats.snap('FTS');

-- INDEX FAST FULL SCAN:
select/*+ INDEX_FFS(XT_NULLS_TEST IX_NULLS_TEST)   */ count(*) cnt from XT_NULLS_TEST where a > :v_null;
exec xt_runstats.snap('IFFS');

--Results
set serveroutput on
exec xt_runstats.print(p_stats_mask => 'bugger gets|consistent gets',p_sta_diff_pct => 1);

drop table xt_nulls_test purge;