等值连接的特例
我遇到了这个特殊的脚本，它使用特殊形式的等连接。
SELECT *
FROM
per_assignments a, per_assigment_types b
WHERE
a.assignment_status_type_id + 0 = b.assignment_status_type_id
为什么等连接中要加零？我开始知道这与避免索引搜索有关，但仍然有人可以解释其完整情况。预先感谢
编辑：
这与表/列声明无关。据我所知，这与SQL调优有关。
这是我发现的：-
- 这用于较小的表。
- 这将一次性搜索整个表，而不是像通常那样进行索引搜索。
但我真的不知道与普通的等连接有什么区别，而且索引如何影响性能。
如果有人可以在特定的上下文中进行描述，并且让我知道我的发现是否错误，那将非常有帮助。感谢您为此付出的时间和精力:-)
列说明：
两个表中的作业状态类型 ID 均声明为 NUMBER(9)
I came across this particular script which uses a special form of equi join.
SELECT *
FROM
per_assignments a, per_assigment_types b
WHERE
a.assignment_status_type_id + 0 = b.assignment_status_type_id
Why is the zero added in the equi join? I came to know that it has something to do with avoiding index search, but still can some one explain the complete picture of the same. Thanks in advance
Edit :
It's not something which is related to the Table / Column declarations. As far as I know it's something to do with SQL tuning.
This is what I found :-
- This is used in smaller tables.
- Instead of doing an index search as done normally, this would search the complete table in one go.
But I really don't know exactly what's the difference with a normal equi-join, moreover how indexing affects performance.
It would be really helpful if some one could describe within the particular context and also let me know if my findings are wrong. Appreciate your time and effort for the same :-)
Column Description:
The assignment status type Id's in both tables are declared as NUMBER(9)
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（1）
取消小表索引使用的原因是性能。当您使用索引执行联接时，需要两次磁盘 I/O 来读取数据。一是读取索引，二是从全表读取数据。对于较小的表，读取整个表并执行全表扫描比执行第二个磁盘 I/O 更快。
这是一个广泛的概括，即使在您的数据库中也可能会不时发生变化。理论上，即使没有提示，SQL 优化器也应该足够聪明，能够识别这种情况，并在索引查找上使用全表扫描。也有可能，如果您将数据添加到一个或两个表，它会将更快的性能从全表扫描转移到索引查找。
我关于调整这些查询的问题是：
我担心的是，这个查询是作为一种巧妙的性能增强而编写的，无论是针对数据库的早期版本，还是只是作为一种巧妙的黑客攻击，而没有意识到查询优化器可能会做得同样好或更好。
The reason for killing the index use for small tables is performance. When you use an index to perform a join it takes two disk I/Os to read data. One to read the index, and a second to read the data from the full table. With smaller tables it is can be faster to read the whole table and perform a full table scan than to perform the second disk I/O.
This is a broad generalization and may vary from time to time even in your database. In theory the SQL optimizer should be smart enough to recognize this condition and use the full table scan over an index lookup even without the hint. It also possible that if you add data to one or both tables, it moves the faster performance from full table scan to index lookup.
The questions I have about tuning these queries would be:
My concern would be this query was written as a clever performance enhancement, either for an earlier version of the database or simply as a clever hack without realizing the query optimizer may do as good or better job.