我在学校参加数据库课程。老师给了我们一个简单的练习:考虑以下简单的模式:
Table Book:
Column title (primary key)
Column genre (one of: "romance", "polar", ...)
Table Author:
Column title (foreign key on Book.title)
Column name
Primary key on (title, name)
问题中有以下一个:
编写查询以返回编写浪漫史书籍的作者的查询。
我提出了这个答案:
select distinct name
from Author where title in (select title from Book where genre = "romance")
但是老师说错了,正确的答案是:
select distinct name
from Book, Author
where Book.title = Author.title
and genre = "romance"
当我要求解释时,我所得到的只是“如果您对课程有更多的关注,您会知道为什么”。辉煌。
那么,为什么我的答案不正确?这些查询之间到底有什么区别?在数据库引擎级别上,它们究竟是做什么的?
最佳答案
那么,为什么我的答案不正确?
您的回答是正确的。
我想老师为什么要标记为错误,他/她尝试练习使用带有该问题的联接。但这是否应该成为问题的一部分。
这些查询之间到底有什么区别
从技术上讲,它们的确不同。具有简单查询优化器的DBMS将以不同于教师答案中联接的方式检索子选择。
如果具有良好优化程序的DBMS实际上针对两个查询提出相同的执行计划,我不会感到惊讶。
编辑
我创建了一些测试数据,其中包含50000本书,50000作者和7种不同类型的测试数据(较小的数字实际上并没有什么意义,因为优化器倾向于随后简单地抓住整个表)。该语句将返回7144行。
PostgreSQL的
执行计划几乎相同,只是对“ join”方法进行了一些小的更改。
这是次选择版本的计划:http://explain.depesz.com/s/eov
这是加入版本的计划:http://explain.depesz.com/s/aTI
出人意料的是,加入版本的成本价值略高。
甲骨文
两种计划都是100%相同的:
-------------------------------------------------- ------------------------------------
| ID |操作|姓名|行|字节| TempSpc |费用(%CPU)|时间|
-------------------------------------------------- ------------------------------------
| 0 |选择声明| 6815 | 399K | | 273(2)| 00:00:04 |
| 1 |哈希唯一| | 6815 | 399K | 464K | 273(2)| 00:00:04 |
| * 2 |哈希联接| | 6815 | 399K | | 172(2)| 00:00:03 |
| * 3 |表访问已满|预订6815 | 166K | | 69(2)| 00:00:01 |
| 4 |表访问已满|作者| 50000 | 1708K | | 103(1)| 00:00:02 |
-------------------------------------------------- ------------------------------------
查看使用autotrace
时的统计信息,也没有任何区别。我没有真正创建跟踪文件进行分析的麻烦,因为我不希望在那里看到任何区别。
如果添加了book.genre
上的索引,事情并不会真正改变。 Oracle坚持全表扫描(即使有100000行)。可能是因为表格不是很宽,并且很多行都放在一个页面上。
PostgreSQL的确对这两个语句都使用了索引,但是计划之间仍然没有真正的区别。
关于sql - 与多个表上的SELECT FROM相比,SELECT FROM WHERE IN,我们在Stack Overflow上找到一个类似的问题:https://stackoverflow.com/questions/10652168/