ORACLE management index

ORACLE management index: independent physical structure, pointer (book catalog, point to table data -- rowid) -- speed query/overload data update. -- OLTP: less indexes -- OLAP: more indexes -- B-tree balanced binary tree, nonleaf, leaf, only two levels. Types of index: logical: single column and connected index; unique index and nonunique; function-based index; domain indexphysical: partitioned or nonpartitioned indexb-tree (normal or reverse key) the longest. All leaf values are pointerbitmapb-tree: index entry header: key column length: key column value:...: The rowid value is unique, and the efficiency is higher. Applicability: high value uniqueness, low update cost, low or option for OLTP. create index xxx on table (column) tablespace xxxx; bitmap, used for data warehousing (OLAP ). create bitmap index xxx on table (column) tablespace xxxx; the index differs from the table's storage parameters: pctused cannot be used. the index can also be used to control the space. Guidelines: use uniform extent sizes; nologging (large index); initrans must at least be equal to the initrans of the table. Storage parameters: alter index xxx pctfree 20 storage (next 200 k pctincreat 20) manual allocation and recycling space: alter index xxx allocate extent (size 100 k datafile '... '); but the data file must be tablespacealter index xxx deallocate; rebuild indexes: online rebuild: The index rebuild can be rebuilt, The concurrency is high, and can be moved to other tablespaces, in this case, you need to recreate it. Space that is not recycled can be released. It can be converted between normal and reverse indexes. However, B-tree and bitmap cannot be converted. Alter index xxx rebuild onling; the table is not locking. It is used to maintain system concurrency during running. Coalescing indexes: alter index xxx coalesce. check validity: anylyze index xxx validate structure. Updated the index statistics process. The index_status table changes. Drop index: useless index, which uses very few indexes (you can delete the index before rebuilding when loading a large amount of data); monitor the index application: alter index xxx monitoring usage; alter index xxx nomonitoring usage; view: dba_indexes, dba_ind_columns; dba_ind_expressions, v $ object_usage;