[OCM 12C] Creating and managing partitioned indexes


” B-tree indexes: the default and the most common

B-tree cluster indexes: defined specifically for cluster

Hash cluster indexes: defined specifically for a hash cluster

Global and local indexes: relate to partitioned tables and indexes

Reverse key indexes: most useful for Oracle Real Application Clusters applications

Bitmap indexes: compact; work best for columns with a small set of values

Function-based indexes: contain the precomputed value of a function/expression

Domain indexes: specific to an application or cartridge.

Tips:

  • Bitmap indexes: suitable to complex WHERE clause in SQL statements.
  • “LONG and LONG RAW columns cannot be indexed.”
  • “Using different tablespaces (on different disks) for a table and its index produces better performance than storing the table and index in the same tablespace. “
  • “Indexes created using NOLOGGING are not archived, perform a backup after you create the index.”
  • “Unique indexes guarantee that no two rows of a table have duplicate values in the key column (or columns). Non-unique indexes do not impose this restriction on the column values.”
  • “Function-based indexes facilitate queries that qualify a value returned by a function or expression. The value of the function or expression is precomputed and stored in the index.”
  • “Oracle Database provides a means of monitoring indexes to determine whether they are being used.”

USER_IND_COLUMNS
USER_PART_KEY_COLUMNS
USER_PART_INDEXES
USER_IND_PARTITIONS
USER_INDEXES

Oracle Database Online Documentation 12c Release 1 (12.1) -> Database Administration Database -> SQL Tuning Guide Part -> 8 Optimizer Access Paths
Oracle Database Online Documentation 12c Release 1 (12.1) -> Database Administration -> Database Administrator’s Guide -> 21 Managing Indexes
Oracle Database Online Documentation 12c Release 1 (12.1) -> Data Warehousing and Business Intelligence -> Database VLDB and Partitioning Guide -> 4 Partition Administration


Related posts

Leave a Comment