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.
FROM table1 AS A
FROM table2 B
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 ...
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 ...
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;
Targeting a single row, this avoids an "unneeded record change", i.e. writing a new row version without need:
DELETE FROM tbl WHERE … AND counter = 1; -- common case first!
IF NOT FOUND THEN
UPDATE tbl SET counter = counter - 1 WHERE …;
Should also be cheaper than a trigger solution, where a trigger ...
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://...
You can use a data modifying common table expression:
with deleted as (
DELETE FROM some_table
WHERE id = 42
INSERT INTO other_table(user_id, "timestamp")
select user_id, timestamp '2020-01-04 21:32:34'
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 ...
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 ...
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 ...
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.
There is no auto cleanup of fragmentation on deleting table rows from an InnoDB table..
You have to defragment the table manually.
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 ...
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.
WHERE OldTable.[date] >= '...
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).
SELECT timestamp FROM logs WHERE userid=1 ORDER BY timestamp DESC LIMIT 1 OFFSET 100
If there are more than 100 rows, this will ...
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 ...
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 ...
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 ...
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 ...
please, try this:
with stable as (
ROW_NUMBER() over(order by [code]) as [ID]
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
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.
curs CURSOR FOR SELECT counter FROM ...
You really want to throw away 650+GB of Data?
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 ...
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 ...
In the Stack Overflow question, What are the Pros and Cons of Cascading delete and updates?
The accepted answer by SQLMenace states:
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
Orphans are possible
If by mistake you ...
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 ...
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 ...
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
, max(created_at) AS maxDate