<snip>
Above all, please remember these two adages...
* The fastest operation is one you never do
* Patient: Doctor! Doctor! It hurts when I do this! (waves arms
comically) Doctor: Then don't do that!
<snip>
One more…most Oracle database features that are cool and helpful cost
additional $$$. ☺
From: oracle-l-bounce@xxxxxxxxxxxxx [mailto:oracle-l-bounce@xxxxxxxxxxxxx] On ;
Behalf Of Tim Gorman
Sent: Tuesday, August 23, 2016 9:17 AM
To: dbakevlar@xxxxxxxxx; JDunn@xxxxxxxxx
Cc: Chris Taylor <christopherdtaylor1994@xxxxxxxxx>; oracle-l@xxxxxxxxxxxxx
Subject: Re: Deletion from large table
John,
Beaten to the punch by my brilliant spouse...
Augmenting her insightful suggestion, if you can partition it in such a way
that the rows to be deleted are isolated into separate partitions away from the
rows being inserted/updated? Queries aren't a concern, but non-isolated DML
would be a problem.
An example would be a DATE column, assuming that rows being deleted are older
than a certain time, while rows being updated are less than a certain time.
If so, then you can employ a parallel CREATE TABLE ... AS SELECT or INSERT /*+
APPEND */ SELECT to extract rows rows you wish to keep from the partition which
you are currently performing deletions. This will result in another standalone
table which you can now exchange with the original partition. An INSERT
operation is always far faster than any UPDATE or DELETE operation, especially
when performed in bulk using APPEND (i.e. direct-path load).
Other benefits are several...
* resulting partition is densely-populated and compacted, as opposed to
sparsely-populated after a mass deletion, causing subsequent operations to be
faster, especially full-table scans
* row density can be even greater if compression is employed during
direct-path INSERT or CTAS
* if the resulting partition is never going to be have DML performed on it
again, it can be relocated to a tablespace to be set READ ONLY
I've presentations, white-papers<http://evdbt.com/papers/>, SQL and PL/SQL
scripts<http://evdbt.com/scripts/>, and videos<http://evdbt.com/videos/> on
these operations on my website, if this helps?
* Presentation "Scaling To Infinity: Partitioning Data Warehouses on Oracle
Database<http://evdbt.com/download/presentation-scaling-to-infinity-partitioning-data-warehouses-on-oracle-database/>"
* White paper "Scaling To Infinity: Partitioning Data Warehouses on Oracle
Database<http://evdbt.com/download/paper-scaling-to-infinity-partitioning-data-warehouses-on-oracle-database-2/>"
* PL/SQL procedure EXCHPART<http://evdbt.com/scripts/>
* Video "The Fastest UPDATE is an
INSERT<http://www.youtube.com/watch?v=pvbTAgq_BBY>"
Above all, please remember these two adages...
* The fastest operation is one you never do
* Patient: Doctor! Doctor! It hurts when I do this! (waves arms
comically) Doctor: Then don't do that!
Hope this helps...
-Tim
On 8/23/16 08:53, Kellyn Pot'Vin-Gorman wrote:
Have you considered partitioning this table and can it “possibly” be
partitioned in a way that would isolate these rows?
Just a wild thought….
Kellyn
On Aug 23, 2016, at 7:39 AM, John Dunn
<JDunn@xxxxxxxxx<mailto:JDunn@xxxxxxxxx>> wrote:
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<mailto: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<mailto: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<http://s.id/> = b.id<http://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
Confidentiality Note: This message contains information that may be
confidential and/or privileged. If you are not the intended recipient, you
should not use, copy, disclose, distribute or take any action based on this
message. If you have received this message in error, please advise the sender
immediately by reply email and delete this message. Although ICAT, Underwriters
at Lloyd's, Syndicate 4242, scans e-mail and attachments for viruses, it does
not guarantee that either are virus-free and accepts no liability for any
damage sustained as a result of viruses. Thank you.
