You're performing a cartesion product on the table with itself -- 7M rows *
7M rows = 49e13 rows! Try:
select c1.f1, c1.f2,c1.f3
from mytable c1, mytable c2
where
c1.f1 = 'ref1' and -- should do a range scan on a non-unique index
c2.f1 = c1.f1 -- this is critical to equating only the correct subset of
rows
c1.f2 <= ' c' and c1.f3 <> c2.f3;
You should run Explain Plan to make sure you're using existing indexes. See
my explan stored procedure on my web site (below). Run it from sql*plus. It
makes running Explain Plan that much easier.
--
- Dan Clamage
http://www.telerama.com/~dclamage
If you haven't crashed the Server,
you haven't been trying hard enough.
J.K. wrote in message <74hev1$7dr$1_at_news.res.ray.com>...
>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.
>
Received on Mon Dec 07 1998 - 12:16:35 CST
