Discussion :

[nosnoss]

Bonjour,

Je voulais savoir si un order by tirait parti des indexes ?

En effet j'ai une requête avec un order by sur 2 colonnes et elle mets longtemps à s'executer avec ou sans index composé sur ces colonnes.

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
 
create index on toto
(champ1 ASC, champ2 ASC);
 
select * from toto order by champ1 ASC, champ2 ASC;

toto comporte bcp de lignes.

Comment faire en sorte que ma requête bénéficie de l'index ?

Merci
Oracle 8i.
toto = à peu près 200 000 lignes

[orafrance]

absolument aucun intérêt puisqu'avec ou sans indexes tu fais un FULL SCAN : il n'y a pas de clause restrictive

une index (comme dans une encyclopédie) sert à trouver une ligne plus rapidement... si tu sélectionnes tout, l'index n'a aucun intérêt, c'est évident.

En revanche si tu as de la mémoire dispo tu peux augmenter la taille de la zone de tri :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

alter session set sort_area_size = xMo;

[nosnoss]

Pourtant n'est il pas bizarre qu'oracle ne se servent pas de l'index pour trieer ? L'index donne déjà l'ordre des lignes, Oracle n'aurait plus qu'à suivent l'index et ainsi ordonner plus rapidement que s'il déclenche sa procédure de tri (meme s'il s'agit d'un tri fusion).

Il me semble en recherchant sur le net que d'autre base de données se servant des index (sous condition qu'ils soient bien créés) pour faire les opérations de tri.

Rien du côté d'oracle ?
Merci

[orafrance]

et à si tu ajoutes un enregistrement qui tombe en plein milieu de la liste tu réorganises l'index ? Ca me parait pas très sérieux comme solution

L'index ne donne pas d'ordre, il donne une adresse sur le disque

[aline]

Tu peux hinter pour utiliser l'index.
pour hinter, il faux qu'il y est une clause restrictive. Tu peux t'en créer une.
Par exemple, champ1 < max (champ1)

ce qui donne:

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
 
 
create index toto_indx on toto
(champ1 ASC, champ2 ASC); 
 
select /*+INDEX( toto_indx toto)*/
 * from toto 
where champ1 < nbrmax
order by champ1 ASC, champ2 ASC;

ainsi tu devrais éviter le tri

[nosnoss]

Merci, je vais me documenter sur les HINTS car je ne les ai jamais utilisés

[jokos2000]

Bonjour,

A titre informatif, qu'en est-il des index triés :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
 
CREATE INDEX nom_index ON nom_table ASC | DESC

Merci pour vos réponse.

Cordialement

[aline]

Bonjour,

A titre informatif, qu'en est-il des index triés :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
 
CREATE INDEX nom_index ON nom_table ASC | DESC

Merci pour vos réponse.

Cordialement

Un index est forcément trié!

[jokos2000]

Qu'en est-il de ce que dit ORAFRANCE alors ?

L'index ne donne pas d'ordre, il donne une adresse sur le disque

[Jaouad]

Lors de la création d'un index , un tri est forcément crée lors de la création de cette index .
notamment lors d'un index b-tree qui ressemble à un arbre avec plusieurs branches

[orafrance]

exactement

c'est à la création de l'index qu'il y a un tri mais c'est tout.

Imagine toi avec une table de 60 millions d'enregistrements ordonnés, par exemple, la liste des français

Tu as une naissance que tu dois ajouter dans la table... tu imagines le temps que ça prendrait si l'index était réorganisé complétement pour réordonner l'ensemble... impossible évidemment

Est-ce que c'est plus clair ?

[jokos2000]

Je suis d'accord avec cela, mais en regardant sur metalink, j'ai lu que l'on pouvait utilisé un index déjà trié dans l'ordre de l'order by et que cela est plus rapide qu'un sort puis FTS :

In certain circumstances it is possible for the whole index to be scanned as opposed to a range scan (i.e. where no constraining predicates are provided fora table).

Full index scans are only available in the CBO as otherwise we are unable to determine whether a full scan would be a good idea or not.

We choose an index Full Scan when we have statistics that indicate that it is going to be more efficient than a Full table scan and a sort.

For example we may do a Full index scan when we do an unbounded scan of an index and want the data to be ordered in the index order.

The optimizer may decide that selecting all the information from the index
and not sorting is more efficient than doing a FTS or a Fast Full Index Scan and then sorting.

An Index full scan will perform single block i/o's and so it may prove to be
inefficient.

e.g.

Index BE_IX is a concatenated index on big_emp (empno,ename)

SQL> explain plan for

select empno,ename from big_emp order by empno,ename;

Query Plan

--------------------------------------------------------------------------------

SELECT STATEMENT [CHOOSE] Cost=26

INDEX FULL SCAN BE_IX [ANALYZED]

Est-ce que cela signifie que si l'on crée cet index :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
 create index toto_indx on toto
(champ1 , champ2) ;

Et que l'on exécute cette requête :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
 select  * from toto
where champ1 < nbrmax
order by champ1 , champ2;

Il devrait utiliser l'index sans faire de sort ?

Merci pour votre réponse.

Cordialement,

[aline]

Est-ce que cela signifie que si l'on crée cet index :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
 create index toto_indx on toto
(champ1 , champ2) ;

Et que l'on exécute cette requête :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
 select  * from toto
where champ1 < nbrmax
order by champ1 , champ2;

Il devrait utiliser l'index sans faire de sort ?

Merci pour votre réponse.

Cordialement,

cela m'étonnerait car ce que Oracle verra, c'est qu'il aura plus de blocs physique à lire.
En effet, il aura à lire tout les blocs de la table + tout les blocs de l'index.
en gros, soit tu payes du cpu, soit tu payes des io.

Mais tu peux le forcer comme je te le disais avec le la condition toujours vraie + le hint.

[orafrance]

Attention, comme le dit aline l'index c'est pas le truc magique qui va tout changer... en plus, dans la note il n'est pas question de supprimer l'order by ou j'ai raté un épisode

le SORT ne peut être éviter (je pense ) qu'en créant l'index JUSTE avant le SELECT mais dans ce cas tu fais le SORT lors de la création de l'index et finalement ça ne comporte aucun intérêt.

Ce que dit ta doc c'est que si tu sélectionnes les colonnes d'une index, l'accés aux blocs de la table n'est pas obligatoirement nécessaire. Tu fais donc un INDEX SCAN au lieu d'un TABLE SCAN, ce qui est intéressant uniquement sur des petits échantillons et en aucun cas sur la totalité de la table

[lalystar]

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
 
create index on toto 
(champ1 ASC, champ2 ASC); 
 
select * from toto order by champ1 ASC, champ2 ASC;

Etant donné que tu fais un SELECT *, Oracle est forcé d'aller à chaque ligne de la table si ta table a plus de colonnes que champ1 et champ2.

Si ta requête était :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
 
select champ1, champ2 from toto order by champ1 ASC, champ2 ASC;

Ca pourrait être intéressant pour Oracle de ne parcourir que l'index.

En plus, on ne sait même pas si champ1 et/ou champ2 est NOT NULL ?
Sinon, les lignes pour lesquelles (champ1, champ2) = (null, null) ne seront pas dans l'index. Et donc Oracle est forcé d'aller à la table, tu auras beau mettre les hints pour forcer Oracle à utiliser l'index, il ne le prendra pas.


Laly.

[Jaouad]

Si l'index comporte toutes les colonnes demandés dans le select , dans ce cas la il y a pas d'accés à la table en plus de l'index , ce qui peut être avantageux si les blocs ne sont pas en mémoire
notamment si tu as un range scan et que les données sont eparpillés sur bcp de blocs différents

[orafrance]

autant dire très rarement

[jokos2000]

Merci pour vos réponses

En fait, mon erreur vient effectivement du fait que je prend * et non :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
 
select  [b]champ1, champ2[/b] from toto
where champ1 < nbrmax
order by champ1 , champ2;

Ce dernier serait plus rapide avec l'index ...

merci à vous

[aline]

En fait, un select * sur la table entière peux être plus efficace en utilisant un index quand même.

select champ1, chmamp2 est évident mais le * est pas mal quand même dans certains cas. Tu echanges de la cpu contre des io. Si tu est court en cpu et pas en io, ou si tu as énormément de données, utiliser l'index pourra être plus efficace. Il permetttra même de commencer à traiter tes données alors que Oracle n'a pas ini de les récupérer ce qui est un plus.