Re: DELETE and WHERE

In article <84vj2v$nnq$1_at_news.imaginet.fr>,   "GERNEZ" <commandos_at_chez.com> wrote:
> Hello,
>
> Does anyone know, it is possible to optimize a delete in a big
table
> ????
> (the table is NOLOGGING option and i have a condition in this
delete)
>
> Thanks
>

SHORT ANSWER: yes.

Your real question is likely: How?

1 create an index that the Optimizer will invoke based on the WHERE clause.

2 the table is partitioned and the WHERE clause restricts the operation to a range of some small number of partitions

3 identify the specific rows through other means (eg get the ROWID when you select it, then when you decide to delete use the ROWID in the WHERE clause.)

Others?

Be careful how you phrase the condition. Operations on the column that is the index will force the Optimizer to not use the INDEX. If the table has not been ANALYZEd recently, then the Optimizer may consider the statistics stale and not use them, possibly leading to slower choices of execution path.

As typical in code discussions, a small example of your code would be useful. Descriptions of ideas you tried that failed are helpful too. (You did this somewhat by mentioning the NOLOGGING option.)

HTH,
 Ed
--
Ed Prochak
Magic Interface, Ltd.
ORACLE Development, conversions, training and support 440-498-3700 magic_at_interfacefamily.com

Sent via Deja.com http://www.deja.com/
Before you buy. Received on Wed Jan 05 2000 - 14:38:06 CST