Sql Server - Отключить столбец из VLT (очень большая таблица)

Ответ 1

Я бы взял один из подходов, уже упомянутых, но с некоторыми ключевыми модификациями. Предполагая, что вы находитесь на SQL Server 2008, выполните следующие действия:

• Сделайте копию существующей очень большой таблицы нулевой длины только с теми столбцами, которые вы хотите сохранить:

  select top 0 {{column subset}} into tbl_tableB from tableA
  

  Обязательно скопируйте все индексы, ограничения и т.д. в новую таблицу. Столбцы идентификаторов будут обрабатываться соответствующим оператором SELECT...INTO.

• Переименуйте исходную таблицу; мы заменим его на следующем шаге.

  exec sys.sp_rename @objname = 'tableA', @newname = 'tbl_tableA'
  

• Создайте представление с использованием имени исходной таблицы и UNION ALL:

  create view tableA
  as
  select {{column subset}} from tbl_tableA
  union all
  select {{column subset}} from tbl_tableB
  

  Это будет поддерживать некоторый уровень совместимости с приложениями, запрашивающими данные. INSERTs, UPDATEs и DELETEs должны обрабатываться через триггеры на представлении. UNION ALL предотвратит давление в tempdb, так как сортировка не будет (по сравнению с прямой UNION), и у нас никогда не будет более одной копии строки, существующей за раз.

• Используйте DELETE в сочетании с предложением OUTPUT для удаления данных в партиях из исходной таблицы и одновременно вставьте их в новую таблицу:

  BEGIN TRAN
  DELETE TOP (1000) /* or whatever batch size you want */
  FROM
      tbl_tableA
  OUTPUT (
      DELETED.{{column subset}} /* have to list each column here prefixed by DELETED. */
  )
  INTO
      tbl_tableB (
          {{column subset}} /* again list each column here */
      )
  /* Check for errors */
  /* COMMIT or ROLLBACK */
  /* rinse and repeat [n] times */
  

• Как только вы закончите с DELETEs/INSERTs, оставьте представление, отбросьте исходную таблицу, переименуйте новую таблицу:

  drop view tableA
  drop table tbl_tableA
  exec sys.sp_rename @objname = 'tbl_tableB', @newname = 'tableA'
  

Главным достоинством этого подхода является то, что DELETE и INSERT происходят одновременно в одной и той же транзакции, то есть данные всегда будут в согласованном состоянии. Вы можете увеличить размер партии, изменив предложение TOP, предоставляя вам больше контроля над использованием и блокировкой журнала транзакций. Я тестировал этот точный подход на таблицах с колонками и без столбцов, и он отлично работает. На очень большой таблице потребуется некоторое время для запуска; может составлять от нескольких часов до нескольких дней, но он будет иметь желаемый результат.

Ответ 2

Сама ALTER TABLE ... DROP является только операцией метаданных, она будет почти мгновенной, пока она может получить исключительную блокировку в таблице, что подразумевает, что все запросы, используемые в таблице, должны стекать (завершаться). Но удаление столбца физически не удаляет их, см. столбцы таблицы SQL Server под капотом.

Следующий шаг - удалить физические столбцы, если это необходимо. Я вызываю, если необходимо ", потому что, в зависимости от типа столбца, это может не стоить усилий. Для столбцов переменной длины вы можете вернуть пространство, запустив DBCC CLEANTABLE. Но если вы удалили столбцы фиксированного размера на несжатой таблице (без сжатия страниц или строк), единственный способ вернуть пространство - это перестроить таблицу (кучу или кластеризованный индекс). Если таблица разделена, вы можете попытаться восстановить автономный один раздел за раз (ALTER TABLE ... REBUILD PARTITION = N). Если нет, ваш лучший снимок - это онлайн-перестройка, если у вас нет столбцов типа MAX (это ограничение sp_rename. В целом вам будет намного лучше, если вы сможете использовать онлайн-версию.

Ответ 3

Я бы сказал, что сочетание другой таблицы и пакетного задания.

1. Создайте новую таблицу с необходимой структурой. Используйте тот же кластерный индексный ключ, что и ваша старая таблица.

2. Создайте представление для объединения старых и новых таблиц, чтобы у вас был постоянный доступ к обоим, если это необходимо. Чтобы ограничить проблемы при производстве, вы можете называть это так же, как и исходную таблицу, и переименовать таблицу в _Old или что-то еще. Только включите нужные поля в представление, а не поля, которые вы бросаете, очевидно.

3 - внутри транзакции:

• Вставьте несколько строк в новую таблицу (скажем, 1 м за раз или что-то еще)
• Удалить из старой таблицы JOIN ing в новой таблице

У этого есть преимущества низкого роста журнала (потому что вы участвуете в доработке), низкий рост базы данных (поскольку количество дополнительных строк никогда не превышает размер вашего пакета), и он увеличивается, поэтому вы можете остановиться, если он станет слишком медленным.

Новости BAD: вы удаляете записи, поэтому, как только вы начинаете, вы в основном привержены этому процессу. Вы также можете получить давление tempdb в виде UNION в зависимости от того, сколько должно выполняться сортировка.

Ответ 4

Возможно, я подумал бы о создании новой секционированной таблицы с необходимой схемой и вставке данных в таблицы коммутаторов, а затем переключении этих таблиц в новую таблицу.

Если вы не очень хорошо знакомы с секционированными таблицами и индексами, я настоятельно рекомендую этот отличный документ Кимберли Триппа.

Когда вы вводите данные в свои таблицы коммутаторов, вы можете принудительно выполнить минимальное ведение журнала, выполнив следующие действия:

• Ваша таблица коммутаторов должна быть пустой.
• Ваша БД должна быть в режиме простого восстановления

• Вам нужно использовать флаг трассировки 610 следующим образом:

  DBCC TRACEON (610)

• Вам нужно использовать подсказку табуляции в таблице:

  INSERT newtable WITH (TABLOCK)
  SELECT col1, col2, col3, col4 
  FROM oldtable
  WHERE col1 BETWEEN min and max
  

• Таблица коммутаторов должна иметь кластеризованный индекс

Удачи. Надеюсь, это будет полезно. Я работаю с VLDB в SQL Server и обнаружил, что разделение довольно неоценимо, когда дело доходит до загрузки и перемещения данных.

Ответ 5

Не могу сказать, что у меня есть опыт с таблицами, размер которых, но если это был я и искал что-то попробовать, я бы попробовал BCP'у данные (только те столбцы, которые вы хотите сохранить), на O/S файл, отбросьте таблицу, а затем переместите данные обратно в новую таблицу только с нужными столбцами. Конечно, это предполагает, что у вас есть возможность отключить сервер во время выполнения этого обслуживания (и у вас есть хорошие резервные копии, прежде чем вы начнете).