Re: Question

From: DA Morgan <damorgan_at_exesolutions.com>
Date: Sat, 04 Jan 2003 09:27:08 -0800
Message-ID: <3E17196C.ABBC4988@exesolutions.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
> Pier

What version of Oracle?
What optimizer?
If CBO did you run DBMS_STATS to analyze the tables for the optimizer How many rows in the tables?

It may well be faster for Oracle to do a full table scan and the optimizer may be making an intelligent decision. On the other hand maybe you have CBO with no statistics so you are crippling your own system.

To answer your question directly go to http://tahiti.oracle.com and look up "HINTS". But I'd check out the other items suggested above first.

Daniel Morgan Received on Sat Jan 04 2003 - 11:27:08 CST