Home » SQL & PL/SQL » SQL & PL/SQL » Insert is too slow......
Insert is too slow...... [message #183774] Sun, 23 July 2006 10:51 Go to next message
rafi_aamiri
Messages: 34
Registered: September 2005
Member
Hi,

I have created two temp tables. (temp1, temp2)

After that an index is created for temp2.

Then insert statement is executed by using union operator and then inserted into the temp2 table.

Here the data inserted into the temp2 table is very huge around 3 millions records.

It takes nearly 5 hours to complete the insert statement.

I think the index is affecting the performance to degrade.

Please let me know whether disabling the index untill the insert is completed is recommended to improve the performance.

Any help is welcome.

Regards,

A.Mohammed Rafi.

Honeywell Migration Project
New Jersey.

Re: Insert is too slow...... [message #183776 is a reply to message #183774] Sun, 23 July 2006 10:59 Go to previous messageGo to next message
BlackSwan
Messages: 25047
Registered: January 2009
Location: SoCal
Senior Member
Ready, Fire, Aim.
Without actual metrics, you are shooting in the dark hoping to get lucky.
I suggest you enable SQL_TRACE to actually see where the time is being spent.

P.S.
3 millions rows is NOT a big table.

How long does it take to do a FTS [count(*)] against TEMP2?
Some what less than 5 hours, I suspect.
Re: Insert is too slow...... [message #183777 is a reply to message #183776] Sun, 23 July 2006 11:01 Go to previous messageGo to next message
rafi_aamiri
Messages: 34
Registered: September 2005
Member
please find the code for your analysis

drop table temp_ed_prod_completed;

create table temp_ed_prod_completed
(
ed_prod_id	char(20) not null  CONSTRAINT temppk_ed_prod_completed  PRIMARY KEY,
proposed_reg_id	char(20) not null,
actual_reg_id	char(20) null,
status		varchar2(10) not null,
ct		integer
);


drop table temp_reg;

create table temp_reg(
reg_id		char(20) 	not null,
item_type	int		not null,
status		int		not null,
student_id	char(20)	not null,
product_id	char(20)	not null
);

create index tempi_reg_sid_pid on temp_reg (student_id, product_id);

insert into temp_reg (reg_id, item_type, status, student_id, product_id)
select 
	t.id, oi.item_typ, oi.status, t.student_id, t.product_id
from
	tpt_transcript t , tpt_oe_order_items oi 
where
	t.order_item_id = oi.id and
		(
			(oi.item_typ = 7 and oi.status in (300, 400, 500)) or
			(oi.item_typ = 1 and oi.status in (100, 103, 300, 400, 500))
		)
union
select 
	r.id, oi.item_typ, oi.status, r.student_id, oi.part_id
from 	
	tpt_registration r ,tpt_oe_order_items oi 
where
	r.id = oi.reg_id and 
	r.student_id is not null and
	oi.status in (300, 400, 500);

commit;
Re: Insert is too slow...... [message #183790 is a reply to message #183777] Sun, 23 July 2006 20:55 Go to previous messageGo to next message
rleishman
Messages: 3724
Registered: October 2005
Location: Melbourne, Australia
Senior Member
- Create the index after the load, it will make the insert faster.
- Use the APPEND hint to get direct-path load (ie. INSERT /*+append*/ INTO ...)
- Change the CHAR columns to VARCHAR2, search on "CHAR vs VARCHAR2" if you need a reason.

Ross Leishman
Re: Insert is too slow...... [message #183839 is a reply to message #183777] Mon, 24 July 2006 02:13 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Unless the two halves of the UNION statement are going to return identical rows, you'd get a noticable performance gain by converting the UNION to a UNION ALL.

The UNION statment is doing a DISTINCT on the data returned, which requires sorting it - that will take a minute or two for 3,000,000 rows.
Re: Insert is too slow...... [message #184600 is a reply to message #183839] Thu, 27 July 2006 03:39 Go to previous messageGo to next message
michael_bialik
Messages: 611
Registered: July 2006
Senior Member
Can you use CREATE AS SELECT with NOLOGGING and to create index
afterwards?

Michael
Re: Insert is too slow...... [message #184651 is a reply to message #183774] Thu, 27 July 2006 06:11 Go to previous message
ebrian
Messages: 2794
Registered: April 2006
Senior Member
Have you tried to APPEND the insert and do PARALLEL execution?
Previous Topic: Need the query
Next Topic: showing errors while creating stored procedure (pls-00103)
Goto Forum:
  


Current Time: Fri Dec 09 09:30:52 CST 2016

Total time taken to generate the page: 0.07583 seconds