[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僞列。