Very slow load with trigger..will more RAM help?

From: <kgrigg_at_acxiom.com>
Date: 1997/11/07
Message-ID: <878933803.15348@dejanews.com>#1/1

Hello All!

I am running Oracle 8 on Win NT server 4.0 on a pentium 200 with 64 MB RAM. This is just a prototyping machine. Most everything loads in avery reasonable amount of time. However, I have one table I am loading with a fairly complex trigger that takes at least a DAY to load for each load. Each load is about 4000 to 6500 records. Here is what the trigger basically does.

For each record inserted, part of its primary key is checked agains a view which is a UNION of 5 tables. If it is present in the view, then the trigger generates a sequence number...this sequence number plus part of the inserted record in inserted into another table. The sequence number and the primary key information is inserted into a 3rd table.

There is one other step to see if the record being inserted into the 1st table is in one particular table and adds a value to the field being inserted into the 3rd table. So, basically, this table mentioned first with this trigger is basically a 'load' table. The values entered into it are checked and split to be entered into 2 other tables.

This process works VERY slow. Would adding more RAM help speed this up? If so, what would be a good amount...$$ isn't really an object here.

This is my first trigger, is there a way to tune it so it would work faster?

All suggestions welcomed!!
TIA! Kelly
kgrigg_at_acxiom.com

Code for Trigger:

declare

	cursor c_individ is
		select i.individual_type
		from individuals i
		where i.book_book_number = :new.book_book_number
		and i.book_pub_date = :new.book_book_pub_date
		and i.section_number = :new.section_number
		and i.page_number = :new.page_number
		and i.listing_number = :new.listing_number;
	cursor c_individ_count is
		select count(x.individual_type)  ind_count
		from individuals x
		where x.book_book_number = :new.book_book_number
		and x.book_pub_date = :new.book_book_pub_date
		and x.section_number = :new.section_number
		and x.page_number = :new.page_number
		and x.listing_number = :new.listing_number;
	cursor c_entry_exists is
		select entries
		from nap_valid_entries
		where entries in
			(select

(to_char(:new.book_book_number)||to_char(:new.book_book_pub_date)||

to_char(:new.section_number)||to_char(:new.page_number)||

			to_char(:new.listing_number))
		 	as entries
			from dual);
	v_entry_exists c_entry_exists%ROWTYPE;
	v_indiv_count c_individ_count%ROWTYPE;
	v_indiv_type individuals.individual_type%TYPE;
begin
                 --Block to load Addresses and Address_Associations tables

from Address_Load
open c_entry_exists;
fetch c_entry_exists into v_entry_exists; close c_entry_exists;
if v_entry_exists.entries IS NOT NULL then

        insert into addresses

values(seq_pre_nap_address_id.nextval,:new.zip_book,:new.room,NULL,NULL,:new ..street_directional,

:new.street_number,:new.street_name,:new.rural_route_address,:new.stated_com
 munity,

	:new.po_box_number,NULL,:new.fire_locator,NULL);
	open c_individ_count;
--Begin search to see if individual and	fetch c_individ_count into

v_indiv_count;
 --if so fill type with P or S, if not then with X

	close c_individ_count;
	if v_indiv_count.ind_count > 0 then
		for individ_record in c_individ loop
			v_indiv_type := individ_record.individual_type;
			insert into address_associations

values(:new.book_book_number,:new.book_book_pub_date,:new.listing_number,

:new.section_number,:new.page_number,seq_pre_nap_address_id.currval,v_indiv_
type);

		end loop;
	else
		insert into address_associations

values(:new.book_book_number,:new.book_book_pub_date,:new.listing_number,:ne w.section_number,

		:new.page_number,seq_pre_nap_address_id.currval,'X');
	end if; --count loop

end if; --exists loop
end;

-------------------==== Posted via Deja News ====-----------------------
      http://www.dejanews.com/     Search, Read, Post to Usenet

Received on Fri Nov 07 1997 - 00:00:00 CST