Re: select from a huge table

From: fumi <fumi__at_tpts5.seed.net.tw>
Date: Tue, 8 Dec 1998 21:10:05 +0800
Message-ID: <74j8qf$gri$5@news.seed.net.tw>

mgogala_at_rocketmail.com ¼¶¼g©ó¤å³¹ <74i1dt$ni3$1_at_nnrp1.dejanews.com>...
>In article <74hev1$7dr$1_at_news.res.ray.com>,
>> I have a table about 7,000,000 records.

[snip]
> select c1.f1, c1.f2,c1.f3 from mytable c1, mytable c2
> where c1.f1 = 'ref1' and c1.f2 <= ' c'
> MINUS
> select c1.f1, c1.f2,c1.f3 from mytable c1, mytable c2
> where c1.f1 = 'ref1' and c1.f2 <= ' c' and c1.f3 = c1.f3
>
>Explanation:
> You'll get two relatively small data sets that are going to be processed
>by efficient sort/merge subsystem. In your case the third condition implies
>full table scan join and in case of a big table, that is expensive.

Oh, No!

It's a stupid job.
The first query returns 12*7,000,000 rows. And the "MINUS" operator will make Oracle to sort these 12*7,000,000 rows. It's predicable the statement will take more long and long time. Received on Tue Dec 08 1998 - 07:10:05 CST