Création d'index incohérent

Bonjour à tous

J'utilise ORACLE 9i.
J'ai une table que je remplis avec un million de lignes. Sur cette table, je n'ai pas créé (encore d'index) ni fait d'analyse.
Je crée ensuite un index (sans analyse).
Lorsque j'exécute une requête devant prendre l'index (pas de doute possible), elle ne le prend pas.
Je supprime l'index puis le recrée (avec le même script), là, la requête prend l'index.

Petit détail : à aucun moment, je n'ai analysé la table ou l'index.
Je ne souhaite pas faire d'analyse des objets (je suis dans le cadre d'une étude qui cherche à valider l'intérêt d'une analyse systématique des objets).

Pourquoi le premier index n'est-il pas "correct" ?

sans code ni explain plan, il va être difficile de répondre

Citation:

Envoyé par Robinson8171

Petit détail : à aucun moment, je n'ai analysé la table ou l'index.
Je ne souhaite pas faire d'analyse des objets (je suis dans le cadre d'une étude qui cherche à valider l'intérêt d'une analyse systématique des objets).

donc tu es en mode RULE donc l'ordre des tables et des colonnes est important... évidemment ce genre de pratique est à proscrire, Oracle n'appréciant guère le mélange avec et sans stats :?

Citation:

Envoyé par orafrance

donc tu es en mode RULE donc l'ordre des tables et des colonnes est important... évidemment ce genre de pratique est à proscrire, Oracle n'appréciant guère le mélange avec et sans stats

Non, je suis en mode CHOOSE et je travaille SANS aucune analyse.

donc tu es en RULE :?

pas de stats = RBO... si ton appli n'impose pas ce mode c'est une très mauvaise idée :?

Citation:

Envoyé par orafrance

donc tu es en RULE :?

pas de stats = RBO... si ton appli n'impose pas ce mode c'est une très mauvaise idée :?

En 9I je ne pense pas que c'est vrai :)
Comment faites-vous pour savoir que votre requête ne prends pas l'index ?

Code:

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
 
SQL> alter session set optimizer_mode=CHOOSE;
 
Session modifiée.
 
SQL> set autotrace traceonly explain
SQL> create table titi as select * from big_table;
 
Table créée.
 
SQL> create index ix_titi on titi (owner, object_name);
 
Index créé.
 
SQL> select * from titi where owner='MNI';
 
Plan d'exécution
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE
   1    0   TABLE ACCESS (BY INDEX ROWID) OF 'TITI'
   2    1     INDEX (RANGE SCAN) OF 'IX_TITI' (NON-UNIQUE)
 
 
 
SQL>

Problème avec CHOOSE / RULE / ...

Je vois ce que tu veux me dire, mais j'ai (pardon) un peu de mal à tout saisir.
Lorsque j'exécute ma requête (select en jointure que ma table et sur une autre de 15 millions de lignes), j'ai le plan d'exécution suivant :
Operation Object Name Rows Bytes Cost Object Node In/Out PStart PStop

Citation:

SELECT STATEMENT Optimizer Mode=CHOOSE 522 K 13391
SORT ORDER BY 522 K 42 M 13391
MERGE JOIN 522 K 42 M 6320
SORT JOIN 14 M 576 M 42
PARTITION RANGE ALL 1 17
INDEX FULL SCAN HB_ETOIL_IN.IX_DWR_F_ELM_PASSAGE_TP_PK 14 M 576 M 42 1 17
SORT JOIN 840 K 34 M 6278
TABLE ACCESS FULL HB_ETOIL_IN.TT_F_ELM_PASSAGE_TP 840 K 34 M 2

La table incriminée est TT_F_ELM_PASSAGE_TP (1 millions de lignes).

L'explain plan indique bien que le mode est CHOOSE... d'où mon incompréhension:(

Citation:

Envoyé par mnitu

En 9I je ne pense pas que c'est vrai :)
Non mais quelle connerie j'ai dit :hola:

Citation:

CHOOSE
The optimizer chooses between a cost-based approach and a rule-based approach, depending on whether statistics are available. This is the default value.

If the data dictionary contains statistics for at least one of the accessed tables, then the optimizer uses a cost-based approach and optimizes with a goal of best throughput.
If the data dictionary contains only some statistics, then the cost-based approach is still used, but the optimizer must guess the statistics for the subjects without any statistics. This can result in suboptimal execution plans.
If the data dictionary contains no statistics for any of the accessed tables, then the optimizer uses a rule-based approach.

Je commence à comprendre, mais j'ai toujours un souci :
Après chargement de la table et création de l'index, j'ai le plan d'exécution suivant :

Citation:

Operation Object Name Rows Bytes Cost Object Node In/Out PStart PStop

SELECT STATEMENT Optimizer Mode=CHOOSE 41 24
SORT ORDER BY 41 3 K 24
NESTED LOOPS 41 3 K 22
TABLE ACCESS FULL HB_ETOIL_IN.TT_F_ELM_PASSAGE_TP 82 3 K 2
PARTITION RANGE ITERATOR KEY KEY
INDEX FAST FULL SCAN HB_ETOIL_IN.IX_DWR_F_ELM_PASSAGE_TP_PK 1 43 4 KEY KEY

Je droppe l'index et je le recrée. J'ai alors le plan suivant :
Operation Object Name Rows Bytes Cost Object Node In/Out PStart PStop

SELECT STATEMENT Optimizer Mode=CHOOSE 1 10874
SORT ORDER BY 1 86 10874
MERGE JOIN 1 86 10872
SORT JOIN 22 M 932 M 42
PARTITION RANGE ALL 1 17
INDEX FULL SCAN HB_ETOIL_IN.IX_DWR_F_ELM_PASSAGE_TP_PK 22 M 932 M 42 1 17
SORT JOIN 1 M 59 M 10830
INDEX FAST FULL SCAN HB_ETOIL_IN.IX_TT_F_ELM_PASSAGE_TP_01 1 M 59 M 4

Pourquoi un changement de comportement ???

Je joins le script de création de l'index...
Code:

1 2 3 4 create index IX_TT_F_ELM_PASSAGE_TP_01 on TT_F_ELM_PASSAGE_TP(DW_SY_PARCOURS, DW_SY_PASSAGE, DW_CO_DA_EFFET_PARCOURS, DW_CO_FAM_CONTENANT) NOLOGGING TABLESPACE HB_ETOIL_IN_INDX01 STORAGE(INITIAL 65536 MINEXTENTS 1 MAXEXTENTS 2147483645 PCTINCREASE 0) INITRANS 2 MAXTRANS 255 PCTFREE 0 PARALLEL (DEGREE 1)

Citation:

Envoyé par mnitu

En 9I je ne pense pas que c'est vrai :)

Je ne raconte pas toujours n'importe quoi ;)

http://download.oracle.com/docs/cd/B...mops.htm#38218

Citation:

CHOOSE

The optimizer chooses between a cost-based approach and a rule-based approach, depending on whether statistics are available. This is the default value.

* If the data dictionary contains statistics for at least one of the accessed tables, then the optimizer uses a cost-based approach and optimizes with a goal of best throughput.
* If the data dictionary contains only some statistics, then the cost-based approach is still used, but the optimizer must guess the statistics for the subjects without any statistics. This can result in suboptimal execution plans.
* If the data dictionary contains no statistics for any of the accessed tables, then the optimizer uses a rule-based approach.

CHOOSE sans stats = RULE... sauf éventuellement avec l'autosampling mais j'en suis même pas sûr ;)

Edit : argh... grillé :aie:

Robinson8171 :arrow: t'as vu comme des balises CODE rendent ton message plus lisible ? :)

Et j'insiste, l'optimiseur utilise les règles donc l'ordre des tables est important... je ne saurais trop te conseiller de calculer les stats :roll:

Pour orafrance (je l'ai déjà dit) :hola:

Pourriez-vous faire une trace sql dans les deux cas ?

1° Pardon pour les balises "code" : je débute... La prochaine fois...

2° revenons à nos moutons : en inversant l'ordre des 2 tables, le plan d'exécution est le même (avec les balises "code" :D)
Code:

1 2 3 4 5 6 7 8 Operation Object Name Rows Bytes Cost Object Node In/Out PStart PStop SELECT STATEMENT Optimizer Mode=CHOOSE 41 24 SORT ORDER BY 41 3 K 24 NESTED LOOPS 41 3 K 22 TABLE ACCESS FULL HB_ETOIL_IN.TT_F_ELM_PASSAGE_TP 82 3 K 2 PARTITION RANGE ITERATOR KEY KEY INDEX FAST FULL SCAN HB_ETOIL_IN.IX_DWR_F_ELM_PASSAGE_TP_PK 1 43 4 KEY KEY
3° Admettons qu'il soit préférable de calculer les stats des tables, quid si les stats ne sont pas à jour (et qu'il ne soit pas possible que les stats soient à jour au moment de la requête).
Quelle est le mieux adapté ? pas d'analyse ? des analyses incorrectes ?

j'ai pas vu la requête SQL que tu exécutes :roll: le hint INDEX t'aidera surement :)

3°) vaut mieux des stats à jour :mouarf:

sachant que les stats n'ont pas forcément besoin d'être à jour à la ligne prêt... un ESTIMATE fonctionne déjà très bien :)

pour info : http://www.oracle.com/technology/pro...ily/Aug17.html

Voici la requête : elle est toute "simple", pas de hint, rien.
D'ailleurs, avec un hint "INDEX", le temps de réponse n'est pas meilleure.
une jointure entre 2 tables :
- DWR_F_ELM_PASSAGE_TP (partitionnée DW_CO_DA_EFFET_PARCOURS de la clé) sur le champ avec 15 millions de lignes et un index sur la clé de jointure
- TT_F_ELM_PASSAGE_TP avec 1 million de lignes et un index sur la clé de jointure
Aucune des tables n'a d'analyse
Code:

1 2 3 4 5 6 7 8 9 10 11 12 13 14 SELECT TT_F_ELM_PASSAGE_TP.DW_SY_PARCOURS, TT_F_ELM_PASSAGE_TP.DW_SY_PASSAGE, TT_F_ELM_PASSAGE_TP.DW_CO_DA_EFFET_PARCOURS, TT_F_ELM_PASSAGE_TP.DW_CO_FAM_CONTENANT FROM DWR_F_ELM_PASSAGE_TP, TT_F_ELM_PASSAGE_TP WHERE DWR_F_ELM_PASSAGE_TP.DW_SY_PARCOURS =TT_F_ELM_PASSAGE_TP.DW_SY_PARCOURS AND DWR_F_ELM_PASSAGE_TP.DW_SY_PASSAGE = TT_F_ELM_PASSAGE_TP.DW_SY_PASSAGE AND DWR_F_ELM_PASSAGE_TP.DW_CO_DA_EFFET_PARCOURS = TT_F_ELM_PASSAGE_TP.DW_CO_DA_EFFET_PARCOURS AND DWR_F_ELM_PASSAGE_TP.DW_CO_FAM_CONTENANT = TT_F_ELM_PASSAGE_TP.DW_CO_FAM_CONTENANT ORDER BY TT_F_ELM_PASSAGE_TP.DW_SY_PARCOURS, TT_F_ELM_PASSAGE_TP.DW_SY_PASSAGE, TT_F_ELM_PASSAGE_TP.DW_CO_DA_EFFET_PARCOURS, TT_F_ELM_PASSAGE_TP.DW_CO_FAM_CONTENANT
Mon problème concernant les analyses est que cela coûte en temps. Il est également problématique de les calculer lorsqu'on charge les tables avec un ETL (nous sommes dans un entrepôt).
Je cherche donc le meilleur compromis pour améliorer les performances : c'est pour ça que j'étudie la solution sans analyse (et j'ai bien dit "étudier").

D'abord vous utilisez le CBO! :mouarf:
Le chemin d'accès INDEX FAST FULL SCAN n'est pas disponible pour le RBO

Citation:

Oracle 9.2 performance tunning guide
...
Fast full scan is available only with the CBO ...

Très probablement vous avez analysé les tables une fois dans le passé et ensuite vous n'avez pas supprimé les statistiques (examinez la colonne last_analyzed dans dba_tables ou dba_indexes pour les objets concernés). Donc vous est dans la pire de situations.:aie:

Citation:

Mon problème concernant les analyses est que cela coûte en temps. Il est également problématique de les calculer lorsqu'on charge les tables avec un ETL (nous sommes dans un entrepôt).
Je cherche donc le meilleur compromis pour améliorer les performances : c'est pour ça que j'étudie la solution sans analyse (et j'ai bien dit "étudier").

Si vous trouvez que le RBO vous suffit mettez optimiser goal = RULE. :yaisse2:Ça marche y compris pour ORACLE10. Si vous voulez utiliser CBO vous n'avez pas besoin forcement d'analyser les tables, indexes ,etc. chaque fois que vous chargez les tables avec ETL. L'idée est que si vous connaissez les bons «*statistiques*» vous pouvez le charger dans les tables sans faire l'analyse.

Citation:

Envoyé par Robinson8171

D'ailleurs, avec un hint "INDEX", le temps de réponse n'est pas meilleure.
une jointure entre 2 tables :

voila pourquoi l'index n'est pas utilisé... il n'est pas intéressant pour ta requête :?