在单个SQL查询中，查询可以从单个表中使用多少个索引？ [英] In a single SQL query how many indexes can the query use from a single table?

问题描述

在oracle中，如果你有一个表，在3个不同的列上有3个索引。单个SQL语句可以从一个表中使用多少个索引？

解决方案

这三种方法都有可能被使用。 Oracle不太可能使用全部三个，或者使用全部三个都是有益的，但这是可能的。

Oracle使用所有三个索引的最简单方法是如果我们讨论的是位图索引，它们是为了组合而设计的，但实际上只适用于数据仓库/ DSS情况，而不适用于OLTP系统。 Oracle也可以将标准b树索引实时转换为位图索引，以便在OLTP系统中对索引进行相同的组合，但这通常效率不高。 / p>

大多数情况下，如果您尝试创建适当的索引来调整OLTP系统中的查询，您需要构建单个复合索引而不是单独创建单列索引，如果有多个列要过滤/加入。

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?

解决方案

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.

这篇关于在单个SQL查询中，查询可以从单个表中使用多少个索引？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！