Home » SQL & PL/SQL » SQL & PL/SQL » Insert 999999 records in table (Oracle PLSQL, 10g, XP)
Insert 999999 records in table [message #576148] Fri, 01 February 2013 00:43 Go to next message
mvmkandan
Messages: 67
Registered: May 2010
Location: Trivendrum
Member
Hi Experts,

I need to insert 999999 records into one table, Already I wrote the below query

DECLARE
   TYPE tt_type IS TABLE OF table_name.no%TYPE
      INDEX BY BINARY_INTEGER;

   tt_type_no   tt_type;
   rec_count           NUMBER;
BEGIN

   SELECT     LEVEL
   BULK COLLECT INTO tt_type_no
         FROM DUAL
   CONNECT BY LEVEL <= 999999;

   FORALL rec_count IN tt_type_no.FIRST .. tt_type_no.LAST
      INSERT INTO table_name
                  (no, checkin_date
                  )
           VALUES (tt_type_no (rec_count), SYSDATE
                  );
   COMMIT;
END;



But It took 5 mins to execute... Is there any other way there to insert fastly.

Thanks
Veera
Re: Insert 999999 records in table [message #576150 is a reply to message #576148] Fri, 01 February 2013 00:46 Go to previous messageGo to next message
muralikri
Messages: 638
Registered: August 2011
Location: chennai
Senior Member

Try without using plsql..sql with append HINT
Re: Insert 999999 records in table [message #576151 is a reply to message #576150] Fri, 01 February 2013 00:50 Go to previous messageGo to next message
Littlefoot
Messages: 19894
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Less than 4 seconds on my database (Oracle 10g R2, not too powerful machine):
SQL> create table test (id number, datum date);

Table created.

SQL> set timing on
SQL> insert into test (id, datum)
  2    select level, sysdate
  3    from dual
  4    connect by level < 999999;

999998 rows created.

Elapsed: 00:00:03.84
SQL>

[EDIT] Ah, yes - 1 record is missing. My bad.

[Updated on: Fri, 01 February 2013 00:52]

Report message to a moderator

Re: Insert 999999 records in table [message #576155 is a reply to message #576151] Fri, 01 February 2013 00:57 Go to previous messageGo to next message
muralikri
Messages: 638
Registered: August 2011
Location: chennai
Senior Member

if you use append hint 2 min taking..

 insert /*+ APPEND */ into test (id, datum)
      select level, sysdate
      from dual
      connect by level <= 999999; 
Re: Insert 999999 records in table [message #576157 is a reply to message #576155] Fri, 01 February 2013 01:01 Go to previous messageGo to next message
mvmkandan
Messages: 67
Registered: May 2010
Location: Trivendrum
Member
Hi,

I forgte to mention one thing.. Already this table has 1,000,000 records.... Now i need to add 999,999 records more. If it is a fresh table then it may be fast. But already it has the records. I can't truncate the table before insert. Because, what the records is stored in the table is important.

Thanks
Veera
Re: Insert 999999 records in table [message #576158 is a reply to message #576155] Fri, 01 February 2013 01:01 Go to previous messageGo to next message
Michel Cadot
Messages: 60018
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
muralikri wrote on Fri, 01 February 2013 07:57
if you use append hint 2 min taking..

Littlefoot wrote on Fri, 01 February 2013 07:50
Less than 4 seconds on my database (Oracle 10g R2, not too powerful machine):


So the conclusion is: do NOT use it!

Note: comparing the times between 2 machines we don't know anything about them is quite silly, isn't it?

Regards
Michel

Re: Insert 999999 records in table [message #576159 is a reply to message #576155] Fri, 01 February 2013 01:01 Go to previous messageGo to next message
Littlefoot
Messages: 19894
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
2 minutes for what? Is your database installed on 286 processor machine whose clock is 4 MHz? Did you try to push the "Turbo" button (so it raises up to 8 MHz)?
Re: Insert 999999 records in table [message #576167 is a reply to message #576159] Fri, 01 February 2013 02:09 Go to previous message
Roachcoach
Messages: 1233
Registered: May 2010
Location: UK
Senior Member
So really what we're saying here is get rid of the pointless PL/SQL Smile


and so long as we're showing off, my box took 1.71 seconds, though most of that time was spent on the connect by. We need better hamsters
Previous Topic: Multiple Queries into different columns
Next Topic: how to create the trigger ?
Goto Forum:
  


Current Time: Mon Dec 22 23:21:35 CST 2014

Total time taken to generate the page: 0.04887 seconds