what types are your fields? you can solve this problem by doing a function based index. ----- Original Message ----- To: "Multiple recipients of list ORACLE-L" <[EMAIL PROTECTED]> Sent: Wednesday, September 10, 2003 4:59 PM
> > DELETE FROM TABLE_MESSAGES > WHERE field1 = lrec_icclaims_dtl (i).seq_id AND > field2 = lrec_icclaims_dtl (i).seq_id AND > type = 'E'; > > > The above code deletes processing messages from a table that may contain > upwards of > 1,000,000 rows or more. We have a situation where we are inserting 100,000 > rows per day into this table. When the above code executes within a package > the whole process basically slows to a stop. With the code commented out > the process runs. Field1 and Field2 are VARCHAR2(30). seq_id is a > NUMBER(9). We believe that Oracle is doing an implicit conversion of the > fields when the code is executed and causing the process to slow down > dramatically. Without doing an explicit conversion with to_char (), because > I believe this will suppress the indexes on field1 and field2, what can be > done to make this efficient as possible? Field1 is in 2 indexes and field2 > is in 1 index and type is not included in any indexes. Does Oracle suppress > the use of indexes when doing an implicit conversion? Can we force index use > with this statement: > > delete /*+ INDEX(<tablename indexname [indexname]>) */ from > table_messages. > > This is the first time we have seen this problem with this table. > > > thanks, > > David Ehresmann > > -- > Please see the official ORACLE-L FAQ: http://www.orafaq.net > -- > Author: Ehresmann, David > INET: [EMAIL PROTECTED] > > Fat City Network Services -- 858-538-5051 http://www.fatcity.com > San Diego, California -- Mailing list and web hosting services > --------------------------------------------------------------------- > To REMOVE yourself from this mailing list, send an E-Mail message > to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in > the message BODY, include a line containing: UNSUB ORACLE-L > (or the name of mailing list you want to be removed from). You may > also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Ryan INET: [EMAIL PROTECTED] Fat City Network Services -- 858-538-5051 http://www.fatcity.com San Diego, California -- Mailing list and web hosting services --------------------------------------------------------------------- To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
