|
今天用DBMS_STATS.GATHER_TABLE_STATS中method_opt => 'FOR ALL INDEXED COLUMNS SIZE SKEWONLY'来自动收集,倾斜索引列柱状图,发现非倾斜列也被收集了,请问这是为什么?
下面过程中隐藏一些真实表名,列名.
SQL> select * from v$version;
BANNER
----------------------------------------------------------------
Oracle9i Enterprise Edition Release 9.2.0.7.0 - 64bit Production
PL/SQL Release 9.2.0.7.0 - Production
CORE 9.2.0.7.0 Production
TNS for IBM/AIX RISC System/6000: Version 9.2.0.7.0 - Production
NLSRTL Version 9.2.0.7.0 - Production
Executed in 0.031 seconds
SQL> select count(*) from A;
COUNT(*)
----------
1789660
Executed in 0.016 seconds
SQL> exec dbms_stats.gather_table_stats(ownname => 'MYDB',tabname => 'A',method_opt => 'FOR ALL INDEXED COLUMNS SIZE SKEWONLY',degree => 4,cascade => TRUE);
PL/SQL procedure successfully completed
Executed in 336.563 seconds
SQL> SELECT COUNT(*), column_name
2 FROM dba_tab_histograms
3 WHERE table_name = 'A'
4 AND column_name = 'ANAME'
5 GROUP BY column_name
6 ORDER BY column_name DESC
7 /
COUNT(*) COLUMN_NAME
---------- --------------------------------------------------------------------------------
201 ANAME
Executed in 0.078 seconds
--ANAME被收集了柱状图,还用了200个桶
SQL> SELECT num_distinct
2 FROM dba_tab_col_statistics
3 WHERE table_name = 'A'
4 AND column_name = 'ANAME'
5 /
NUM_DISTINCT
------------
1789659
--A表一共1789660行,ANAME列有1789659各不同的值
Executed in 0.031 seconds
SQL> SELECT *
2 FROM (SELECT COUNT(*), ANAME
3 FROM A
4 GROUP BY ANAME
5 ORDER BY COUNT(*) DESC)
6 WHERE rownum < 5
7 /
COUNT(*) ANAME
---------- ------------------------------
2 CNCB5+J005DN
1 123121
1 1231214
1 061024-3
Executed in 11.078 seconds
--ANAME列只有一个重复值,这也能算是倾斜?
请问当我使用for indexed columns size skewonly来收集时,oracle如何判断列是否倾斜,为啥我这个明明不倾斜的列会被收集柱状图?
谢谢~
[ 本帖最后由 zergduan 于 2008-11-18 16:53 编辑 ] |
|