| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Usenet -> c.d.o.misc -> Re: Very slow load with trigger..will more RAM help?
kgrigg_at_acxiom.com wrote:
>
> 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
OK.
First thing is that the strategy seems to be wrong: single-row triggers
are
always going to be slow for a bulk load.
I would always prefer SQL statements of the sort:
insert into addresses (<column_list>) -- ALWAYS use a column_list
select ... from address_load AL
where exists
(select null from individuals I where I.col1 = AL.col1 and I.col2 = AL.col2 ...)
and
insert into address_associations (<column_list>)
select ..., 'X' from address_load AL
where not exists
(select null from nap_valid_entries NVE where ...)
This should be fine for the addresses table.
However, you have two complications for the maintenance of the
address_associations table:
1) You need the same sequence number to be used as was used in the
insert into addresses.
2) You need the same individual_type (from individuals) where a row DOES
exist
in nap_valid_entries.
There may be ways round this, but let's stick with your existing strategy:
book_book_number||book_book_pub_date||section_number||page_number||listing_number
from each of the five unioned tables.
This means that no indexes can be used on ANY of the tables in the view.
You are therefore having to do a full table scan on each of the five
unioned tables.
For every row inserted!
THIS IS THE MAIN REASON FOR THE POOR PERFORMANCE.
Redefine the view to make these separate view columns.
Your c_entries_exists cursor then becomes: select null -- you don't actually need any values, do you?
from nap_valid_entries where book_book_number = :new.book_number and book_pub_date = :new.book_pub_dateand ...
Assuming that at least some (preferably all) of these columns are
indexed in
ALL of the tables comprising the view, then they can be used as an
access path.
3) The most elegant/efficient way to insert into address_associations is:
open c_entries_exists;
fetch c_entries_exists into dummy; --defined as varchar2(1);
if c_entries_exists%found then
ind_type := :v_indiv_type;
else
ind_type := 'X'
end if;
close c_entries_exists;
Then you just need one insert statement for address_associations, using the above value of ind_type.
HTH.
--
Chrysalis
FABRICATI DIEM, PVNC
('To Protect and to Serve')
Terry Pratchett : "Guards Guards"
Received on Sat Nov 08 1997 - 00:00:00 CST
 |
 |