在进行统计信息收集直方图的时候，method_opt参数指定auto的时候，
ORACLE根据数据的分布情况和列的负载情况来决定是否收集直方图。
method_opt 参数的各个选项的意思如下：
Accepts:
FOR ALL [INDEXED | HIDDEN] COLUMNS [size_clause]
FOR COLUMNS [size clause] column|attribute [size_clause] [,column|attribute [size_clause]...]
size_clause is defined as size_clause := SIZE {integer | REPEAT | AUTO | SKEWONLY}
- integer : Number of histogram buckets. Must be in the range [1,254].
- REPEAT : Collects histograms only on the columns that already have histograms.
- AUTO : Oracle determines the columns to collect histograms based on data distribution and the workload of the columns.
- SKEWONLY : Oracle determines the columns to collect histograms based on the data distribution of the columns.
ORACLE有个内部表col_usage$来记录列的使用情况的：
这个表的定义如下：
create table col_usage$
(
obj# number, /* object number */
intcol# number, /* internal column number */
equality_preds number, /* equality predicates */
equijoin_preds number, /* equijoin predicates */
nonequijoin_preds number, /* nonequijoin predicates */
range_preds number, /* range predicates */
like_preds number, /* (not) like predicates */
null_preds number, /* (not) null predicates */
timestamp date /* timestamp of last time this row was changed */
)
storage (initial 200K next 100k maxextents unlimited pctincrease 0)
/
create unique index i_col_usage$ on col_usage$(obj#,intcol#)
storage (maxextents unlimited)
/
列的含义比较好懂，通过名字就能猜出来,就不多说了。
以下是一个简单的测试：
> create table test as select rownum id,object_name name from all_objects where rownum<=10;
Table created.
> desc test
Name Null? Type
----------------------------------------- -------- ----------------------------
ID NUMBER
NAME NOT NULL VARCHAR2(30)
> select object_id from user_objects where object_name='TEST';
OBJECT_ID
----------
11003802
> set linesize 300
> select * from col_usage$ where obj#=11003802;
no rows selected
> select * from test where id=1;
ID NAME
---------- ------------------------------------------------------------
1 CON$
> exec dbms_stats.flush_database_monitoring_info;
PL/SQL procedure successfully completed.
> select * from col_usage$ where obj#=11003802;
OBJ# INTCOL# EQUALITY_PREDS EQUIJOIN_PREDS NONEQUIJOIN_PREDS RANGE_PREDS LIKE_PREDS NULL_PREDS TIMESTAMP
---------- ---------- -------------- -------------- ----------------- ----------- ---------- ---------- -------------------
11003802 1 1 0 0 0 0 0 2012-02-27 13:07:43
> select * from test where id between 1 and 2;
ID NAME
---------- ------------------------------------------------------------
1 CON$
2 I_COL2
> exec dbms_stats.flush_database_monitoring_info;
PL/SQL procedure successfully completed.
> select * from col_usage$ where obj#=11003802;
OBJ# INTCOL# EQUALITY_PREDS EQUIJOIN_PREDS NONEQUIJOIN_PREDS RANGE_PREDS LIKE_PREDS NULL_PREDS TIMESTAMP
---------- ---------- -------------- -------------- ----------------- ----------- ---------- ---------- -------------------
11003802 1 1 0 0 1 0 0 2012-02-27 13:08:07
> select * from test where id is null;
no rows selected
> exec dbms_stats.flush_database_monitoring_info;
PL/SQL procedure successfully completed.
> select * from col_usage$ where obj#=11003802;
OBJ# INTCOL# EQUALITY_PREDS EQUIJOIN_PREDS NONEQUIJOIN_PREDS RANGE_PREDS LIKE_PREDS NULL_PREDS TIMESTAMP
---------- ---------- -------------- -------------- ----------------- ----------- ---------- ---------- -------------------
11003802 1 1 0 0 1 0 1 2012-02-27 13:08:39
> select * from test where name like 't%';
no rows selected
> exec dbms_stats.flush_database_monitoring_info;
PL/SQL procedure successfully completed.
> select * from col_usage$ where obj#=11003802;
OBJ# INTCOL# EQUALITY_PREDS EQUIJOIN_PREDS NONEQUIJOIN_PREDS RANGE_PREDS LIKE_PREDS NULL_PREDS TIMESTAMP
---------- ---------- -------------- -------------- ----------------- ----------- ---------- ---------- -------------------
11003802 1 1 0 0 1 0 1 2012-02-27 13:08:39
11003802 2 0 0 0 0 1 0 2012-02-27 13:18:11
> create table t as select * from test;
Table created.
> select count(1) from test a,t b where a.id=b.id;
COUNT(1)
----------
10
> exec dbms_stats.flush_database_monitoring_info;
PL/SQL procedure successfully completed.
> select * from col_usage$ where obj#=11003802;
OBJ# INTCOL# EQUALITY_PREDS EQUIJOIN_PREDS NONEQUIJOIN_PREDS RANGE_PREDS LIKE_PREDS NULL_PREDS TIMESTAMP
---------- ---------- -------------- -------------- ----------------- ----------- ---------- ---------- -------------------
11003802 1 1 1 0 1 0 1 2012-02-27 13:19:37
11003802 2 0 0 0 0 1 0 2012-02-27 13:18:11
> select count(1) from test a,t b where a.id<>b.id;
COUNT(1)
----------
90
> exec dbms_stats.flush_database_monitoring_info;
PL/SQL procedure successfully completed.
> select * from col_usage$ where obj#=11003802;
OBJ# INTCOL# EQUALITY_PREDS EQUIJOIN_PREDS NONEQUIJOIN_PREDS RANGE_PREDS LIKE_PREDS NULL_PREDS TIMESTAMP
---------- ---------- -------------- -------------- ----------------- ----------- ---------- ---------- -------------------
11003802 1 1 1 1 1 0 1 2012-02-27 13:19:58
11003802 2 0 0 0 0 1 0 2012-02-27 13:18:11
阅读(1115) | 评论(0) | 转发(1) |
