Chinaunix首页 | 论坛 | 博客
  • 博客访问: 1022032
  • 博文数量: 584
  • 博客积分: 2293
  • 博客等级: 大尉
  • 技术积分: 3045
  • 用 户 组: 普通用户
  • 注册时间: 2006-03-28 11:15
文章分类

全部博文(584)

文章存档

2012年(532)

2011年(47)

2009年(5)

我的朋友

分类:

2012-03-27 09:52:39

原文地址:列的使用负载情况 作者:TOMSYAN

 
在进行统计信息收集直方图的时候,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          0          1 2012-02-27 13:19:58
  11003802          2              0              0                 0           0          1          0 2012-02-27 13:18:11
 
 
 
 
 
 
阅读(440) | 评论(0) | 转发(0) |
给主人留下些什么吧!~~