| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Usenet -> c.d.o.server -> Re: delete from table
Hello,
Thank you Alberto the second index was the solution.
For anyone who has the same problem:
> Hi,
>
> thank you very much, the solution IS there now!
> Makes a lot of sense that Oracle, even if the master records are deleted
> first,
> has to check for their existence again when the FK rows are deleted.
> I created an index on the PK, took about 5 sec for 200,000
> rows.
> Do I have to be carefull with anything specific about that index?
> Maybe recreate it from time to time?
Just rebuild it sometimes, along with all the other indexes. You could write a script that automates this task, or perhaps there's some standard package of Oracle (the ones beginning with DBMS_) that could be used for that purpose, since it's a common problem; i'm not sure.
It's better to rebuild than to drop and recreate,
it's less hassle (you don't have to clone the
parameters such as INITIAL, NEXT, INITTRANS...,
and especially the tablespace where the index resides)
and the index is always available.
(But you must have enough space in the temporary
tablespace because Oracle will build a new copy
there and than it will swap the copy with the old
index just at the end, so you must have space for
two copies of the index).
It's also more performant because Oracle will use
the old index for retrieving the data in sort order
(even if the old index is not balanced, it's still more
efficient to get the datas there instead of performing
a full table scan and then a very time-consuming
ordering, which will be always performed on disk
instead of in memory if the table has many rows.
That's because the leaves of the index are double-linked
together in sort order; this is called a FAST FULL TABLE
scan, that it would be better called FAST FULL INDEX SCAN).
 |
 |