Créer un index sur un trunc date

Version imprimable

20/09/2011, 15h41
skulled

Créer un index sur un trunc date

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:

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

Merci
20/09/2011, 15h50
McM

Code:

CREATE INDEX client_date ON CLIENT (TRUNC(CLIENT_DATE))

Bonjour,

Oui c'est possible :ccool:

Code:

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

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

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; /

Citation:
Envoyé par ORA-007
Puis n'oubli pas de calculer les stats apres :
Code:

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:

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');

20/09/2011, 16h24
Mohamed.Houri
Citation:

Envoyé par McM

Code:

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:

1 2 3 4 select a, b,c from t where trunc(client_date) = to_date('20092011','ddmmrrrrr');
20/09/2011, 16h30
skulled
Merci à tous ça fonctionne, j'ai testé en lançant un plan d'éxecution et je vois bien que l'index est utilisé :)
Citation:
Envoyé par Mohamed.Houri

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:

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

Citation:

Envoyé par Mohamed.Houri

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:

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:

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:

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:

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

ce n'est pas la bonne approche

au lieu de
Code:

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:

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

22/09/2011, 11h47
McM

C'est pour l'égalité où ça ne marche pas ton exemple.
22/09/2011, 13h34
laurentschneider

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

Code:

SELECT * FROM CLIENT WHERE CLIENT_DATE < trunc(sysdate) and CLIENT_DATE >= trunc(sysdate-1) ;
22/09/2011, 13h50
McM

Tu avoueras que pour ce cas, c'est moins intuitif comme code.
22/09/2011, 17h16
laurentschneider

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

ne pas utiliser < et > quand on a un index sur la date est un manque de logique !
22/09/2011, 17h37
McM

:sm::fessee::sm::scarymov::sm::sm:

non.. toujours pas ?

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

allez, un dernier pour la route : :sm:
22/09/2011, 18h35
Waldar

Citation:

Envoyé par laurentschneider

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

Manque de logique ou manque de pratique ? ;)
23/09/2011, 09h00
Mohamed.Houri
Citation:

Envoyé par laurentschneider

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

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?
23/09/2011, 11h20
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 :mrgreen:

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