” 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
Hi! I am Bruno, a Brazilian born and bred. Former Oracle ACE, Computer Scientist, MSc in Data Science, over ten years of experience in companies such as IBM, Epico Tech, and Playtech based in three different countries (Brazil, Hungary, and Sweden) and joined projects remotely in many others. I am super excited to show you my interest in Databases, Cloud, Data Science, Data Engineering, Bigdata, AI, Programming, Software Engineering, and data in general.
(Continue reading)