视图杂记5

•DBA_TABLES and DBA_OBJECT_TABLES
•DBA_TAB_STATISTICS and DBA_TAB_COL_STATISTICS
•DBA_TAB_HISTOGRAMS
•DBA_TAB_COLS
•DBA_COL_GROUP_COLUMNS
•DBA_INDEXES and DBA_IND_STATISTICS
•DBA_CLUSTERS
•DBA_TAB_PARTITIONS and DBA_TAB_SUBPARTITIONS
•DBA_IND_PARTITIONS and DBA_IND_SUBPARTITIONS
•DBA_PART_COL_STATISTICS
•DBA_PART_HISTOGRAMS
•DBA_SUBPART_COL_STATISTICS
•DBA_SUBPART_HISTOGRAMS

Example 13-1 Viewing Height-Balanced Histogram Statistics
BEGIN
  DBMS_STATS.GATHER_table_STATS (
    OWNNAME    => 'OE',
    TABNAME    => 'INVENTORIES',
    METHOD_OPT => 'FOR COLUMNS SIZE 10 quantity_on_hand' );
END;
/

SELECT COLUMN_NAME, NUM_DISTINCT, NUM_BUCKETS, HISTOGRAM
FROM   USER_TAB_COL_STATISTICS
WHERE  TABLE_NAME = 'INVENTORIES' AND COLUMN_NAME = 'QUANTITY_ON_HAND';

SELECT ENDPOINT_NUMBER, ENDPOINT_VALUE
FROM   USER_TAB_HISTOGRAMS
WHERE  TABLE_NAME = 'INVENTORIES' AND COLUMN_NAME = 'QUANTITY_ON_HAND'
ORDER BY ENDPOINT_NUMBER;

-----------------------------------------------------------------------------------------------------------------------------------------------------------------------
The database automatically creates frequency histograms instead of height-balanced histograms under the following conditions:
•The number of distinct values is less than or equal to the number of histogram buckets specified (up to 254).
•It is not true that each column value repeats only once.

Example 13-2 Viewing Frequency Histogram Statistics
BEGIN
  DBMS_STATS.GATHER_TABLE_STATS (
    OWNNAME    => 'OE',
    TABNAME    => 'INVENTORIES',
    METHOD_OPT => 'FOR COLUMNS SIZE 20 warehouse_id' );
END;
/

SELECT COLUMN_NAME, NUM_DISTINCT, NUM_BUCKETS, HISTOGRAM
FROM   USER_TAB_COL_STATISTICS
WHERE  TABLE_NAME = 'INVENTORIES' AND COLUMN_NAME = 'WAREHOUSE_ID';

-----------------------------------------------------------------------------------------------------------------------------------------------------------------------

ALTER INDEX MONITORING USAGE;

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

转载于:http://blog.itpub.net/22308399/viewspace-750706/