Discussion :

[skulled]

Bonjour,

Je souhaiterais savoir si il est possible de créer un index sur le champs "Date" d'une table mais précisément sur le "trunc(CHAMP_DATE) ".
Car mes requetes sont souvent portées sur le trunc de la date.

Exemple :

Code :

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

SELECT * FROM CLIENT WHERE trunc(CLIENT_DATE) <= trunc(sysdate-1)

Merci

[McM]

Code :

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

CREATE INDEX client_date ON CLIENT (TRUNC(CLIENT_DATE))

[ORA-007]

Bonjour,

Oui c'est possible

Code :

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

CREATE INDEX INDX_CLIENT_xx ON CLIENT (TRUNC(CLIENT_DATE));

Puis n’oublie pas de calculer les stats après :

Code :

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

1
2
3
4
5
6
7
8
9
 
BEGIN
    DBMS_STATS.gather_table_stats(tabname => 'CLIENT'
            ,ownname => <le owner de ta table>
            ,cascade => TRUE
            ,estimate_percent => 25
    );
end;
/

[Mohamed.Houri]

Puis n'oubli pas de calculer les stats apres :

Code :

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

1
2
3
4
5
6
7
8
9
 
BEGIN
    DBMS_STATS.gather_table_stats(tabname => 'CLIENT'
            ,ownname => <le owner de ta table>
            ,cascade => TRUE
            ,estimate_percent => 25
    );
end;
/

Certainement, mais comme vous le savez peut-être, un index de ce type est caractérisé par une colonne cachée et donc il va falloir bien faire attention à ce que le CBO connaisse les statistiques sur cette colonne cachée

Code :

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

1
2
3
4
5
6
7
BEGIN
dbms_stats.gather_table_stats(ownname=> user
                                        , tabname=> 'T'
                                       , estimate_percent=>null
                                        , cascade=>true
                                        , method_opt=> 'FOR ALL HIDDEN COLUMNS SIZE 1');

[Mohamed.Houri]

Code :

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

CREATE INDEX client_date ON CLIENT (TRUNC(CLIENT_DATE))

Et n'oubliez pas une chose aussi:

Pour que votre requête puisse bénéficier de ce nouvel index il faut que la clause where coincide parfaitement avec la définition de cet index

Code :

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

1
2
3
4
 
select a, b,c
from t
where trunc(client_date) = to_date('20092011','ddmmrrrrr');

[skulled]

Merci à tous ça fonctionne, j'ai testé en lançant un plan d'éxecution et je vois bien que l'index est utilisé

Et n'oubliez pas une chose aussi:

Pour que votre requête puisse bénéficier de ce nouvel index il faut que la clause where coincide parfaitement avec la définition de cet index

Code :

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

1
2
3
4
 
select a, b,c
from t
where trunc(client_date) = to_date('20092011','ddmmrrrrr');

Merci pour l'info!

[ORA-007]

Certainement, mais comme vous le savez peut-être, un index de ce type est caractérisé par une colonne cachée et donc il va falloir bien faire attention à ce que le CBO connaisse les statistiques sur cette colonne cachée

Alors il me semble que par defaut il le fait :

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
CREATE TABLE TEST_GREG (
ID_TEST NUMBER,
CHAMP1  VARCHAR2(50),
CHAMP2  VARCHAR2(50),
CHAMP3  VARCHAR2(50),
CHAMP4  VARCHAR2(50)
);
 
 
INSERT INTO TEST_GREG
VALUES (1,'A','A','A','A');
INSERT INTO TEST_GREG
VALUES (2,'A','A','A','B');
INSERT INTO TEST_GREG
VALUES (3,'A','A','B','B');
INSERT INTO TEST_GREG
VALUES (4,'A','B','B','B');
INSERT INTO TEST_GREG
VALUES (5,'A','B','B','B');
 
COMMIT;
 
CREATE INDEX INDX_TEST_GREG_01 ON TEST_GREG(UPPER(champ1));
 
select column_name, num_distinct, hidden_column, virtual_column 
from all_tab_cols
where table_name='TEST_GREG';

Donne :

Code :

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

1
2
3
4
5
6
7
8
 
   	COLUMN_NAME	NUM_DISTINCT	HIDDEN_COLUMN	VIRTUAL_COLUMN
	ID_TEST		                                       NO	          NO
	CHAMP1		                                       NO	          NO
	CHAMP2		                                       NO	          NO
	CHAMP3		                                       NO	          NO
	CHAMP4		                                       NO	          NO
	SYS_NC00006$		                               YES	          YES

Puis :

Code :

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

1
2
3
4
5
6
7
8
9
10
11
12
BEGIN
    DBMS_STATS.gather_table_stats(tabname => 'TEST_GREG'
            ,ownname => 'TRANSCO'
            ,cascade => TRUE
            ,estimate_percent => 25
    );
end;
/
 
select column_name, num_distinct, hidden_column, virtual_column 
from all_tab_cols
where table_name='TEST_GREG';

Donne :

Code :

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

1
2
3
4
5
6
7
8
 
   	COLUMN_NAME	NUM_DISTINCT	HIDDEN_COLUMN	VIRTUAL_COLUMN
	ID_TEST		           5                            NO	          NO
	CHAMP1		           1                            NO	          NO
	CHAMP2		           2                            NO	          NO
	CHAMP3		           2                            NO	          NO
	CHAMP4		           2                            NO	          NO
	SYS_NC00006$		   1                            YES	          YES

[laurentschneider]

ce n'est pas la bonne approche

au lieu de

Code :

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

1
2
create index i on client(trunc(CLIENT_DATE));
SELECT * FROM CLIENT WHERE trunc(CLIENT_DATE) <= trunc(sysdate-1);

il est beaucoup plus simple, naturel et performant de faire

Code :

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

1
2
create index i on client(CLIENT_DATE);
SELECT * FROM CLIENT WHERE CLIENT_DATE < trunc(sysdate);

[McM]

C'est pour l'égalité où ça ne marche pas ton exemple.

[laurentschneider]

L'égalité de "trunc(date)=hier" est un fait une période entre hier matin et hier soir

Code :

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

SELECT * FROM CLIENT WHERE CLIENT_DATE < trunc(sysdate) and CLIENT_DATE >= trunc(sysdate-1) ;

[McM]

Tu avoueras que pour ce cas, c'est moins intuitif comme code.

[laurentschneider]

même sous la torture je n'avouerai rien du tout

ne pas utiliser < et > quand on a un index sur la date est un manque de logique !

[McM]

non.. toujours pas ?

Bon, ça devient vite l'enfer si tu dois coder des IN avec plusieurs dates..



allez, un dernier pour la route :

[Waldar]

ne pas utiliser < et > quand on a un index sur la date est un manque de logique !

Manque de logique ou manque de pratique ?

[Mohamed.Houri]

même sous la torture je n'avouerai rien du tout

ne pas utiliser < et > quand on a un index sur la date est un manque de logique !

Si je suis votre logique, quelque soit la situation dans laquelle on se trouve, avoir :

1. un "Btree-index" sur une date
2. un ''Function-based'' index sur une date
3. un index partitionné sur une date
4. un bitmap index sur une date
5. etc.... sur une date

si dans cette situation nos requêtes sur cette date n'utilisent pas l'opérateur < ou l'opérateur > alors notre application(ou ceux qui l'ont conçue) manque de logique

C'est cela que vous voulez exprimez?

[laurentschneider]

Mohamed,

Oui, c'est ça le point, si celui qui a conçu l'application a fait du TRUNC(d) sans remarquer que le TRUNC(d) n'employait pas l'index sur la date, alors dans ce cas on pourrait faire un function-based-index sur le trunc.

Cependant si l'on écrit soit même l'application, alors pas de trunc et pas de fbi.

@McM : l'enfer c'est de devoir tuner avec des fbi des requetes qui durent des heures parceque les developpeurs ont eu la paresse d'écrire leur IN en BETWEEN

PS: un bitmap index sur une date sûrement pas! à moins que la date soit toujours le 14 juillet 1789 ou le 1 aout 1291