If you delete a lot of records, and you keep only a few you can
do a create table as
then you drop the table, and rename the last.
:)
2016-08-26 9:18 GMT-04:00 Mark W. Farnham <mwf@xxxxxxxx>:
A couple questions came up in the thread I have researched somewhat
deeply. Please take notice of several “IF” conditions wrapped around the
use cases:
The first big “IF” is that there is some impediment to using the “Scaling
to Infinity” approach. Copy keeping what you want by partition and
exchanging that back in and eventually dropping the exchanged out table
beats any other method I’ve discovered with respect to minimizing redo and
undo generation by a huge amount that scales upwards in savings the more
you need to get rid of.
Even IF you do not have and cannot afford the partitioning option, you can
use “poor man’s partitioning” of several varieties dating back to 1988 that
pale in comparison to the elegance of what Tim Gorman has described and
documented so well. There are too many varieties for a list, but one of the
interesting tricks that **might** be germane here is to create at least
two views to the underlying table. Create a synonym to one of the views.
All user access except possibly inserts if you also use the insertable
union all view with multiple base tables trick so all new records go into
the latest base table. Then you’ll need another synonym to the insert point.
Then you add to the filters on a view that is not currently pointed to by
the synonym and create or replace the synonym to the view that now has more
virtual deletions. Existing running queries continue on, having had the
synonym resolved in their past, and new queries see only the new more
restricted view. If you used the time based additional base table approach
with a union all view, eventually you reach the point where even
non-exchange based copy keep is a slight concurrency hiccup (and possible
row duplications are handled in a somewhat awkward interim view being
active that masks them). Then you create a view that simply does not
reference the base table that no longer has a unique row reference.
Of course the need to do this was replaced at scale by partition exchange.
But if you cannot do partition exchange, this rotation may work for you.
Avoiding deletes, since a recoverable projection of the deleted tuples has
to be created somewhere, is a big deal.
The other question was about deletion in row order. Yeah: If big delete
you must, that is the way to do it. I have not benchmarked that since disk
farms pretty much sucked though. Sometimes it is useful to generate a list
of the block_ids, so you can scan the zeroeth rowid of the deleted from
blocks with an inlist after the delete commit to make them puke any delayed
block cleanouts. (This is also a technique to puke trash for tables that
are nearly always only in the pga; so far it seems to always read into
buffer cache if you specify a single row per block.)
mwf
*From:* oracle-l-bounce@xxxxxxxxxxxxx [mailto:oracle-l-bounce@
freelists.org] *On Behalf Of *Jonathan Lewis
*Sent:* Thursday, August 25, 2016 3:32 AM
*To:* Martin Berger
*Cc:* JDunn@xxxxxxxxx; Oracle-L Freelists; Andrew Kerber; Chris Taylor
*Subject:* RE: Deletion from large table
The most appropriate choice depends so much on the logical and physical
constraints. In the case of "get rid of a lot of very old history, but
spread the job out over time" there can even be the argument for a simple
repeate tablescan with "delete where rownum <= {X}; commit; wait a bit and
repeat" because even though you keep tablescanning the stuff that no longer
needs deleting you're doing block cleanout on the blocks that changed on
the last pass.
Regards
Jonathan Lewis
http://jonathanlewis.wordpress.com
@jloracle
------------------------------
*From:* Martin Berger [martin.a.berger@xxxxxxxxx]
*Sent:* 25 August 2016 08:13
*To:* Jonathan Lewis
*Cc:* JDunn@xxxxxxxxx; Oracle-L Freelists; Andrew Kerber; Chris Taylor
*Subject:* RE: Deletion from large table
Yes,
The idea was to commit after each deletion. If required wait some time or
put it into a job to spread the load from a one-time punch to a smooth
distribution.
Martin
^∆x
Am 25.08.2016 09:09 schrieb "Jonathan Lewis" <jonathan@xxxxxxxxxxxxxxxxxx
:
Are you going to commit between each delete ?
If not there's the read-consistency problem - maybe addressable by
modifying the code and using AS OF SCN; if so then that's what
dbms_parallel_execute can do for you.
Regards
Jonathan Lewis
http://jonathanlewis.wordpress.com
@jloracle
------------------------------
*From:* Martin Berger [martin.a.berger@xxxxxxxxx]
*Sent:* 25 August 2016 08:04
*To:* Andrew Kerber
*Cc:* Jonathan Lewis; JDunn@xxxxxxxxx; Chris Taylor;
oracle-l@xxxxxxxxxxxxx
*Subject:* Re: Deletion from large table
In case the single transaction is "to big" for whatever reason, and it
needs to spit into chunks (WHERE ROWNUM < 1000000) has anyone experience if
it makes sense to "cluster" by blocks?
delete from big_table where big_table.rowid in
(select rowid from
(select rowid, rownum rn
from big_tablenot where exists (select 1 from small_table s where
s.id = b.id)
order by rowid)
where rn < 1000000)
The idea is to modify few table blocks per transaction, and as the
available space is clustered as well, new inserts will (hopefully) be in
same or adjacent blocks.
I don't recommend doing so, it's more a question about findings,
side-effects, ...
Martin
2016-08-23 16:57 GMT+02:00 Andrew Kerber <andrew.kerber@xxxxxxxxx>:
I have generally had good performance with syntax like this:
delete from big_table where id in (select big_table.id from small_table,
big_table where small_table.id=big_table.id (+) and small_table.id is
null)
On Tue, Aug 23, 2016 at 9:38 AM, Jonathan Lewis <
jonathan@xxxxxxxxxxxxxxxxxx> wrote:
Best access path does vary with circumstances.
If you're expecting lots of inserts while doing the deletes you may find
that as the delete progresses the rate slows down and the volume of undo
applied for read-consistency climbs.
If you see that as a problem it may be that finding an index that lets you
walk the big table in reverse order of data arrival may (slightly
counter-intuitively) improve performance.
Under any circumstances deleting by tablescan and deleting by index range
scan behave differently with respect to index maintenance (this note on big
updates also applies to big deletes: http://jonathanlewis.
wordpress.com/2006/11/22/tuning-updates/
<https://jonathanlewis.wordpress.com/2006/11/22/tuning-updates/> ).
Regards
Jonathan Lewis
http://jonathanlewis.wordpress.com
@jloracle
------------------------------
*From:* oracle-l-bounce@xxxxxxxxxxxxx [oracle-l-bounce@xxxxxxxxxxxxx] on
behalf of John Dunn [JDunn@xxxxxxxxx]
*Sent:* 23 August 2016 14:39
*To:* Chris Taylor
*Cc:* oracle-l@xxxxxxxxxxxxx
*Subject:* RE: Deletion from large table
Unfortunately it’s a nightly thing….whilst updates are still going on….
*John *
*From:* Chris Taylor [mailto:christopherdtaylor1994@xxxxxxxxx]
*Sent:* 23 August 2016 14:38
*To:* John Dunn
*Cc:* oracle-l@xxxxxxxxxxxxx
*Subject:* Re: Deletion from large table
Is this a one time thing, or a regularly occurring thing? (A one time
data cleanup versus a nightly routine)
If it's a one time data cleanup (or rarely needed), I'd recommend saving
off the rows you want to keep into another table, truncate the big_table
and reload the rows from the temporary table you created to save the rows
you wanted.
Delete is one of the (if not THE) single most expensive operation you can
run in a database (but I'm sure you're aware of that but wanted to mention
it).
Chris
On Tue, Aug 23, 2016 at 5:17 AM, John Dunn <JDunn@xxxxxxxxx> wrote:
I need to delete large numbers of rows from a large table based upon
whether a record exists in a small table.
I am currently using :
delete from big_table where not exists (select 1 from
small_table s where s.id = b.id)"
big_table may have up to 100,000 rows for the same id value.
small_table will only have one row per id value
Is there a better way to code this?
*John *
--
Andrew W. Kerber
'If at first you dont succeed, dont take up skydiving.'
--
Martin Berger martin.a.berger@xxxxxxxxx
+43 660 2978929 <+436602978929>
@martinberx <https://twitter.com/martinberx>
http://berxblog.blogspot.com
