Fastest way to delete data?
Vložit
- čas přidán 24. 05. 2023
- If you need to remove rows that are no longer required in your database, there are a lot of options you can explore. But what is the BEST way to do it?
Follow me!
blog: connor-mcdonald.com
twitter: / connor_mc_d
The Podcast!
podcasts.apple.com/au/podcast...
open.spotify.com/show/5dqVVft...
Subscribe for new tech videos every week
Other social media channels here: linktr.ee/connor
A free Oracle database forever ?! Oh yes indeed!
www.oracle.com/cloud/free
Music: Night Owl (Broke For Free), Kevin Mcleod (incomptech), Dyalla
#database #sql #performance
Nice summary. Thanks Connor.
Absolutely agree 💯
Hi Connor... please make a detailed video on how to effectively read awr and addm reports.
Your last method at the end with MOVE ONLINE is elegant. If that's not possible, a fast solution for an in-place DELETE on large non-partitioned tables is to sort the rows for deletion by DB block ID by using the ROWID, may be using a temp table, and then batch deleting the rows. This reduces the db file sequential reads by clustering by physical location. Linux SD devices and often SAN devices will do a read ahead and pre-cache adjacent data blocks.
As always , there is tradeoff , in ordet o move the table and keep the needed rows (assuming we need to delete 20% of the table) we still need almost double the space. however, it is a great option !!!
Hi..can you make a video on varchar 32k. I used in my table and found the row size got increased and the table is largest in db. Is v32k taking lots of space in asm .should i move to clob?
Nice
Can we use multi threadding. I. e. dividing deleting rows with some logic and distributing to multiple session. Like dbms_parallel_execute where rows are distributed in chunks and each chunk is allocated to each session.
If we try this out on a 5 TB table and since its online, Database would need additional 5 TB to create a MV in background to support online?
Sir, is it true ?
Seems like a nuke would be faster.