Exécution plus longue avec une condition supplémentaire
Bonjour,
Lorsque je lance cette requete :
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
28
29
| select o.ord_id,
l.orl_id,
o.odo_code,
nvl(l.rcp_company, o.rcp_company) as rcp_company,
nvl(l.rcp_contact, o.rcp_contact) as rcp_contact,
o.ord_pricettc / 10000 as ord_pricettc,
o.ord_reliquat / 10000 as ord_reliquat,
abs(l.orl_qtytodel) as orl_qtytodel,
abs(l.orl_qty) as orl_qty,
l.orl_detail as prd_title,
o.sta_invcode,
l.sta_code,
decode(l.sta_code, 'V830', 'A envoyer', 'Commande à éditer') libStatut,
a.nfr_id,
ld.dlo_code,
nvl(l.oad_id, o.oad_id) as oad_id,
o.oad_invid
from ctb_order o
inner join ctb_ordline l
on (l.ord_id = o.ord_id)
inner join ctb_ordaddress a
on (l.oad_id = a.oad_id )
left join ctb_ordldelivery ld
on (ld.ord_id = l.ord_id and ld.orl_id = l.orl_id and
ld.sta_code < 'Z999')
where l.sta_code >= 'O500'
and l.sta_code < 'V840'
and l.del_code = 'REIM'
and l.orl_canceldt is null |
Elle s'exécute assez rapidement, environ 0,5 sec
Voici sont plan d'exécution
Code:
1
2
3
4
5
6
7
8
9
10
11
12
13
| SELECT STATEMENT, GOAL = ALL_ROWS 1049 120 25080
NESTED LOOPS
NESTED LOOPS 1049 120 25080
NESTED LOOPS OUTER 689 120 20040
NESTED LOOPS 404 120 17040
TABLE ACCESS BY INDEX ROWID CCE3 CTB_ORDLINE 164 120 15600
INDEX RANGE SCAN CCE3 IDX$$_60380001 5 251
TABLE ACCESS BY INDEX ROWID CCE3 CTB_ORDADDRESS 2 1 12
INDEX UNIQUE SCAN CCE3 PK_CTB_ORDADDRESS 1 1
TABLE ACCESS BY INDEX ROWID CCE3 CTB_ORDLDELIVERY 3 1 25
INDEX RANGE SCAN CCE3 PK_CTB_ORDLDELIVERY 2 1
INDEX RANGE SCAN CCE3 PK_CTB_ORDER 2 1
TABLE ACCESS BY INDEX ROWID CCE3 CTB_ORDER 3 1 42 |
par contre ce que je n'explique pas c'est lorsque je rajoute un filtre elle s'exécute en 60 sec...
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
28
29
30
| select o.ord_id,
l.orl_id,
o.odo_code,
nvl(l.rcp_company, o.rcp_company) as rcp_company,
nvl(l.rcp_contact, o.rcp_contact) as rcp_contact,
o.ord_pricettc / 10000 as ord_pricettc,
o.ord_reliquat / 10000 as ord_reliquat,
abs(l.orl_qtytodel) as orl_qtytodel,
abs(l.orl_qty) as orl_qty,
l.orl_detail as prd_title,
o.sta_invcode,
l.sta_code,
decode(l.sta_code, 'V830', 'A envoyer', 'Commande à éditer') libStatut,
a.nfr_id,
ld.dlo_code,
nvl(l.oad_id, o.oad_id) as oad_id,
o.oad_invid
from ctb_order o
inner join ctb_ordline l
on (l.ord_id = o.ord_id)
inner join ctb_ordaddress a
on (l.oad_id = a.oad_id )
left join ctb_ordldelivery ld
on (ld.ord_id = l.ord_id and ld.orl_id = l.orl_id and
ld.sta_code < 'Z999')
where l.sta_code >= 'O500'
and l.sta_code < 'V840'
and l.del_code = 'REIM'
and l.orl_canceldt is null
and a.nfr_id=3159893 |
Pour il y a bien un index sur NFR_ID.
Voici le plan d'exécution de cette requete :
Code:
1
2
3
4
5
6
7
8
9
10
11
12
| SELECT STATEMENT, GOAL = ALL_ROWS 18 1 209
NESTED LOOPS
NESTED LOOPS 18 1 209
NESTED LOOPS OUTER 15 1 167
NESTED LOOPS 12 1 142
INDEX RANGE SCAN CCE3 CEFKOAD_HAV_NFR2 3 3 36
TABLE ACCESS BY INDEX ROWID CCE3 CTB_ORDLINE 4 1 130
INDEX RANGE SCAN CCE3 IDX$$_55D80001 2 2
TABLE ACCESS BY INDEX ROWID CCE3 CTB_ORDLDELIVERY 3 1 25
INDEX RANGE SCAN CCE3 PK_CTB_ORDLDELIVERY 2 1
INDEX RANGE SCAN CCE3 PK_CTB_ORDER 2 1
TABLE ACCESS BY INDEX ROWID CCE3 CTB_ORDER 3 1 42 |
Le cout est vraiment réduit pourtant.
J'ai reconstruit les index mais rien n'a changé.
Auriez-vous une idée? :calim2:
Merci d'avance.
