Re: select from a huge table

From: fumi <fumi__at_tpts5.seed.net.tw>
Date: Tue, 8 Dec 1998 21:16:03 +0800
Message-ID: <74j8qg$gri$6@news.seed.net.tw>

J.K. ¼¶¼g©ó¤å³¹ <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.

It seems that you miss a criterion " c1.f1=c2.f1 ". If so, you may create a index on the column f1, the running time will less than 1 second.
If not, go on.

Notice the "FROM" clause:
In your original SQL statement, it will take 7000000*7000000 steps using nested loop to find the result. You know, the " c1.f1='ref1' " criterion distills 12 row from 7000000 rows. If you change the table order in FROM clause, (i.e., rewrite to "from mytable c2, mytable c1"), it will take 7000000+12*7000000 steps to find out the result. Just doing this can significantlly reduce the running time.

Hope this helps. Received on Tue Dec 08 1998 - 07:16:03 CST