Completly different execution plans select vs. update with same where clause
Date: Thu, 27 Mar 2008 15:49:05 +0100
Message-Id: <200803271549.05405.mseiwert@hbv.de>
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
-- http://www.freelists.org/webpage/oracle-lReceived on Thu Mar 27 2008 - 09:49:05 CDT