-
1. Re: Update small table with slow subselect
alter session set sql_trace=true;
rerun;
tkprof the output
paste it here
-
2. Re: Update small table with slow subselect
>I could use PL/SQL with a loop for the select and inside the update, this should speed up from hours to minutes.
PL/SQL is rarely faster than plain SQL
-
3. Re: Update small table with slow subselect
Clearly it's not an update issue, but a view issue.
What is the cost of the view Select?
Did you check the execution plan?
Is it using indexes?
Think like this, for each row in your update table an select on the view is ran, if it takes 7 min in order to run the view once, 3 - 8 hours you will have when you multiply 7x84.
One thing that come across is, you are using only one field of your view. Isn't simple to just select direct the table that you want that result, with the view's where clause?
-
4. Re: Update small table with slow subselect
do you have to use the view? do you have index on MONAT and DL on the view/table?
-
5. Re: Update small table with slow subselect
Igor Laguardia-Oracle wrote:
Clearly it's not an update issue, but a view issue.
Clearly you cant say that. If there was an on update trigger on the table that wouldnt show up in the autotrace but would in the sql trace.
-- create a 9 row table
create table t_up as select * from all_objects where rownum < 10;
-- create a 150k row table
create table t_up2 as select * from all_objects;
-- every time I update t_up, do something silly like insert 150k rows to t_up2
create or replace trigger trig_up before update on t_up
for each row
BEGIN
insert into t_up2
(select * from all_objects);
end;
-- now update t_up, 9 rows get updated but it takes 19 seconds
16:31:43 SQL> update t_up set object_name = 'BOO';
9 rows updated.
Elapsed: 00:00:19.66
Execution Plan
----------------------------------------------------------
Plan hash value: 1638310970
---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | UPDATE STATEMENT | | 9 | 153 | 3 (0)| 00:00:01 |
| 1 | UPDATE | T_UP | | | | |
| 2 | TABLE ACCESS FULL| T_UP | 9 | 153 | 3 (0)| 00:00:01 |
---------------------------------------------------------------------------
Note
-----
- dynamic sampling used for this statement (level=2)
Statistics
----------------------------------------------------------
1008 recursive calls
77183 db block gets
2311727 consistent gets
0 physical reads
72372940 redo size
844 bytes sent via SQL*Net to client
792 bytes received via SQL*Net from client
3 SQL*Net roundtrips to/from client
16201 sorts (memory)
0 sorts (disk)
9 rows processed
16:32:10 SQL>
Still think its clearly the view thats the problem? Now, it may be the view, but you do not know that without the trace
(sorry for the format of above, I dont have buttons for coding)
-
6. Re: Update small table with slow subselect
Would you bet your monthly salary in a trigger, or in a 7min runtime view that is used in the update? Think that clearly you got my point.
-
7. Re: Update small table with slow subselect
Hi,
Can you post the plan for it once executing this query, does any indexes exists on those segments / (view - related segments)
select t.row_id,FCR7*100
from SMALL_TABLE T,
SLOW_VIEW D
where D.MONAT = T.MONAT
AND D.DL = T.DL
- Pavan Kumar N
-
8. Re: Update small table with slow subselect
Igor Laguardia-Oracle wrote:
Would you bet your monthly salary in a trigger, or in a 7min runtime view that is used in the update? Think that clearly you got my point.
I wouldnt gamble it is or it isnt until I seen the trace. then I would know what Im gambling on.
-
9. Re: Update small table with slow subselect
1 person found this helpfulFrom the look of the plan you are calculating the results of the view for each row in small table. Operation 4 in the plan seems to suggest that there is some sort of ordering inside the view which is preventing the view from being merged which may help your plan.
Could you post the definition of the view?
To prevent the view from being executed for each row you could do something like:
create table slow_view_t as select monat ,dl ,fcr7*100 fc7 from slow_view; create unique index slow_view_t_idx on slow_view_t (monat, dl); UPDATE (SELECT t.fcr7 old_value ,d.fcr7 new_value from SMALL_TABLE T JOIN slow_view_t_idx d on D.MONAT = T.MONAT and D.DL = T.DL ) SET old_value = new_value;
But this could cause inconsistent results if the data that slow_view uses is updated between the create table starting and the update.
There also may be a much better optimization that could be found if we looked at the definition of the view.
-
10. Re: Update small table with slow subselect
I did not find an answer to my question: Is there a more elegant way for this update? Optimizer Hint?
I cant optimize the SLOW_VIEW. This is a complex view, based on other views with big tables.
So the only question was to avoid the this view is queried 84 times.
My solution is now to use PL/SQL:
FOR D IN
(SELECT FCR7, DL, MONAT FROM SLOW_VIEW
)
LOOP
UPDATE SMALL_TABLE T
SET FCR7 = D.FCR7*100
WHERE D.MONAT = T.MONAT
AND D.DL = T.DL ;
END LOOP;
The Update in my original Post took about 8 hours, now it is about 8 minutes.
Joachim
-
11. Re: Update small table with slow subselect
Your PL/SQL technique is not equivalent to the original UPDATE statement. The UPDATE will update every row in SMALL_TABLE, setting FCR7 to the calculated value or to NULL if there is no match in the view. Your PL/SQL will not update any rows where there is no match.
So one or the other is a bug waiting to bite
-
12. Re: Update small table with slow subselect
Maybe a simple merge would be better since you won't be updating non-matched rows (as John Watson suggested).
merge into small_table t
using slow_view d
on (d.monat = t.monat and d.dl = t.dl)
when matched then update set t.fcr7 = d.fcr7 * 100;
-
13. Re: Update small table with slow subselect
1 person found this helpfulA quick and dirty to do what you're currently doing, but in pure SQL with a single generation of the view data:
UPDATE
SMALL_TABLE T
SET FCR7=
(
with D as (
SELECT /*+ materialize */
FCR7, MONAT, DL
FROM SLOW_VIEW
)
select fcr7
from D
WHERE D.MONAT = T.MONAT
AND D.DL = T.DL
)
;
Your plan should then show TEMP TABLE TRANSFORMATION as the slow view result is copied into a global temporary table, and the last two lines of the plan should show something like:
VIEW
TABLE ACCESS FULL SYS_TEMP_xxxxxxxxxxx
After the view has been instantiated the resulting 63 row table will be scanned 84 times - which shouldn't take very long.
Regards
Jonathan Lewis
http://jonathanlewis.wordpress.com
UPDATE: Added in the two columns which I'd originally missed from the factored subquery.
-
14. Re: Update small table with slow subselect
The problem seems to be that you are running the slow subquery one time for each row in the small table. I if you do the math 74 rows x 7 mins, you end up at about 9 hours. Remove som time due to caching effect and you´ld probably end up somewhere in the range you mention.
I suggest you rewrite the query to something where you incorporate the ids from the small table, maybe by using subquery factoring:
with slow_view_data as
(SELECT
FCR7*100 new_value, D.MONAT, D.DL
FROM SLOW_VIEW D
WHERE (D.DL, D.MONAT) in (select DL, MONAT from SMALL_TABLE st)
)
UPDATE
SMALL_TABLE T
SET FCR7=
(SELECT
new_value
FROM SLOW_VIEW_data D
WHERE D.MONAT = T.MONAT
AND D.DL = T.DL
)
;
I haven´t tested this code, just showing you the outline here. The subquery, slow_view_data will be run only once an can be used as a regular table in the following statement. Let me know if it works or anything is unclear, and I´ll set up a tested example.
Register and Participate in Oracle's online communities. Learn from thousand of experts, get answers to your questions and share knowledge with peers.