[20111215]sys.col_usage$和intcol# = 1001的问题.txt

[20111215]sys.col_usage$和intcol# = 1001的问题.txt

讨论链接:http://www.itpub.net/thread-1454515-1-1.html

col_usage$字典基表，其目的在于监控column在SQL语句作为predicate的情况，col_usage$的出现完善了CBO中柱状图自动收集的机制。

SMON会每15分钟将shared pool中的predicate columns的数据刷新到col_usage$基表中(until periodically about every 15 minutes SMON flush the data into the data dictionary)，另外当instance shutdown时SMON会扫描col_usage$并找出已被drop表的相关predicate columns记录，并删除这部分”orphaned”孤儿记录。手动可以使用exec DBMS_STATS.FLUSH_DATABASE_MONITORING_INFO()刷新.

SQL> SELECT *  FROM SYS.col_usage$ WHERE intcol# >= 1001;

      OBJ#    INTCOL# EQUALITY_PREDS EQUIJOIN_PREDS NONEQUIJOIN_PREDS RANGE_PREDS LIKE_PREDS NULL_PREDS TIMESTAMP
---------- ---------- -------------- -------------- ----------------- ----------- ---------- ---------- -------------------
     13036       1001              1              0                 0           0          0          0 2009-08-15 00:25:25
     55847       1001              6              0                 0           0          0          0 2011-09-23 11:57:00
     56506       1001              8              0                 0           0          0          0 2011-11-15 10:45:00
     57114       1001              0              1                 0           0          0          0 2009-08-15 00:33:36
     58047       1001              1              0                 0           0          0          0 2009-08-15 00:33:36

intcol#表示表中字段的列。

而我查询对应的表根本不可能存在这么大的column, 而SYS.col_usage$中记录的

1.建立测试脚本:

建立一个shell,形成一个1001字段的表的建立脚本。
$ cat cr_column_1001.sh

#! /bin/bash
echo "create table t3 ("
for (( i = 1; i < 1001; i++))
do
    echo "c$i varchar2(1),"
done
echo "c$i varchar2(1)"
echo ");"


SQL> @aaa
c1001 varchar2(1)
*
ERROR at line 1002:
ORA-01792: maximum number of columns in a table or view is 1000

提示很明显,不能建立的字段超过1000.修改以上脚本，建立1000个字段的表。建立成功！但是如果这时要建立一个函数索引，命令如下：

SQL> create index if_t3_a on t3(upper(c1));
create index if_t3_a on t3(upper(c1))
                                 *
ERROR at line 1:
ORA-01792: maximum number of columns in a table or view is 1000

可以发现，提示依旧不能建立函数索引，主要问题是建立的一个函数索引相当于建立一个虚拟列，这样已经超出了1000个字段的限制。

那么intcol# >= 1001;表示什么呢？

很简单就是表的rowid。例子如下：

SQL> SELECT * FROM SYS.col_usage$ WHERE obj# IN (SELECT object_id FROM dba_objects WHERE wner = 'SCOTT' AND object_name = 'DEPT');

      OBJ#    INTCOL# EQUALITY_PREDS EQUIJOIN_PREDS NONEQUIJOIN_PREDS RANGE_PREDS LIKE_PREDS NULL_PREDS TIMESTAMP
---------- ---------- -------------- -------------- ----------------- ----------- ---------- ---------- -------------------
     73199          1             10              2                 0           0          0          0 2011-12-13 11:44:28

很简单我以前做过deptno=:b 的查询。

如果我执行
select * from dept where dname='SALES';

    DEPTNO DNAME          LOC
---------- -------------- -------------
        30 SALES          CHICAGO

刷新SYS.col_usage$表,手动执行exec DBMS_STATS.FLUSH_DATABASE_MONITORING_INFO()[如果不刷新，也许要等15分钟]。

SQL> SELECT * FROM SYS.col_usage$ WHERE obj# IN (SELECT object_id FROM dba_objects WHERE wner = 'SCOTT' AND object_name = 'DEPT');

      OBJ#    INTCOL# EQUALITY_PREDS EQUIJOIN_PREDS NONEQUIJOIN_PREDS RANGE_PREDS LIKE_PREDS NULL_PREDS TIMESTAMP
---------- ---------- -------------- -------------- ----------------- ----------- ---------- ---------- -------------------
     73199          1             10              2                 0           0          0          0 2011-12-13 11:44:28
     73199          2              1              0                 0           0          0          0 2011-12-15 09:31:04

可以发现dname已经出现在SYS.col_usage$视图中。

SQL> select * from dept where rowid='AAAR3vAAEAAAACHAAC';

    DEPTNO DNAME          LOC
---------- -------------- -------------
        30 SALES          CHICAGO

SQL> exec DBMS_STATS.FLUSH_DATABASE_MONITORING_INFO();

SQL> SELECT * FROM SYS.col_usage$ WHERE obj# IN (SELECT object_id FROM dba_objects WHERE wner = 'SCOTT' AND object_name = 'DEPT');

      OBJ#    INTCOL# EQUALITY_PREDS EQUIJOIN_PREDS NONEQUIJOIN_PREDS RANGE_PREDS LIKE_PREDS NULL_PREDS TIMESTAMP
---------- ---------- -------------- -------------- ----------------- ----------- ---------- ---------- -------------------
     73199          1             10              2                 0           0          0          0 2011-12-13 11:44:28
     73199          2              1              0                 0           0          0          0 2011-12-15 09:31:04
     73199       1001              1              0                 0           0          0          0 2011-12-15 09:33:29

intcol#=1001出现，说明1001表示rowid伪列。

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/267265/viewspace-713345/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/267265/viewspace-713345/