This topic has been archived. It cannot be replied.
-
工作学习 / IT技术讨论 / 这个 SQL 不太难。目的： 想知道表a中哪些纪录存在于表b
方法：
Select * from FRIEND where name in ( select name from TORONTOIAN)
问题：
FRIEND 有 100 个， TORONTOIAN 有 10 个 Millain. 这个 sql 慢的很。 有什么好办法吗？
-aka(棒棒);
2004-8-18
{223}
(#1847981@0)
-
if name is indexed in torontoian, it should be fast. But better idea is to use join instead of depending on optimization of DB. e.g. 8i or later will optimize it while 8.0.x will not.
-ra_95(小人-寻找生命的标价);
2004-8-18
(#1848008@0)
-
可以在Friend的name字段上建立一个index,可以提高查询效率！
-hard20(hard20);
2004-8-18
(#1848094@0)
-
Wrong answer. Go back to college.
-ra_95(小人-寻找生命的标价);
2004-8-19
(#1848315@0)
-
what do u mean by this????
-hard20(hard20);
2004-8-19
(#1849385@0)
-
do not know if it is rightHow about this
Select * from FRIEND where name in ( select name from TORONTOIAN where name in ( select name from FRIEND) )
I do not know if this is right.
Actually the trouble in your query is there is a full table scan on TORONTOIAN , so it is slow. If you can use the indexes on the TORONTOIAN , then it could be faster.
-lin2461(Ellen);
2004-8-18
{331}
(#1848011@0)
-
select * from FRIEND a, TORONTO b where a.name=b.name
The key is if there is possible to convert subquery to join. use join. For better performanceIf there is index for the key in both table, the query will be very fast.
Some DB will convert subquery to join. It depend on the db (oracle, db2...), the query hint and the statistics of the table.
Some DB will create tempoerty index for the key when join two table.....All depend on the DB engine.
what u can do is don't reply the engine do performance tuning for you. do it by yourself - give straight forward sql statement to DB engine.
-647i(aaaa);
2004-8-19
{451}
(#1848114@0)
-
1. Torontoian 是 按名字索引的。 2。 Friend 的 name 是 unique， 而 Torontoian 的 name 不是 unique . Join table 之后 是不是必须要 distinct? 3. 我的问题是哪种方式效率最高。
-aka(棒棒);
2004-8-19
(#1848184@0)
-
use join and change * to all the field name you need will make it faster. about distinct, it depends on what result you need. In your select * case, unless talbe a has duplicate records, there have no difference.
-guestagain(guest again);
2004-8-19
(#1848185@0)
-
select * from friend F where exists (select 1 from torontian T where T.name = F.name and rownum = 1)
-ra_95(小人-寻找生命的标价);
2004-8-19
(#1848313@0)
-
rownum? not all database has this
-guestagain(guest again);
2004-8-19
(#1848359@0)
-
aka: if u have oracle instance, you can download a free copy of toad. input the sql, and check the explain and cost of the sql statement.
-647i(aaaa);
2004-8-19
(#1849297@0)
-
i do not think your answer is right.rownum =1,what do u mean by that,just one row return from sub query?
-hard20(hard20);
2004-8-19
(#1849401@0)
-
SQL is not for millions. I used native method, it returns in 2 secs. Thanks.
-aka(棒棒);
2004-8-21
(#1851627@0)