在 中我介绍了SMON后台进程维护字典基表COL_USAGE$一些细节，有网友阅读了这篇文档后发现其数据库的COL_USAGE$中存在INTCOL#=1001的记录。 INTCOL#列表示internal column number对应于COL$基表的INTCOL#，注意Internal Column Number与COL#(column number as created)是不同的。$ORACLE_HOME/rdbms/admin/sql.bsq对于INTCOL#给出了解释:
* If a table T(c1, addr, c2) contains an ADT column addr which is stored * exploded, the table will be internally stored as * T(c1, addr, C0003$, C0004$, C0005$, c2) * Of these, only c1, addr and c2 are user visible columns. Thus, the * user visible column numbers for (c1, addr, C0003$, C0004$, C0005$, c2) * will be 1,2,0,0,0,3. And the corresponding internal column numbers will * be 1,2,3,4,5,6. * * Some dictionary tables like icol$, ccol$ need to contain intcol# so * that we can have indexes and constraints on ADT attributes. Also, these * tables also need to contain col# to maintain backward compatibility. * Most of these tables will need to be accessed by col#, intcol# so * indexes are created on them based on (obj#, col#) and (obj#, intcol#). * Indexes based on col# have to be non-unique if ADT attributes might * appear in the table. Indexes based on intcol# can be unique.
这里的ADT指的是抽象数据类型(Abstract DataType is a user defined data type)，例如:
CREATE OR REPLACE TYPE persons_address AS OBJECT ( streetNumber NUMBER, streetName VARCHAR2(30), citySuburb VARCHAR2(30), state VARCHAR2(4), postCode NUMBER);
熟读Oracle官方文档的朋友一定会记得，Oracle中单表的column总数存在一个上限:1000，即单表不能拥有超过1000个列。 但令人疑惑的是INTCOL#居然是1001，显然1001是某种magic number，而不是指第1001列。 搞清楚这个问题后，再进一步探索就不难发现问题的关键了:
SQL> select * from v$version;BANNER----------------------------------------------------------------Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64biPL/SQL Release 10.2.0.4.0 - ProductionCORE 10.2.0.4.0 ProductionTNS for Linux: Version 10.2.0.4.0 - ProductionNLSRTL Version 10.2.0.4.0 - ProductionSQL> select * from global_name;GLOBAL_NAME--------------------------------------------------------------------------------www.oracledatabase12g.comSQL> drop table maclean;Table dropped.SQL> create table maclean(oppo_find_me int);Table created.SQL> select object_id from dba_objects where object_name='MACLEAN'; OBJECT_ID---------- 1343832SQL> select intcol# from col_usage$ where obj#=1343832;no rows selectedSQL> insert into maclean values(1);1 row created.SQL> commit;Commit complete.SQL> select rowid from maclean; ROWID------------------AAFIFYAABAAByPKAAASQL> delete maclean where rowid='AAFIFYAABAAByPKAAA';1 row deleted.SQL> commit;Commit complete.SQL> exec DBMS_STATS.FLUSH_DATABASE_MONITORING_INFO;PL/SQL procedure successfully completed.SQL> select intcol#,equality_preds from col_usage$ where obj#=1343832; INTCOL# EQUALITY_PREDS---------- -------------- 1001 1通过这一点可以了解那些字典基表是以rowid为条件来查询或更新的SQL> select owner || '.' || object_name 2 from dba_objects 3 where object_id in (select obj# from col_usage$ where intcol# = 1001);OWNER||'.'||OBJECT_NAME--------------------------------------------------------------------------------SYS.COL$SYS.CDEF$SYS.VIEW$SYS.SUMPARTLOG$SYS.SUM$SYS.SUMKEY$SYS.SUMAGG$SYS.SUMPRED$SYS.SUMQB$SYS.PS$SYS.AW_OBJ$OWNER||'.'||OBJECT_NAME--------------------------------------------------------------------------------SYS.AW_PROP$SYS.WRI$_ADV_OBJECTSWMSYS.WM$WORKSPACES_TABLESYS.MACLEANCTXSYS.DR$INDEXXDB.XDB$H_INDEXXDB.XDB$RESOURCEEXFSYS.RLM$SCHACTLISTSYS.AW$EXPRESSMACLEAN.SAMPLE
总结: Oracle最早在9i中引入了col_usage$字典基表，其目的在于监控column在SQL语句作为predicate的情况，col_usage$的出现完善了CBO中柱状图自动收集的机制。该字典基表上的INTCOL#列代表Internal Column Number以标识ADT列。
INTCOL#等于1001代表ROWID伪列，也就是相关对象曾使用ROWID充当predicate。