Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.misc -> Re: select from a huge table
In article <74hev1$7dr$1_at_news.res.ray.com>,
cljlk_at_hotmail.com (J.K.) wrote:
> PLEASE HELP !!!!!
>
> I have a table about 7,000,000 records.
>
> I get 12 records if I use "select * from mytable where f1 = 'ref1'";
>
> but, my question is I need to use the following statement:
>
> select c1.f1, c1.f2,c1.f3 from mytable c1, mytable c2
> where c1.f1 = 'ref1' and c1.f2 <= ' c' and c1.f3 <> c2.f3;
>
> I wait for 15 min, I still can not get the result, it is hanged there.
>
> Any help will be appreciated. Many thanks.
>
>
Provided you have either composite index on f1 and f2 or separate indexes on
both fields, I believe this is going to be much faster:
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.
--
Mladen Gogala
-----------== Posted via Deja News, The Discussion Network ==---------- http://www.dejanews.com/ Search, Read, Discuss, or Start Your Own Received on Mon Dec 07 1998 - 20:03:41 CST