浅析DBA_TAB_COLUMNS，DBA_TAB_COL_STATISTICS，DBA_TAB_COLS的关系

DBA_TAB_COLUMNS： DBA_TAB_COLUMNS describes the columns of all tables, views, and clusters in the database。描述了所有表、视图、簇的列
DBA_TAB_COL_STATISTICS ： DBA_TAB_COL_STATISTICS contains column statistics and histogram information extracted from “DBA_TAB_COLUMNS”.从DBA_TAB_COLUMNS中获取的统计信息，oracle有意将此视图制作成执行收集统计信息后的列统计信息视图
DBA_TAB_COLS： DBA_TAB_COLS describes the columns of all tables, views, and clusters in the database. This view differs from "DBA_TAB_COLUMNS" in that hidden columns are not filtered out.看下表定义就知道什么意思了
查看DBATABCLOUMNS视图
SQL> select dbms_metadata.get_ddl('VIEW','DBA_TAB_COLUMNS','SYS') from dual;


DBMS_METADATA.GET_DDL('VIEW','DBA_TAB_COLUMNS','SYS')
--------------------------------------------------------------------------------


  CREATE OR REPLACE FORCE VIEW "SYS"."DBA_TAB_COLUMNS" ("OWNER", "TABLE_NAME", "
COLUMN_NAME", "DATA_TYPE", "DATA_TYPE_MOD", "DATA_TYPE_OWNER", "DATA_LENGTH", "D
ATA_PRECISION", "DATA_SCALE", "NULLABLE", "COLUMN_ID", "DEFAULT_LENGTH", "DATA_D
EFAULT", "NUM_DISTINCT", "LOW_VALUE", "HIGH_VALUE", "DENSITY", "NUM_NULLS", "NUM
_BUCKETS", "LAST_ANALYZED", "SAMPLE_SIZE", "CHARACTER_SET_NAME", "CHAR_COL_DECL_
LENGTH", "GLOBAL_STATS", "USER_STATS", "AVG_COL_LEN", "CHAR_LENGTH", "CHAR_USED"
, "V80_FMT_IMAGE", "DATA_UPGRADED", "HISTOGRAM") AS
  select OWNER, TABLE_NAME,
       COLUMN_NAME, DATA_TYPE, DATA_TYPE_MOD, DATA_TYPE_OWNER,
       DATA_LENGTH, DATA_PRECISION, DATA_SCALE, NULLABLE, COLUMN_ID,


DBMS_METADATA.GET_DDL('VIEW','DBA_TAB_COLUMNS','SYS')
--------------------------------------------------------------------------------
       DEFAULT_LENGTH, DATA_DEFAULT, NUM_DISTINCT, LOW_VALUE, HIGH_VALUE,
       DENSITY, NUM_NULLS, NUM_BUCKETS, LAST_ANALYZED, SAMPLE_SIZE,
       CHARACTER_SET_NAME, CHAR_COL_DECL_LENGTH,
       GLOBAL_STATS, USER_STATS, AVG_COL_LEN, CHAR_LENGTH, CHAR_USED,
       V80_FMT_IMAGE, DATA_UPGRADED, HISTOGRAM
  from DBA_TAB_COLS
 where HIDDEN_COLUMN = ‘NO’


SQL>  select  hidden_column,count(*) from dba_tab_cols group by hidden_column;


HID   COUNT(*)
--- ----------
NO 90673
YES  2535


SQL> select count(*) from dba_tab_columns;


  COUNT(*)
----------
     90673
dba_tab_columns 视图获取了dba_tab_cols 的HIDDEN_COLUMN = ‘NO’的行

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

转载于:http://blog.itpub.net/31461640/viewspace-2141156/