Oracle composite index for range query conditions

If your only goal is to create an index to optimize this query, you'd prefer that the columns in the composite index be ordered with the most selective column first. If the predicates on latitude eliminate substantially more rows than the other predicates, it will be more efficient to have that column first. If the predicates on owner_id eliminate substantially more rows than the other predicates, it will be more efficient to have that column first.

In reality, though, we are rarely creating indexes whose only purpose is to optimize a single query. Generally, in order to make the overhead of index maintenance worthwhile, we want our indexes to be useful across many queries. In the case of a composite index, that means ordering the columns by the probability that a query will have predicates on that column. If you have a composite index on owner_id, create_time, latitude, longitude, for example, you can use that for queries that just specify predicates on owner_id. But you would not, realistically, use that index for queries that just specify predicates on longitude.

First, bear in mind that the "B" in "B-Tree" is not "binary".

Second, when it comes to indexing in Oracle you also have the choice of a bitmap index if:

1. You have an enterprise edition license
2. You do not have many sessions concurrently modifying the table
3. Your indexed values are not close to being unique (statements that bitmap indexes are usable only for low cardinality columns are generally exaggerated)

One type of query that bitmap indexes excel at is in efficiently combining predicates on multiple columns, especially where the set of predicated columns varies (which may not be the case for you of course). If you meet the three conditions above then it would be worth testinig the effect of having four separate bitmap indexes on the table.

One easy brute-force solution is to create multiple index combinations on the same table, run the query with EXPLAIN PLAN turned on then choose the index that your DBMS prefers to use.