Hi, could anybody explain to me why the execution plan of the select statement differs completly from the execution plan of the update statement? While the select statement uses a very good plan using nested loops and highly indexed access the update statement full scans the tables accessed through the view v_titel2 ? Please see attached textfiles for execution plans. If more information is needed I could provide 10046 and 10053 traces. SELECT titel FROM v_titel2 WHERE tiar_id IN (2, 6) AND send_id IN ( (SELECT send_id FROM termine t1 WHERE NVL (format_id, 0) = 6 AND EXISTS (SELECT 'X' FROM imp_termine WHERE ID = t1.ID AND job = 2666))) AND titel LIKE '%/%'; UPDATE v_titel2 SET titel = 'XXX' WHERE tiar_id IN (2, 6) AND send_id IN ( (SELECT send_id FROM termine t1 WHERE NVL (format_id, 0) = 6 AND EXISTS (SELECT 'X' FROM imp_termine WHERE ID = t1.ID AND job = 2666))) AND titel LIKE '%/%'; Thank you in advance for helping. Very best regards, Michael
SQL> explain plan for update v_titel2 set titel = 'XXX' 2 WHERE tiar_id IN (2, 6) 3 AND send_id IN ( 4 (SELECT send_id 5 FROM termine t1 6 WHERE NVL (format_id, 0) = 6 7 AND EXISTS (SELECT 'X' 8 FROM imp_termine 9 WHERE ID = t1.ID AND job = 2666))) 10 AND titel LIKE '%/%'; Explained. SQL> @$ORACLE_HOME/rdbms/admin/utlxplp.sql ------------------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes |TempSpc| Cost | ------------------------------------------------------------------------------------------------- | 0 | UPDATE STATEMENT | | 221 | 449K| | 25355 | | 1 | UPDATE | V_TITEL2 | | | | | |* 2 | HASH JOIN | | 221 | 449K| | 25355 | | 3 | VIEW | VW_NSO_1 | 221 | 2873 | | 68 | | 4 | SORT UNIQUE | | 221 | 5525 | | | | 5 | NESTED LOOPS | | 221 | 5525 | | 68 | |* 6 | INDEX RANGE SCAN | IMP_TERMINE_JOB_IND | 221 | 2431 | | 3 | |* 7 | TABLE ACCESS BY INDEX ROWID| TERMINE | 1 | 14 | | 1 | |* 8 | INDEX UNIQUE SCAN | TERMINE_PK | 1 | | | | | 9 | VIEW | V_TITEL2 | 2377K| 4692M| | 24672 | |* 10 | HASH JOIN | | 2377K| 165M| 5000K| 24672 | |* 11 | TABLE ACCESS FULL | TITELNAMEN | 93054 | 3907K| | 1882 | |* 12 | TABLE ACCESS FULL | TITELINSTANZ | 4560K| 130M| | 19802 | ------------------------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 2 - access("V_TITEL2"."SEND_ID"="VW_NSO_1"."SEND_ID") 6 - access("IMP_TERMINE"."JOB"=2666) 7 - filter(NVL("T1"."FORMAT_ID",0)=6) 8 - access("IMP_TERMINE"."ID"="T1"."ID") 10 - access("A"."TITELNAMEN_ID"="B"."ID") 11 - filter("B"."TITEL" LIKE '%/%') 12 - filter("A"."TIAR_ID"=2 OR "A"."TIAR_ID"=6) Note: cpu costing is off 31 rows selected. SQL> spool off
SQL> explain plan for select titel from v_titel2 2 WHERE tiar_id IN (2, 6) 3 AND send_id IN ( 4 (SELECT send_id 5 FROM termine t1 6 WHERE NVL (format_id, 0) = 6 7 AND EXISTS (SELECT 'X' 8 FROM imp_termine 9 WHERE ID = t1.ID AND job = 2666))) 10 AND titel LIKE '%/%'; Explained. SQL> @$ORACLE_HOME/rdbms/admin/utlxplp.sql ----------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost | ----------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 155 | 11160 | 231 | | 1 | NESTED LOOPS | | 155 | 11160 | 231 | | 2 | NESTED LOOPS | | 297 | 8613 | 142 | | 3 | VIEW | VW_NSO_1 | 221 | 2873 | 68 | | 4 | SORT UNIQUE | | 221 | 5525 | | | 5 | NESTED LOOPS | | 221 | 5525 | 68 | |* 6 | INDEX RANGE SCAN | IMP_TERMINE_JOB_IND | 221 | 2431 | 3 | |* 7 | TABLE ACCESS BY INDEX ROWID| TERMINE | 1 | 14 | 1 | |* 8 | INDEX UNIQUE SCAN | TERMINE_PK | 1 | | | | 9 | INLIST ITERATOR | | | | | |* 10 | INDEX RANGE SCAN | TITELINSTANZ_CK2 | 1 | 16 | 1 | |* 11 | TABLE ACCESS BY INDEX ROWID | TITELNAMEN | 1 | 43 | 1 | |* 12 | INDEX UNIQUE SCAN | TITELNAMEN_PK | 1 | | | ----------------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 6 - access("IMP_TERMINE"."JOB"=2666) 7 - filter(NVL("T1"."FORMAT_ID",0)=6) 8 - access("IMP_TERMINE"."ID"="T1"."ID") 10 - access("A"."SEND_ID"="VW_NSO_1"."SEND_ID" AND ("A"."TIAR_ID"=2 OR "A"."TIAR_ID"=6)) 11 - filter("B"."TITEL" LIKE '%/%') 12 - access("A"."TITELNAMEN_ID"="B"."ID") Note: cpu costing is off 31 rows selected. SQL> spool off