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