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

Oracle Discussion :

Utilisation d'un IN / table de un million d'enregistrements


Sujet :

Oracle

Vue hybride

Message précédent Message précédent   Message suivant Message suivant
  1. #1
    Membre confirmé
    Administrateur de base de données
    Inscrit en
    Juillet 2006
    Messages
    98
    Détails du profil
    Informations personnelles :
    Âge : 40

    Informations professionnelles :
    Activité : Administrateur de base de données

    Informations forums :
    Inscription : Juillet 2006
    Messages : 98
    Par défaut Utilisation d'un IN / table de un million d'enregistrements
    Salut, j'ai une requête à améliorer.

    Admettons une requête du genre et que j'ai un index sur le champ status.
    Code : Sélectionner tout - Visualiser dans une fenêtre à part
    1
    2
    3
    SELECT * FROM TABLE1 
    WHERE STATUS IN (1, 2)
    ORDER BY ID
    Dans ce cas mon index n'est pas utilisé et le "explain plan" m'indique un full table scan.

    Si je fais seulement un
    Code : Sélectionner tout - Visualiser dans une fenêtre à part
    1
    2
    3
    SELECT * FROM TABLE1 
    WHERE STATUS IN (2)
    ORDER BY ID
    mon index est utilisé et le résultat s'affiche beaucoup plus vite que si je n'avais pas d'index du tout sur le champ status.

    Même principe avec STATUS = 1 vs STATUS = 1 OR STATUS = 2.

    J'ai besoin d'avoir de quoi de performant pour répondre à une table d'un millions d'enregistrements avec un IN (1, 2, ...)


    J'ai essayé de forcer l'index avec
    Code : Sélectionner tout - Visualiser dans une fenêtre à part
    1
    2
    select /*+ INDEX(a ix_test) */ from table1 a
    where status in (1,2)...
    ça change rien.

    La seule chose que j'ai trouvé qui est vraiment mais alors là vraiment performant, c'est de créer ma table en faisant des partitions basées sur la colonne statut.

    Si je fais ça et je fais un in (1, 2, 4) là les partitions et les index font que ça reviens vraiment vite. Par contre pour utilisé le partitionning il faut une license qui vaut assez cher...

    Donc, je me demandais si quelqu'un aurait une idée pour m'aider (sans partionning).

    Merci !

  2. #2
    Membre Expert

    Homme Profil pro
    Administrateur de base de données
    Inscrit en
    Juin 2012
    Messages
    612
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Âge : 36
    Localisation : France, Alpes Maritimes (Provence Alpes Côte d'Azur)

    Informations professionnelles :
    Activité : Administrateur de base de données
    Secteur : Conseil

    Informations forums :
    Inscription : Juin 2012
    Messages : 612
    Par défaut
    Bonjour,

    C'est normal que IN et OR fasse la même chose.

    La solution pour utiliser l'index serait de faire
    Code : Sélectionner tout - Visualiser dans une fenêtre à part
    1
    2
    3
    4
    5
    6
    7
    SELECT * FROM TABLE1
    WHERE STATUS = 1
    ORDER BY ID
    UNION ALL
    SELECT * FROM TABLE1
    WHERE STATUS = 2
    ORDER BY ID
    Mais c'est probablement moins performant (ou en tout cas le coût estimé est moins bon) sinon je pense que l'optimiseur aurait fait la réécriture tout seul.

    Je ne suis pas sûr qu'il y ait beaucoup de solution, la requête à faire est telle qu'elle est et on ne peut parfois pas vraiment optimiser.
    Quelle est la sélectivité des statuts ?

  3. #3
    Membre Expert

    Homme Profil pro
    Inscrit en
    Mars 2010
    Messages
    536
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Localisation : France

    Informations forums :
    Inscription : Mars 2010
    Messages : 536
    Par défaut
    Vous feriez mieux de poster les deux plans d'exécution pour appréciation. Il suffit parfois d'un seul enregistrement de plus pour faire basculer une exécution d'un index range scan vers un full table scan

    philosophy

  4. #4
    Membre Expert
    Inscrit en
    Août 2009
    Messages
    1 073
    Détails du profil
    Informations forums :
    Inscription : Août 2009
    Messages : 1 073
    Par défaut
    Citation Envoyé par tommey Voir le message
    Donc, je me demandais si quelqu'un aurait une idée pour m'aider (sans partionning).
    Comme le dit Mohammed.Mouri, il faudrait voir les traces et les plans pour pouvoir être précis dans le diagnostic. Cependant, il est probable que les statistiques remontent à l'optimiseur que les valeurs 1 et 2 du statut occupent une très forte volumétrie dans la table. Et donc qu'il n'est pas forcément pertinent de faire :
    - un scan d'index qui remonte de toute façon une très grosse partie de la table
    - à partir de l'index, aller chercher les infos dans la table
    - et accessoirement à la fin trier le tout.

    Une remarque en passant : la requête avec les UNION n'est pas valable, car rien ne garantira l'ordre final (il faudrait placer le order by dans un select englobant).

  5. #5
    Membre Expert

    Homme Profil pro
    Administrateur de base de données
    Inscrit en
    Juin 2012
    Messages
    612
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Âge : 36
    Localisation : France, Alpes Maritimes (Provence Alpes Côte d'Azur)

    Informations professionnelles :
    Activité : Administrateur de base de données
    Secteur : Conseil

    Informations forums :
    Inscription : Juin 2012
    Messages : 612
    Par défaut
    Citation Envoyé par Rei Ichido Voir le message
    Une remarque en passant : la requête avec les UNION n'est pas valable, car rien ne garantira l'ordre final (il faudrait placer le order by dans un select englobant).
    Oups, effectivement, je n'avais pas du tout fait attention à l'ORDER BY
    Bien vu, merci

  6. #6
    Membre chevronné
    Profil pro
    Inscrit en
    Février 2010
    Messages
    412
    Détails du profil
    Informations personnelles :
    Localisation : France

    Informations forums :
    Inscription : Février 2010
    Messages : 412
    Par défaut
    Citation Envoyé par Rei Ichido Voir le message
    Une remarque en passant : la requête avec les UNION n'est pas valable, car rien ne garantira l'ordre final (il faudrait placer le order by dans un select englobant).
    C'est faux.
    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
    18
    19
    20
    21
    22
    23
    24
    25
    26
    27
    28
    29
    30
    31
    32
    33
    34
    35
    36
    37
    38
    39
    40
    41
    TMP@MINILHC >select * from tmp;
     
             N         N2
    ---------- ----------
             1          2
             1          1
             2          1
     
    Elapsed: 00:00:00.23
    TMP@MINILHC >select * from tmp union all select * from tmp union all select * from tmp order by 1,2 desc;
     
             N         N2
    ---------- ----------
             1          2
             1          2
             1          2
             1          1
             1          1
             1          1
             2          1
             2          1
             2          1
     
    9 rows selected.
     
    Elapsed: 00:00:00.01
    TMP@MINILHC >select * from tmp union all select * from tmp union all select * from tmp order by 1 desc,2;
     
             N         N2
    ---------- ----------
             2          1
             2          1
             2          1
             1          1
             1          1
             1          1
             1          2
             1          2
             1          2
     
    9 rows selected.



    Citation Envoyé par FSiebert Voir le message
    La solution pour utiliser l'index serait de faire [... UNION ALL ... ]
    Et le debut est faux aussi, vu que le IN peut tres bien utiliser l'index:
    (lignes 45 et 71 pour la difference)
    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
    18
    19
    20
    21
    22
    23
    24
    25
    26
    27
    28
    29
    30
    31
    32
    33
    34
    35
    36
    37
    38
    39
    40
    41
    42
    43
    44
    45
    46
    47
    48
    49
    50
    51
    52
    53
    54
    55
    56
    57
    58
    59
    60
    61
    62
    63
    64
    65
    66
    67
    68
    69
    70
    71
    72
    73
    74
    75
    76
    77
    78
    79
     
    TMP@MINILHC >create table tmp as select case mod(level,2) when 0 then 0 else level end n,lpad(level,8,'x') vc from dual connect by level<1e6;
     
    Table created.
     
    Elapsed: 00:00:04.12
    TMP@MINILHC >create index tmp_ind on tmp(n);
     
    Index created.
     
    Elapsed: 00:00:04.04
    TMP@MINILHC >insert into tmp select * from tmp;
    999999 rows created.
     
    Elapsed: 00:00:50.31
    TMP@MINILHC >set autotrace traceonly
     
    TMP@MINILHC >exec dbms_stats.gather_table_stats(user,'TMP')
     
    PL/SQL procedure successfully completed.
     
    Elapsed: 00:00:05.64
    TMP@MINILHC >exec dbms_stats.gather_index_stats(user,'TMP_IND')
     
    PL/SQL procedure successfully completed.
     
    Elapsed: 00:00:00.71
    TMP@MINILHC >select * from tmp where n in (0,13);
    Error ORA-942 while gathering statistics
    SP2-0612: Error generating AUTOTRACE report
     
    1000000 rows selected.
     
    SP2-0612: Error generating AUTOTRACE report
    Elapsed: 00:00:03.37
     
    Execution Plan
    ----------------------------------------------------------
    Plan hash value: 3389504856
     
    --------------------------------------------------------------------------
    | Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
    --------------------------------------------------------------------------
    |   0 | SELECT STATEMENT  |      |   976K|    11M|  1372   (3)| 00:00:17 |
    |*  1 |  TABLE ACCESS FULL| TMP  |   976K|    11M|  1372   (3)| 00:00:17 |
    --------------------------------------------------------------------------
     
    Predicate Information (identified by operation id):
    ---------------------------------------------------
     
       1 - filter("N"=0 OR "N"=13)
     
    SP2-0612: Error generating AUTOTRACE STATISTICS report
    TMP@MINILHC >select * from tmp where n in (2,13);
    Error ORA-942 while gathering statistics
    SP2-0612: Error generating AUTOTRACE report
     
    SP2-0612: Error generating AUTOTRACE report
    Elapsed: 00:00:00.00
     
    Execution Plan
    ----------------------------------------------------------
    Plan hash value: 3354077773
     
    ----------------------------------------------------------------------------------------
    | Id  | Operation                    | Name    | Rows  | Bytes | Cost (%CPU)| Time     |
    ----------------------------------------------------------------------------------------
    |   0 | SELECT STATEMENT             |         |     4 |    48 |     6   (0)| 00:00:01 |
    |   1 |  INLIST ITERATOR             |         |       |       |            |          |
    |   2 |   TABLE ACCESS BY INDEX ROWID| TMP     |     4 |    48 |     6   (0)| 00:00:01 |
    |*  3 |    INDEX RANGE SCAN          | TMP_IND |     4 |       |     4   (0)| 00:00:01 |
    ----------------------------------------------------------------------------------------
     
    Predicate Information (identified by operation id):
    ---------------------------------------------------
     
       3 - access("N"=2 OR "N"=13)
     
    SP2-0612: Error generating AUTOTRACE STATISTICS report
    Et dans le cas ou il s'agit d'utiliser l'index pour une seule des deux valeurs, je vois mal l'interet vu que le FTS de la premiere valeur va lire tout la table toutes manieres

  7. #7
    Membre Expert Avatar de ojo77
    Homme Profil pro
    Architecte de base de données
    Inscrit en
    Décembre 2010
    Messages
    680
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Âge : 51
    Localisation : France, Rhône (Rhône Alpes)

    Informations professionnelles :
    Activité : Architecte de base de données
    Secteur : High Tech - Produits et services télécom et Internet

    Informations forums :
    Inscription : Décembre 2010
    Messages : 680
    Par défaut
    A priori le calcul d'un histogramme sur la colonn status devrait résoudre le problème.

    La question que se pose l'otimiseur est de savoir combien de blocs de données doivent être remontés de la table pour la contrainte status=1 et pour contrainte status=2.

    Vous dites avoir 1 million de lignes. Je suppose le nombre de status plus limité (mettons 10) je suppose aussi que le status est toujours renseigné et je suppose encore que vous n'avez pas d'histogramme à votre disposition et je suppose enfin que vous avez 100 lignes par bloc (donc 10 000 blocs).

    L'optimiseur va alors se demander combien de lignes sont sensées être remontées pour chaque valeur. Ne s'appuyant que sur ses statistiques il va obtenir le résultat de 200 000.

    Il va ensuite, s'il en a la possibilité estimer la dispersion des données dans le segment (au travers des blocs). Pour cela il va s'appuyer sur la statistique clustering factor de l'index sur la colonne s'il existe. (sinon il estime qu'il y a une distribution équilibrée des données). Plus la table est triée dans l'ordre des données de votre colonne plus l'optimiseur a de chance de prendre l'index).

    Dans notre exemple, si le données sont triées dans la table comme dans l'index, il aura à lire 2000 blocs de la table et les blocs de l'index (mettons 1000) soit en tout 3000 blocs => il choisira alors l'index.

    Si par contre les données sont plus uniformément réparties dans les blocs, il estimera qu'il doit lire les 10 000 blocs de la table plus éventuellement les 3 000 blocs d'index s'il doit le prendre. Il choisira donc de ne pas le prendre.

    Je pense que c'est quelque chose de cet ordre qui se passe pour votre requête.

    Vous dites avoir constaté que le passage par index est plus rapide. C'est donc que l'optimiseur ne dispose pas des informations suffisantes pour avoir une bonne estimation du nombre de blocs à lire pour effectuer votre requête.

    Le positionnement d'un histogramme sur la colonne status améliorera son estimation du nombre de lignes ramenées et pourra éventuellement refaire basculer le plan d'exécution vers un passage par index.

    J'ai fait beaucoup de suppositions dans ma réponse, mieux connaitre vos statistiques et la topologie de votre (vos) tables permettrait un meilleur diagnostique. D'où la demande de M. Houri de vos plans d'exécution.

    La version d'oracle que vous utilisez pourrait aussi être utile

  8. #8
    Membre confirmé
    Administrateur de base de données
    Inscrit en
    Juillet 2006
    Messages
    98
    Détails du profil
    Informations personnelles :
    Âge : 40

    Informations professionnelles :
    Activité : Administrateur de base de données

    Informations forums :
    Inscription : Juillet 2006
    Messages : 98
    Par défaut
    Bonjour à tous et merci pour vos réponses.

    Pour commencer voici les counts de chaque status :

    Code : Sélectionner tout - Visualiser dans une fenêtre à part
    1
    2
    3
      SELECT status, COUNT (*)
        FROM T_CON002_INPUT_PAYLOAD
    GROUP BY status
    Résultat :

    STATUS COUNT(*)
    1 6837
    2 252408
    4 953
    3 6313
    0 698438


    Ensuite si j'essaie :

    Code : Sélectionner tout - Visualiser dans une fenêtre à part
    1
    2
    3
    select * from  T_CON002_INPUT_PAYLOAD
    where status in (1)
    order by id
    Voici le plan d'exécution :

    Plan
    SELECT STATEMENT ALL_ROWSCost: 106 737 Bytes: 25 281 690 Cardinality: 192 990
    3 SORT ORDER BY Cost: 106 737 Bytes: 25 281 690 Cardinality: 192 990
    2 TABLE ACCESS BY INDEX ROWID TABLE CON3FEF01.T_CON002_INPUT_PAYLOAD Cost: 101 076 Bytes: 25 281 690 Cardinality: 192 990
    1 INDEX RANGE SCAN INDEX CON3FEF01.IX_TEST Cost: 362 Cardinality: 192 990


    Maintenant si je fais :
    Code : Sélectionner tout - Visualiser dans une fenêtre à part
    1
    2
    3
    select * from  T_CON002_INPUT_PAYLOAD
    where status in (1, 2)
    order by id
    Voici le plan :
    Plan
    SELECT STATEMENT ALL_ROWSCost: 146 684 Bytes: 50 563 380 Cardinality: 385 980
    2 SORT ORDER BY Cost: 146 684 Bytes: 50 563 380 Cardinality: 385 980
    1 TABLE ACCESS FULL TABLE CON3FEF01.T_CON002_INPUT_PAYLOAD Cost: 135 364 Bytes: 50 563 380 Cardinality: 385 980


    Vous avez raison par contre pour le union all, ça semble donner de bon résultat quand même.

    Voici mon test :
    Code : Sélectionner tout - Visualiser dans une fenêtre à part
    1
    2
    3
    4
    5
    6
    7
    8
    select * from (
    select * from T_CON002_INPUT_PAYLOAD A
    WHERE STATUS =4
    union all
    select * from T_CON002_INPUT_PAYLOAD A
    WHERE STATUS =1)
    where create_date > '2012-08-20'
    ORDER BY create_date
    Et le plan :
    Plan
    SELECT STATEMENT ALL_ROWSCost: 328 072 Bytes: 256 676 434 Cardinality: 1 929 898
    7 SORT ORDER BY Cost: 328 072 Bytes: 256 676 434 Cardinality: 1 929 898
    6 VIEW CON3FEF01. Cost: 270 751 Bytes: 256 676 434 Cardinality: 1 929 898
    5 UNION-ALL
    2 TABLE ACCESS BY INDEX ROWID TABLE CON3FEF01.T_CON002_INPUT_PAYLOAD Cost: 101 078 Bytes: 25 281 690 Cardinality: 192 990
    1 INDEX RANGE SCAN INDEX CON3FEF01.IX_TEST Cost: 362 Cardinality: 192 990
    4 TABLE ACCESS BY INDEX ROWID TABLE CON3FEF01.T_CON002_INPUT_PAYLOAD Cost: 101 078 Bytes: 25 281 690 Cardinality: 192 990
    3 INDEX RANGE SCAN INDEX CON3FEF01.IX_TEST Cost: 362 Cardinality: 192 990


    Attention, ne considérer pas trop le nombre de bytes que ma requête retourne car j'ai un champ clob qui contient un gros xml pour chaque enregistrement.

    Bref, je pense que je vais essayer de me débrouiller avec le union all, et je vous reviens si ça ne fonctionne pas...

    Merci à tous

  9. #9
    Membre Expert Avatar de ojo77
    Homme Profil pro
    Architecte de base de données
    Inscrit en
    Décembre 2010
    Messages
    680
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Âge : 51
    Localisation : France, Rhône (Rhône Alpes)

    Informations professionnelles :
    Activité : Architecte de base de données
    Secteur : High Tech - Produits et services télécom et Internet

    Informations forums :
    Inscription : Décembre 2010
    Messages : 680
    Par défaut
    Ok,

    On constate donc bien que vous n'avez pas d'histogramme: il considère une cardinalité égale au nombre de lignes (moins les nulls) total de la base divisé par le nombre de valeurs distinctes, en l'occurrence 192 990 .

    Si l'approximation est bonne pour status=2 elle ne l'est pas pour les autres valeurs => positionnez un histogramme ça peut fonctionner. Attention cependant, il y a 1 quart des valeurs qui valent 2, il reste quand même de fortes chances que le choix de l'optimiseur s'oriente vers un FTS.

    Questions

    Le paramètre "_no_or_expansion" est-il positionné à true ?
    Vos statistiques systèmes sont-elles calculées ?
    Quelle est la valeur du paramètre optimizer_index_cost_adj ?
    Quelle est la valeur du paramètre dbfile_multibloc_read_count ?

    Quel plan est pris par
    Code : Sélectionner tout - Visualiser dans une fenêtre à part
    1
    2
    3
    SELECT * FROM  T_CON002_INPUT_PAYLOAD
    WHERE STATUS IN (2)
    ORDER BY id
    ?

  10. #10
    Membre Expert

    Homme Profil pro
    Inscrit en
    Mars 2010
    Messages
    536
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Localisation : France

    Informations forums :
    Inscription : Mars 2010
    Messages : 536
    Par défaut
    Quelle version d'Oracle?

    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
     
    STATUS COUNT(*)
    1 6837
    2 252408
    4 953
    3 6313
    0 698438
     
    SELECT * FROM  T_CON002_INPUT_PAYLOAD
    WHERE STATUS IN (1)
    ORDER BY id Voici le plan d'exécution :
     
    Plan
    SELECT STATEMENT ALL_ROWSCost: 106 737 Bytes: 25 281 690 Cardinality: 192 990 
    3 SORT ORDER BY Cost: 106 737 Bytes: 25 281 690 Cardinality: 192 990 
    2 TABLE ACCESS BY INDEX ROWID TABLE CON3FEF01.T_CON002_INPUT_PAYLOAD Cost: 101 076 Bytes: 25 281 690 Cardinality: 192 990 
    1 INDEX RANGE SCAN INDEX CON3FEF01.IX_TEST Cost: 362 Cardinality: 192 990
    (a) Le nombre d'enregistrement total semble donc être = 964949
    (b) Comme il existe 5 valeurs distinctes de la colonne status sa selectivité est égale à 1/5
    (c) La cardinality du premier select est donc card = (a) * (b) = 964949/5 = 192990

    Code : Sélectionner tout - Visualiser dans une fenêtre à part
    1
    2
    3
    4
    5
    6
    7
    8
     
    SELECT * FROM  T_CON002_INPUT_PAYLOAD
    WHERE STATUS IN (1, 2)
    ORDER BY id Voici le plan :
    Plan
    SELECT STATEMENT ALL_ROWSCost: 146 684 Bytes: 50 563 380 Cardinality: 385 980 
    2 SORT ORDER BY Cost: 146 684 Bytes: 50 563 380 Cardinality: 385 980 
    1 TABLE ACCESS FULL TABLE CON3FEF01.T_CON002_INPUT_PAYLOAD Cost: 135 364 Bytes: 50 563 380 Cardinality: 385 980
    Lorsqu'il y a plus d'un élément dans la liste IN la cardinality calculée sera

    (d) card = cardinality à une seule valeur * nombre d'eléments dans la liste IN
    card = 192990 * 2 = 385980

    et ainsi de suite, la formule ci-dessus continuera à s'appliquer jusqu'à ce que le nombre d'éléments excède le nombre de valeurs distintes de la colonne statut.

    Si vous êtes en 8i, il existe un bug dans le CBO lors de l'utilisation d'un IN avec une liste à cause de la transformation en OR. Pour cela il suffit d'utiliser le hint /*+ use_concat */ et le CBO va utiliser les fameuses UNION ALL.

    Si vous êtes dans une version plus récente, il va falloir poster l'explain plan avec sa partie predicate et avec les informations E-Rows et A-Rows

+ Répondre à la discussion
Cette discussion est résolue.

Discussions similaires

  1. Utilisation d'une Single Table Inheritance et des migrations
    Par redpopo dans le forum Ruby on Rails
    Réponses: 2
    Dernier message: 08/12/2008, 16h55
  2. Utilisation des index sur tables à jeux de caractères différents
    Par globule71 dans le forum Administration
    Réponses: 0
    Dernier message: 11/07/2008, 10h28
  3. utilisation d'index de table
    Par medjrd dans le forum Langage SQL
    Réponses: 1
    Dernier message: 19/06/2007, 11h02
  4. Réponses: 8
    Dernier message: 13/11/2006, 22h01
  5. Réponses: 1
    Dernier message: 07/06/2006, 11h44

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