Hot answers tagged

12

How does SQL Server know which rows to delete? To understand how it's processed, it's probably more helpful to look at the execution plan for the query. Setup scripts are at the end. First, let's change the query a little bit to something that should throw an error, but doesn't. DELETE A FROM table1 AS A WHERE EXISTS ( SELECT 1/0 FROM table2 B ...


10

You need to delete rows in batches to avoid lock escalation. Lock escalation happens when SQL Server switches from row or page locks to locking the entire table. Lock escalation conserves memory when SQL Server detects a large number of row or page locks have been taken, and more are needed to complete the operation. This may be why you're having blocking ...


5

So my question is, am I logging every delete from every index when deleting from the table? Of course you are. The log contains a record for every change to every page with sufficient detail to either redo or undo the change. If so, is that normal behaviour or could the reason be because of missing clustered index? Unrelated. You're deleting 18 million ...


4

Create a temp table, put in your users and use that in your delete statement: CREATE TABLE delete_users (user_id ... primary key); INSERT INTO delete_users (user_id) values (...),(...)...; DELETE FROM [dbo].[Table] x WHERE EXISTS ( SELECT 1 FROM delete_users y WHERE x.user_id = y.user_id ); DROP TABLE delete_users;


3

Targeting a single row, this avoids an "unneeded record change", i.e. writing a new row version without need: DO $$ BEGIN DELETE FROM tbl WHERE … AND counter = 1; -- common case first! IF NOT FOUND THEN UPDATE tbl SET counter = counter - 1 WHERE …; END IF; END $$; Should also be cheaper than a trigger solution, where a trigger ...


3

There are several ways to delete 'most' of a table. Perhaps the best is SET @@innodb_file_per_table = ON; -- if this is not already on (optional) CREATE TABLE new LIKE real; INSERT INTO new SELECT * FROM real WHERE ... -- the rows you want to _keep_; RENAME TABLE real TO old, new TO real; DROP TABLE old; More techiques: http://...


3

You can use a data modifying common table expression: with deleted as ( DELETE FROM some_table WHERE id = 42 returning user_id ) INSERT INTO other_table(user_id, "timestamp") select user_id, timestamp '2020-01-04 21:32:34' from deleted;


3

Use DBCC CLONEDATABASE to generate a new copy without data, drop the original database an rename the generated clone.


2

With RMAN you can do point-in-time recovery (if properly) configured, so you could restore the database to the moment before the drop. Also, you can even restore just a single table from a backup. Other than that you have the recyclebin from which you can recover dropped tables and you have also Flashback Database. So to recover from a dropped table there ...


2

Aside from the aforementioned 'oops' where you delete a record you didn't intend to and now a lot of associated records are gone as well, you probably simply can't cascade delete everything. A thing to keep in mind when using cascades is that it can cause conflicts if there's multiple cascade paths. SQL Server and other DBMS will block the creation of the ...


2

Are there any disadvantages, gotchas, or things to bear in mind when adding ON UPDATE CASCADE ON DELETE CASCADE to every single foreign key in the database? One reason not to : Oops!! If you accidentally delete the wrong record, then you wipe out everything that references on that record. If that record happens to be the one at the root of your entire ...


2

You cannot. You need to have SYSDBA privilege on the database. SYSDBA doest not allow to delete from DBA_AUDIT_TRAIL view but it allows to use DBMS_AUDIT_MGMT which is the right way to purge the audit trail.


2

Yes, index modifications are logged in pretty much the same way as "data" modifications.


2

There is no auto cleanup of fragmentation on deleting table rows from an InnoDB table.. You have to defragment the table manually. METHOD #1 You can run the following OPTIMIZE TABLE table1; which will execute the following fotr you ALTER TABLE table1 ENGINE=InnoDB; ANALYZE TABLE table1; This might lock the table if you still have a lot of rows (this ...


2

Another way you can handle this that would minimize downtime (assuming the majority of the data is being deleted) is you can INSERT everything within the last month into a new Table. DROP the old Table, and use sp_rename to instantly rename your new Table to the old Table's name. For example: SELECT * INTO NewTable FROM OldTable WHERE OldTable.[date] >= '...


1

You don't need the values keyword when you use IN delete from some_table where (col1, col2) in ( (1,2), (3,4) );


1

If you have an index on user_id, you can drop it and replace it with an index on (user_id,timestamp). This will also save a sort when displaying the latest log entries (WHERE user_id=... ORDER BY timestamp DESC LIMIT n). Then: SELECT timestamp FROM logs WHERE userid=1 ORDER BY timestamp DESC LIMIT 1 OFFSET 100 If there are more than 100 rows, this will ...


1

Your main issue is you're using an INNER JOIN between Machines and Orders which means only return the rows that match (in other words, only the rows where Orders exist). Rather you should use an OUTER JOIN, in this case a LEFT OUTER JOIN (which can simply be written as LEFT JOIN) so you get all the rows of Models and Machines, and the ones that don't have ...


1

I don't expect a big difference between both queries. Details depend on undisclosed information and data distribution. What actually makes a big difference is to have an index supporting it optimally. One might think that you already have that with your PK: primary key(action_date, item_num) But that's not so. The sequence of index expressions matters in ...


1

the server goes through every row of table1 and checks, if there is a corresponding id in table2 and deletes it , if that is the case..


1

Your second query will fail, because d0 only has a column seller_id. Your first query deletes all rows because the id in select id from d0 cannot refer to a column from d0, so it is taken as a column from the outer query, and the whole thing effectively becomes WITH d0 AS (...) DELETE FROM seller WHERE seller.id IN (SELECT seller.id FROM d0) and that ...


1

Assuming you want to group by code, name, and type fields and get the aggregation of deviceType's, in SQL Server 2014 you can use this workaround of stuff and FOR XML. This is generic and will work with any values in the deviceType field. One difference in this solution compared to your example output is the original rows/id's in the table get updated and ...


1

please, try this: with stable as ( SELECT ROW_NUMBER() over(order by [code]) as [ID] , [code] , [name] , [type] , CASE WHEN (SUM([web]) = 1 AND SUM([mobile]) = 1) THEN 'web&mobile' WHEN (SUM([web]) = 1 ) THEN 'web' WHEN (SUM([mobile]) = 1) THEN 'mobile' ELSE null END AS deviceType FROM ( select [id],...


1

The reference documentation made it quite difficult to understand how to put all elements together, especially because most examples mainly use cursors for scrolling through multiple results. In the end I was able to create a query with a single table lookup based on this tutorial. DO $$ DECLARE value integer; curs CURSOR FOR SELECT counter FROM ...


1

You really want to throw away 650+GB of Data? In Production? Really? Truncate is the fastest way to empty an existing table ... as long as you really are emptying those Tables and that the 650+GB you want to get rid of isn't just 2% of all the data in those tables! In that case, delete is your only option. It will be slower than truncate and it will take ...


1

DBMS_AUDIT_MGMT First execute INIT_CLEANUP, that you need to use only once. Next execute CREATE_PURGE_JOB with use_last_arch_timestamp => true, which creates the scheduled job for cleaning the audit trail, also need to use only once. Finally you need to take care of advancing the last archive timestamp with SET_LAST_ARCHIVE_TIMESTAMP, which you can ...


1

In the Stack Overflow question, What are the Pros and Cons of Cascading delete and updates? The accepted answer by SQLMenace states: Pros: When you delete a row from the Parent table all the foreign key rows are deleted This is usually faster than implementing this with triggers Orphaned rows are unlikely Cons Orphans are possible If by mistake you ...


1

The documentation describes that well: The standard form of VACUUM removes dead row versions in tables and indexes and marks the space available for future reuse. However, it will not return the space to the operating system, except in the special case where one or more pages at the end of a table become entirely free and an exclusive table lock can be ...


1

I've had to do this a couple of times and this is my preferred method for transforming a very large table into a partitioned one with minimal downtime. It does however, rely on the fact that you are fine with waiting a few days/weeks for it to complete the process (don't panic - you will understand why it takes this long later on): Create a new PARTITION ...


1

If you want to purge an outdated data then your query is a bit complicated: DELETE FROM fares AS f1 LEFT JOIN ( SELECT origin_id , destination_id , market_id , cabin , tripType , max(created_at) AS maxDate FROM fares GROUP ...


Only top voted, non community-wiki answers of a minimum length are eligible