Bonjour,
J'ai la requete ci-dessous qui passe en mode deferred et je ne comprend pas pourquoi.
Il n'y a aucun index sur le champ INDCLI_CLI_AH_ID, ni INDCLI_CRITERE !
update WA_INDIVIDU_CLIENT_WEBFID
set INDCLI_CLI_AH_ID = (select min(INDCLI_ID) from WA_INDIVIDU_CLIENT_WEBFID T2
where WA_INDIVIDU_CLIENT_WEBFID.INDCLI_PRENOM_NOM = T2.INDCLI_PRENOM_NOM
and WA_INDIVIDU_CLIENT_WEBFID.INDCLI_ADRESSE1 = T2.INDCLI_ADRESSE1
and WA_INDIVIDU_CLIENT_WEBFID.INDCLI_CODE_POSTAL = T2.INDCLI_CODE_POSTAL
and WA_INDIVIDU_CLIENT_WEBFID.INDCLI_ID <> T2.INDCLI_ID),
INDCLI_CRITERE = 1
where exists (select INDCLI_ID from WA_INDIVIDU_CLIENT_WEBFID T2
where WA_INDIVIDU_CLIENT_WEBFID.INDCLI_PRENOM_NOM = T2.INDCLI_PRENOM_NOM
and WA_INDIVIDU_CLIENT_WEBFID.INDCLI_ADRESSE1 = T2.INDCLI_ADRESSE1
and WA_INDIVIDU_CLIENT_WEBFID.INDCLI_CODE_POSTAL = T2.INDCLI_CODE_POSTAL
and isnull(WA_INDIVIDU_CLIENT_WEBFID.INDCLI_PRENOM_NOM ,' ') <> ' '
and isnull(WA_INDIVIDU_CLIENT_WEBFID.INDCLI_ADRESSE1,' ') <> ' '
and isnull(WA_INDIVIDU_CLIENT_WEBFID.INDCLI_CODE_POSTAL,' ') <> ' '
and (WA_INDIVIDU_CLIENT_WEBFID.INDCLI_CLI_AH_ID = 0 or WA_INDIVIDU_CLIENT_WEBFID.INDCLI_CLI_AH_ID > T2.INDCLI_ID)
and WA_INDIVIDU_CLIENT_WEBFID.INDCLI_ID <> T2.INDCLI_ID)
Plan dexécution :
Merci de votre aide.
Warnings: --->
W (1):
W (2): QUERY PLAN FOR STATEMENT 1 (at line 1).
W (3):
W (4):
W (5): STEP 1
W (6): The type of query is UPDATE.
W (7): The update mode is deferred.
W (8):
W (9): FROM TABLE
W (10): WA_INDIVIDU_CLIENT_WEBFID
W (11): Nested iteration.
W (12): Table Scan.
W (13): Forward scan.
W (14): Positioning at start of table.
W (15): Using I/O Size 16 Kbytes for data pages.
W (16): With LRU Buffer Replacement Strategy for data pages.
W (17):
W (18): FROM TABLE
W (19): WA_INDIVIDU_CLIENT_WEBFID
W (20): T2
W (21): EXISTS TABLE : nested iteration.
W (22): Index : idx_npc
W (23): Forward scan.
W (24): Positioning by key.
W (25): Keys are:
W (26): INDCLI_PRENOM_NOM ASC
W (27): INDCLI_ADRESSE1 ASC
W (28): INDCLI_CODE_POSTAL ASC
W (29):
W (30): Run subquery 1 (at nesting level 1).
W (31): Using I/O Size 2 Kbytes for index leaf pages.
W (32): With LRU Buffer Replacement Strategy for index leaf pages.
W (33): Using I/O Size 2 Kbytes for data pages.
W (34): With LRU Buffer Replacement Strategy for data pages.
W (35): TO TABLE
W (36): WA_INDIVIDU_CLIENT_WEBFID
W (37): Using I/O Size 2 Kbytes for data pages.
W (38): STEP 1
W (39):
W (40): NESTING LEVEL 1 SUBQUERIES FOR STATEMENT 1.
W (41):
W (42): QUERY PLAN FOR SUBQUERY 1 (at nesting level 1 and at line 2).
W (43):
W (44): Correlated Subquery.
W (45): Subquery under an EXPRESSION predicate.
W (46):
W (47):
W (48): STEP 1
W (49): The type of query is SELECT.
W (50): Evaluate Ungrouped MINIMUM AGGREGATE.
W (51):
W (52): FROM TABLE
W (53): WA_INDIVIDU_CLIENT_WEBFID
W (54): T2
W (55): Nested iteration.
W (56): Index : idx_npc
W (57): Forward scan.
W (58): Positioning by key.
W (59): Keys are:
W (60): INDCLI_PRENOM_NOM ASC
W (61): INDCLI_ADRESSE1 ASC
W (62): INDCLI_CODE_POSTAL ASC
W (63): Using I/O Size 2 Kbytes for index leaf pages.
W (64): With LRU Buffer Replacement Strategy for index leaf pages.
W (65): Using I/O Size 2 Kbytes for data pages.
W (66): With LRU Buffer Replacement Strategy for data pages.
W (67):
W (68): END OF QUERY PLAN FOR SUBQUERY 1.
W (69):
W (70): [
Partager