Résultats non ordonnés dans SQL

Je l'ai lu encore et encore que SQL, en son cœur, est un model non ordonné. Cela signifie que l'exécution de la même requête SQL plusieurs fois peut renvoyer le jeu de résultats dans un ordre différent, sauf si une clause "order by" est incluse. Quelqu'un peut-il expliquer pourquoi une requête SQL peut-elle renvoyer le jeu de résultats dans un ordre différent dans différentes instances d'exécution de la requête? Ce n'est peut-être pas toujours le cas, mais c'est certainement possible.

Algorithmiquement parlant, le plan de requête ne joue-t-il aucun rôle dans la détermination de l'ordre des résultats lorsqu'il n'y a pas de clause "order by"? Je veux dire quand il y a un plan de requête pour une requête, comment l'algorithm ne returnne-t-il pas toujours datatables dans le même ordre?

Note: Je ne remets pas en question l'utilisation de l'ordre par, je request pourquoi il n'y a pas de garantie, comme dans, j'essaie de comprendre les défis pour lesquels il n'y a aucune garantie.

Certains exemples SQL Server où le même plan d'exécution peut renvoyer des résultats ordonnés différemment sont

Une parsing d'index non ordonnée peut être effectuée dans l' ordre de répartition ou dans l'ordre des keys en fonction du niveau d'isolement en vigueur.
La fonction de balayage joyeux permet aux parsings d'être partagées entre des requêtes simultanées.
Les plans parallèles sont souvent non déterministes et l'ordre des résultats peut dépendre du degré de parallélisme sélectionné lors de l'exécution et de la charge de travail simultanée sur le server.
Si le plan comporte des loops nestedes avec une préextraction non ordonnée, cela permet au côté interne de la jointure de continuer en utilisant datatables de toutes les E / S qui se sont déroulées en premier

Martin Smith a quelques bons exemples, mais le moyen le plus simple de démontrer quand SQL Server va changer le plan utilisé (et donc l'ordre qu'une requête sans ORDER BY sera utilisée, basé sur le plan différent) est d'append un index de couverture . Prenez cet exemple simple:

CREATE TABLE dbo.floob ( blat INT PRIMARY KEY, x VARCHAR(32) ); INSERT dbo.floob VALUES(1,'zzz'),(2,'aaa'),(3,'mmm');

Cela va order par le PK en cluster:

 SELECT x FROM dbo.floob;

Résultats:

 x ---- zzz aaa mmm

Maintenant, ajoutons un index qui couvre la requête ci-dessus.

 CREATE INDEX x ON dbo.floob(x);

L'index provoque une recompilation de la requête ci-dessus lorsque nous l'exécutons à nouveau; maintenant il command par le nouvel index, car cet index fournit un moyen plus efficace pour SQL Server de returnner les résultats pour satisfaire la requête:

 SELECT x FROM dbo.floob;

Résultats:

 x ---- aaa mmm zzz

Démo SQL Fiddle

Jetez un coup d'oeil aux plans – aucun opérateur de sorting ne dispose d'une input de command indépendante de l'ordre inhérent à l'index, et il scanne tout l'index car il doit le faire (et le less cher pour SQL Serveur pour parsingr l'index est dans l'ordre). (Bien sûr, même dans ces cas simples, certains des facteurs de la réponse de Martin pourraient influencer un ordre différent, mais cela est vrai en l'absence de l'un de ces facteurs.)

Comme d'autres l'ont indiqué, la seule façon de se fier à la command est de spécifier une command par . S'il vous plaît écrivez cela quelque part. Peu importe le nombre de scénarios où cette croyance peut se briser; le fait qu'il y en ait même un soit inutile pour essayer de find des lignes direcsortingces quand vous pouvez être paresseux et ne pas utiliser une clause ORDER BY. Utilisez-le toujours, ou préparez-vous à ce que datatables ne reviennent pas toujours dans le même ordre.

Quelques pensées liées à ceci:

Citation de Wikipedia:

"Comme SQL est un langage de programmation déclaratif, les requêtes SELECT spécifient un set de résultats, mais ne spécifient pas comment le calculer.La database traduit la requête en un" plan de requête " qui peut varier entre les exécutions , les bases de données et les logiciels de database. est appelé "optimiseur de requête" car il est responsable de find le meilleur plan d'exécution possible pour la requête, dans les limites des contraintes applicables. "

Tout dépend de ce que l'optimiseur de requête choisit comme parsing de plan, parsing d'index, search d'index, etc.

D'autres facteurs pouvant influencer le choix d'un plan sont les statistics de table / index et le reniflage de parameters pour n'en citer que quelques-uns.

Bref, la command n'est jamais garantie sans clause ORDER BY.

C'est simple: si vous avez besoin des données commandées, utilisez un ORDER BY. C'est pas difficile!

Il ne peut pas vous causer un problème aujourd'hui ou la semaine prochaine ou même le mois prochain mais un jour il le fera.

J'ai été sur un projet où nous avions besoin de réécrire des dizaines (ou peut-être des centaines) de requêtes après une mise à niveau vers Oracle 10g a provoqué l'évaluation de GROUP BY d'une manière différente que sur Oracle 9i, ce qui signifie que les requêtes n'étaient pas nécessairement ordonné par les colonnes groupées plus. Pas amusant et simple à éviter.

Rappelez-vous que SQL est un langage déclaratif, donc vous dites au SGBD ce que vous voulez et le SGBD est en train de déterminer comment l'get. Il ramènera les mêmes résultats à chaque fois mais pourra évaluer de manière différente à chaque fois: il n'y a aucune garantie.

Juste un exemple simple d'où cela pourrait vous causer des problèmes est que de nouvelles lignes apparaissent à la fin de la table si vous select dans la table …. jusqu'à ce qu'ils ne le font pas parce que vous avez supprimé certaines lignes et le SGBD décide de remplir dans l'espace vide.

Il y a un nombre inconnu de façons dont il peut aller mal, sauf si vous utilisez ORDER BY.

Pourquoi l'eau bout-elle à 100 degrés C? Parce que c'est comme ça que c'est défini.

Pourquoi n'y a-t-il aucune garantie concernant la command de résultats sans ORDER BY? Parce que c'est comme ça que c'est défini.

Le SGBD utilisera probablement le même plan de requête la prochaine fois et ce plan de requête returnnera probablement datatables dans le même ordre: mais ce n'est pas une garantie, même pas proche d'une garantie.

Si vous ne spécifiez pas ORDER BY, la command dépendra du plan utilisé, par exemple si la requête a effectué une parsing de table et n'a utilisé aucun index, le résultat sera l'ordre naturel ou l'ordre du PK. Cependant, si le plan décide d'utiliser IndexA basé sur columnA, la command sera dans cet ordre. Avoir du sens?