Concatenated indexes in Oracle

In Oracle, when you create an index that includes more than one column, it's often referred to as a concatenated, composite, or multicolumn index. These indexes are particularly useful when you frequently query multiple columns together in a WHERE clause or when joining tables. Here's the process for creating a concatenated index in an Oracle database:

1. Prerequisites: Before creating the index, ensure you have:
   1. The `CREATE INDEX` system privilege or the `CREATE ANY INDEX` system privilege.
   2. The table on which you're creating the index should exist and be populated with data.
2. Considerations:
   1. Column Order: The order of columns in the concatenated index can affect performance. Typically, you'd place the most frequently queried and selective columns first.
   2. Cardinality: Columns with higher cardinality (more unique values) are often better candidates for leading columns in a concatenated index.
3. Creating a Concatenated Index: Use the `CREATE INDEX` statement. The basic syntax is as follows:
   

   CREATE INDEX index_name 
   ON table_name (column1, column2, ...);
   

   
   For instance, if you want to create a concatenated index on the `employees` table for the `last_name` and `first_name` columns, you'd use:
   

   CREATE INDEX idx_employee_name 
   ON employees (last_name, first_name);
   

4. Verifying the Index Creation: After creating the index, it's good practice to verify its existence and structure:
   

   SELECT INDEX_NAME, TABLE_NAME, COLUMN_NAME
   FROM USER_IND_COLUMNS
   WHERE TABLE_NAME = 'EMPLOYEES' AND INDEX_NAME = 'IDX_EMPLOYEE_NAME'
   ORDER BY COLUMN_POSITION;
   

5. Maintenance and Monitoring:
   1. Statistics Gathering: Ensure that you gather statistics on your indexes regularly using the `DBMS_STATS` package. Accurate statistics help the optimizer make informed decisions.
      

      EXEC DBMS_STATS.GATHER_INDEX_STATS(ownname => 'YOUR_SCHEMA', indname => 'IDX_EMPLOYEE_NAME');
      

   2. Monitoring Performance: Monitor the performance of the index, ensuring it provides the desired performance benefits. Oracle provides various views, such as `V$SQL` and `DBA_HIST_SQLSTAT`, to analyze SQL execution and performance.
   
   
6. Best Practices
   1. Avoid Over-indexing: While concatenated indexes can enhance query performance, having too many indexes can slow down DML operations like INSERT, UPDATE, or DELETE. Ensure that the benefits of the index outweigh the overheads.
   2. Regular Review: Regularly review the utility of concatenated indexes, especially after significant changes in query patterns or data volume.

Creating a concatenated index in Oracle is straightforward. However, careful planning regarding column order and regular monitoring ensures that the index remains beneficial throughout its lifecycle. Proper index management can drastically enhance query performance, ensuring optimal data retrieval times. As an Oracle DBA, leveraging concatenated indexes strategically will play a pivotal role in database performance tuning endeavors.

---

Oracle makes it possible to create an index with more than one index column. These are known as concatenated or composite indexes, because multiple columns are linked together in a series in a single index B-tree. Concatenated indexes are very useful in cases where multiple criteria are used to access a table. To illustrate, consider the following index definition:

Create index
  Student_idx
On
   student
(
   student_level,
   major,
   last_name
);

In these multi-key indexes, Oracle will create each index node to contain all of the keys. When we issue a query that uses the high-order index column in the WHERE clause, Oracle uses this index to speed access to the table rows. Let us examine the examples below:

This query can use the concatenated index because the high-order key of student_level is referenced in the query.

In this query the index is also used because both of the high-order keys are present in the index.

In this query, the index is NOT used because the student_level is not referenced in the WHERE clause. To summarize, the concatenated index is very useful when your tables normally have multiple column specifications in the SQL queries.
When defining a concatenated index, you should place the column with the most distinct values first. For example, if you were creating a concatenated index on state_name (50 distinct values) and customer_status (10 distinct values), you should define state_name first in the index. The next lesson examines the function-based indexes of Oracle8.

Question: Which aspects of indexing must you evaluate when considering whether to use bitmap indexing on a given table?
When evaluating whether to use bitmap indexing on a given table in Oracle, several key aspects need to be considered. A bitmap index is a type of database index that uses bitmaps and can provide significant performance benefits in the right context, especially in data warehousing environments. However, their application in other scenarios can be detrimental. The following aspects should be assessed:

1. Cardinality: Bitmap indexes are particularly suitable for low cardinality columns, i.e., columns with a small number of distinct values relative to the total number of rows in the table. If the column has high cardinality, a B-tree index is generally more efficient.
2. Data Distribution: Bitmap indexes are effective when the distribution of data in the column is uneven, i.e., when certain values occur much more frequently than others. Bitmap indexes can compactly represent and rapidly filter based on these frequently occurring values.
3. Concurrency and Write Operations: Bitmap indexes are not ideal in situations where there are frequent concurrent write operations (INSERT, UPDATE, DELETE) due to the locking issues. These indexes can lock many rows during updates, which can degrade performance in OLTP systems with high concurrency.
4. Read-Intensive Workloads: Bitmap indexes are best suited for read-intensive workloads, such as data warehousing and reporting systems, where queries are complex, and data is loaded in bulk rather than updated frequently.
5. Complex Queries: Bitmap indexes can significantly speed up complex queries involving multiple conditions. They allow the database to quickly combine multiple bitmap indexes using bitwise operations, which can be significantly faster than the corresponding operations with B-tree indexes.
6. Storage Space: Bitmap indexes usually require less storage space compared to B-tree indexes for low cardinality columns, which can be an important consideration if space is a constraint.
7. Index-Only Access Paths: Consider if queries on the table could benefit from index-only access paths, where the query can be resolved entirely using the index without accessing the actual table. Bitmap indexes can often support such access paths.
8. Data Warehouse Schema Design: If your schema is a star schema typical of a data warehouse, bitmap indexes on the fact table's foreign key columns can be beneficial, especially when star transformation is used.

Choosing the right indexing strategy requires a deep understanding of your data and the nature of your workload. Regularly review and tune your indexing strategy based on changes in data volume, distribution, and query patterns to ensure optimal database performance.
This page describes aspects of indexing that you must evaluate when considering whether to use bitmap indexing on a given table:

1. performance,
2. storage, and
3. maintenance.

• Performance Considerations Bitmap indexes can substantially improve performance of queries with the following characteristics:
  1. The WHERE clause contains multiple predicates on low- or medium-cardinality columns
  2. The individual predicates on these low- or medium-cardinality columns select a large number of rows
  3. Bitmap indexes have been created on some or all of these low- or medium-cardinality columns
  4. The tables being queried contain many rows
  
  You can use multiple bitmap indexes to evaluate the conditions on a single table. Bitmap indexes are thus highly advantageous for complex ad hoc queries that contain lengthy WHERE clauses. Bitmap indexes can also provide optimal performance for aggregate queries and for optimizing joins in star schemas.

1. Cardinality: The number of distinct values in a column
2. Bitmap: A binary array method for indexing
3. B-tree: An Oracle index structure for columns with many distinct values
4. STAR TRANSFORMATION: An execution plan where intermediate result are kept in memory
5. STAR: An execution plan where intermediate results are stored in temporary segments
6. Concatenation: The process of joining column values together

Discussion on tuning with Oracle indexes

1. When and how do you use STAR index queries over STAR transformations and vice versa?
2. What tips and suggestions would you offer others in working with concatenated indexes, based on your experience with Oracle indexes?