"Ehresmann, David" wrote:
> 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
>
David,
Oracle does indeed suppress the use of indexes, or at least uses them
differently (must be careful with new versions - a full index scan might
not be affected by conversion) for the very simple reason that indexes
are trees, and that you descend a tree by doing comparisons. As you may
have noticed if you have ever numbered files and listed them, as it
happens '10' is smaller than '2' - lexicographic and numerical order are
different (no better with dates). Descending a tree built on comparisons
giving different results is impossible. A bit like searching a book by
title in a bookstore where shelves are ordered by author name.
You should indeed do an explicit conversion of seq_id - no it won't
prevent the use of the indexes on field1 and field2, quite the contrary.
The problem you may have is with to_char() appending a space or
converting differently. I suggest you force you NLS numeric parameters
somewhere, to be sure you get nothing fanciful, and that you specify the
conversion mask. A ltrim(to_char()) may be handy to get rid of unwanted
left spaces.
Since you seem to be keen on speed, perhaps you should also reconsider
the loop in which you are executing your delete. Have you thought about
doing a
DELETE FROM TABLE_MESSAGES
WHERE field1 in (select ltrim(to_char())
from ...
where contions of death)
AND field2 = field1
AND type = 'E';
Don't tell me that you want intemediate commits. Size your undo
properly, and don't be shy. Likely to be much faster.
--
Regards,
Stephane Faroult
Oriole Software
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: Stephane Faroult
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).
