Home » SQL & PL/SQL » SQL & PL/SQL » index
index [message #291914] Mon, 07 January 2008 04:02 Go to next message
vijaysimha
Messages: 5
Registered: January 2008
Junior Member
Can any one tel me the best way to truncate and
insert the records using a procedure such that the performance of the procedure is maintained...
Re: index [message #291918 is a reply to message #291914] Mon, 07 January 2008 04:16 Go to previous messageGo to next message
Michel Cadot
Messages: 64119
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
truncate table mytable;
insert into mytable (the records I want to insert).

Regards
Michel
Re: index [message #291930 is a reply to message #291918] Mon, 07 January 2008 04:53 Go to previous messageGo to next message
vijaysimha
Messages: 5
Registered: January 2008
Junior Member
Here i need to insert more than 5Lacks of records.
It is taking more time to execute it....
Re: index [message #291937 is a reply to message #291930] Mon, 07 January 2008 05:11 Go to previous messageGo to next message
Littlefoot
Messages: 20896
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
There's far too few information to help; all we know that you'd like to insert 500.000 records into some table (if '5Lacks' is, actually, 5 lakhs and if 'lakh' equals 10E5, according to Wikipedia).

What do you call "more time"? How long does it take now? Why do you think it shouldn't take that much time? Would you mind to provide additional information? How do you do that? Using SQL? Are those records in a flat file and you'll insert them using SQL*Loader, for example? Is there any index involved? A database trigger, perhaps? Something else?
Re: index [message #291946 is a reply to message #291937] Mon, 07 January 2008 05:23 Go to previous messageGo to next message
vijaysimha
Messages: 5
Registered: January 2008
Junior Member
yes it is 5LAKH records.

I am thinking to create an index but if the table is truncated then indexes are also removed, For the next insertion there will be no index on that table.

If there is a way that DATA is populated with into the table with more speed. it is taking more than 15mins...

Im using SQL loader and the records has to fetch from another table..........

[Updated on: Mon, 07 January 2008 05:25]

Report message to a moderator

Re: index [message #291955 is a reply to message #291946] Mon, 07 January 2008 05:31 Go to previous messageGo to next message
Michel Cadot
Messages: 64119
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:

yes it is 5LAKH records

LAKH is something unknown out of your country.
Use "standard" english.

Quote:

I am thinking to create an index but if the table is truncated then indexes are also removed,

Wrong the indexes are also truncated, not removed.

Quote:

If there is a way that DATA is populated with into the table with more speed.

Yes. drop the indexes, load, recreate the indexes.

Quote:

Im using SQL loader and the records has to fetch from another table..........

This is not possible.
SQL*Loader load from a FILE not a TABLE.
Precise what you want to do.

Regards
Michel

Re: index [message #291961 is a reply to message #291955] Mon, 07 January 2008 05:39 Go to previous messageGo to next message
vijaysimha
Messages: 5
Registered: January 2008
Junior Member
U mean to say that DROPING the table is better than TRUNCATING
Re: index [message #291963 is a reply to message #291961] Mon, 07 January 2008 05:41 Go to previous messageGo to next message
Michel Cadot
Messages: 64119
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Where I say that? Drop the indexes.

Regards
Michel
Re: index [message #291976 is a reply to message #291963] Mon, 07 January 2008 06:02 Go to previous message
vijaysimha
Messages: 5
Registered: January 2008
Junior Member
ok i droped the indexes, ececuted using SQL query INSERT into table_name SELECT * from tabl1
Previous Topic: HOW TO USE RECORD STRUCTURE IN A PROCEDURE
Next Topic: CREATE TABLE throws ORA-00972 Identifier is too long
Goto Forum:
  


Current Time: Tue Dec 06 08:46:01 CST 2016

Total time taken to generate the page: 0.23269 seconds