Clustered Index et non clustered index

Salut les experts SQL Server,

Je ne sais plus si j'ai posé la même question ici mais plus je lis à droite à gauche de choses sur SQL Server et moins c'est clair pour moi.

Quand je crée une table dite Clustered Table, elle a un Clustered Index, on est d'accord? Si oui, on a bien créé deux objets sur le disque dur : une table + un index et les données de la colonne indexée sont dupliquées, OK? A la différence de Oracle, les données dans la table sont triées : on insère pas les dans n'importe quel bloc : OK?

Dans le cas d'une table avec un index non clustered, on a bien créé deux objets sur le disque dur : une table + un index et les données de la colonne indexée sont dupliquées, OK? Et là, comme pour Oracle, les données dans la table ne sont pas triées : on insère dans n'importe quel bloc : OK?

Si j'ai bien compris : dans les deux cas on a bien deux objets de créés, une table et un index. Si c'est cela, j'avoue ne pas comprendre l'intérêt de la Clustered Table car pourquoi vouloir une table avec des données triées? Est-ce pour avoir un excellent Clustering Factor (terme Oracle)?

J'en suis au point où je pensais que la clustered table SQL Server était l'équivalent de la table IOT d'Oracle mais peut-être que je me trompe...

Merci pour vos réponses :D

Citation:

---

Envoyé par Ikebukuro

Salut les experts SQL Server,

Je ne sais plus si j'ai posé la même question ici mais plus je lis à droite à gauche de choses sur SQL Server et moins c'est clair pour moi.

Quand je crée une table dite Clustered Table, elle a un Clustered Index, on est d'accord? Si oui, on a bien créé deux objets sur le disque dur : une table + un index et les données de la colonne indexée sont dupliquées, OK?

---

non. Un seul objet, pas de duplication, c'est un des intérêts de l'index CLUSTERED (littéralement EMPLACÉ (l'index étant emplacédans la table))

Citation:

---

Envoyé par Ikebukuro

A la différence de Oracle, les données dans la table sont triées : on insère pas les dans n'importe quel bloc : OK?

---

Non, enfin pas tout à fait.... par défaut Oui, mais Oracle permet de créer la même chose avec un nom différent "Index Organized table"...

Citation:

---

Envoyé par Ikebukuro

Dans le cas d'une table avec un index non clustered, on a bien créé deux objets sur le disque dur : une table + un index et les données de la colonne indexée sont dupliquées, OK? Et là, comme pour Oracle, les données dans la table ne sont pas triées : on insère dans n'importe quel bloc : OK?

---

oui

Citation:

---

Envoyé par Ikebukuro

....J'en suis au point où je pensais que la clustered table SQL Server était l'équivalent de la table IOT d'Oracle mais peut-être que je me trompe...

---

Ben si !!!!

La grande différence c'est que le moteur de SQL Server est optimisé pour les tables clustered tandis que celui d'oracle est optimisé pour les tables en HEAP.... Et la différence est significative en terme de qualité du plan de requête et donc performance, car il ne passe quasiment jamais par la table en mode SCAN !!!!

Autre information importante, dans la littérature consacré au SGBDR on parle de pages, pas de blocs... La encore oracle se distingue pour embrouiller les pistes !

A +

Ce n'est pas si mauvais que ça les IOT chez Oracle, mais c'est moins dans la culture des développeurs.
Il faut garantir un accès par la PK pour éviter de devoir construire des index additionnels, mais ça marche.
De mémoire la gestion des index additionnels est mieux réalisée chez MS, mais j'ai pas trop bricolé depuis la 11gR2 donc ça a peut-être évolué.

La différence important entre une table HEAP et IOT c'est qu'une HEAP peut être lue et chargée plus rapidement qu'une IOT (pas d'insert append, le fast index full scan est moins performant que le fast table full scan), et y'a sûrement aussi une meilleure compression pour les tables HEAP.

Dans un environnement OLTP, SQL-Server s'en sort mieux qu'Oracle DB car on ne fait que des accès avec quelques lignes sur les identifiants des clustered index, et comme sur SQL-Server dès qu'on ajoute une PK la table par défaut est en clustered index, ça marche très bien :)

Sur un environnement OLAP, Oracle DB s'en sort mieux que MS SQL-Server grâce aux fast table full scan (et si on a un Exadata les données sont en plus filtrées à la lecture) et aux hash joins plus aboutis, surtout quand on a beaucoup de colonnes en jointure. Mais on peut toujours coller des IOT sur des tables de référence.

Je vous remercie pour vos réponses :)

Citation:

---

Envoyé par Waldar

...
Sur un environnement OLAP, Oracle DB s'en sort mieux que MS SQL-Server grâce aux fast table full scan (et si on a un Exadata les données sont en plus filtrées à la lecture) et aux hash joins plus aboutis, surtout quand on a beaucoup de colonnes en jointure. Mais on peut toujours coller des IOT sur des tables de référence.

---

Tu devrais préciser pour Oracle de l'OLAP sur une base relationnelle et non sur un cube, car là SQL Server s'en sort mieux !

Mais depuis les index columstore en OLTP (ce que oracle ne fait pas) et avec le mode "batch" des opérations, je pense qu'aujourd'hui Oracle est largué !

A +

Sur Oracle il n'y a jamais eu vraiment besoin de cube (ça existe mais c'est compliqué à mettre en œuvre et le gain de temps pas évident).
Le problème des cubes chez MS c'est qu'ils sont rapides à requêter mais quand même très long à rafraîchir.

Je n'ai pas regardé les index columnstore donc je ne peux pas trop en causer.
En googlant rapidement je suis tombé sur cette tentative de comparaison (qui au final dit qu'on ne peut pas trop comparer) :
https://www.brentozar.com/archive/20...server-oracle/

Faut voir, car le format colonne présente sont lot de challenges aussi.
Super efficaces en lecture, en stockage et en RAM, mais plus compliquées à rafraîchir, et coût CPU important si on doit reconstruire la ligne pour faire une jointure.

Pour la petite histoire sur teradata on fait du stockage lignes, colonnes ou hybride aussi mais en général le surcoût CPU n'est que peu profitable - sachant qu'on n'est pas du tout une base colonne au départ, on adapte le produit en conservant ce qui existe déjà.
On continue d'améliorer ce format mais l'effet waouh qui peut exister sur une requête type ne se retrouve pas forcément sur une production avec des besoins beaucoup plus divers.
Limite on est meilleur à lire des données stockées en colonnes sur Azure BLOB et à les remettre en lignes à la demande, mais c'est une autre histoire ;)

Citation:

---

Envoyé par Waldar

Sur un environnement OLAP, Oracle DB s'en sort mieux que MS SQL-Server grâce aux fast table full scan (et si on a un Exadata les données sont en plus filtrées à la lecture) et aux hash joins plus aboutis, surtout quand on a beaucoup de colonnes en jointure. Mais on peut toujours coller des IOT sur des tables de référence.

---

Loin de mon intention de comparer Oracle et SQL Server ici mais je pense qu'il faudrait reconsidérer la question avec l'apparition des columnstore sous SQL Server et le mode batch comme stipulé par SQLPro. C'est devenu une fonctionnalité vraiment abouti depuis SQL Server 2016 et cela continue à s'améliorer au fur et à mesure des versions.

Pour en avoir mis quelques uns sur des DW ca a été le jour et la nuit au niveau performance.

Citation:

---

Envoyé par Waldar

Faut voir, car le format colonne présente sont lot de challenges aussi.
Super efficaces en lecture, en stockage et en RAM, mais plus compliquées à rafraîchir, et coût CPU important si on doit reconstruire la ligne pour faire une jointure.

---

Sur des environnements OLTP avec du "real-time operational analytics" (pour éviter justement le problème de rafraichissement des cubes), l'implémentation devient en effet un peu plus tricky même s'il est vrai qu'on peut limiter l'overhead lié à la construction des groupes de lignes / segments compressés avec des index columtores filtrés ou en retardant la compression des lignes. Le problème à mon avis n'est pas tellement ici et le plus gros challenge qu'on a dû faire face est l'influence des columnstore sur certaines opérations OLTP classiques qui ont été ralenti même si dans l'ensemble on a quand même drastiquement diminuer la consommation CPU de certaines requêtes de Reporting (vive la compression + mode batch). Pas encore eu le temps d'investiguer :)

++

Citation:

---

Envoyé par mikedavem

Loin de mon intention de comparer Oracle et SQL Server ici mais je pense qu'il faudrait reconsidérer la question avec l'apparition des columnstore sous SQL Server et le mode batch comme stipulé par SQLPro.

---

Y'a pas de mal à comparer ;)
J'ai plutôt fait du Oracle DB dans ma carrière car c'est ce que mes clients avaient en majorité quand je faisais du consulting en SSII ou à mon compte, mais quand j'ai du faire du SQL-Server j'ai bien aimé aussi (après un petit temps d'adaptation).
Et au final je me retrouve chez encore un autre éditeur de SGBD :aie:

Merci pour le retour d'expérience en tout cas, toujours enrichissant !

Citation:

---

Envoyé par mikedavem

... le plus gros challenge qu'on a dû faire face est l'influence des columnstore sur certaines opérations OLTP classiques qui ont été ralenti même si dans l'ensemble on a quand même drastiquement diminuer la consommation CPU de certaines requêtes de Reporting (vive la compression + mode batch)....

---

je confirme ! Dès que tu met du CLUSTERED COLUMNSTORE tu as intérêt à réviser toutes les autres requêtes.... Et souvent il y a des surprises !

A +

Ca pourrait être intéressant d'avoir la table de fait en clustered index et une vue indexée en columnstore ?
Comme ça les opérations classiques notamment d'alimentation ou OLTP se font sur la table et les opérations d'agrégats sur la vue, et on laisse SQL-Server maintenir tout ça comme un grand ?

Pas trop d'intérêt à mon sens, et je sais même pas si c'est faisable.....
Un index columnstore pouvant avoir jusqu'à 1024 colonnes !

A +

Citation:

---

Ca pourrait être intéressant d'avoir la table de fait en clustered index et une vue indexée en columnstore ?
Comme ça les opérations classiques notamment d'alimentation ou OLTP se font sur la table et les opérations d'agrégats sur la vue, et on laisse SQL-Server maintenir tout ça comme un grand ?

---

Si on parle bien de tables de faits, alors on parle probablement de chargement en masse. Dans ce cas on peut directement insérer les données dans la structure compressée columnstore.

Si on fait du OLTP tant qu'on a pas atteint un certain nombre de lignes on insère de toute facon dans le delta store, ce qui revient presque à insérer dans une structure B-Tree classique. Le problème serait surtout les UPDATE dans les lignes déjà compressés en columnstore. C'est quelque chose qui est connu et qui devient bcp moins efficace. C'est pour cela que je parle de retarder la compression dans le columnstore si on est en OLTP afin de limiter cet effet.

A mon avis vaut mieux avoir un columnstore qu'une vue indexée pour le coup. Le columnstore est vraiment efficace lorsqu'il s'agit de retrouver d'aggrége les données et ceci pour les raisons cités par SQLPro (Compression et Batch mode).
Il existe même la possibilité de faire d'élimination de segments directement depuis le stokcage pour récupérer uniquement les lignes concernées

++

Citation:

---

Envoyé par mikedavem

...
Il existe même la possibilité de faire d'élimination de segments directement depuis le stokcage pour récupérer uniquement les lignes concernées...

---

Ce qui se rapproche de ce que fait Oracle avec EXADATA....

A +

Hello amis DBAs,

Une nouvelle question : avec SQL Server il est possible de créer une table sans PK ni colonne UNIQUE (tout comme Oracle), donc SQL Server crée une table et pas d'index.
Maintenant j'insère des tonnes de données, mes SELECTs sont lents et c'est là que je me rends compte de mon erreur. Si j'ajoute un index clustered sur une colonne, Oracle va me créer un index en plus de la table, on est d'accord, il ne va pas créer un Clusterde Index et supprimer la table?


Dans l'exemple ci-dessous repris d'un livre

Code:

---

1 2 3 4 5 6 7 8 9 10 11

CREATE TABLE dbo.CIDemo ( ID INT IDENTITY, DummyText VARCHAR(30) ) ; -- Dans la base j'ai une table, pas d'index : OK?   GO --Create clustered index CREATE UNIQUE CLUSTERED INDEX CI_CIDemo ON dbo.CIDemo([ID]) ; -- Dans la base j'ai une table et un index et cet index doublonne les données de la colonne ID : OK?

---

Citation:

---

Envoyé par Ikebukuro

HSi j'ajoute un index clustered sur une colonne, Oracle va me créer un index en plus de la table, on est d'accord, il ne va pas créer un Clusterde Index et supprimer la table?

---

Je suppose qu'on parle de SQL Server (et non d'Oracle ) ?

Alors non, SQL Server va créer un index cluster, qui viendra remplacer la table HEAP.

Oui, on parle bien de SQL Server.
Si je te comprends, la table HEAP sera supprimée et toutes les données de la table iront dans le Clustered Index, qui fait office de nouvelle table.
OK, je ne m'y attendais pas... je trouve cela violent (dropper une table peut être très long, créer un index idem) mais au moins le résultat est plus simple à comprendre.

La table n'est pas droppée.... Elle est juste "défragmentée" par un tri des lignes et le rajout de la structure de navigation de l'index BTree.

A +

OK, la Heap Table est transformée en Clustered Table. Ceci étant dit, ce doit quand même être une opération assez lourde...

OUI !!!

A +

La lourdeur de l'opération dépendra bien entendu du volume de la table en question mais :

1/ l'opération peut être faite en ligne (non bloquante), sans parallélisme, donc avec un impact modéré sur les autres requêtes en cours.
2/ cette opération est censée être exceptionnelle, et a priori pas en prod, ou dans le cadre d'une MEP/maintenance pour corriger une erreur de conception... en effet, les tables HEAP n'ont pas vocation à être des tables "vivantes", elles supportent mal les mises à jours (lignes déportées,...). Leur cadre d'utilisation est plus proche des tables temporaires (opération d'imports, ...).

Donc dans les faits... rien de gênant a priori