[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 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:

可以发现，提示依旧不能建立函数索引，主要问题是建立的一个函数索引相当于建立一个虚拟列，这样已经超出了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');

     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分钟]。

     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';

SQL> exec DBMS_STATS.FLUSH_DATABASE_MONITORING_INFO();

     73199       1001              1              0                 0           0          0          0 2011-12-15 09:33:29

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