Je mettez à jour une colonne sur une table à l'aide de données d'une autre table. La clause voir Ce violon SQL des deux tables où je tente de mettre à jour La requête échoue pour certaines lignes de ce Cette question est liée à mon ici où j'avais des valeurs distinctes d'un ensemble de données volumineux à l'aide de J'ai utilisé la première mise à jour Déclaration fournie par @binoténaire. Pour les petites tables, il fonctionne dans un éclair. L'exemple est doté d'une table avec 20 000 enregistrements et la mise à jour a été complétée dans 20 secondes. Mais une autre table avec 9 millions de disques plus est en cours d'exécution depuis 20 heures jusqu'à présent !. Voir ci-dessous la sortie pour Des idées sur la façon de faire ce type de mise à jour plus rapidement? Même si cela signifie à l'aide d'une autre instruction de mise à jour ou même à l'aide d'une fonction personnalisée pour boucler et faire la mise à jour. où est basée sur plusieurs colonnes et certaines des colonnes sont NULL. De ma pensée, ces NULLS sont ce que sont jeter votre standard Set de table de mise à jour x = y où a = b instruction. table_one en fonction des données de table_two .
Ma requête ressemble actuellement à ceci: table_one.x n'est pas mise à jour lorsque l'une des colonnes Dans l'une ou l'autre table est null . c'est-à-dire que cela ne se met à jour que lorsque toutes les colonnes ont des données. distinct sur . Ce que je veux maintenant, c'est de remplir le jeu de données volumineux avec une valeur de la table qui a des champs uniques. Expliquer fonction expliquer l'option analyser a pris aussi pour toujours pour que je l'a annulé.
5 Réponses :
Vous pouvez utiliser une fonction de contrôle nulle comme la NVL d'Oracle. Pour Postgres, vous devrez utiliser coalesce .
c'est à dire Votre requête peut ressembler à:
coalesce (x, y, 1) = coalesce (y, x, 1) Cette condition est toujours true si x ou y est null
Etant donné que null = null code> évalue vers false code> Vous devez vérifier si deux champs sont tous les deux null code> en plus de la vérification de l'égalité: and table_one.id between x and y
J'ai un défi avec le code sans coalesce. Pour une table avec 20 000 lignes, la déclaration de mise à jour prend environ 20 secondes. Mais pour une table avec 9 millions de lignes, elle a maintenant été 20h et la déclaration de mise à jour n'est pas encore terminée. Des pointeurs? J'ai mis à jour la question avec la sortie de expliquer
Je veux commencer une prime sur cette question. C'est la première fois que je ne le fais pas, je ne suis donc pas très sûr quelles implications il y a pour avoir une réponse acceptée sur une question avec une prime. Je vais donc défendre la réponse acceptée pour l'instant et soulevez la prime, puis voyez comment elle se débrouille. Quoi qu'il en soit, voyons si nous pouvons faire la requête plus rapide que ce que tout ce qui signifie qu'il y ait là-bas.
+1 La solution de coalesce est la voie à suivre, vous obtiendrez un plan de jointure de fusion avec elle. Le "T1.c1 = T2.c1 ou T1.c1 est NULL et T2.c1 est NULL" entraînera des jointures de boucle imbriquées. C'est une chose que vous voulez éviter.
Essayez ci-dessous, similaire à ce qui précède @binoternary. Vient de me battre à la réponse.
Votre requête actuelle rejoint deux tables à l'aide d'une boucle imbriquée code>, ce qui signifie que le serveur traite les lignes
UPDATE table_one SET table_one.x = table_two.y FROM table_two WHERE table_one.id >= <some_starting_value> AND table_one.id < <some_ending_value> AND COALESCE(table_one.invoice_number, '') = table_two.invoice_number AND COALESCE(table_one.submitted_by, '') = table_two.submitted_by AND COALESCE(table_one.passport_number, '') = table_two.passport_number AND COALESCE(table_one.driving_license_number, '') = table_two.driving_license_number AND COALESCE(table_one.national_id_number, '') = table_two.national_id_number AND COALESCE(table_one.tax_pin_identification_number, '') = table_two.tax_pin_identification_number AND COALESCE(table_one.vat_number, '') = table_two.vat_number AND COALESCE(table_one.ggcg_number, '') = table_two.ggcg_number AND COALESCE(table_one.national_association_number, '') = table_two.national_association_number
Vous êtes une solution est très similaire à celui d'un autre que j'ai été mentionné. Laissez-moi vous expliquer et vous me dites si cela pourrait être une amélioration de ce que vous avez suggéré. L'idée est de créer une nouvelle colonne sur les deux tables, appelez-le id_merge . Fusionne ensuite toutes les colonnes "distinctes" dans une colonne à l'aide de coalesce de sorte que vous remplacez les nuls. Après cela, vous indexez ces deux nouvelles colonnes, puis exécutez une déclaration de mise à jour standard. Voir ce violon . Je l'ai couru contre 9 millions de lignes et j'ai achevé environ 30 minutes. Cependant, j'ai une table avec 65 millions de rangées! Peut-il être amélioré? Merci
L'index sur table_two devrait inclure colonne_y . Ce devrait être la dernière colonne de l'index. Index sur table_two devrait être unique. Si vous NE PAS Créer un index sur table_one , sa mise à jour peut être plus rapide.
Donc tu veux dire quelque chose comme ça? créer index table_two_idx_01 sur table_two (id_merge_y, colonne_y); ? Pouvez-vous expliquer pourquoi? Je pensais que le Select sera basé sur la colonne id_merge_y ? En outre, pourquoi pas index table_one ? Ma compréhension est que depuis table_one.id_merge_x sera comparé à table_two.id_merge_y , puis un index sur ces deux colonnes aiderait à accélérer les choses?
Dans SQL Server, je m'attendrais à ce qu'il fasse une différence. À Postgres - peut-être pas. Essayez à la fois des variantes et comparez les plans d'exécution. Le serveur doit lire chaque ligne dans TABLE_OON . Pour chaque ligne dans TABLE_One Server doit trouver une ligne dans table_two aussi vite que possible. Ici l'indice aide. Si colonne_y fait partie de l'index, le serveur peut lire uniquement l'index; Sinon, cela doit faire une recherche supplémentaire. Avoir l'index unique donne un indice supplémentaire utile à optimiser.
Vous pouvez utiliser la fonction de Coalesce qui retournera true chaque fois que toute variable passée est null. La fonction de contrôle null vous aidera.
La déclaration est invalide. Vous ne pouvez pas faire référence à une deuxième table sans utiliser de
à partir de dans l'instruction de la mise à jour .De plus, si vous faites une joindre intérieur les lignes où la colonne donnée est NULL serait ignorée.
table_one.x n'est pas mis à jour lorsque l'une des colonnes de l'une ou l'autre table estnull Que attendez-vous exactement quand il y a exactement quand il y a des nulls impliqués? Que la mise à jour arrive lorsque l'une ou l'autre colonne estnull ou uniquement lorsque les deux sont?Je m'attends à ce que
table_one.x soit mis à jour lorsque les neuf colonnes des deux tables sont une correspondance exacte. Le problème se pose lorsque deux colonnes sont identiques, mais elles ne sont pas identiques sur la base de données, mais plutôt sur une valeurnull . C'est comme SQL ne reconnaît pas lenullJe ne suis pas sûr que la bounty va mais je suppose que ça finit avec binoternaire depuis sa première partie. En ce qui concerne la vitesse, j'ai fini par utiliser un processus totalement différent pour que les choses puissent bouger plus rapidement, comme le montre mon commentaire à la réponse de @vladimir. Y a-t-il un moyen de partager Bounty 50/50 entre réponses?