数据字典基表---COL_USAGE$(二)
TED NONE 653 1 32049 2013-12-18 14:28:51
T1 OBJECT_TYPE NONE 42 1 32049 2013-12-18 14:28:51
T1 DATA_OBJECT_ID NONE 2822 1 2859 2013-12-18 14:28:51
TABLE_NAME COLUMN_NAME HISTOGRAM NUM_DISTINCT NUM_BUCKETS SAMPLE_SIZE LAST_ANALYZED
---------- -------------------- --------------- ------------ ----------- ----------- -------------------
T1 OBJECT_ID NONE 32049 1 32049 2013-12-18 14:28:51
T1 SUBOBJECT_NAME NONE 280 1 569 2013-12-18 14:28:51
T1 OBJECT_NAME NONE 30536 1 32049 2013-12-18 14:28:51
已选择14行。
SQL> SELECT COUNT(*) FROM SCOTT.T1 WHERE OBJECT_ID < 200;
COUNT(*)
----------
199
SQL> EXEC DBMS_STATS.FLUSH_DATABASE_MONITORING_INFO;
PL/SQL 过程已成功完成。
SQL> select * from col_usage$ where obj#=98581;
OBJ# INTCOL# EQUALITY_PREDS EQUIJOIN_PREDS NONEQUIJOIN_PREDS RANGE_PREDS LIKE_PREDS NULL_PREDS TIMESTAMP
---------- ---------- -------------- -------------- ----------------- ----------- ---------- ---------- -------------------
98581 3 0 0 0 1 0 0 2013-12-18 14:29:45
SQL> SELECT table_name, column_name,histogram,num_distinct,num_buckets,sample_size,last_analyzed FROM DBA_TAB_COL_STATISTICS WHERE OWNER='SCOTT';
TABLE_NAME COLUMN_NAME HISTOGRAM NUM_DISTINCT NUM_BUCKETS SAMPLE_SIZE LAST_ANALYZED
---------- -------------------- --------------- ------------ ----------- ----------- -------------------
T1 EDITION_NAME NONE 0 0 2013-12-18 14:28:51
T1 NAMESPACE NONE 18 1 32048 2013-12-18 14:28:51
T1 SECONDARY NONE 1 1 32049 2013-12-18 14:28:51
T1 GENERATED NONE 2 1 32049 2013-12-18 14:28:51
T1 TEMPORARY NONE 2 1 32049 2013-12-18 14:28:51
T1 STATUS NONE 1 1 32049 2013-12-18 14:28:51
T1 TIMESTAMP NONE 721 1 32048 2013-12-18 14:28:51
T1 LAST_DDL_TIME NONE 724 1 32048 2013-12-18 14:28:51
T1 CREATED NONE 653 1 32049 2013-12-18 14:28:51
T1 OBJECT_TYPE NONE 42 1 32049 2013-12-18 14:28:51
T1 DATA_OBJECT_ID NONE 2822 1 2859 2013-12-18 14:28:51
TABLE_NAME COLUMN_NAME HISTOGRAM NUM_DISTINCT NUM_BUCKETS SAMPLE_SIZE LAST_ANALYZED
---------- -------------------- --------------- ------------ ----------- ----------- -------------------
T1 OBJECT_ID NONE 32049 1 32049 2013-12-18 14:28:51
T1 SUBOBJECT_NAME NONE 280 1 569 2013-12-18 14:28:51
T1 OBJECT_NAME NONE 30536 1 32049 2013-12-18 14:28:51
已选择14行。
SQL>
UPDATE SCOTT.T1 SET OBJECT_ID = MOD(OBJECT_ID,25);
已更新32049行。
SQL> EXEC DBMS_STATS.FLUSH_DATABASE_MONITORING_INFO;
PL/SQL 过程已成功完成。
SQL> select * from col_usage$ where obj#=98581;
OBJ# INTCOL# EQUALITY_PREDS EQUIJOIN_PREDS NONEQUIJOIN_PREDS RANGE_PREDS LIKE_PREDS NULL_PREDS TIMESTAMP
---------- ---------- -------------- -------------- ----------------- ----------- ---------- ---------- -------------------
98581 3 0 0 0 1 0 0 2013-12-18 14:29:45
SQL> exec dbms_stats.gather_table_stats(ownname=>'SCOTT',TABNAME=>'T1',METHOD_OPT=>'FOR ALL COLUMNS SIZE AUTO');
PL/SQL 过程已成功完成。
SQL> SELECT table_name, column_name,histogram,num_distinct,num_buckets,sample_size,last_analyzed FROM DBA_TAB_COL_STATISTICS WHERE OWNER='SCOTT';
TABLE_NAME COLUMN_NAME HISTOGRAM NUM_DISTINCT NUM_BUCKETS SAMPLE_SIZE LAST_ANALYZED
---------- -------------------- ---------------