How to make inserts faster [message #317569] |
Fri, 02 May 2008 00:15 ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
hidnana
Messages: 87 Registered: November 2007 Location: chennai
|
Member |
|
|
We have a few general suggetions to make a 'select' query faster (a few examples are by having an index, avoiding UNION preferring UNION ALL, using exists instead of IN..), similarly do we have a list of general suggestion or checklist to ensure that our INSERT are also run faster ? One point I could think of is dropping/disabling the index during insert and rebuild it later.
Please enlighten me.
|
|
|
Re: How to make inserts faster [message #317577 is a reply to message #317569] |
Fri, 02 May 2008 01:26 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
hidnana
Messages: 87 Registered: November 2007 Location: chennai
|
Member |
|
|
This is the code snippet. I currently don't have the Explain plan for this. I will try to get it.
BEGIN
FORALL l_nm_loop IN l_nm_start_cnt..l_nm_end_cnt
INSERT INTO TVD_TL_PIP_AMOUNT (PIP_AMOUNT_ID, VALUATION_ITERATION_ID, REATTRBN_KEYDATE_ELIGIBLITY_ID,
SCALED_AMOUNT, SWITCHABLE_AMOUNT, HYB_SWITCHABLE_AMOUNT,
TOTAL_AMOUNT, MINIMUM_ADDITION_AMOUNT, FINAL_PIP_AMOUNT, MINIMUM_APPLIED_IND,
CREATE_DATE_TIME, CREATE_USER_ID,
LAST_UPDATE_DATE_TIME, UPDATE_USER_ID)
VALUES (TVD_TL_PIP_AMOUNT_SEQ.NEXTVAL, l_nm_valuation_iteration_id, TO_NUMBER (g_a100_sm_reckey (l_nm_loop)),
g_a242_sm_scaled_pip (l_nm_loop), g_a242_sm_switchable_pip (l_nm_loop), g_a242_sm_hyb_npsw_pip (l_nm_loop),
g_a242_sm_total_pip (l_nm_loop), g_a242_sm_min_amt (l_nm_loop), g_a242_sm_final_pip (l_nm_loop), g_a100_sm_min_applied (l_nm_loop),
SYSDATE, g_vc_userid,
SYSDATE, g_vc_userid);
l_nm_recs_inserted := l_nm_recs_inserted + SQL%ROWCOUNT;
COMMIT;
l_nm_start_cnt := l_nm_end_cnt + 1;
l_nm_end_cnt := l_nm_end_cnt + l_nm_increment;
EXCEPTION
WHEN OTHERS THEN
p_pip_exceptions_logging (l_vc_component_name, SQLERRM);
p_delete_pip_records (l_nm_valuation_iteration_id, l_nm_del_status);
l_nm_exit_status := 1;
END;
END LOOP;
|
|
|
Re: How to make inserts faster [message #317578 is a reply to message #317569] |
Fri, 02 May 2008 01:29 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
![](http://www.orafaq.com/forum/images/custom_avatars/102589.gif) |
Michel Cadot
Messages: 68675 Registered: March 2007 Location: Nanterre, France, http://...
|
Senior Member Account Moderator |
|
|
If you have "insert values" it does not matter.
If you have "insert select", the select part is optimized in the same way than a classic "select". You can, if your hardware allow it:
a) use direct load (append hint)
b) use parallel
c) disable constraint
d) drop/disable index
a) and b) imply you have large number of rows
a) and b) imply you have to commit just after if you want to reuse the table -> can't be done in a transaction processing
a) implies you know how to handle the case of instance/media crash
b) implies you have multiple disks/cpu for this table otherwise it is likely you just waste cpu and time
c) implies you have to execute a fix batch if you can't reenable the constraints
d) implies you have to recreate/rebuild the indexes, so does it save you time? It depends on the ratio number of inserted rows/overall number of rows.
...
By the way, your "suggestions" on select are not quite true.
UNION must be used only if you don't want duplicates and UNION ALL if you don't care about them, there is no choice.
IN and EXISTS are faster each one, it depends on the query (if EXISTS were always faster, Oracle would suppress IN). Anyway, if your statistics are up to date, Oracle will (most often) internally rewrite your query to use the correct predicate.
Regards
Michel
|
|
|
Re: How to make inserts faster [message #317579 is a reply to message #317577] |
Fri, 02 May 2008 01:37 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
![](http://www.orafaq.com/forum/images/custom_avatars/102589.gif) |
Michel Cadot
Messages: 68675 Registered: March 2007 Location: Nanterre, France, http://...
|
Senior Member Account Moderator |
|
|
My first comment in your PL/SLQ is: why not a single SQL?
Then get rid of the commit, it will:
- greatly slow down the performances
- need you to know how to handle in case of failure (program, instance, media)
Then get rid of this WHEN OTHERS block, only trap the errors you know how to handle and let the other raised.
Think about it, for instance, what happen to your logic if the error is "unable to extent segment"?
There is no need of an explain plan for an "insert values", there is just one line: "insert statement".
Regards
Michel
|
|
|
|
Re: How to make inserts faster [message #317596 is a reply to message #317579] |
Fri, 02 May 2008 02:27 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
hidnana
Messages: 87 Registered: November 2007 Location: chennai
|
Member |
|
|
Can there a be a commit after every n records (say 10000) are inserted. Will that help ?
And if I remove WHEN OTHERS, there is a chance of lot of unhandled exceptions isn't it ?
|
|
|
|
|
Re: How to make inserts faster [message #317629 is a reply to message #317607] |
Fri, 02 May 2008 04:10 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
hidnana
Messages: 87 Registered: November 2007 Location: chennai
|
Member |
|
|
thanks Michael. The values for the columns to be inserted are fetched from various arrays and hence the need to loop through the arrays.
Is it possible to do it in a single insert statement ? How do we do it ?
|
|
|
|