Skip to main content

What are some methods for optimizing Oracle databases for large data inserts?

 For large data inserts I can suggest you few things like:



  1. Use trigger(PL/SQL)
  2. Use APPEND hint
  3. Remove indexes on tables

Firstly, while you use triggers in the table it could leave data to be logically corrupt. And it will then perform insert in a very conventional way. Which is a time consuming process and won’t helps us!


Secondly, using APPEND hint will help us to an extend.

So, APPEND hint tells the optimizer to perform a direct-insert into the table, which improves the performance.

Now there is a way which we could achieve this by minimizing the Redo generation.

What Redo do is; it basically ensures the recoverability of data to the database. It writes down every transaction to the archive log.


Let’s take a scenario, where if the database is running on the NOARCHIVELOG mode, using APPEND hint will reduce the redo generation i.e; it won’t write into the archive log anymore and thus increases the speed.

But then it won’t be able to recover at any point in time if your data is ambiguous.


Furthermore, in the ARCHIVELOG mode, using APPEND hint won’t help us and will not reduce the redo generation until and unless your table is in NOLOGGING mode.

But also, in NOLOGGING mode our data is no longer protected. In fact, in OLTP it is very rare to see your table to NOLOGGING mode for obvious reasons.


Lastly, yes we can drop the indexes, constraints when bulk inserting into tables. Coz writing down index for every row formed will depreciate the performance of our db.

So, Ya! These are the methods for optimizing Oracle database for large data inserts.

I hope you got this!

Cheers! 👍

Link to my space: Click here

Labels:

Comments

Post a Comment

Popular posts from this blog

Glowing Border effect using html/css

  {html code} <html>     <head>         <link href='E:\html\.vscode\.vscode\style.css' type='text/css' rel='stylesheet'>         <title>Glowing Border</title>     </head>     <body>         <div class='box'>             <div class='text'>             <h2><u>Glowing Border</u></h2>             <p>HTML and CSS are technically not the programming languages, they are the scripting languages.              Usually used for the front-end development.</p>             </div>         </div>             </body> </html> {css code} body{     background: black;     display: ...
2 comments
Read more

Stock Market using Python

 "The stock market is a device for transferring money from the impatient to the patient." - Warren Buffett Today we'll look into few ways for accessing the stock market. And we'll do this using Python ! Now, as we know that there are 2 stock exchange in India; BSE and NSE So we'll get the data from both! To begin with let's access the data from BSE first. (P.S: I certainly like the 2nd and the 3rd method to access stock market!) * So, to import the BSE data we need to " pip install bsedata ". => And then import the module, => Create an object to store the Driver Class => Then we need to do " getQuote('script_code')" where we need to provide a script code of a company which we need to access. Just like here we have given; => And from here we can see that the script code was for the company named "V-MART". But we can't remember all the script code hence we need to download this script file from the BSE websi...
Post a Comment
Read more