” 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, and I am also a naturalized Swedish citizen. I am a former Oracle ACE and, to keep up with academic research, I am a Computer Scientist with an MSc in Data Science and another MSc in Software Engineering. I have over ten years of experience working with companies such as IBM, Epico Tech, and Playtech across three different countries (Brazil, Hungary, and Sweden), and I have joined projects remotely in many others. I am super excited to share my interests in Databases, Cybersecurity, Cloud, Data Science, Data Engineering, Big Data, AI, Programming, Software Engineering, and data in general.
(Continue reading)