CODEWITHSUNDEEP
sqldatabaseoracle-databaseindexing
bdavies6086
bdavies6086

Reputation: 382

In a single SQL query how many indexes can the query use from a single table?

In oracle if you had a table with say 3 indexes on 3 different columns. How many of those indexes could a single SQL statement possibly use from the one table?

Upvotes: 2

Views: 1773

Answers (1)

Justin Cave
Justin Cave

Reputation: 231711

It's possible that all three could be used. It's not particularly likely that Oracle would use all three, or that using all three would be beneficial, but it's possible.

The simplest way for Oracle to use all three indexes would be if we were talking about bitmap indexes, which are designed to be combined, but are really only appropriate in data warehouse/ DSS situations, not OLTP systems. It's also possible that Oracle could do an on-the-fly conversion of a "standard" b-tree index into a bitmap index in order to do the same combining of indexes in an OLTP system but that generally isn't terribly efficient.

Most of the time, if you are attempting to create appropriate indexes to tune a query in an OLTP system, you'd want to build a single composite index rather than creating separate single-column indexes if there are multiple columns that you want to filter/ join on.

Upvotes: 7

Related Questions