Oracle updating large tables
In either case, each table is processed by a single thread. We recommend you to use this approach in production operation because you can easily schedule update statistics to run automatically at regular intervals.
We recommend you to update statistics at least weekly or even daily for Oracle 10g.
If the statistics are out-of-date, the CBO might generate inappropriate access paths (such as using the wrong index), resulting in poor performance. If statistics are available for a table, the database system uses the cost-based optimizer. Since from Oracle 10g the rule-based optimizer is no longer supported, in this case auto-sampling is performed.
When you sign in to comment, IBM will provide your email, first name and last name to DISQUS.
Let’s say this is how STUDENT_N looks before it is merged with STUDENT: merge into student a 2 using 3 (select id, name, score 4 from student_n) b 5 on (= b.id) 6 when matched then 7 update set = 8 , a.score = b.score 9 when not matched then 10 insert (a.id, a.name, a.score) 11 values (b.id, b.name, b.score); 5 rows merged.
select * from student; ID NAME SCORE ---------- --------------- ---------- 1 Jack 540 2 Rose 3 William 650 4 Caledon 620 5 Fabrizio 600 6 Thomas 7 Ruth 690 11 Brock 705 10 Lizzy 9 Wallace 600 8 Spicer 620 11 rows selected. But it does not work with a merge query (probably because of the update part): “Missing IN or OUT parameter at index:: 1” Would would you advise to retrieve the value of a field (typically an id) of the row that has been inserted or updated?
Oracle’s MERGE statement is tailor-made for situations when you want to do an "upsert" i.e.