Need to optimize query [message #404159] |
Wed, 20 May 2009 11:23  |
sanyadu
Messages: 30 Registered: October 2006 Location: Chicago
|
Member |
|
|
Hi ,
Hi am facing performance issues while inserting 26000 records, it took me around 30 minutes to insert the records .
Below are test script
Inserted records are similar to this records. Please help me immediately if possible.
Create table test_insert
(
column1 number(10),
column2 number(10),
column3 date,
column4 date,
column5 varchar2(20),
column6 date,
column6 varchar2(20)
)
INSERT INTO X ( column1,
column2,
column3,
column4,
column5,
column6,
column6 )
VALUES ( 379127,
177284,
TO_DATE('05/19/2009 12:00:00 AM', 'MM/DD/YYYY HH12:MI:SS AM'),
TO_DATE('05/19/2009 12:00:00 AM', 'MM/DD/YYYY HH12:MI:SS AM'),
'sandeep',
TO_DATE('05/19/2009 12:00:00 AM', 'MM/DD/YYYY HH12:MI:SS AM'),
'sandeep' )
Thanks
/
|
|
|
Re: Need to optimize query [message #404161 is a reply to message #404159] |
Wed, 20 May 2009 11:25   |
ThomasG
Messages: 3212 Registered: April 2005 Location: Heilbronn, Germany
|
Senior Member |
|
|
Read the sticky post in the Performance Tuning section. Then have a look at the explain plan, if that doesn't give a clue trace the session and process the trace file with tkprof.
|
|
|
|
|
Re: Need to optimize query [message #404167 is a reply to message #404159] |
Wed, 20 May 2009 11:34   |
JRowbottom
Messages: 5933 Registered: June 2006 Location: Sunny North Yorkshire, ho...
|
Senior Member |
|
|
What indexes, triggers and constraints do you have on the table?
How are you inserting the data? Do you actually have list of 26,000 Insert statements, or is this something else that you're not telling us.?
|
|
|
|
|
Re: Need to optimize query [message #404180 is a reply to message #404169] |
Wed, 20 May 2009 12:00   |
JRowbottom
Messages: 5933 Registered: June 2006 Location: Sunny North Yorkshire, ho...
|
Senior Member |
|
|
Where do you get this data from?
If you could use EXP to get the data out of the other table and IMP to bring it in (or even better EXPDP and IMPDP), then an import of this size should take about 30 seconds.
|
|
|
|
Re: Need to optimize query [message #404328 is a reply to message #404181] |
Thu, 21 May 2009 06:36  |
JRowbottom
Messages: 5933 Registered: June 2006 Location: Sunny North Yorkshire, ho...
|
Senior Member |
|
|
Particularly if there were indexes or constraints on the table.
Basically, you've picked a really slow way to get your data into the database.
It will run a little quicker if you can execute this on the server, as you'll reduce your network overhead, but it won't make a massive improvement.
If you'd ever answered about the indexes, constraints and triggers I could give you advice about what to do with them, but you decided not to bother answering.
|
|
|