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 ...
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 ...
Since you don't have enough space to run a vacumm or rebuild, you can always rebuild your postgresql databases by restoring them. Restoring the databases, tables, indexes will free up space and defragment. Afterwards, you can setup automated maintenance to vacumm your databases on a regular basis.
1 Backup all of the databases on your postgresql server
This difference only seems to apply when the object is a B+tree. When removing the primary key on the table variable so it is a heap I got the following results
But with the PK I found a similar pattern in my tests as well with typical results below.
| @table | #table | ##table | [...
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 ...
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 ...
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 inherit from a master table, lets call it log. That would make deleting very cheap: ...
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 ...
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 ...
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 ...
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 = @...
The row-versioning framework introduced in SQL Server 2005 is used to support a number of features, including the new transaction isolation levels READ_COMMITTED_SNAPSHOT and SNAPSHOT. Even when neither of these isolation levels are enabled, row-versioning is still used for AFTER triggers (to facilitate generation of the inserted and deleted pseudo-tables), ...
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 ...
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 ...
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;
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 ...
For large deletes in batches, consider specifying a clustered index key range instead of using TOP so that a clustered index seek can be used in the plan. Below is an example.
@BATCHSIZE INT = 4000
, @ITERATION INT = 0
, @TOTALROWS INT = 0
, @MSG VARCHAR(500)
, @STARTTIME DATETIME
, @ENDTIME DATETIME
, @StartValue int = ...