Re: performance question

From: Wanderley <wces123_at_yahoo.com>
Date: Sun, 05 Jan 2003 04:17:34 GMT
Message-ID: <3E17B1DF.6070401_at_yahoo.com>

Pier Gaetano Novara wrote:
> I have this statement:
> DELETE FROM
> TMP_TABLE_MERGE
> WHERE EXISTS
> (SELECT 1 FROM
> TMP_TABLE_1 T1
> WHERE
> TMP_TABLE_MERGE.OBJECT_ID = T1.OBJECT_ID)
>
> In both the table TMP_TABLE_MERGE and TMP_TABLE_1 the field OBJECT_ID is the
> primary key but in the Explain Plan the table TMP_TABLE_MERGE is acessed in
> full scan mode.
> Do someone knows how can I force the use of the primary keys?
> Thanks
>
>

In this case, you can't avoid a full table scan. Oracle will have to visit each and every row on the TMP_TABLE_MERGE table and check if the EXISTS is true for that row using the primary key on the sub-select.

If TMP_TABLE_1 is a small table (few rows compared to TMP_TABLE_MERGE), then I guess your best bet would be a PL/SQL loop:

BEGIN
   FOR T1 IN (SELECT OBJECT_ID FROM TMP_TABLE_1) LOOP

     DELETE FROM TMP_TABLE_MERGE
     WHERE OBJECT_ID = T1.OBJECT_ID;

   END LOOP;
END; Again, Oracle will do a full table scan on TMP_TABLE_1, so this is only worth the trouble if TMP_TABLE_1 is small compared to TMP_TABLE_MERGE.

Regards. Received on Sun Jan 05 2003 - 05:17:34 CET