I had a similar problem. As it turns out, those ON DELETE CASCADE triggers were slowing things down quite a bit, because those cascaded deletions were awfully slow.
I solved the problem by creating indexes on the foreign key fields on the referencing tables, and I went from taking a bunch of hours for the deletion to a few seconds.
You have a few options. The best option is to run a batch delete so that triggers are not hit. Disable the triggers before deleting, then re-enable them. This saves you a very large amount of time. For example:
ALTER TABLE tablename DISABLE TRIGGER ALL;
ALTER TABLE tablename ENABLE TRIGGER ALL;
A major key here is you want to minimize the ...
Your second option is far cleaner and will perform well enough to make that worth it. Your alternative is to build gigantic queries which will be quite a pain to plan and execute. In general you are going to be better off letting PostgreSQL do the work here. In general, I have found updates on tens of thousands of rows in the manner you are describing to ...
Please look at the Architecture of InnoDB (picture from Percona CTO Vadim Tkachenko)
The rows you are deleting is being written into the undo logs. The file ibdata1 should be growing right now for the duration of the delete. According to mysqlperformanceblog.com's Reasons for run-away main Innodb Tablespace:
Lots of Transactional Changes
Very Long ...
The easiest method to solve the problem is to query detailed timing from the PostgreSQL: EXPLAIN. For this you need to find at minimum a single query that does complete but takes longer than expected. Let's say that this line would look like
delete from mydata where id='897b4dde-6a0d-4159-91e6-88e84519e6b6';
Instead of really running that command you can do
NOTE: I have tested this on 9.1. I have no 9.0 server lying around here. I am preeeettty sure though it will work on 9.0 though.
CAUTION (As noted in the comments by @erny):
Note that high CPU load due to I/O operations may be expected.
You can do this with pretty much no down-time by using a temporary tablespace. The down-time will be in the form of ...
Quoting the manual:
There are two ways to delete rows in a table using information
contained in other tables in the database: using sub-selects, or
specifying additional tables in the USING clause. Which technique is
more appropriate depends on the specific circumstances.
Bold emphasis mine. Using information that is not contained in another table ...
I think we may have overcomplicated the answer that was in required in my case. I have no doubt that both Roland & Rick James are correct with their creation of a temporary table, injecting only rows that pass the filter NOT LIKE '-%' but the solution for me was "easier" because there was an important error I was unaware of until now and for that I ...
To answer your main question directly, the sorts are there to present rows to update operators (performing deletions in this case) in index key order. The principle at work here is that sorting on the keys will promote sequential access to the index.
This can be a good optimization, though the details depend on your hardware, how likely the affected pages ...
I am generally wary of cascaded deletes (and other automatic actions that could drop/damage data), either via triggers or ON <something> CASCADE. Such facilities are very powerful, but also potentially dangerous.
So, is cascading delete a correct choice here?
It would certainly do what you are looking for it to do: remove related records when ...
You can delete databases with DBCA which takes care of most of it.
Or you can do as below, but this will do the same as removing the datafiles, redo logs, controlfiles manually.
sqlplus / as sysdba
startup mount exclusive restrict
rman target /
drop database including backups noprompt;
After this, you still have to remove the entry that belongs ...
There is no feature built in to delete rows automatically on a time-based regime (that I would know of).
You could run a daily (you decide) cron-job to schedule simple DELETE commands or use pgAgent for the purpose.
Or you could use partitioning with weekly partitions. That makes deleting very cheap: just keep the latest two weeks and drop older partitions.
Assuming all the related tables have correct indexing for the delete paths, you could try:
OPTION (LOOP JOIN, FAST 1, USE HINT ('FORCE_LEGACY_CARDINALITY_ESTIMATION'));
If that works, try to reduce it to the minimal number of hints.
These sorts of plans are very challenging for cardinality ...
The DML versus DDL distinction isn't as clear as their names imply, so things get a bit muddy sometimes.
Oracle clearly classifies TRUNCATE as DDL in the Concepts Guide, but DELETE as DML.
The main points that put TRUNCATE in the DDL camp on Oracle, as I understand it, are:
TRUNCATE can change storage parameters (the NEXT parameter), and those are part ...
Here is one case where it would be hard to write it without:
DELETE /* FROM */ t1 -- this FROM is optional
FROM dbo.t1 -- this FROM is mandatory
INNER JOIN dbo.t2 AS t2
ON t1.key = t2.key
WHERE t2.key IN (1,2,3);
DELETE /* FROM */ t1 -- this FROM is optional
FROM dbo.t1 -- this FROM is mandatory
First of all, check the SQL errorlog to see if it actually hit a max size for the log. If it did, then the query has no hope of completing, it is probably already in a rollback state.
Even if it is, I always prefer to kill the spid manually (use sp_who2 or sp_WhoIsActive to find the spid, then do a kill 59 or whatever). You also can't check the rollback ...
Very simple indeed, but you do need to include the other WHERE clause as well:
DELETE FROM batch bp
USING sender_log sl
WHERE bp.log_id = sl.id
AND bp.protocol = 'someprotocol'
RETURNING bp.*, sl.*;
And to actually return what your question outlines, you need to include both tables in the RETURNING clause.
Should be something like this:
table_example1 AS A
INNER JOIN table_example2 AS B
ON A.COLUMN1 =B.COLUMN1
AND A.COLUMN2 = B.COLUMN2
COLUMN_DATETIME > @Period;
DELETE FROM A
FROM dbo.table_example1 AS A
FROM dbo.table_example2 AS B
B.COLUMN1 = A....
That is the whole point of foreign key constraints: they stop you deleting data that is referred to elsewhere in order to maintain referential integrity.
There are two options:
Delete the rows from INVENTORY_ITEMS first, then the rows from STOCK_ARTICLES.
Use ON DELETE CASCADE for the in the key definition.
1: Deleting In Correct Order
The most ...
The only query in what you're showing above appears to be this one, repeated a few times:
select * from [dbo].[FinanceDetail] trd WITH (UPDLOCK, SERIALIZABLE)
where trd.HeaderId = @HeaderId
DELETE from dbo.FinanceDetail
where HeaderId = @...
There is no any field combination which identifies the record uniqually.
I see at least 2 different solutions.
First solution: move unique records to a copy of table and replace original table.
CREATE TABLE temp LIKE products;
INSERT INTO temp
SELECT DISTINCT * FROM products;
DROP TABLE products;
RENAME TABLE temp TO products;
Second solution: add ...
Disabling triggers may be a threat to DB integrity and cannot be recommended; however if you are sure your operation is constraint-failure-proof, you can disable triggers, with the following:
SET session_replication_role = replica;
Run the DELETE here.
To restore triggers, run:
SET session_replication_role = DEFAULT;
You can break it up into chunks - delete in a loop; each delete iteration it's own transaction and then clearing the log at the end of each loop iteration. Finding the optimal chunk size will take some testing.
I suggest you take a look at this article by Aaron Bertrand, where he explains the details and runs tests for different scenarios, to show the ...
Roland's suggestion can be sped up some by doing both things at once:
CREATE TABLE tablename_new LIKE tablename;
ALTER TABLE tablename_new ENGINE = InnoDB;
INSERT INTO tablename_new
SELECT * FROM tablename WHERE `columnname` NOT LIKE '-%' ORDER BY primary_key;
tablename TO tablename_old,
tablename_new TO tablename
DROP TABLE ...
The table itself is only locked in ROW EXCLUSIVE mode, which shouldn't prevent any normal operations on the table, only things like DROP, ALTER, and CREATE INDEX.
Each individual row that is being deleted will be locked for the duration. This should only block other processes if those other processes are trying to update the rows (or delete them themselves)...
SQL Server never randomly chooses to delete or not delete a row. When you send a valid DELETE statement to SQL Server, it executes it. Guaranteed. Period. If there is an error in the statement, SQL Server will return an error. Are you seeing errors?
The far more likely problem is the query itself is not targeting the rows you think it is, or those rows ...
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
Make a temp table, switch it in and out, and copy the last 30 days data into it.
# Make empty temp table
CREATE TABLE NOTIFICATION_NEW LIKE NOTIFICATION;
# Switch in new empty temp table
RENAME TABLE NOTIFICATION TO NOTIFICATION_OLD,NOTIFICATION_NEW TO NOTIFICATION;
# Retrieve last 30 days data
INSERT INTO NOTIFICATION SELECT * FROM ...
Sure, put a clustered index on it. Tables with a clustered index will automatically deallocate space.
Otherwise, you're looking at:
ALTER TABLE (mytablename) REBUILD - which takes it offline
Doing deletes with TABLOCK hints
TRUNCATE TABLE (mytablename)
I know some folks think it's trendy, but heaps just aren't a good fit for active OLTP systems that have ...
It seems likely the very large ntext data is highly fragmented, causing a large amount of random I/O (or other inefficiencies) when locating LOB fragments to delete. Maybe the elastic thingy needs more I/O horsepower too.
You may need to export and reload the data to solve this problem. Copying to a new table, dropping the old, then renaming the new would ...