[20170604]12c Top Frequency histogram 2
[20170604]12c Top Frequency histogram補充.txt
1.環境:
SCOTT@test01p> @ ver1
PORT_STRING VERSION BANNER CON_ID
------------------------------ -------------- -------------------------------------------------------------------------------- ----------
IBMPC/WIN_NT64-9.1.0 12.1.0.1.0 Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production 0
--//如果要建立Top Frequency histogram必須要滿足幾個條件:
--//連結 raajeshwaran.blogspot.co.id/2016/06/top-frequency-histogram-in-12c.html
The database creates a Top frequency histogram, when the following criteria are met.
NDV is greater than n, where n is the requested number of buckets (default 254)
The percentage of rows occupied by Top-frequent values is greater than or equal to the threshold p where p is (1-(1/n)*100).
The estimate_percent parameter in dbms_stats gathering procedure should be auto_sample_size (set to default)
SCOTT@test01p> create table t as select * from dba_objects;
Table created.
select column_name,num_distinct,density,histogram,SAMPLE_SIZE
from user_tab_col_statistics
where table_name ='T'
and column_name ='OWNER';
COLUMN_NAME NUM_DISTINCT DENSITY HISTOGRAM SAMPLE_SIZE
-------------------- ------------ ---------- --------------- -----------
OWNER 32 .03125 NONE 91695
--//12c ctas 建立統計資訊,但是不會建立直方圖.density 1/32=.03125.
SCOTT@test01p> select count(*) from t;
COUNT(*)
----------
91695
--//隨手寫的sql語句:
with a as (select distinct owner,count(*) over(partition by owner) n1 ,count(*) over () n2 from t order by 2 desc ),
b as (select owner,n1,n2,sum(n1) over (order by n1 desc) n3 from a order by n1 desc)
select rownum,owner,n1,n2,n3,round(n3/n2,5) x1,round(1-1/rownum,5) x2 from b;
ROWNUM OWNER N1 N2 N3 X1 X2
------ ----------------- ----- ---------- ---------- ---------- ----------
1 SYS 41942 91695 41942 .45741 0
2 PUBLIC 37142 91695 79084 .86247 .5
3 APEX_040200 3405 91695 82489 .8996 .66667
4 ORDSYS 3157 91695 85646 .93403 .75
5 MDSYS 1819 91695 87465 .95387 .8
6 XDB 985 91695 88450 .96461 .83333
7 SYSTEM 641 91695 89091 .9716 .85714
8 CTXSYS 405 91695 89496 .97602 .875
9 WMSYS 387 91695 89883 .98024 .88889
10 DVSYS 352 91695 90235 .98408 .9
11 SH 309 91695 90544 .98745 .90909
12 ORDDATA 292 91695 90836 .99063 .91667
13 LBACSYS 209 91695 91045 .99291 .92308
14 OE 142 91695 91187 .99446 .92857
15 SCOTT 96 91695 91283 .99551 .93333
16 GSMADMIN_INTERNAL 77 91695 91360 .99635 .9375
17 IX 58 91695 91418 .99698 .94118
18 DBSNMP 55 91695 91473 .99758 .94444
19 PM 44 91695 91517 .99806 .94737
20 HR 35 91695 91552 .99844 .95
21 OLAPSYS 25 91695 91577 .99871 .95238
22 OJVMSYS 23 91695 91600 .99896 .95455
23 DVF 19 91695 91619 .99917 .95652
24 FLOWS_FILES 13 91695 91632 .99931 .95833
25 AUDSYS 12 91695 91644 .99944 .96
26 ORDPLUGINS 10 91695 91664 .99966 .96154
27 OUTLN 10 91695 91664 .99966 .96296
28 BI 8 91695 91688 .99992 .96429
29 ORACLE_OCM 8 91695 91688 .99992 .96552
30 SI_INFORMTN_SCHEM 8 91695 91688 .99992 .96667
31 APPQOSSYS 5 91695 91693 .99998 .96774
32 TEST 2 91695 91695 1 .96875
D:\temp>cat a1.sql
cat a1.sql
exec dbms_stats.gather_table_stats(ownname=>user,tabname=>'T',method_opt=>'for columns owner size &1');
select column_name,num_distinct,density,histogram,SAMPLE_SIZE from user_tab_col_statistics where table_name ='T' and column_name ='OWNER';
SCOTT@test01p> @ a1.sql 2
PL/SQL procedure successfully completed.
COLUMN_NAME NUM_DISTINCT DENSITY HISTOGRAM SAMPLE_SIZE
-------------------- ------------ ---------- --------------- -----------
OWNER 32 .03125 HYBRID 5500
SCOTT@test01p> @ a1.sql 3
PL/SQL procedure successfully completed.
COLUMN_NAME NUM_DISTINCT DENSITY HISTOGRAM SAMPLE_SIZE
-------------------- ------------ ---------- --------------- -----------
OWNER 32 5.4529E-06 TOP-FREQUENCY 91695
SCOTT@test01p> @ a1.sql 4
PL/SQL procedure successfully completed.
COLUMN_NAME NUM_DISTINCT DENSITY HISTOGRAM SAMPLE_SIZE
-------------------- ------------ ---------- --------------- -----------
OWNER 32 5.4529E-06 TOP-FREQUENCY 91695
SCOTT@test01p> @ a1.sql 31
PL/SQL procedure successfully completed.
COLUMN_NAME NUM_DISTINCT DENSITY HISTOGRAM SAMPLE_SIZE
-------------------- ------------ ---------- --------------- -----------
OWNER 32 5.4529E-06 TOP-FREQUENCY 91695
SCOTT@test01p> @ a1.sql 32
PL/SQL procedure successfully completed.
COLUMN_NAME NUM_DISTINCT DENSITY HISTOGRAM SAMPLE_SIZE
-------------------- ------------ ---------- --------------- -----------
OWNER 32 5.4529E-06 FREQUENCY 91695
--//除了bucket=2,32建立的直方圖HYBRID,FREQUENCY外,建立的都是TOP-FREQUENCY.
--//以10個bucket為例.解方程式(90235-x)/(91695-x)=0.9 ,得到x=77095.也就是要減少77095.
--//delete t where owner='SYS' and rownum<=41000;
--//delete t where owner='PUBLIC' and rownum<=36095;
SCOTT@test01p> delete t where owner='SYS' and rownum<=41000;
41000 rows deleted.
SCOTT@test01p> delete t where owner='PUBLIC' and rownum<=36095;
36095 rows deleted.
SCOTT@test01p> commit ;
Commit complete.
with a as (select distinct owner,count(*) over(partition by owner) n1 ,count(*) over () n2 from t order by 2 desc ),
b as (select owner,n1,n2,sum(n1) over (order by n1 desc) n3 from a order by n1 desc)
select rownum,owner,n1,n2,n3,round(n3/n2,5) x1,round(1-1/rownum,5) x2 from b where rownum<=11;
ROWNUM OWNER N1 N2 N3 X1 X2
------ ----------- ---- ---------- ---------- ---------- ----------
1 APEX_040200 3405 14600 3405 .23322 0
2 ORDSYS 3157 14600 6562 .44945 .5
3 MDSYS 1819 14600 8381 .57404 .66667
4 PUBLIC 1047 14600 9428 .64575 .75
5 XDB 985 14600 10413 .71322 .8
6 SYS 942 14600 11355 .77774 .83333
7 SYSTEM 641 14600 11996 .82164 .85714
8 CTXSYS 405 14600 12401 .84938 .875
9 WMSYS 387 14600 12788 .87589 .88889
10 DVSYS 352 14600 13140 .9 .9
11 SH 309 14600 13449 .92116 .90909
11 rows selected.
--//backet=10,前面10個值佔90%.
SCOTT@test01p> @ a1 10
PL/SQL procedure successfully completed.
COLUMN_NAME NUM_DISTINCT DENSITY HISTOGRAM SAMPLE_SIZE
-------------------- ------------ ---------- --------------- -----------
OWNER 32 .000034247 TOP-FREQUENCY 14600
--//再減少1條記錄.
SCOTT@test01p> delete t where owner='SYS' and rownum<=1;
1 row deleted.
SCOTT@test01p> commit ;
Commit complete.
ROWNUM OWNER N1 N2 N3 X1 X2
------ ----------- ---- ---------- ---------- ---------- ----------
1 APEX_040200 3405 14599 3405 .23324 0
2 ORDSYS 3157 14599 6562 .44948 .5
3 MDSYS 1819 14599 8381 .57408 .66667
4 PUBLIC 1047 14599 9428 .6458 .75
5 XDB 985 14599 10413 .71327 .8
6 SYS 941 14599 11354 .77772 .83333
7 SYSTEM 641 14599 11995 .82163 .85714
8 CTXSYS 405 14599 12400 .84937 .875
9 WMSYS 387 14599 12787 .87588 .88889
10 DVSYS 352 14599 13139 .89999 .9
11 SH 309 14599 13448 .92116 .90909
11 rows selected.
--//現在前10佔.89999.
SCOTT@test01p> @ a1 10
PL/SQL procedure successfully completed.
COLUMN_NAME NUM_DISTINCT DENSITY HISTOGRAM SAMPLE_SIZE
-------------------- ------------ ---------- --------------- -----------
OWNER 32 .018378 HYBRID 14599
--//可以發現建立的直方圖不是TOP-FREQUENCY,而是HYBRID(混合型直方圖).
--//轉化成TOP-FREQUENCY.
SCOTT@test01p> insert into t select * from dba_objects where owner='SYS' and rownum=1;
1 row created.
SCOTT@test01p> commit ;
Commit complete.
SCOTT@test01p> @ a1 10
PL/SQL procedure successfully completed.
COLUMN_NAME NUM_DISTINCT DENSITY HISTOGRAM SAMPLE_SIZE
-------------------- ------------ ---------- --------------- -----------
OWNER 32 .000034247 TOP-FREQUENCY 14600
--//以上內容是昨天的測試.
--//前面我提到如果取樣不是auto_sample_size,也可能不行,測試看看.
2.取樣大小Estimate_Percent => NULL.
SCOTT@test01p> exec dbms_stats.gather_table_stats(ownname=>user,tabname=>'T',method_opt=>'for columns owner size 10',Estimate_Percent => NULL);
PL/SQL procedure successfully completed.
SCOTT@test01p> select column_name,num_distinct,density,histogram,SAMPLE_SIZE from user_tab_col_statistics where table_name ='T' and column_name ='OWNER';
COLUMN_NAME NUM_DISTINCT DENSITY HISTOGRAM SAMPLE_SIZE
-------------------- ------------ ---------- --------------- -----------
OWNER 32 .018379 HYBRID 14600
--//可以發現如果全取樣,反而生成混合型直方圖.
3.取樣大小Estimate_Percent => SYS.DBMS_STATS.AUTO_SAMPLE_SIZE,Block_sample => TRUE.
SCOTT@test01p> exec dbms_stats.gather_table_stats(ownname=>user,tabname=>'T',method_opt=>'for columns owner size 10',Estimate_Percent => SYS.DBMS_STATS.AUTO_SAMPLE_SIZE,Block_sample => TRUE);
PL/SQL procedure successfully completed.
SCOTT@test01p> select column_name,num_distinct,density,histogram,SAMPLE_SIZE from user_tab_col_statistics where table_name ='T' and column_name ='OWNER';
COLUMN_NAME NUM_DISTINCT DENSITY HISTOGRAM SAMPLE_SIZE
-------------------- ------------ ---------- --------------- -----------
OWNER 32 .000034247 TOP-FREQUENCY 14600
--//可以發現Estimate_Percent=>SYS.DBMS_STATS.AUTO_SAMPLE_SIZE,不管是塊取樣依舊.
4.取樣Estimate_Percent => 100,90看看.
SCOTT@test01p> exec dbms_stats.gather_table_stats(ownname=>user,tabname=>'T',method_opt=>'for columns owner size 10',Estimate_Percent => 100);
PL/SQL procedure successfully completed.
SCOTT@test01p> select column_name,num_distinct,density,histogram,SAMPLE_SIZE from user_tab_col_statistics where table_name ='T' and column_name ='OWNER';
COLUMN_NAME NUM_DISTINCT DENSITY HISTOGRAM SAMPLE_SIZE
-------------------- ------------ ---------- --------------- -----------
OWNER 32 .062908991 HEIGHT BALANCED 14600
SCOTT@test01p> exec dbms_stats.gather_table_stats(ownname=>user,tabname=>'T',method_opt=>'for columns owner size 10',Estimate_Percent => 90);
PL/SQL procedure successfully completed.
SCOTT@test01p> select column_name,num_distinct,density,histogram,SAMPLE_SIZE from user_tab_col_statistics where table_name ='T' and column_name ='OWNER';
COLUMN_NAME NUM_DISTINCT DENSITY HISTOGRAM SAMPLE_SIZE
-------------------- ------------ ---------- --------------- -----------
OWNER 32 .0625 HEIGHT BALANCED 13108
--//看來僅僅Estimate_Percent => SYS.DBMS_STATS.AUTO_SAMPLE_SIZE才會生成TOP-FREQUENCY直方圖.
SCOTT@test01p> exec dbms_stats.gather_table_stats(ownname=>user,tabname=>'T',method_opt=>'for columns owner size 10',Estimate_Percent => SYS.DBMS_STATS.AUTO_SAMPLE_SIZE);
PL/SQL procedure successfully completed.
SCOTT@test01p> select column_name,num_distinct,density,histogram,SAMPLE_SIZE from user_tab_col_statistics where table_name ='T' and column_name ='OWNER';
COLUMN_NAME NUM_DISTINCT DENSITY HISTOGRAM SAMPLE_SIZE
-------------------- ------------ ---------- --------------- -----------
OWNER 32 .000034247 TOP-FREQUENCY 14600
--//有機會研究HYBRID直方圖.
1.環境:
SCOTT@test01p> @ ver1
PORT_STRING VERSION BANNER CON_ID
------------------------------ -------------- -------------------------------------------------------------------------------- ----------
IBMPC/WIN_NT64-9.1.0 12.1.0.1.0 Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production 0
--//如果要建立Top Frequency histogram必須要滿足幾個條件:
--//連結 raajeshwaran.blogspot.co.id/2016/06/top-frequency-histogram-in-12c.html
The database creates a Top frequency histogram, when the following criteria are met.
NDV is greater than n, where n is the requested number of buckets (default 254)
The percentage of rows occupied by Top-frequent values is greater than or equal to the threshold p where p is (1-(1/n)*100).
The estimate_percent parameter in dbms_stats gathering procedure should be auto_sample_size (set to default)
SCOTT@test01p> create table t as select * from dba_objects;
Table created.
select column_name,num_distinct,density,histogram,SAMPLE_SIZE
from user_tab_col_statistics
where table_name ='T'
and column_name ='OWNER';
COLUMN_NAME NUM_DISTINCT DENSITY HISTOGRAM SAMPLE_SIZE
-------------------- ------------ ---------- --------------- -----------
OWNER 32 .03125 NONE 91695
--//12c ctas 建立統計資訊,但是不會建立直方圖.density 1/32=.03125.
SCOTT@test01p> select count(*) from t;
COUNT(*)
----------
91695
--//隨手寫的sql語句:
with a as (select distinct owner,count(*) over(partition by owner) n1 ,count(*) over () n2 from t order by 2 desc ),
b as (select owner,n1,n2,sum(n1) over (order by n1 desc) n3 from a order by n1 desc)
select rownum,owner,n1,n2,n3,round(n3/n2,5) x1,round(1-1/rownum,5) x2 from b;
ROWNUM OWNER N1 N2 N3 X1 X2
------ ----------------- ----- ---------- ---------- ---------- ----------
1 SYS 41942 91695 41942 .45741 0
2 PUBLIC 37142 91695 79084 .86247 .5
3 APEX_040200 3405 91695 82489 .8996 .66667
4 ORDSYS 3157 91695 85646 .93403 .75
5 MDSYS 1819 91695 87465 .95387 .8
6 XDB 985 91695 88450 .96461 .83333
7 SYSTEM 641 91695 89091 .9716 .85714
8 CTXSYS 405 91695 89496 .97602 .875
9 WMSYS 387 91695 89883 .98024 .88889
10 DVSYS 352 91695 90235 .98408 .9
11 SH 309 91695 90544 .98745 .90909
12 ORDDATA 292 91695 90836 .99063 .91667
13 LBACSYS 209 91695 91045 .99291 .92308
14 OE 142 91695 91187 .99446 .92857
15 SCOTT 96 91695 91283 .99551 .93333
16 GSMADMIN_INTERNAL 77 91695 91360 .99635 .9375
17 IX 58 91695 91418 .99698 .94118
18 DBSNMP 55 91695 91473 .99758 .94444
19 PM 44 91695 91517 .99806 .94737
20 HR 35 91695 91552 .99844 .95
21 OLAPSYS 25 91695 91577 .99871 .95238
22 OJVMSYS 23 91695 91600 .99896 .95455
23 DVF 19 91695 91619 .99917 .95652
24 FLOWS_FILES 13 91695 91632 .99931 .95833
25 AUDSYS 12 91695 91644 .99944 .96
26 ORDPLUGINS 10 91695 91664 .99966 .96154
27 OUTLN 10 91695 91664 .99966 .96296
28 BI 8 91695 91688 .99992 .96429
29 ORACLE_OCM 8 91695 91688 .99992 .96552
30 SI_INFORMTN_SCHEM 8 91695 91688 .99992 .96667
31 APPQOSSYS 5 91695 91693 .99998 .96774
32 TEST 2 91695 91695 1 .96875
D:\temp>cat a1.sql
cat a1.sql
exec dbms_stats.gather_table_stats(ownname=>user,tabname=>'T',method_opt=>'for columns owner size &1');
select column_name,num_distinct,density,histogram,SAMPLE_SIZE from user_tab_col_statistics where table_name ='T' and column_name ='OWNER';
SCOTT@test01p> @ a1.sql 2
PL/SQL procedure successfully completed.
COLUMN_NAME NUM_DISTINCT DENSITY HISTOGRAM SAMPLE_SIZE
-------------------- ------------ ---------- --------------- -----------
OWNER 32 .03125 HYBRID 5500
SCOTT@test01p> @ a1.sql 3
PL/SQL procedure successfully completed.
COLUMN_NAME NUM_DISTINCT DENSITY HISTOGRAM SAMPLE_SIZE
-------------------- ------------ ---------- --------------- -----------
OWNER 32 5.4529E-06 TOP-FREQUENCY 91695
SCOTT@test01p> @ a1.sql 4
PL/SQL procedure successfully completed.
COLUMN_NAME NUM_DISTINCT DENSITY HISTOGRAM SAMPLE_SIZE
-------------------- ------------ ---------- --------------- -----------
OWNER 32 5.4529E-06 TOP-FREQUENCY 91695
SCOTT@test01p> @ a1.sql 31
PL/SQL procedure successfully completed.
COLUMN_NAME NUM_DISTINCT DENSITY HISTOGRAM SAMPLE_SIZE
-------------------- ------------ ---------- --------------- -----------
OWNER 32 5.4529E-06 TOP-FREQUENCY 91695
SCOTT@test01p> @ a1.sql 32
PL/SQL procedure successfully completed.
COLUMN_NAME NUM_DISTINCT DENSITY HISTOGRAM SAMPLE_SIZE
-------------------- ------------ ---------- --------------- -----------
OWNER 32 5.4529E-06 FREQUENCY 91695
--//除了bucket=2,32建立的直方圖HYBRID,FREQUENCY外,建立的都是TOP-FREQUENCY.
--//以10個bucket為例.解方程式(90235-x)/(91695-x)=0.9 ,得到x=77095.也就是要減少77095.
--//delete t where owner='SYS' and rownum<=41000;
--//delete t where owner='PUBLIC' and rownum<=36095;
SCOTT@test01p> delete t where owner='SYS' and rownum<=41000;
41000 rows deleted.
SCOTT@test01p> delete t where owner='PUBLIC' and rownum<=36095;
36095 rows deleted.
SCOTT@test01p> commit ;
Commit complete.
with a as (select distinct owner,count(*) over(partition by owner) n1 ,count(*) over () n2 from t order by 2 desc ),
b as (select owner,n1,n2,sum(n1) over (order by n1 desc) n3 from a order by n1 desc)
select rownum,owner,n1,n2,n3,round(n3/n2,5) x1,round(1-1/rownum,5) x2 from b where rownum<=11;
ROWNUM OWNER N1 N2 N3 X1 X2
------ ----------- ---- ---------- ---------- ---------- ----------
1 APEX_040200 3405 14600 3405 .23322 0
2 ORDSYS 3157 14600 6562 .44945 .5
3 MDSYS 1819 14600 8381 .57404 .66667
4 PUBLIC 1047 14600 9428 .64575 .75
5 XDB 985 14600 10413 .71322 .8
6 SYS 942 14600 11355 .77774 .83333
7 SYSTEM 641 14600 11996 .82164 .85714
8 CTXSYS 405 14600 12401 .84938 .875
9 WMSYS 387 14600 12788 .87589 .88889
10 DVSYS 352 14600 13140 .9 .9
11 SH 309 14600 13449 .92116 .90909
11 rows selected.
--//backet=10,前面10個值佔90%.
SCOTT@test01p> @ a1 10
PL/SQL procedure successfully completed.
COLUMN_NAME NUM_DISTINCT DENSITY HISTOGRAM SAMPLE_SIZE
-------------------- ------------ ---------- --------------- -----------
OWNER 32 .000034247 TOP-FREQUENCY 14600
--//再減少1條記錄.
SCOTT@test01p> delete t where owner='SYS' and rownum<=1;
1 row deleted.
SCOTT@test01p> commit ;
Commit complete.
ROWNUM OWNER N1 N2 N3 X1 X2
------ ----------- ---- ---------- ---------- ---------- ----------
1 APEX_040200 3405 14599 3405 .23324 0
2 ORDSYS 3157 14599 6562 .44948 .5
3 MDSYS 1819 14599 8381 .57408 .66667
4 PUBLIC 1047 14599 9428 .6458 .75
5 XDB 985 14599 10413 .71327 .8
6 SYS 941 14599 11354 .77772 .83333
7 SYSTEM 641 14599 11995 .82163 .85714
8 CTXSYS 405 14599 12400 .84937 .875
9 WMSYS 387 14599 12787 .87588 .88889
10 DVSYS 352 14599 13139 .89999 .9
11 SH 309 14599 13448 .92116 .90909
11 rows selected.
--//現在前10佔.89999.
SCOTT@test01p> @ a1 10
PL/SQL procedure successfully completed.
COLUMN_NAME NUM_DISTINCT DENSITY HISTOGRAM SAMPLE_SIZE
-------------------- ------------ ---------- --------------- -----------
OWNER 32 .018378 HYBRID 14599
--//可以發現建立的直方圖不是TOP-FREQUENCY,而是HYBRID(混合型直方圖).
--//轉化成TOP-FREQUENCY.
SCOTT@test01p> insert into t select * from dba_objects where owner='SYS' and rownum=1;
1 row created.
SCOTT@test01p> commit ;
Commit complete.
SCOTT@test01p> @ a1 10
PL/SQL procedure successfully completed.
COLUMN_NAME NUM_DISTINCT DENSITY HISTOGRAM SAMPLE_SIZE
-------------------- ------------ ---------- --------------- -----------
OWNER 32 .000034247 TOP-FREQUENCY 14600
--//以上內容是昨天的測試.
--//前面我提到如果取樣不是auto_sample_size,也可能不行,測試看看.
2.取樣大小Estimate_Percent => NULL.
SCOTT@test01p> exec dbms_stats.gather_table_stats(ownname=>user,tabname=>'T',method_opt=>'for columns owner size 10',Estimate_Percent => NULL);
PL/SQL procedure successfully completed.
SCOTT@test01p> select column_name,num_distinct,density,histogram,SAMPLE_SIZE from user_tab_col_statistics where table_name ='T' and column_name ='OWNER';
COLUMN_NAME NUM_DISTINCT DENSITY HISTOGRAM SAMPLE_SIZE
-------------------- ------------ ---------- --------------- -----------
OWNER 32 .018379 HYBRID 14600
--//可以發現如果全取樣,反而生成混合型直方圖.
3.取樣大小Estimate_Percent => SYS.DBMS_STATS.AUTO_SAMPLE_SIZE,Block_sample => TRUE.
SCOTT@test01p> exec dbms_stats.gather_table_stats(ownname=>user,tabname=>'T',method_opt=>'for columns owner size 10',Estimate_Percent => SYS.DBMS_STATS.AUTO_SAMPLE_SIZE,Block_sample => TRUE);
PL/SQL procedure successfully completed.
SCOTT@test01p> select column_name,num_distinct,density,histogram,SAMPLE_SIZE from user_tab_col_statistics where table_name ='T' and column_name ='OWNER';
COLUMN_NAME NUM_DISTINCT DENSITY HISTOGRAM SAMPLE_SIZE
-------------------- ------------ ---------- --------------- -----------
OWNER 32 .000034247 TOP-FREQUENCY 14600
--//可以發現Estimate_Percent=>SYS.DBMS_STATS.AUTO_SAMPLE_SIZE,不管是塊取樣依舊.
4.取樣Estimate_Percent => 100,90看看.
SCOTT@test01p> exec dbms_stats.gather_table_stats(ownname=>user,tabname=>'T',method_opt=>'for columns owner size 10',Estimate_Percent => 100);
PL/SQL procedure successfully completed.
SCOTT@test01p> select column_name,num_distinct,density,histogram,SAMPLE_SIZE from user_tab_col_statistics where table_name ='T' and column_name ='OWNER';
COLUMN_NAME NUM_DISTINCT DENSITY HISTOGRAM SAMPLE_SIZE
-------------------- ------------ ---------- --------------- -----------
OWNER 32 .062908991 HEIGHT BALANCED 14600
SCOTT@test01p> exec dbms_stats.gather_table_stats(ownname=>user,tabname=>'T',method_opt=>'for columns owner size 10',Estimate_Percent => 90);
PL/SQL procedure successfully completed.
SCOTT@test01p> select column_name,num_distinct,density,histogram,SAMPLE_SIZE from user_tab_col_statistics where table_name ='T' and column_name ='OWNER';
COLUMN_NAME NUM_DISTINCT DENSITY HISTOGRAM SAMPLE_SIZE
-------------------- ------------ ---------- --------------- -----------
OWNER 32 .0625 HEIGHT BALANCED 13108
--//看來僅僅Estimate_Percent => SYS.DBMS_STATS.AUTO_SAMPLE_SIZE才會生成TOP-FREQUENCY直方圖.
SCOTT@test01p> exec dbms_stats.gather_table_stats(ownname=>user,tabname=>'T',method_opt=>'for columns owner size 10',Estimate_Percent => SYS.DBMS_STATS.AUTO_SAMPLE_SIZE);
PL/SQL procedure successfully completed.
SCOTT@test01p> select column_name,num_distinct,density,histogram,SAMPLE_SIZE from user_tab_col_statistics where table_name ='T' and column_name ='OWNER';
COLUMN_NAME NUM_DISTINCT DENSITY HISTOGRAM SAMPLE_SIZE
-------------------- ------------ ---------- --------------- -----------
OWNER 32 .000034247 TOP-FREQUENCY 14600
--//有機會研究HYBRID直方圖.
來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/267265/viewspace-2140274/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- Oracle 12c新特性 - Top frequency histogram 2OracleHistogram
- Oracle 12c新特性 - Top frequency histogram 3OracleHistogram
- Oracle 12c新特性 - Top frequency histogram 1OracleHistogram
- [20170603]12c Top Frequency histogram.txtHistogram
- Oracle 12c新特性 - Hybrid histogram 2OracleHistogram
- about histogram(2)Histogram
- Oracle 12c新特性 - Hybrid histogram 3OracleHistogram
- Oracle 12c新特性 - Hybrid histogram 1OracleHistogram
- 12C SQL-TOPSQL
- [20190703]12c Hybrid histogram.txtHistogram
- linux - word frequencyLinux
- Histogram總結Histogram
- about histogram(1)Histogram
- Individual Project - Word_frequencyProject
- Individual Project - Word frequency programProject
- [PT]Column Histogram StatisticsHistogram
- Record for Individual Project ( Word frequency program )Project
- [LeetCode] 451. Sort Characters By FrequencyLeetCode
- histogram與10053(zt)Histogram
- Histogram Investigation（轉自kamus）Histogram
- histogram一點研究（待整理）Histogram
- oracle 12c release 2 安裝Oracle
- Oracle 12c In-Memory Option - 2Oracle
- 2 28TOP100
- elasticsearch 聚合之 date_histogram 聚合ElasticsearchHistogram
- elasticsearch 之 histogram 直方圖聚合ElasticsearchHistogram直方圖
- [LeetCode] 2080. Range Frequency QueriesLeetCode
- db2top監視命令DB2
- oracle直方圖histogram小記(一)Oracle直方圖Histogram
- 轉貼oracle直方圖histogram(二)Oracle直方圖Histogram
- 解析Oracle Database Concepts 12c（2）OracleDatabase
- 關於12C RAC 上的top5 問題：enq: IV - contentionENQ
- Lc 895. Maximum Frequency Stack 最大頻率棧 JSJS
- Index Range Scan成本 Histogram 和 10053IndexHistogram
- HOG特徵（Histogram of Gradient）學習總結HOG特徵Histogram
- 使用dbms_stats但不生成histogram的方法Histogram
- Storm常見模式2——TOP N介紹ORM模式
- Oracle 12c 2 Nodes sandbox RAC environment on your laptop!OracleAPT