Deleting taking an excessive amount of time from a table with a Join Index
Hi All and a Happy New Year.
We have a new requirement to place a Join Index (that also happens to be ML-PPI) over two tables to aid performance for a Tactical Workload. The JI works well with excellent response times for a very large dataset. (One Table is 1.5Tb and the other 330Mb) The JI is 1.3Tb.
The data is inherently skewed owing to this data being account indexed based and some customers submitting many more transactions than others. This is done on purpose to permit NUPI access.
The JI is sparse by having a NOT IN clause to exclude these skewed accounts and a bi weekly skewed data removal process that moves the skewed data out of the main tables, bearing in mind the skewed accounts are not in scope for the JI.
When we added the JI we had to build the it by first emptying the smaller table, creating the JI and reloading the data a month at a time to avoid spool out issues.
When the, above mentioned, de-skew process now runs, the delete process, that took 5 mins has now had to be aborted after 4 hours and checking the row counts with an access lock the rate of removal was approx 100 rows per minute, we need to remove 6,000,000 rows.
We know the JI has impacted this but we don't know why to this extent. Can anyone help please.