Re: SQL Statement

I am not really sure about the 8000 question you have. But returning and looking through 8000 rows in your subquery seems a bit excessive.
Have you tried not using a subquery at all and just joining the two tables like so

SELECT A.COL_1, A.COL_2, A.COL_3
FROM TABLE_A A,
  TABLE_B B
WHERE A.COL_4 = 'VAR_1'

AND A.COL_1 = B.COL_1
and B.COL_2 = 'VAR_2' 
AND B.COL_3 = 'VAR_3' 
AND B.COL_4 >= SYSDATE ;

I think that this will give you the same results because you are joing A to B for every row the A.COL_1 equals B.COL_1. As far as performance goes, it certainly wont hurt to try this out.

On Tue, 28 Jan 1997 10:25:13 -0800, Paul Ferrie <DASPAF_at_UWOADMIN.UWO.CA> wrote:
>Can anyone tell me why this is happening and what can be done to rectify
>this problem...
>
>I've got the following SQL...
>
>SELECT TABLE_A.COL_1, TABLE_A.COL_2, TABLE_A.COL_3
>FROM DATABASE.TABLE_A
>WHERE TABLE_A.COL_4 = 'VAR_1' AND
> TABLE_A.COL_1 IN
> (SELECT /*+ INDEX(TABLE_B.INDEX_1) */ TABLE_B.COL_1
> FROM DATABASE.TABLE_B
> WHERE TABLE_B.COL_2 = 'VAR_2' AND
> TABLE_B.COL_3 = 'VAR_3' AND
> TABLE_B.COL_4 >= SYSDATE);
>
>Both Table_A and Table_B are 100,000+ rows!
>
>When I execute this, it works just fine up to a point! If the
>sub-select returns less then ~8000 rows, the primary select excutes very
>quickly. However, if the sub-select returns more than ~8000 rows, the
>primary select goes into a sweep. The DBA's don't know why this is
>happening! Is there something at the ORACLE side that needs to be set
>(eg. buffer sizes?, temp space?, indexes?). I've also noticed that if I
>execute the sub-select on its own and return less than ~8000 rows and
>then re-execute right after, the results come back instantly. However,
>if the sub-select returns more then ~8000 rows and I re-execute it, it
>takes the same amount of time as the original select (ie. the original
>results are not held in the buffer).
>
>Any ideas???
>
>Thanks!
>
>Paul Ferrie
>Advancement Services
>The University of Western Ontario
>DASPAF_at_UWOADMIN.UWO.CA
Received on Wed Jan 29 1997 - 00:00:00 CST