Snippet Name: INDEXES: Compute Statistics
Description: When an index has no statistics, Oracle9i and earlier will neither see nor make use of the index. If this is the case then you'll need to issue the commands shown in Example 1.
In Oracle 10g, the COMPUTE STATISTICS clause isn't needed anymore. Index statistics are collected automatically when an object is created, and are automatically refreshed when stale.
Also see: » INDEXES: View table indexes
» INDEXES: Analyze Index
» INDEXES: Block Dump
» INDEXES: Rebuild Reverse
» INDEXES:
» INDEXES: ENABLE (function-based index)
» INDEXES: DISABLE (function-based index)
» INDEXES: Alter Index Parallel
» INDEXES: Alter Index Deallocate Unused
» INDEXES: Alter Index Allocate Extent
» INDEXES: Virtual / NoSegment
» INDEXES: Reverse Key Indexes
» INDEXES: Bitmap Join Indexes
» INDEXES: Bitmap Indexes
» INDEXES: Unique indexes
» INDEXES: Parallel Index
» INDEXES: SORT and NOSORT
» INDEXES: Function-Based Index
» INDEXES: DROP index
» INDEXES: Alter index
» INDEXES: Single Column Non-unique
» INDEXES: Index Usage Notes
» Compressed Indexes
» Create INDEX
Comment: (none)
Language: PL/SQL
Highlight Mode: PLSQL
Last Modified: March 10th, 2009
|
-- example 1:
CREATE INDEX cust_sales_idx;
EXEC DBMS_STATS.Gather_Index_Stats('region', 'cust_sales_idx');
-- Oracle 9i introduced the "compute statistics" clause:
CREATE INDEX cust_sales COMPUTE STATISTICS; |