Re: Deletion from large table

Very true! Thanks for the reminder! Partitioning still has a 25% uplift on the Enterprise Edition license.

Since Oracle purchased partitioning technology from DEC (RDB) in 1996 and incorporated it into Oracle8 in 1997, this has likely been one of the most lucrative investments Oracle has ever made.

On the other hand, when used correctly, it is worth every cent.

On 8/23/16 09:45, Sweetser, Joe wrote:
>
> <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 $$$. J
>
> *From:*oracle-l-bounce_at_freelists.org
> [mailto:oracle-l-bounce_at_freelists.org] *On Behalf Of *Tim Gorman
> *Sent:* Tuesday, August 23, 2016 9:17 AM
> *To:* dbakevlar_at_gmail.com; JDunn_at_sefas.com
> *Cc:* Chris Taylor <christopherdtaylor1994_at_gmail.com>;
> oracle-l_at_freelists.org
> *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
> o 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_at_sefas.com
> <mailto:JDunn_at_sefas.com>> wrote:
>
> Unfortunately it’s a nightly thing….whilst updates are still
> going on….
>
> *John*
>
> *From:*Chris Taylor [mailto:christopherdtaylor1994_at_gmail.com]
> *Sent:*23 August 2016 14:38
> *To:*John Dunn
> *Cc:*oracle-l_at_freelists.org <mailto:oracle-l_at_freelists.org>
> *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_at_sefas.com
> <mailto:JDunn_at_sefas.com>> 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 wheres.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.

--
http://www.freelists.org/webpage/oracle-l