Re: select from a huge table

In article <74j8qf$gri$5_at_news.seed.net.tw>,   "fumi" <fumi__at_tpts5.seed.net.tw> wrote:
>
> 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.
>
>

You are right. I missed the fact that conditions aore only on "c1". It doesn't look like a good query, though. This query should be completely rewritten using indexes.

--
Mladen Gogala

-----------== Posted via Deja News, The Discussion Network ==---------- http://www.dejanews.com/ Search, Read, Discuss, or Start Your Own Received on Tue Dec 08 1998 - 09:45:01 CST