IdentifiantMot de passe
Loading...
Mot de passe oublié ?Je m'inscris ! (gratuit)
Navigation

Inscrivez-vous gratuitement
pour pouvoir participer, suivre les réponses en temps réel, voter pour les messages, poser vos propres questions et recevoir la newsletter

SQL Oracle Discussion :

Théorie du partitionnement


Sujet :

SQL Oracle

  1. #1
    Expert éminent sénior
    Avatar de SheikYerbouti
    Profil pro
    Inscrit en
    Mai 2003
    Messages
    6 760
    Détails du profil
    Informations personnelles :
    Localisation : France

    Informations forums :
    Inscription : Mai 2003
    Messages : 6 760
    Points : 11 862
    Points
    11 862
    Par défaut Théorie du partitionnement
    Bonjour,

    Je travaille sur une table contenant plusieurs millions d'enregistrements.
    Chaque année, elle grossit d'autres millions de lignes.
    Le traitement s'effectue toujours sur une année donnée, donc une partie de la table. Le partionnement de cette table sur la colonne annee (number) semble approprié.
    Le simple fait de créer les différentes partitions est-il suffisant, ou faut-il également agir sur l'indexation. Aujourd'hui, seule un PK est définie sur cette table. J'ajoute, pour finir, qu'il est hors de question de modifier les requêtes existantes qui agissent sur cette table.
    D'avance merci.
    Rédacteur Oracle (Oracle ACE)
    Guide Oracle ,Guide PL/SQL, Guide Forms 9i/10g, Index de recherche
    Je ne réponds pas aux questions techniques par MP
    Blogs: Forms-PL/SQL-J2EE - Forms Java Beans

  2. #2
    Expert éminent sénior Avatar de mnitu
    Homme Profil pro
    Ingénieur développement logiciels
    Inscrit en
    Octobre 2007
    Messages
    5 611
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Localisation : France, Marne (Champagne Ardenne)

    Informations professionnelles :
    Activité : Ingénieur développement logiciels
    Secteur : High Tech - Éditeur de logiciels

    Informations forums :
    Inscription : Octobre 2007
    Messages : 5 611
    Points : 11 252
    Points
    11 252
    Par défaut
    Citation Envoyé par SheikYerbouti Voir le message
    Bonjour,

    Je travaille sur une table contenant plusieurs millions d'enregistrements.
    Chaque année, elle grossit d'autres millions de lignes.
    Le traitement s'effectue toujours sur une année donnée, donc une partie de la table. Le partionnement de cette table sur la colonne annee (number) semble approprié.
    Le simple fait de créer les différentes partitions est-il suffisant, ou faut-il également agir sur l'indexation. Aujourd'hui, seule un PK est définie sur cette table. J'ajoute, pour finir, qu'il est hors de question de modifier les requêtes existantes qui agissent sur cette table.
    D'avance merci.
    Les requêtes qui utilisent la clé primaire n'ont pas besoin de partitionement.
    Les requêtes qui font full table scan vont automatiquement béneficier de la partition.
    Mais il faut voir la tête des requêtes pour parler index.
    Il y a un chapitre (3) du Effective Oracle By Design du Tom Kyte disponible en télechargement sur sont site qui parle du partitionement.
    Il semble que le but du partitionement dans ton cas c'est la performance, est-ce vrai ?

  3. #3
    En attente de confirmation mail
    Inscrit en
    Mars 2010
    Messages
    205
    Détails du profil
    Informations forums :
    Inscription : Mars 2010
    Messages : 205
    Points : 230
    Points
    230
    Par défaut
    Tu peux aussi créer des partitions sur tes index, sachant que ce ne sera pas l'idéal pour la PK, puisque tu devras le déclarer en global sur la table, ce qui est moins pratique de d'avoir une partition locale, où on a correspondance parfaite entre partition de table et partition d'index.

    On peut ruser pour contourner cela en mettant l'année dans la PK, mais c'est bricoler...

    Ca dépend aussi si tes traitements se servent beaucoup de la PK, si ce n'est pas le cas, partitionner les tables suffit à réduire les temps de traitement.

  4. #4
    Expert éminent sénior
    Avatar de SheikYerbouti
    Profil pro
    Inscrit en
    Mai 2003
    Messages
    6 760
    Détails du profil
    Informations personnelles :
    Localisation : France

    Informations forums :
    Inscription : Mai 2003
    Messages : 6 760
    Points : 11 862
    Points
    11 862
    Par défaut
    oui, les traitements n'utilisent que la PK.
    Rédacteur Oracle (Oracle ACE)
    Guide Oracle ,Guide PL/SQL, Guide Forms 9i/10g, Index de recherche
    Je ne réponds pas aux questions techniques par MP
    Blogs: Forms-PL/SQL-J2EE - Forms Java Beans

  5. #5
    En attente de confirmation mail
    Inscrit en
    Mars 2010
    Messages
    205
    Détails du profil
    Informations forums :
    Inscription : Mars 2010
    Messages : 205
    Points : 230
    Points
    230
    Par défaut
    Dans ce cas, on peut préfixer la PK par l'année, on pourra alors construire un index partitionné en local sur la table partitionnée, ce qui veut dire qu'on est sûr dans ce cas de n'utiliser qu'une partition de table et la partition correspondante de l'index.

  6. #6
    Expert éminent sénior
    Avatar de SheikYerbouti
    Profil pro
    Inscrit en
    Mai 2003
    Messages
    6 760
    Détails du profil
    Informations personnelles :
    Localisation : France

    Informations forums :
    Inscription : Mai 2003
    Messages : 6 760
    Points : 11 862
    Points
    11 862
    Par défaut
    Dans notre cas, l'année est en 2ème position dans la PK et je ne suis pas certain que la passer en première position n'ait auncune incidence sur les requêtes existantes.
    Rédacteur Oracle (Oracle ACE)
    Guide Oracle ,Guide PL/SQL, Guide Forms 9i/10g, Index de recherche
    Je ne réponds pas aux questions techniques par MP
    Blogs: Forms-PL/SQL-J2EE - Forms Java Beans

  7. #7
    Expert éminent sénior Avatar de mnitu
    Homme Profil pro
    Ingénieur développement logiciels
    Inscrit en
    Octobre 2007
    Messages
    5 611
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Localisation : France, Marne (Champagne Ardenne)

    Informations professionnelles :
    Activité : Ingénieur développement logiciels
    Secteur : High Tech - Éditeur de logiciels

    Informations forums :
    Inscription : Octobre 2007
    Messages : 5 611
    Points : 11 252
    Points
    11 252
    Par défaut
    Citation Envoyé par SheikYerbouti Voir le message
    oui, les traitements n'utilisent que la PK.
    Donc partitionner ne servira pas à grand chose.
    Mais, est ce que t'as un exemple (table, pk, requête)?
    Et c'est quoi le problème ?

  8. #8
    Expert éminent sénior
    Avatar de SheikYerbouti
    Profil pro
    Inscrit en
    Mai 2003
    Messages
    6 760
    Détails du profil
    Informations personnelles :
    Localisation : France

    Informations forums :
    Inscription : Mai 2003
    Messages : 6 760
    Points : 11 862
    Points
    11 862
    Par défaut
    Voici le plan sur la table partitionnée avec sa PK sans index particulier:
    Code : Sélectionner tout - Visualiser dans une fenêtre à part
    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    14
     
     
    SQL> SELECT * FROM XXXX WHERE annee = 2008 and ROWNUM < 100
      2  /
    99 ligne(s) sélectionnée(s).
     
    Plan d'exécution
    ----------------------------------------------------------
       0      SELECT STATEMENT Optimizer=ALL_ROWS (Cost=2 Card=99 Bytes=52
              47)
       1    0   COUNT (STOPKEY)
       2    1     PARTITION RANGE (SINGLE) (Cost=2 Card=101 Bytes=5353)
       3    2       TABLE ACCESS (FULL) OF 'XXXX' (TABLE) (Cost=2 Card=1
              01 Bytes=5353)
    Il semblerait que la bonne partition sélectionée.
    Rédacteur Oracle (Oracle ACE)
    Guide Oracle ,Guide PL/SQL, Guide Forms 9i/10g, Index de recherche
    Je ne réponds pas aux questions techniques par MP
    Blogs: Forms-PL/SQL-J2EE - Forms Java Beans

  9. #9
    Expert éminent sénior
    Avatar de SheikYerbouti
    Profil pro
    Inscrit en
    Mai 2003
    Messages
    6 760
    Détails du profil
    Informations personnelles :
    Localisation : France

    Informations forums :
    Inscription : Mai 2003
    Messages : 6 760
    Points : 11 862
    Points
    11 862
    Par défaut
    Citation Envoyé par mnitu Voir le message
    Donc partitionner ne servira pas à grand chose.
    Mais, est ce que t'as un exemple (table, pk, requête)?
    Et c'est quoi le problème ?
    Il n'a pas de problème a proprement parler. Je cherche des solutions d'optimisation s'il y en a. cette table fait 70 millions de lignes et il me semblait que le partitionnement était justement fait pour améliorer les temps de traitement des requêtes.
    Rédacteur Oracle (Oracle ACE)
    Guide Oracle ,Guide PL/SQL, Guide Forms 9i/10g, Index de recherche
    Je ne réponds pas aux questions techniques par MP
    Blogs: Forms-PL/SQL-J2EE - Forms Java Beans

  10. #10
    Expert éminent sénior Avatar de mnitu
    Homme Profil pro
    Ingénieur développement logiciels
    Inscrit en
    Octobre 2007
    Messages
    5 611
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Localisation : France, Marne (Champagne Ardenne)

    Informations professionnelles :
    Activité : Ingénieur développement logiciels
    Secteur : High Tech - Éditeur de logiciels

    Informations forums :
    Inscription : Octobre 2007
    Messages : 5 611
    Points : 11 252
    Points
    11 252
    Par défaut
    Citation Envoyé par SheikYerbouti Voir le message
    Voici le plan sur la table partitionnée avec sa PK sans index particulier:
    Code : Sélectionner tout - Visualiser dans une fenêtre à part
    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    14
     
     
    SQL> SELECT * FROM XXXX WHERE annee = 2008 and ROWNUM < 100
      2  /
    99 ligne(s) sélectionnée(s).
     
    Plan d'exécution
    ----------------------------------------------------------
       0      SELECT STATEMENT Optimizer=ALL_ROWS (Cost=2 Card=99 Bytes=52
              47)
       1    0   COUNT (STOPKEY)
       2    1     PARTITION RANGE (SINGLE) (Cost=2 Card=101 Bytes=5353)
       3    2       TABLE ACCESS (FULL) OF 'XXXX' (TABLE) (Cost=2 Card=1
              01 Bytes=5353)
    Il semblerait que la bonne partition sélectionée.
    C'est une requête qui fait un FULL et qui n'utilise pas la clé primaire.
    Si l'annee est un deuxième position dans l'index il se peut q'un index skip scan en profite.

  11. #11
    Expert éminent sénior Avatar de mnitu
    Homme Profil pro
    Ingénieur développement logiciels
    Inscrit en
    Octobre 2007
    Messages
    5 611
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Localisation : France, Marne (Champagne Ardenne)

    Informations professionnelles :
    Activité : Ingénieur développement logiciels
    Secteur : High Tech - Éditeur de logiciels

    Informations forums :
    Inscription : Octobre 2007
    Messages : 5 611
    Points : 11 252
    Points
    11 252
    Par défaut
    Citation Envoyé par SheikYerbouti Voir le message
    Il n'a pas de problème a proprement parler. Je cherche des solutions d'optimisation s'il y en a. cette table fait 70 millions de lignes et il me semblait que le partitionnement était justement fait pour améliorer les temps de traitement des requêtes.
    Je dirait plutôt d'améliorer les temps de traitement des certaines requêtes.

  12. #12
    Expert éminent sénior
    Avatar de SheikYerbouti
    Profil pro
    Inscrit en
    Mai 2003
    Messages
    6 760
    Détails du profil
    Informations personnelles :
    Localisation : France

    Informations forums :
    Inscription : Mai 2003
    Messages : 6 760
    Points : 11 862
    Points
    11 862
    Par défaut
    Code : Sélectionner tout - Visualiser dans une fenêtre à part
    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    14
    15
    16
    17
     Nom                                       NULL ?   Type
     ----------------------------------------- -------- ------------
     SOC                                       NOT NULL NUMBER(11)
     ANNEE                                     NOT NULL NUMBER(11)
     EMPLOYE                                   NOT NULL NUMBER(11)
     C1                                        NOT NULL VARCHAR2(1)
     C2                                        NOT NULL NUMBER(11)
     T01                                               FLOAT(126)
     T02                                               FLOAT(126)
     T03                                               FLOAT(126)
     T04                                               FLOAT(126)
     T05                                               FLOAT(126)
     T06                                               FLOAT(126)
     T07                                               FLOAT(126)
     T08                                               FLOAT(126)
     T09                                               FLOAT(126)
     T10                                               FLOAT(126)
    et la pk:
    Code : Sélectionner tout - Visualiser dans une fenêtre à part
    1
    2
    3
    4
    5
    6
    7
    8
    9
    ALTER TABLE XXXX
      ADD CONSTRAINT XXXX_PK PRIMARY KEY (
        SOC,
        ANNEE,
        EMPLOYE,
        C1,
        C2
      )
    /
    Requete:
    Code : Sélectionner tout - Visualiser dans une fenêtre à part
    1
    2
    3
    4
    5
    6
    7
    Select T01
    From   XXX
    Where  SOC = 
    And    ANNEE = 
    And    EMPLOYE = 
    And    C1 = 
    And    C2 =
    Rédacteur Oracle (Oracle ACE)
    Guide Oracle ,Guide PL/SQL, Guide Forms 9i/10g, Index de recherche
    Je ne réponds pas aux questions techniques par MP
    Blogs: Forms-PL/SQL-J2EE - Forms Java Beans

  13. #13
    En attente de confirmation mail
    Inscrit en
    Mars 2010
    Messages
    205
    Détails du profil
    Informations forums :
    Inscription : Mars 2010
    Messages : 205
    Points : 230
    Points
    230
    Par défaut
    Tu n'utilises pas la PK dans ta requête, donc ne change rien à l'index, partitionne la table par année et tu profiteras du "partition pruning" d'Oracle, tu ne balayeras que la partition désirée

  14. #14
    Expert éminent sénior
    Avatar de SheikYerbouti
    Profil pro
    Inscrit en
    Mai 2003
    Messages
    6 760
    Détails du profil
    Informations personnelles :
    Localisation : France

    Informations forums :
    Inscription : Mai 2003
    Messages : 6 760
    Points : 11 862
    Points
    11 862
    Par défaut
    Citation Envoyé par sgora Voir le message
    Tu n'utilises pas la PK dans ta requête,...
    Je crois que tu as mal lu. Ma requête tape exactement dans la PK
    Rédacteur Oracle (Oracle ACE)
    Guide Oracle ,Guide PL/SQL, Guide Forms 9i/10g, Index de recherche
    Je ne réponds pas aux questions techniques par MP
    Blogs: Forms-PL/SQL-J2EE - Forms Java Beans

  15. #15
    En attente de confirmation mail
    Inscrit en
    Mars 2010
    Messages
    205
    Détails du profil
    Informations forums :
    Inscription : Mars 2010
    Messages : 205
    Points : 230
    Points
    230
    Par défaut
    Ah pardon, oui celle-là utilise la PK, mais dans ce cas le partitionnement n'améliorera pas le temps d'exécution.

    SELECT T01
    FROM XXX
    WHERE SOC =
    AND ANNEE =
    AND EMPLOYE =
    AND C1 =
    AND C2 =

  16. #16
    Modérateur
    Avatar de Waldar
    Homme Profil pro
    Customer Success Manager @Vertica
    Inscrit en
    Septembre 2008
    Messages
    8 452
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Âge : 46
    Localisation : France, Val de Marne (Île de France)

    Informations professionnelles :
    Activité : Customer Success Manager @Vertica
    Secteur : High Tech - Éditeur de logiciels

    Informations forums :
    Inscription : Septembre 2008
    Messages : 8 452
    Points : 17 814
    Points
    17 814
    Par défaut
    Vous n'effectuez jamais de calcul d'agrégat ?
    La table des ventes que je suis, qui n'est pas foncièrement énorme (40 M lignes) est partitionnée par mois (ça fait des partitions de 250 / 500 K lignes), mais l'intérêt et de pouvoir faire des calculs de CA soit par mois, soit par année, et c'est très rapide ainsi.

    Les partitions permettent aussi de gérer facilement les historisations, si vous voulez gardez cinq ans en ligne, avec des partitions c'est enfantin.

  17. #17
    Expert éminent sénior
    Avatar de SheikYerbouti
    Profil pro
    Inscrit en
    Mai 2003
    Messages
    6 760
    Détails du profil
    Informations personnelles :
    Localisation : France

    Informations forums :
    Inscription : Mai 2003
    Messages : 6 760
    Points : 11 862
    Points
    11 862
    Par défaut
    Désolé, ja parlais de la dernière requête fournie.
    Rédacteur Oracle (Oracle ACE)
    Guide Oracle ,Guide PL/SQL, Guide Forms 9i/10g, Index de recherche
    Je ne réponds pas aux questions techniques par MP
    Blogs: Forms-PL/SQL-J2EE - Forms Java Beans

  18. #18
    Expert éminent sénior Avatar de mnitu
    Homme Profil pro
    Ingénieur développement logiciels
    Inscrit en
    Octobre 2007
    Messages
    5 611
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Localisation : France, Marne (Champagne Ardenne)

    Informations professionnelles :
    Activité : Ingénieur développement logiciels
    Secteur : High Tech - Éditeur de logiciels

    Informations forums :
    Inscription : Octobre 2007
    Messages : 5 611
    Points : 11 252
    Points
    11 252
    Par défaut
    Peut être que j’ai raté quelque chose mais, je pense que pour ce type de requête (celle qui utilise les colonnes de la table primaire) le partitionnement ne va pas rapporter grand chose. Je suis curieux du résultat d’un test. Peut être pour que ce type de requête qui utilise toujours l’index partitionner seulement l’index pourrait être intéressant.

    J’ai encore deux remarques:

    Très probablement que l’index a un bon facteur de clustering, les données arrivant exercice par exercice.

    Essaie d’étudier que est-ce que la compression d’index sur le premières 2 colonnes donne. Regarde dans statistique de l'index, la colonne blevel.

  19. #19
    Expert éminent sénior
    Avatar de SheikYerbouti
    Profil pro
    Inscrit en
    Mai 2003
    Messages
    6 760
    Détails du profil
    Informations personnelles :
    Localisation : France

    Informations forums :
    Inscription : Mai 2003
    Messages : 6 760
    Points : 11 862
    Points
    11 862
    Par défaut
    ... partitionner seulement l'index...
    Mais encore ?
    Rédacteur Oracle (Oracle ACE)
    Guide Oracle ,Guide PL/SQL, Guide Forms 9i/10g, Index de recherche
    Je ne réponds pas aux questions techniques par MP
    Blogs: Forms-PL/SQL-J2EE - Forms Java Beans

  20. #20
    En attente de confirmation mail
    Inscrit en
    Mars 2010
    Messages
    205
    Détails du profil
    Informations forums :
    Inscription : Mars 2010
    Messages : 205
    Points : 230
    Points
    230
    Par défaut
    Je conseille pas la compression, à moins que tables et index soient en read only, si on fait insert update ou delete en nombre sur une table comprimée, le prix à payer est important.

+ Répondre à la discussion
Cette discussion est résolue.
Page 1 sur 3 123 DernièreDernière

Discussions similaires

  1. Pb de truncate sur table partitionnée
    Par Mateo dans le forum Oracle
    Réponses: 14
    Dernier message: 29/11/2004, 10h58
  2. Partitionnement sous Redhat
    Par thomas_strass dans le forum Administration système
    Réponses: 4
    Dernier message: 15/09/2004, 18h07
  3. Partitionner des tables existantes
    Par zestrellita dans le forum Administration
    Réponses: 13
    Dernier message: 29/04/2004, 17h49
  4. Partitionner avec fdisk
    Par saibe dans le forum Administration système
    Réponses: 4
    Dernier message: 13/08/2003, 11h01

Partager

Partager
  • Envoyer la discussion sur Viadeo
  • Envoyer la discussion sur Twitter
  • Envoyer la discussion sur Google
  • Envoyer la discussion sur Facebook
  • Envoyer la discussion sur Digg
  • Envoyer la discussion sur Delicious
  • Envoyer la discussion sur MySpace
  • Envoyer la discussion sur Yahoo