Composite Index

Question

There are fields in a table a, b, c; to a, b, c build into a composite index; find the condition that a> 10, b = 3, c <1; terms of how to find the best arrangement to make performance

Answer 1

Assuming the question was

A table has 3 NUMBER columns ( col_a, col_b and col_c). What index would be best to serve a query such as

WHERE col_a > 10 AND col_b = 3 and col_c < 1

The answer is, it depends. Mostly on data distribution but partly on volume and query. You want to consider the total number of rows in the table, the number of distinct values for each column (and the number of nulls), the clustering factor, and the query patterns.

Some 'thought points' :

If a column, or combination of columns, is expected to be unique, then the uniqueness should be enforced by a constraint which requires those columns to be the leading columns of an index.

If a column represents a sequence and it is common to filter to select the 'latest' (or earliest), then it is useful to have that as the leading column in an index.

If OLTP operations are done on the column (ie small inserts/updates/deletes) then this generally precludes BITMAP indexes due to locking/concurrency issues.

In non-OLTP environment, bitmap indexes on non-unique single columns should be considered as an alternative to composite indexes. Especially on low-cardinality data, they can save a considerable amount of space (which can also reduce memory usage and waits for physical IO).