” 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
*The views expressed here are my own and do not represent those of my employer.*
Hello, I’m Bruno — a dual citizen of Brazil and Sweden. I bring a global perspective shaped by experiences in both South America and Europe, with a strong focus on collaboration and innovation across cultures. I am a Computer Scientist, PhD Candidate in Information and Communication Technologies, focusing on Data Science and Artificial Intelligence, and hold dual Master’s degrees in Data Science and Cybersecurity. With over fifteen years of international experience spanning Brazil, Hungary, and Sweden, I have collaborated with global organizations such as IBM, Playtech, and Oracle, as well as contributed remotely to projects across multiple regions. My professional interests include Databases, Cybersecurity, Cloud Computing, Data Science, Data Engineering, Big Data, Artificial Intelligence, Programming, and Software Engineering, all driven by a deep passion for transforming data into strategic business value.