SUPPRIMER la totalité de la table à l'exception d'une ligne

Supposons que j'ai une base de données avec une table qui contient 200k+ lignes.
Ce tableau est fixe tuple avec l'id 1800. Le reste de la n-uplets de la séquence commence à 300k+.
J'ai un besoin de nettoyer ce tableau, supprimer tous les enregistrements sans les supprimer du registre avec l'id 1800. Je suis venu avec 3 types de requête je pourrais peut-être lancer:

DELETE FROM table WHERE id > 1800
DELETE FROM table WHERE id <> 1800
DELETE FROM table WHERE id NOT IN (1800)

J'ai le sentiment que la première est plus rapide que les autres, mais je ne suis pas sûr, comme toutes les autres données ont id plus de 1800.

Lequel est le plus rapide, et pourquoi? Aussi, si il ya un moyen plus rapide pour supprimer les enregistrements à l'exclusion de celui qui ne peut pas être supprimé, faites le moi savoir.

Le plus rapide sera probablement pour copier un enregistrement dans une table temporaire, tronquer la table, puis insérez un enregistrement
À moins que l'optimiseur de requête est le total de la merde (ce qui est très rare), les trois sont les mêmes.
Il dépend. Si l'id est un (primaire) clé (ou un champ indexé) les trois requêtes ont des performances comparables. Autre chose : quelque chose se passe, en fonction des indices et de la cardinalité de l'id de domaine.
Vous pouvez être intéressé par la étroitement liée réponses ici et ici.
Existe-il des clés étrangères impliquées? Si oui, qui pourrait grandement affecter les performances. De toute façon, la copie d'enregistrer et de tronquer pourrait fonctionner, si il n'y a pas les clés étrangères. Si il y a des clés étrangères, puis en supprimant toutes les autres lignes est le chemin à parcourir, malheureusement comme il est assez lent. Aussi, si il y a des FKs puis assurez-vous qu'ils ont des indices sur la referncing lignes.

OriginalL'auteur MurifoX | 2013-01-16

  1. 6

    Le moyen le plus rapide dans la plupart des bases de données:

    1. Sélectionnez le dossier avec l'id 1800 dans une table temporaire
    2. Chute de la table d'origine
    3. De copier les données de la table temporaire dans le tableau

    Certes, cela peut ne pas être possible en raison de déclencheurs, les contraintes et les autorisations. Dans de nombreuses bases de données que vous pouvez faire quelque chose de similaire en modifiant (2) pour tronquer la table au lieu de la laisser tomber.

    Quant à votre question de départ, les frais généraux avec le fait de supprimer les lignes et les données associées avec eux va dominer la requête. Comment vous faites la comparaison n'est pas pertinente.

    Exemple de code

    create temp table saved as
    select * from t where id = 1800

truncate table t

insert into t
    select * from saved

    Je ne suis pas sûr de Postgres des conventions de nommage pour les tables temporaires, mais c'est l'idée.

    +1 me battre pour elle
    Bien que ce soit rapide, il le fait avec des instructions DDL au lieu de DML, qui peut être désastreux si vous avez à vous soucier de l'inter-table de dépendances. Je serais favorable à l'ajout d'un index sur id (si elle n'y est pas déjà) et en faisant de la delete si vous voulais plus sûr de la cohérence des données; parce que, pour exécuter le supprimer, vous devez sélectionner d'abord les enregistrements (donc, un index accélère le cadre de l'opération). Cependant, si la vitesse brute est votre seule préoccupation, c'est probablement le moyen le plus rapide pour aller.
    Tronquer est presque toujours mieux que de laisser tomber et de recréer une table. Que faire si il y a des clés étrangères etc? Beaucoup plus simple à tronquer, et tout aussi rapide que la suppression et recréation de la table. (si pas plus)

    OriginalL'auteur Gordon Linoff

  2. 6

    Tant que celles-ci affectent les mêmes dossiers, ceux-ci ont des performances similaires.

    Il ya une petite chance la première utilisation d'un index de recherche plutôt que plus efficace full table scan, mais c'est négligeable.

    Merci pour la réponse très. Cependant, je vous l'accepter @GordonLinoff réponse, car il fournit une technique différente pour supprimer les enregistrements.

    OriginalL'auteur Quassnoi

  3. 3

    Si vous ne pouvez pas déplacer l'ID d'une nouvelle table, vous pourriez vouloir essayer et de supprimer des groupes ou des lots. Parfois, avoir une opération avec une grande partie des enregistrements n'est pas menée de la manière la plus rapide. C'est le cas pour toute base de données oracle et microsoft base de données de produits inclus.

    BEGIN TRANSACTION;
DELETE FROM table WHERE id >= 0 and  id < 20000 and id != 1800;
COMMIT TRANSACTION;
BEGIN TRANSACTION;
DELETE FROM table WHERE id >= 20000 and  id < 40000 and id != 1800;
COMMIT TRANSACTION;
etc
etc
    C'est bon à savoir.

    OriginalL'auteur Kuberchaun