Sorry for the previous mail, I've just realized the original mail had a date
from year 2009 though it was sent on December 11th, causing it to be in the
top of my inbox after several days away.
Anyway, if you still need this, you can modify the last part of your query:
from
AND
TO_NUMBER(TO_CHAR(RUBRIQUE_QUANTITE_JOURNALIERE.QTEJR_GASDAY,'YYYY')) =
2006)
to
AND
RUBRIQUE_QUANTITE_JOURNALIERE.QTEJR_GASDAY between TO_DATE('2006/01/01
00:00:00','YYYY/MM/DD HH24:MI:SS') and TO_DATE('2006/12/31
23:59:59','YYYY/MM/DD HH24:MI:SS')
That way you can have index access on your QTEJR_GASDAY field.
Anyway, I believe this would be of insignificant help though you can try.
I would also recommend that you have a single index based on the following fields:
QTEJR_GASDAY, QTEJR_CSVCREATIONDATE, MSG_NUMERO, PROC_TYPE, PTR_CODE,
RUBRQTE_NUMERO (I believe that for better performance QTEJR_GASDAY should be
your first field in the index, and the others can go in any order).
I hope this helps.
Regards,
--Claudio
On Tue, 12 Dec 2006 14:17:46 +0100, David NGUYEN wrote
> hello experts
>
> i am sorry to reply so late cause our Lotus messenger has got a
> problem since yesterday...everything is ok now. so this is the query
> that i run yesterday. any suggestion is wellcome.
>
> david...
>
> SELECT
> V_RUBRQTE_LISTE_RUBRIQUE.RUBRQTE_NUMERO,
> T_LAST_RUB_QTEJ.QTEJR_VALUE_FLOAT,
> RUBRIQUE_QUANTITE_JOURNALIERE.QTEJR_GASDAY,
> V_RUBRQTE_LISTE_RUBRIQUE.LISTE_PARTIES
> FROM
> V_RUBRQTE_LISTE_RUBRIQUE,
> T_LAST_RUB_QTEJ,
> RUBRIQUE_QUANTITE_JOURNALIERE
> WHERE
> (
>
V_RUBRQTE_LISTE_RUBRIQUE.RUBRQTE_NUMERO=RUBRIQUE_QUANTITE_JOURNALIERE.RUBRQTE_NUMERO
) AND ( T_LAST_RUB_QTEJ.MSG_NUMERO=RUBRIQUE_QUANTITE_JOURNALIERE.MSG_NUMERO
and T_LAST_RUB_QTEJ.PROC_TYPE=RUBRIQUE_QUANTITE_JOURNALIERE.PROC_TYPE
and T_LAST_RUB_QTEJ.PTR_CODE=RUBRIQUE_QUANTITE_JOURNALIERE.PTR_CODE and
>
>
T_LAST_RUB_QTEJ.QTEJR_CSVCREATIONDATE=RUBRIQUE_QUANTITE_JOURNALIERE.QTEJR_CSVCREATIONDATE
and T_LAST_RUB_QTEJ.QTEJR_GASDAY=RUBRIQUE_QUANTITE_JOURNALIERE.QTEJR_GASDAY and
>
> T_LAST_RUB_QTEJ.RUBRQTE_NUMERO=RUBRIQUE_QUANTITE_JOURNALIERE.RUBRQTE_NUMERO
) AND ( V_RUBRQTE_LISTE_RUBRIQUE.RUBRQTE_NUMERO IN (12833, 11038, 6653,
3706, 1086, 673, 2477, 1087, 7844, 56, 13, 630, 1757, 1088, 7845, 10, 1200,
23, 39, 1089, 1090, 7846, 666, 667, 1178, 668, 669, 1091, 7847, 19, 738,
35, 51, 1092, 7848, 54, 27, 43, 1044, 1045, 1797, 5821, 1043, 1093, 2485,
2484, 2480, 1798, 1094, 7849, 7264, 7850, 1046, 1858, 1799, 1047, 1048,
1049, 8104, 1097, 2617, 2597, 6284, 2598, 1082, 159, 11, 24, 40, 1201,
3858, 6904, 4021, 1083, 160, 631, 199, 1041, 161, 17, 32, 48, 1179, 200, 1084,
162, 28, 44, 628, 1085, 163, 20, 36, 52, 739, 3877, 6284, 2599, 2600, 2601,
634, 3858, 6904, 200, 673, 638, 1099, 2477, 8025, 8045, 6865, 1757, 857,
1200, 8926, 1800, 1818, 2298, 2417, 560, 1377, 276, 453, 5822, 5805, 7224,
7245, 217, 283, 897, 8624, 494, 495, 496, 4839, 4838, 5502, 1784, 720,
5499, 718, 5957, 6324, 6325, 6440, 6441, 6442, 6443, 6444, 7444, 7445, 7446,
7447, 7448, 7449, 7464, 7804, 7805, 8109, 8110, 8455, 8456, 8457, 8458,
8459, 8460, 8461, 8462, 8463, 8464, 8465, 8565, 8566, 8749, 8753, 8754,
8755, 8756, 8757, 8758, 9292, 9790, 9791, 9848, 9888, 9931, 9933, 10408, 8668,
9077, 9299, 10414, 10550, 7468, 8750, 9081, 9305, 9427, 10415, 8645, 8629,
6471, 6472, 6473, 7479, 8751, 9311, 10416, 6366, 11036, 4124, 4123, 4121,
6402, 4122, 5957, 200, 4117, 3442, 3451, 8667, 8226, 8330, 4121, 3453, 3454,
7806, 8946, 7474, 8567, 8568, 6354, 6355, 6356, 6357, 6358, 6359, 6360,
7480, 7481, 6446, 6452, 9091, 8849, 3462, 6487, 6439, 6437, 9079, 9083,
9066, 8648, 8566, 6324, 11036, 8645, 8565, 6378, 6344, 12032, 10050, 9054,
11820, 11821, 11822, 11812, 11813, 11814, 11815, 11823, 11816, 11817,
11824, 11818, 11819, 738, 8926, 12132, 6325, 7464, 6144, 11834, 11359, 11360,
11361, 11362, 11363, 11364, 11353, 11354, 11355, 11356, 13073, 14, 858,
11940, 11956, 12404, 10568, 6415, 11352) AND
TO_NUMBER(TO_CHAR(RUBRIQUE_QUANTITE_JOURNALIERE.QTEJR_GASDAY,'YYYY')) = 2006)
>
> ---------------------------------------------------------------------
> TO REPLY TO EVERYBODY , PLEASE CLICK REPLY-ALL, NOT JUST REPLY
> To post a dba job: http://jobs.lazydba.com
> To Subscribe : http://www.LazyDBA.com
> To unsubscribe: http://www.lazydba.com/unsubscribe.html
Oracle LazyDBA home page