Reprise du message précédent :

Pas vraiment http://www.postgresql.org/docs/cur [...] RT-EXAMPLE

C'est le seul reproche que j'ai faire a postgresql, mais ça m'ajoute quand même pas mal de merdouilles ( surtout que je joue avec deux classes de champs: de vrais champs sql, et un hstore )

Et pendant ce temps là http://www.lemagit.fr/economie/bus [...] l-cluster/
 
Maintenant on peut avoir des clé étrangère dans un cluster mysql \o/

dites je lutte comme une merde pour un truc qui m'a l'air ultra simple.
 
supposons une table:
 
id       nom         quantité
1        toto         5
2        tutu         3
3        toto         10
 
 
je veux obtenir pour chaque nom, la quantité totale, c'est à dire le champ quantité multiplié par le nombre d'occurences, donc:
 
toto  15
tutu  3
 
j'y arrive pas

select nom, sum(quantite)
from matable
group by nom

putain mais oui c'est évident.
 
en fait au départ yavait un count, et j'ai bloqué à vouloir le garder
 
merci

Y a des conventions de nommage SQL ?
Notamment la casse des tables et colonnes, lowercase séparé par des underscores on est d'accord ?

Pas vraiment de conventions, mais globalement tu vas souvent trouver ce que tu proposes, oui.
Cela dit, les "vieux" vont fréquemment préférer les majuscules, mais comme de toute manière c'est insensible à la casse...

tiens en parlant de conventions, vous avez pas un beau pdf genre A3 ou A2, qui récapitule les conventions de hommage "classiques" en dev, pour afficher au mur du bureau?

Le "SELECT 1" d'Oracle qui renvoie toujours 1, peut importe si les conditions sont remplies ou pas

Plait-il? SELECT 1 FROM dual WHERE 1=2 ne me renvoie rien.

Développe? Je serais curieux de voir ça.

SELECT 1 FROM foo.bar WHERE cond1 = 124 AND cond2 = 'rrrrr'
 
Bon après c'est peut-être le driver PHP qui est pourrit, faudrait que j'essaye dans SQL Dev.

Même en php j'ai jamais eu le problème. I call pebkac.

Effectivement, j'arrive plus à reproduire le cas

Salut
 
Une p'tite question Oracle vite fait, j'en fait pas un topic parce que la reponse est pas vitale, meme si ca peut me servir, c'est plutot par curiosite.
 
J'ai une requete assez simple pour laquelle Oracle prend "systematiquement" un mauvais plan.
 
J'ai deux tables dans un environnement de dev, en gros:
table1(id, champ1) -> environ 275 millions de lignes (partitionee, toussa, la n'est pas la question)
table2(id, champ2, champ3, id_table1) -> entre 500 et 1000 lignes
 
En dehors des cles primaires, ya un index sur table1(champ1), un autre sur table2(champ2, champ3), et plusieurs indexs composes sur table2 comprenant id_table1 (mais les plans montrent qu'ils ne sont jamais utilises de toute facon).
 
Ma requete c'est:

 
Oracle privilegie systematiquement de passer par l'index sur table1(champ1) et de faire la jointure en partant de table1, alors que c'est toujours plus long.
Cas 1) Pour reference, si je fais un

Ca retourne en quelques secondes, environ 250 enregistrements au total.
 
Cas 2) Si je fais un  

avec t1.champ1='BLA' qui retourne seulement quelques enregistrements de table1, ca revient deja en plusieurs fois le temps que le cas 1 (avec evidemment moins de 250 resultats - parfois aucun mais ca prend quand meme plusieurs fois le temps du cas 1 pour me dire qu'il ne trouve rien)
 
Cas 3) Si je fais un  

avec t1.champ1='BLU' qui retourne quelques milliers d'enregistrements de table1, j'annule la requete apres plusieurs minutes a mouliner sans retour.
 
Cas 4) Si je fais

Oracle decide quand meme de passer par un autre index compose qui comprend champ1! Meme resultat, je tue la requete apres quelques minutes de pedalage dans la semoule.
 
Cas 5) Et finalement si je fais

La y'a plus d'index avec champ1 dispo, Oracle se decide finalement a faire le join depuis l'autre cote et ca retourne sensiblement dans le meme temps que le cas 1 (avec ou sans resultats).
 
Au final je me retrouve a mettre des hints dans une vue - bon c'est pas la mort, mais je trouve pas ca terrible. Par exemple si quelqu'un declare un nouvel index compose avec champ1, ou change les noms des indexs, ca ne marchera plus.
 
Du coup mes questions:
1 - Comment ca se fait qu'Oracle ne puisse pas "voir" par lui-meme que ca va etre plus rapide d'attaquer du cote de table2? J'ai rafraichi les indexs sur les tables mais ca n'a rien change. Ya rien d'autre a faire que les hints?
2 - Est-il possible de declarer des hints de type NO_INDEX sur un champ (plutot qu'un index)? En gros, de me debarasser des noms d'index pour etre plus souple en cas d'evolution. ORDERED/LEADING ne font rien dans ce cas, puisqu'ils jouent sur l'ordre des jointures, et pas sur l'"angle d'attaque" des jointures.
 
Voila stout! Vous inquietez pas, je pleurerai pas si personne n'a d'idees

 
faut faire gaffe aux colonnes qui ont un nom de fonction j'me suis pris la tête un moment sur une colonne order

"date" aussi
faut expliciter "date_creation",

'jour    
 
Un module (j'ai pas trouvé de bonne analogie) peut envoyer ou recevoir des messages.
Un module a également une liste de sous modules, dans la table un sous module hérite de module par STI.
 
Un message référence un sender_id et un receiver_id.
 
Pour récupérer les messages d'un module, je SELECT message WHERE message.sender_id = module_id OR message.receiver_id = module_id (à la louche).
 
Je veux récupérer tous les messages du périmètre d'un module, c'est-à-dire tous les messages d'un module ou de l'un de ses sous-module. La cible c'est du SQL Server, mais je cherche plus la logique que la syntaxe exacte.
 
Merci

il peut y avoir des sous-modules de sous-modules?

 
A tout hasard, tu as tenté un USE_MERGE ou un USE_HASH ? Meme si je suppose que dans ton cas, la Nested Loops serait le graal.
Sinon, en astuce de voyou, as tu tenté de mettre dans la jointure des conditions supplémentaires mais toujours vraies sur id_table1, du genre t2.id_table1 not in ( 0 )       ?  

 
Non, il n'y a qu'un niveau (un sous module n'a pas de fils), j'aurais du préciser.
 

dans ce cas là ça doit pouvoir marcher assez facilement avec une simple autojointure, non?

Ha mais j'en doute pas
C'est juste que des années d'ORM ont ravagé mes maigres compétences SQL, si un module n'avait qu'un seul fils je m'en sortirais mais là je bloque.
 
Y'a un truc du genre "WHERE message_id IN module.childs" ?

SELECT msg.*
FROM message msg
     JOIN module pere ON (pere.id = msq.id_emetteur OR pere.id = msg.id_destinataire)
     LEFT OUTER JOIN module fils ON fils.id_pere = pere.id AND (fils.id = msq.id_emetteur OR fils.id = msg.id_destinataire)

 
Ce genre de truc?

De ce que j'en comprend ça pourrait coller.

J'ai ajouté une clause au premier JOIN pour setter l'ID du module pour lequel on fait la recherche :

SELECT msg.*
    FROM message msg
        JOIN module pere ON (pere.id = 42 AND (pere.id = msq.id_emetteur OR pere.id = msg.id_destinataire))
        LEFT OUTER JOIN module fils ON fils.id_pere = pere.id AND (fils.id = msq.id_emetteur OR fils.id = msg.id_destinataire)

Les résultats retournés ne correspondent pas à la réalité des données, mais je continue sur cette base.

Edit : pris séparément, chacun des deux JOIN donne les bons résultats, mais la requête globale me renvoi l'intersection des deux ensemble plutôt que l'addition.

 
 
Déjà peut-être faire un p'tit calcul de stats histoire que le CBO soit à jour. (Bah oui j'ai bases oltp avec les mêmes stats depuis 4 ans)
 
Après il faudrait analyser les plans d'exécution pour savoir comment la base réagit (chaque base est différente).
 
(ci-dessous je déduis)
Cas 1:
Ici le CBO a du commencer par la table 2 avec du nested loop sur l'index de la colonne de jointure de la table 1
 
Cas 2:
L'index de la table 1 doit être monté en mémoire et là que la requête passe du temps.  
Le CBO se dit que la clause where de la table 1 est discriminant et décide d'utiliser un indexe où est indexé (^^) champs1.
Je suppose que que l'indexe utilisé est surement celui qui correspond à la clé primaire et avec 250 millions de ligne dans la table a surement un clustering factor proche de 250M donc l'indexe doit être relativement gros.
 
Cas 3:  
Là c'est difficile à dire, soit on est dans le Cas 2 ou très proche, ou soit c'est selon les stats surtout si y'a des histos, le CBO a surement voulu faire un full scan....
 
Cas 4 et 5:
Tu as la réponse ^^  
 
 
Comme le disait poulpeleach, un use_nl serait le top. (là le CBO partirait prioritairement sur la petite volumétrie).  
Comme tu l'as énoncé (et même donné la réponse), la table 2 à seulement quelques milliers de lignes face à la table 1 qui en a des millions et fondamentalement en nested loop, on commencerait par la table 2. (sous réserve d'avoir l'index kivabien sur la table 1)
 
Mais je commencerai peut-être à regarder d'abords du côté des calculs de statistiques ^^
 
My 2 cents

Euh, Oracle recalcule pas les stats des index lors de leur rebuild? (que lasnoufle a bien fait)

SELECT msg.*
    FROM message msg
        JOIN module pere ON (pere.id = 42 AND (pere.id = msq.id_emetteur OR pere.id = msg.id_destinataire))
        LEFT OUTER JOIN module fils ON fils.id_pere = pere.id AND (fils.id = msq.id_emetteur OR fils.id = msg.id_destinataire)

 
Toujours bloqué, à vot' bon coeur
Je retombe toujours un peu sur la même solution, le problème étant bien évidemment que cette requête me donne l'intersection des ensembles définis par mes JOIN, alors que je cherche l'équivalent de l'addition des ensembles, et si possible en une seule requête (j'imagine que sinon, UNION fait l'affaire).
 

SELECT msg.*
       FROM message msg
           INNER JOIN module parent ON (parent.id = msq.id_emetteur OR parent.id = msg.id_destinataire)
           INNER JOIN module child ON child.parent_id = parent.id AND (child.id = msq.sender_id OR child.id = msg.receiver_id)
       WHERE parent.id = 42;

SELECT msg.*
       FROM message msg
           INNER JOIN module parent ON (parent.id = msq.id_emetteur OR parent.id = msg.id_destinataire)
           INNER JOIN module child ON child.parent_id = parent.id AND (child.id = msq.sender_id OR child.id = msg.receiver_id)
       WHERE parent.id = 42;

 
 
Et un truc du genre :  
 

SELECT msg.*
FROM message msg
WHERE msg.id_emetteur=42
OR  msg.id_destinataire=42
OR  msg.id_destinataire IN (
 SELECT fils.id 
 FROM module fils  
 WHERE fils.id_pere=42
)
OR  msg.id_emetteur IN (
 SELECT fils.id 
 FROM module fils  
 WHERE fils.id_pere=42
)

 
(en version plus "large" :  
 

)

 
Ça doit le faire à première vue, mais idéalement je lorgne vers la solution élégante sans une bardée de SELECT imbriqués. Disons que comme ça, je vois pas pourquoi ce cas simple ne pourrait pas se résoudre avec une ou deux jointures, et j'en profiterais pour reprendre un peu mes bases de SQL.

SELECT msg.*
       FROM message msg
           INNER JOIN module parent ON (parent.id = msq.id_emetteur OR parent.id = msg.id_destinataire)
           INNER JOIN module child ON child.parent_id = parent.id AND (child.id = msq.sender_id OR child.id = msg.receiver_id)
       WHERE parent.id = 42;

En fait il y a même pas besoin d'autojointure, c'est un jointure simple :

SELECT msg.*
    FROM message msg
        INNER JOIN module mod  ON (mod.id = msq.id_emetteur OR mod.id = msg.id_destinataire) AND (mod.id = 42 OR mod.parent_id = 42)

 
Nan pas de recalcul de stats lors du rebuild.
 
Mais dans le cas de Lasnoufle, il faut vraiment voir les plans d'exécution dans chacun des cas.  
Car en y repensant il y a des paramétrages qui influencent beaucoup les plans notamment dans son cas optimizer_index_caching et optimizer_index_cost_adj.
Selon le paramétrage de ces 2 valeurs (dynamiques), l'optimizer privilégiera du full scan ou de l'index scan.

SELECT msg.*
    FROM message msg
        INNER JOIN module mod  ON (mod.id = msq.id_emetteur OR mod.id = msg.id_destinataire) AND (mod.id = 42 OR mod.parent_id = 42)

 
 
Ça à l'air de le faire, il faut que je contrôle avec un jeu de données plus important mais c'est cohérent. Il y a un DISTINCT à ajouter pour éviter de se retrouver avec les doublons (l'émetteur est le module concerné et le récepteur l'un de ses sous modules par exemple), mais je suis confiant.
 
Merci beaucoup en tout cas !

Salut et merci pour les reponses! Je precise que je m'en sors en optimization sous Oracle tant que ca reste pas trop complique, mais ca a ete appris sur le tas et ce genre de trucs que je decris la me depasse un peu. Du coup vos reponses m'ont fait lire la doc et d'autres trucs sur les plans, nested loops etc et j'ai appris plein de trucs! Pas encore eu le temps d'essayer mais ca risque bien de marcher nickel.
 
Pour les autres suggestiongs:
- j'utilise gather_table_statistics, et les indexs sont reconstruits automatiquement toutes les semaines; ya pas eu de gros mouvements sur les deux tables depuis un bail (la 250m ne bouge jamais en dev, et l'autre prend p'tetre 10/15 lignes par semaine), et ya bien eu reconstruction depuis la derniere collecte de stats.
- pour optimizer_index_caching et optimizer_index_cost_adj, deja va falloir que j'aille voir ce que ca fait, mais je prevois deja deux problemes potentiels: le premier c'est que je ne controle pas ce genre de parametres en prod et c'est la croix et la banniere pour les faire changer, et le second c'est que je devine que ca influence toutes les requetes, et des requetes, en production, il y en a plein de differentes, donc aucune chance qu'on me laisse passer sans faire une grosse batterie de tests de regression.
 
En tout cas, encore merci!
A+

SELECT msg.*
       FROM message msg
           INNER JOIN module parent ON (parent.id = msq.id_emetteur OR parent.id = msg.id_destinataire)
           INNER JOIN module child ON child.parent_id = parent.id AND (child.id = msq.sender_id OR child.id = msg.receiver_id)
       WHERE parent.id = 42;

Au cas ou, gaffe tu as "msq" au lieu de "msg" dans tes requêtes.

Typo pendant la recopie, mais merci

Je suis en train de développer un système de template avec héritage :
 
template (id, owner_id, parent_id, tpl)
 
parent_id est par défaut à null, et référence un id de la table template lorsqu'il y a héritage.
 
Le soucis est que la colonne owner_id peut référencer deux tables : group et user, un groupe ou un utilisateur peuvent être propriétaire.
 
Pour les différencier j'ai pensé à une colonne supplémentaire de type flag, stockant un booléen pour identifier à quelle table fait référence la colonne owner_id, mais ça me semble être un mauvais design
 
Le problème étant que je ne vois pas de solutions correctes

Perso je préfère avoir 2 colonnes de jointure (owner_group_id & owner_user_id) qu'avoir une colonne qui joint sur 2 tables différentes en fonction d'un flag mais je suis pas sur que ce soit un "best practice" ! ^^

la solution de mechkurt est la bonne, ne serait-ce que pour avoir une clé étrangère sur chaque colonne.

Concept d'héritage 3FN soit :
- tu spécialises donc dans le cas de mechkurt, tu distingues 2 colonnes de jointures  
- tu généralises mais dans ce cas il faut matérialiser une entité propre et donc tu auras les tables group, user et par exemple owner.
 
Dans le second cas, il faut vraiment que owner ait ses propres propriété pour partir sur cet héritage sinon rester dans le cas de mechkurt la meilleurs solution semblerait-il dans ton cas.