Link to home
Create AccountLog in
Avatar of cookiejar
cookiejarFlag for United States of America

asked on

Speed up insert

I am inserting 2+ million rows and will grow. It takes 30 hours to insert 2+ million rows
Do you have any recommendations on how I could speed up the insert?

I am doing the following in a stored procedure:

  cusor c is
      select columns from multiple tables;

truncate table
Mark indices unusable
Encountered one problem though.  Marked the primary key unusable but when inserting data, encountered this error:  Q2PK or partition of such index is in unusable state.

for c_rec IN c
loop
   
   doing an evaluation to set variables to insert into table
   insert into table
           values(
                   )
    commit;
end loop;
Avatar of David VanZandt
David VanZandt
Flag of United States of America image

At least add the hint for INSERT /* +APPEND */.

Add a higher loop counter to break the commit into a less-frequent cycle -- see E-E search for specifics.

Have you considered simply putting the instance into noarchivelog mode for the duration?  Also, perhaps just ignore the indexes until after the rows are inserted.

Can you switch from the single-row insert to a direct or bulk load: http://asktom.oracle.com/pls/apex/f?p=100:11:0::::P11_QUESTION_ID:228657900346252297.
Avatar of cookiejar

ASKER

I am making the indexes unusable with the exception of the primary key.  I made it unusable but upon an insert, I encountered this error: Q2PK or partition of such index is in unusable state.  I am a novist.  How can I make a primary key unusable?
SOLUTION
Avatar of David VanZandt
David VanZandt
Flag of United States of America image

Link to home
membership
Create an account to see this answer
Signing up is free. No credit card required.
Create Account
I would like to use bulk collect but one of the experts recommended not to:
Bulk collect can be faster but not always.  The reason is because everything is in memory.  It might not make sense to have everything in memory and can degrade overall system performance. by: slightwvPosted on 2012-08-20 at 22:51:27ID: 38314158
What do you think about doing the load without archive logs (38572739)?
Bulk insert are almost always faster.

especially when doing that much inserts I would advice bulk inserts.

You do 2+ million context switches..!

If you need help using bulk collect into and forall insert, let me know
> What do you think about doing the load without archive logs (38572739)?
How do you do log without archive logs?  We create our table with nologging.  Is this what you're referring to?
One technique for improving throughput is to bounce the database (preferably with no other users on), and open it with archiving disabled -- not just the table. In the meantime, if you can get away with removing the primary key (and disabling any others), it would turn the index creation into a subordinate step.
dvz is this the correct way to drop the primary key?

('ALTER TABLE Q2PK DROP PRIMARY KEY CASCADE NOLOGGING')
> One technique for improving throughput is to bounce the database (preferably with no > >other users on), and open it with archiving disabled -- not just the table. In the meantime, >if you can get away with removing the primary key (and disabling any others), it would >turn the index creation into a subordinate step.

I don't administrative rights on the database.  When we run the stored procedure in production, we can't do this operation.
<shrugs> then stick with dropping the indexes until after the table is loaded.  BTW, can you turn on parallelism for this process?
ASKER CERTIFIED SOLUTION
Link to home
membership
Create an account to see this answer
Signing up is free. No credit card required.
Create Account
Cookie, regarding 38573242, that's the correct syntax (without the parenthesis), and of course add the semi-colon or next line right slash to execute the statement.

A good practice is to include the schema owner with the table, to reduce "whoops".
Avatar of slightwv (䄆 Netminder)
slightwv (䄆 Netminder)

I should also add that in an 'important' database like production, I would not suggest nologging unless you can live without the object until you can manually recover it.

Turning it off then re-enabling it will invalidate all previous backups you may have on tape.

>>Do you have any recommendations on how I could speed up the insert?

Don't truncate the table.  Track 'changes' and only apply them.  This may involve custom trigger code.

Have you looked into materialized views?