Find INTCOL#=1001 in col_usage$?

在<了解你所不知道的SMON功能(四):维护col_usage$字典基表>中我介绍了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 - 64bi
PL/SQL Release 10.2.0.4.0 - Production
CORE    10.2.0.4.0      Production
TNS for Linux: Version 10.2.0.4.0 - Production
NLSRTL Version 10.2.0.4.0 - Production

SQL> select * from global_name;

GLOBAL_NAME
--------------------------------------------------------------------------------
www.oracledatabase12g.com

SQL> 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
----------
   1343832

SQL> select intcol# from col_usage$ where obj#=1343832;

no rows selected

SQL> insert into maclean values(1);

1 row created.

SQL> commit;

Commit complete.

SQL> select rowid from maclean;    

ROWID
------------------
AAFIFYAABAAByPKAAA

SQL> 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_OBJECTS
WMSYS.WM$WORKSPACES_TABLE
SYS.MACLEAN
CTXSYS.DR$INDEX
XDB.XDB$H_INDEX
XDB.XDB$RESOURCE
EXFSYS.RLM$SCHACTLIST
SYS.AW$EXPRESS
MACLEAN.SAMPLE

总结: Oracle最早在9i中引入了col_usage$字典基表，其目的在于监控column在SQL语句作为predicate的情况，col_usage$的出现完善了CBO中柱状图自动收集的机制。该字典基表上的INTCOL#列代表Internal Column Number以标识ADT列。INTCOL#等于1001代表ROWID伪列，也就是相关对象曾使用ROWID充当predicate。