Créer un index sur un trunc date [Résolu]

[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 :

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

Merci

[McM]

Code :

CREATE INDEX client_date ON CLIENT (TRUNC(CLIENT_DATE))

[ORA-007]

Bonjour,

Oui c'est possible

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

[Mohamed.Houri]

Envoyé par ORA-007

Puis n'oubli pas de calculer les stats apres :

Code :

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

[Mohamed.Houri]

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

[skulled]

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

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 :

Merci pour l'info!

[ORA-007]

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

[laurentschneider]

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

[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 :

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]

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 ?

[Mohamed.Houri]

Envoyé par 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 !

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