SQL> create table h(x number);
表已创建。
SQL> declare
2 i number;
3 begin
4 for i in 1..3296 loop
5 insert into h values (1);
6 end loop;
7 for i in 1..100 loop
8 insert into h values (3);
9 end loop;
10 for i in 1..798 loop
11 insert into h values (5);
12 end loop;
13 for i in 1..3970 loop
14 insert into h values (7);
15 end loop;
16 for i in 1..16293 loop
17 insert into h values (10);
18 end loop;
19 for i in 1..3399 loop
20 insert into h values (16);
21 end loop;
22 for i in 1..3651 loop
23 insert into h values (27);
24 end loop;
25 for i in 1..3892 loop
26 insert into h values (32);
27 end loop;
28 for i in 1..3521 loop
29 insert into h values (39);
30 end loop;
31 for i in 1..1080 loop
32 insert into h values (49);
33 end loop;
34
35 commit;
36 end;
37 /
PL/SQL 过程已成功完成。
SQL>
SQL> select count(*) from h;
COUNT(*)
----------
40000
SQL> select x,count(*) from h group by x order by x;
X COUNT(*)
---------- ----------
1 3296
3 100
5 798
7 3970
10 16293
16 3399
27 3651
32 3892
39 3521
49 1080
已选择10行。
SQL> select x as x,count(*) as cardinality,sum(count(*)) over (order by x range unbounded preceding) as cum_cardinality from h group by x;
X CARDINALITY CUM_CARDINALITY
---------- ----------- ---------------
1 3296 3296
3 100 3396
5 798 4194
7 3970 8164
10 16293 24457
16 3399 27856
27 3651 31507
32 3892 35399
39 3521 38920
49 1080 40000
已选择10行。
SQL> execute dbms_stats.gather_table_stats(ownname=>'SCOTT',tabname=>'H',estimate_percent => 100,method_opt => 'for columns size auto x',cascade=>true);
PL/SQL 过程已成功完成。
SQL> select table_name,column_name,num_distinct,num_buckets,histogram from user_tab_col_statistics where table_name='H';
TABLE_NAME COLUMN_NAME NUM_DISTINCT NUM_BUCKETS HISTOGRAM
------------------------------ ------------------------------ ------------ ----------- ---------------
H X 10 1 NONE
现在来使用一下x列：
SQL> select count(*) from h where x=10;
COUNT(*)
----------
16293
SQL> execute dbms_stats.gather_table_stats(ownname=>'SCOTT',tabname=>'H',estimate_percent => 100,method_opt => 'for columns size auto x',cascade=>true);
PL/SQL 过程已成功完成。
SQL>
SQL> select table_name,column_name,num_distinct,num_buckets,histogram from user_tab_col_statistics where table_name='H';
TABLE_NAME COLUMN_NAME NUM_DISTINCT NUM_BUCKETS HISTOGRAM
------------------------------ ------------------------------ ------------ ----------- ---------------
H X 10 10 FREQUENCY
SQL>
SQL> select table_name,column_name,endpoint_number,endpoint_value from user_tab_histograms where table_name='H';
TABLE_NAME COLUMN_NAM ENDPOINT_NUMBER ENDPOINT_VALUE
------------------------------ ---------- --------------- --------------
H X 3296 1
H X 3396 3
H X 4194 5
H X 8164 7
H X 24457 10
H X 27856 16
H X 31507 27
H X 35399 32
H X 38920 39
H X 40000 49
已选择10行。
