So I am wondering if there is a definitive answer to this question.
Also, does it matter if the index is clustered vs. non-clustered. Is it the same in all RDBMS implementations or is the exact behavior going to be proprietary?
所以我想知道这个问题是否有明确的答案。此外,索引是聚簇还是非聚簇是否重要。在所有RDBMS实现中是否相同,或者确切的行为是专有的?
5 个解决方案
#1
6
SQL is a declarative language, not a procedural one. Each SQL implementation is going to have its own quirks about implementation details like which indexes get used, how the optimizer decides which indexes to use, what the SQL programmer can do to affect the choice, and so on.
SQL是一种声明性语言,而不是程序性语言。每个SQL实现都会有一些关于实现细节的怪癖,比如使用哪些索引,优化器如何决定使用哪些索引,SQL程序员可以做些什么来影响选择,等等。
#2
1
Use of indexes is not a part of the SQL standard, but rather an implementation detail of the particular DBMS.
索引的使用不是SQL标准的一部分,而是特定DBMS的实现细节。
Ideally, it shouldn't affect it, since it doesn't affect the actual rows that are returned.
理想情况下,它不应该影响它,因为它不会影响返回的实际行。
But I've seen queries on an unnamed DBMS that does change index use based on the SQL query order.
但是我看到了一个未命名的DBMS上的查询,它确实根据SQL查询顺序更改了索引的使用。
#3
1
The ordering of the where clause shouldn't affect the query plan or the indices used in any respectable database although I have seen at least one (non-respectable) database where this wasn't the case.
where子句的顺序不应该影响查询计划或任何可敬的数据库中使用的索引,尽管我已经看到至少一个(不可尊重的)数据库,但事实并非如此。
#4
1
At one time (long ago, i.e. until about 1995) Oracle used to have only a "rule based optimiser", and with this it was certainly the case that the order of predicates in the WHERE clause, and the order of tables in the FROM clause (there was no JOIN syntax then), affected the query plan: this was documented to be the case. However, cost-based optimisers (which Oracle has had since then) attempt to examine all possible plans (or at least, as many as they can within some sensible parameters) and choose the most efficient.
曾经(很久以前,即直到1995年左右)Oracle过去只有一个“基于规则的优化器”,而且这肯定是WHERE子句中谓词的顺序以及FROM中表的顺序子句(当时没有JOIN语法),影响了查询计划:这是记录的情况。但是,基于成本的优化器(Oracle自那时起就已经尝试过)尝试检查所有可能的计划(或至少在一些合理的参数范围内尽可能多的计划)并选择最有效的计划。
#5
0
This is tough to answer because I think no one really knows, including the DBMS engineers! LOL, that is sarcasm, but what I really mean is that it is inherently non-deterministic. I could be wrong, but it really does boil down to the DB engine implementation, since the ANSI SQL standard, or any other standard does not to my knowledge regulate this notion of index semantics. I do know, however, that the FIRST reference to any indexed field matters, as it marks the top of the decision tree for the query engine. From there and depending on the number and type of indexes, the query engine may choose to use the first and most matching index it finds, or it may decide to "optimize" and instead use another index. That, I think, is the part that makes it non-deterministic.
这很难回答,因为我认为没有人真正知道,包括DBMS工程师!大笑,这是讽刺,但我真正的意思是它本质上是非确定性的。我可能是错的,但它确实归结为数据库引擎实现,因为ANSI SQL标准或任何其他标准据我所知不会规范索引语义的概念。但是,我知道,对任何索引字段的FIRST引用很重要,因为它标记了查询引擎的决策树的顶部。从那里开始,根据索引的数量和类型,查询引擎可以选择使用它找到的第一个和最匹配的索引,或者它可以决定“优化”而是使用另一个索引。我认为,这是使其不确定的部分。
#1
6
SQL is a declarative language, not a procedural one. Each SQL implementation is going to have its own quirks about implementation details like which indexes get used, how the optimizer decides which indexes to use, what the SQL programmer can do to affect the choice, and so on.
SQL是一种声明性语言,而不是程序性语言。每个SQL实现都会有一些关于实现细节的怪癖,比如使用哪些索引,优化器如何决定使用哪些索引,SQL程序员可以做些什么来影响选择,等等。
#2
1
Use of indexes is not a part of the SQL standard, but rather an implementation detail of the particular DBMS.
索引的使用不是SQL标准的一部分,而是特定DBMS的实现细节。
Ideally, it shouldn't affect it, since it doesn't affect the actual rows that are returned.
理想情况下,它不应该影响它,因为它不会影响返回的实际行。
But I've seen queries on an unnamed DBMS that does change index use based on the SQL query order.
但是我看到了一个未命名的DBMS上的查询,它确实根据SQL查询顺序更改了索引的使用。
#3
1
The ordering of the where clause shouldn't affect the query plan or the indices used in any respectable database although I have seen at least one (non-respectable) database where this wasn't the case.
where子句的顺序不应该影响查询计划或任何可敬的数据库中使用的索引,尽管我已经看到至少一个(不可尊重的)数据库,但事实并非如此。
#4
1
At one time (long ago, i.e. until about 1995) Oracle used to have only a "rule based optimiser", and with this it was certainly the case that the order of predicates in the WHERE clause, and the order of tables in the FROM clause (there was no JOIN syntax then), affected the query plan: this was documented to be the case. However, cost-based optimisers (which Oracle has had since then) attempt to examine all possible plans (or at least, as many as they can within some sensible parameters) and choose the most efficient.
曾经(很久以前,即直到1995年左右)Oracle过去只有一个“基于规则的优化器”,而且这肯定是WHERE子句中谓词的顺序以及FROM中表的顺序子句(当时没有JOIN语法),影响了查询计划:这是记录的情况。但是,基于成本的优化器(Oracle自那时起就已经尝试过)尝试检查所有可能的计划(或至少在一些合理的参数范围内尽可能多的计划)并选择最有效的计划。
#5
0
This is tough to answer because I think no one really knows, including the DBMS engineers! LOL, that is sarcasm, but what I really mean is that it is inherently non-deterministic. I could be wrong, but it really does boil down to the DB engine implementation, since the ANSI SQL standard, or any other standard does not to my knowledge regulate this notion of index semantics. I do know, however, that the FIRST reference to any indexed field matters, as it marks the top of the decision tree for the query engine. From there and depending on the number and type of indexes, the query engine may choose to use the first and most matching index it finds, or it may decide to "optimize" and instead use another index. That, I think, is the part that makes it non-deterministic.
这很难回答,因为我认为没有人真正知道,包括DBMS工程师!大笑,这是讽刺,但我真正的意思是它本质上是非确定性的。我可能是错的,但它确实归结为数据库引擎实现,因为ANSI SQL标准或任何其他标准据我所知不会规范索引语义的概念。但是,我知道,对任何索引字段的FIRST引用很重要,因为它标记了查询引擎的决策树的顶部。从那里开始,根据索引的数量和类型,查询引擎可以选择使用它找到的第一个和最匹配的索引,或者它可以决定“优化”而是使用另一个索引。我认为,这是使其不确定的部分。