[20180122]列统计与直方图.txt
[20180122]列统计与直方图.txt
--//昨天看了https://jonathanlewis.wordpress.com/2018/01/18/column-stats/,提到分析method的各种写法,自己重复验证看看:
1.环境:
SCOTT@book> @ ver1
PORT_STRING
VERSION BANNER
------------------------------ --------------
--------------------------------------------------------------------------------
x86_64/Linux
2.4.xx 11.2.0.4.0 Oracle Database 11g Enterprise Edition Release
11.2.0.4.0 - 64bit Production
create table t1 as select object_type o1, object_type o2, object_type o3, object_id, object_name from all_objects where rownum <= 50000;
SCOTT@book> select column_name, num_distinct, histogram, num_buckets,
last_analyzed from user_tab_cols where table_name = 'T1' order by
column_id;
COLUMN_NAME NUM_DISTINCT HISTOGRAM NUM_BUCKETS
LAST_ANALYZED
-------------------- ------------ --------------- -----------
-------------------
O1
NONE
O2
NONE
O3
NONE
OBJECT_ID
NONE
OBJECT_NAME NONE
--//建立后没有分析.
2.测试:
exec dbms_stats.gather_table_stats(user,'t1',method_opt=>'for all columns size 1 for columns o1 o2 o3 size 15');
SCOTT@book> select column_name, num_distinct, histogram, num_buckets,
last_analyzed,histogram from user_tab_cols where table_name = 'T1' order by
column_id;
COLUMN_NAME NUM_DISTINCT HISTOGRAM NUM_BUCKETS
LAST_ANALYZED HISTOGRAM
-------------------- ------------
--------------- ----------- -------------------
---------------
O1 33 NONE 1
2018-01-22 08:52:44 NONE
O2 33
NONE 1 2018-01-22 08:52:44
NONE
O3 33 HEIGHT BALANCED 15 2018-01-22
08:52:44 HEIGHT BALANCED
OBJECT_ID 50000
NONE 1 2018-01-22 08:52:44
NONE
OBJECT_NAME 28576 NONE 1 2018-01-22
08:52:44 NONE
--//注意看O1,O2字段没有建立直方图.
SCOTT@book> exec
dbms_stats.gather_table_stats(user,'t1',method_opt=>'for all columns size 1
for columns size 15 o1 o2 o3');
PL/SQL procedure successfully completed.
SCOTT@book> select column_name, num_distinct, histogram, num_buckets,
last_analyzed,histogram from user_tab_cols where table_name = 'T1' order by
column_id;
COLUMN_NAME NUM_DISTINCT HISTOGRAM NUM_BUCKETS
LAST_ANALYZED HISTOGRAM
-------------------- ------------
--------------- ----------- -------------------
---------------
O1 33 HEIGHT BALANCED 15
2018-01-22 08:57:37 HEIGHT BALANCED
O2 33 HEIGHT
BALANCED 15 2018-01-22 08:57:37 HEIGHT
BALANCED
O3 33 HEIGHT BALANCED 15
2018-01-22 08:57:37 HEIGHT BALANCED
OBJECT_ID 50000
NONE 1 2018-01-22 08:57:37
NONE
OBJECT_NAME 28576 NONE 1 2018-01-22
08:57:37 NONE
--//这样O1,O2,O3字段建立直方图.
--//我个人喜欢的写法是:
SCOTT@book> exec
dbms_stats.gather_table_stats(user,'t1',method_opt=>'for all columns size 1
for columns o1 size 15 o2 size 16 o3 size 17');
PL/SQL procedure successfully
completed.
SCOTT@book> select column_name, num_distinct, histogram, num_buckets,
last_analyzed,histogram from user_tab_cols where table_name = 'T1' order by
column_id;
COLUMN_NAME NUM_DISTINCT HISTOGRAM NUM_BUCKETS
LAST_ANALYZED HISTOGRAM
-------------------- ------------
--------------- ----------- -------------------
---------------
O1 33 HEIGHT BALANCED 15
2018-01-22 08:58:46 HEIGHT BALANCED
O2 33 HEIGHT
BALANCED 16 2018-01-22 08:58:46 HEIGHT
BALANCED
O3 33 HEIGHT BALANCED 17
2018-01-22 08:58:46 HEIGHT BALANCED
OBJECT_ID 50000
NONE 1 2018-01-22 08:58:46
NONE
OBJECT_NAME 28576 NONE 1 2018-01-22
08:58:46 NONE
--//这样O1,O2,O3字段建立直方图.作者提倡的写法: "for columns {size clause}
{column list}".这样反过来我个人不习惯.^_^.
--//作者还给出一些例子,大家执行测试:
for columns o1 size 12, o2 size 13, o3 size
14
for columns o1 size 15 o2 size 16 o3 size 17
for columns size 18 o1
size 19 o2 size 20 o3
for columns size 21 o1 o2 size 22 o3
for columns o1
size 12, o2 size 12, o3 size 13, object_id size 13 object_name size 14
for
columns size 22 o1 o2 for columns size 23 o3 object_id for columns size 24
object_name
--//总之注意这样写法'for all columns size 1 for columns size 15 o1 o2 o3'可能不满足需要.在工作中要引起足够的注意.
【推荐】编程新体验,更懂你的AI,立即体验豆包MarsCode编程助手
【推荐】凌霞软件回馈社区,博客园 & 1Panel & Halo 联合会员上线
【推荐】抖音旗下AI助手豆包,你的智能百科全书,全免费不限次数
【推荐】博客园社区专享云产品让利特惠,阿里云新客6.5折上折
【推荐】轻量又高性能的 SSH 工具 IShell:AI 加持,快人一步
· [.NET]调用本地 Deepseek 模型
· 一个费力不讨好的项目,让我损失了近一半的绩效!
· .NET Core 托管堆内存泄露/CPU异常的常见思路
· PostgreSQL 和 SQL Server 在统计信息维护中的关键差异
· C++代码改造为UTF-8编码问题的总结
· 一个费力不讨好的项目,让我损失了近一半的绩效!
· 清华大学推出第四讲使用 DeepSeek + DeepResearch 让科研像聊天一样简单!
· 实操Deepseek接入个人知识库
· CSnakes vs Python.NET:高效嵌入与灵活互通的跨语言方案对比
· Plotly.NET 一个为 .NET 打造的强大开源交互式图表库