SQL> drop table t_inc;

Table dropped.

SQL> drop table t_inc2;

Table dropped.

SQL> drop table tmp_hist;

Table dropped.

SQL> -- helper table ordered
SQL> create table t_inc
  2  (x number,
  3  pad varchar2(100)
  4  );

Table created.

SQL> -- populate the table: the key i occures i*i*i times
SQL> declare
  2  i pls_integer;
  3  begin
  4  for i in 1 .. 50 loop
  5   dbms_output.put_line('processing '|| i);
  6   insert into t_inc
  7   select i, lpad('x',100,'x') from dual
  8   connect by level <= i*i*i;
  9  end loop;
 10  commit;
 11  end;
 12  /

PL/SQL procedure successfully completed.

SQL> ---
SQL> -- destroy the order of the table
SQL> create table T_INC2 as
  2  select * from t_inc
  3  order by DBMS_RANDOM.VALUE(0,1);

Table created.

SQL> ---
SQL> create index t_inc_idx2 on t_inc2 (x);

Index created.

SQL> --- ANALYZE
SQL> begin
  2  dbms_stats.gather_table_stats(ownname=>user, tabname=>'t_inc2', method_opt=>'for all columns size 254', cascade => true);
  3  end;
  4  /

PL/SQL procedure successfully completed.

SQL> select num_rows, sample_size from user_tables where table_name = 'T_INC2';

  NUM_ROWS SAMPLE_SIZE                                                          
---------- -----------                                                          
   1625625     1625625                                                          

SQL> select histogram, num_buckets from user_tab_columns where table_name = 'T_INC2' AND column_name = 'X';

HISTOGRAM       NUM_BUCKETS                                                     
--------------- -----------                                                     
FREQUENCY                45                                                     

SQL> --- repeat the gather stats several times and check the histogram of the column X
SQL> -- prepare the table to save results
SQL> create table tmp_hist as
  2  select endpoint_value from user_tab_histograms f
  3  where table_NAME  = 'T_INC2'
  4  and column_name = 'X'
  5  and 1=0
  6  ;

Table created.

SQL> -- loop
SQL> declare
  2  i pls_integer;
  3  begin
  4  for i in 1..10 loop
  5  dbms_stats.gather_table_stats(ownname=>user, tabname=>'t_inc2', method_opt=>'for all columns size 254', cascade => true);
  6  insert into tmp_hist select endpoint_value from user_tab_histograms f
  7  where table_NAME  = 'T_INC2' and column_name = 'X';
  8  commit;
  9  end loop;
 10  end;
 11  /

PL/SQL procedure successfully completed.

SQL> -- statistics for endpoint_value
SQL> -- look for the value with count of half of the maximum count
SQL> select endpoint_value,  count(*) from tmp_hist
  2  group by endpoint_value  order by endpoint_value;

ENDPOINT_VALUE   COUNT(*)                                                       
-------------- ----------                                                       
             4          2                                                       
             5          2                                                       
             6          4                                                       
             7          6                                                       
             8          8                                                       
             9          9                                                       
            10         10                                                       
            11         10                                                       
            12         10                                                       
            13         10                                                       
            14         10                                                       

ENDPOINT_VALUE   COUNT(*)                                                       
-------------- ----------                                                       
            15         10                                                       
            16         10                                                       
            17         10                                                       
            18         10                                                       
            19         10                                                       
            20         10                                                       
            21         10                                                       
            22         10                                                       
            23         10                                                       
            24         10                                                       
            25         10                                                       

ENDPOINT_VALUE   COUNT(*)                                                       
-------------- ----------                                                       
            26         10                                                       
            27         10                                                       
            28         10                                                       
            29         10                                                       
            30         10                                                       
            31         10                                                       
            32         10                                                       
            33         10                                                       
            34         10                                                       
            35         10                                                       
            36         10                                                       

ENDPOINT_VALUE   COUNT(*)                                                       
-------------- ----------                                                       
            37         10                                                       
            38         10                                                       
            39         10                                                       
            40         10                                                       
            41         10                                                       
            42         10                                                       
            43         10                                                       
            44         10                                                       
            45         10                                                       
            46         10                                                       
            47         10                                                       

ENDPOINT_VALUE   COUNT(*)                                                       
-------------- ----------                                                       
            48         10                                                       
            49         10                                                       
            50         10                                                       

47 rows selected.

SQL> -- use this value in the flip script
SQL> spool off;