REPOST: Re: Can joins be less efficient?

From: Ilya Zvyagin 2147483647 <ziv_at_fct.ru>
Date: Fri, 25 Jan 2002 12:45:58 +0300
Message-ID: <3$--$%%%%-_---%%_$@news.noc.cabal.int>

"Vikas" <g_vikas_at_hotmail.com> wrote in message news:30e95949.0201241658.3eeec754_at_posting.google.com...
> Actually, in some cases where you have large tables (think DW with
> 100s of Millions of rows) the join is sometimes more costly than the
> code you are mentioning here. Depends on
> partitioning/indexing/cpus/optimizer/etc....

No, never for ONE row if tables have indices. It might be consequences of bad statistics or bad query plan but this could be managed.

The only case I know is two tables without any indices.

A - Na records
B - Nb records

select B.* from A, B where A.key = B.FK and A.val = @someval

will cost Na * Nb records of IO
and

select @a_key = A.key from A where A.val = @someval select B.* from B where B.FK = @a_key

will cost Na + Nb records of IO

WAS CANCELLED BY =======: From: "Ilya Zvyagin 2147483647" <ziv_at_fct.ru> Control: cancel <1011951738.934285_at_gatekeeper.fct.ru> Subject: cmsg cancel <1011951738.934285_at_gatekeeper.fct.ru> Date: Mon, 28 Jan 2002 03:08:39 GMT Message-ID: <cancel.1011951738.934285_at_gatekeeper.fct.ru> X-No-Archive: yes Newsgroups: microsoft.test,alt.flame.niggers,comp.databases.oracle.server NNTP-Posting-Host: w088.z064003087.lax-ca.dsl.cnc.net 64.3.87.88 Lines: 1 Path: news.uni-stuttgart.de!news.belwue.de!news-stu1.dfn.de!news-koe1.dfn.de!news-was.dfn.de!news-spur1.maxwell.syr.edu!news.maxwell.syr.edu!out.nntp.be!propagator-SanJose!in.nntp.be!news-in-sanjose!sjc-feed.news.verio.net!sea-feed.news.verio.net!news.verio.net!msrnewsc1!cppssbbsa01.microsoft.com!tkmsftngp01!tkmsftngp04!u&n&a&c&anceller Xref: news.uni-stuttgart.de control:40723822

This message was cancelled from within The Unacanceller's glorious new software, Lotus 1-2-3 For Rogue Cancellers. Received on Fri Jan 25 2002 - 03:45:58 CST