Home » SQL & PL/SQL » SQL & PL/SQL » Query on 'AFTER INSERT ON '
Query on 'AFTER INSERT ON ' [message #190419] Wed, 30 August 2006 11:13 Go to next message
sharath160
Messages: 9
Registered: August 2006
Junior Member
We use the following query to update our main table 'Item_Table' from every insert into temporary table 'Temp_catalog_Table'. This is causing a performance impact. We check whole table space of 'Item_Table' for every inserted record in 'Temp_catalog_Table'. Is there any way to do this more easily to increase performance. I tried UNION, JOINs but not very successful.

CREATE OR REPLACE TRIGGER Trig1
AFTER INSERT ON TEMP_CATALOG_Table
FOR EACH ROW
DECLARE
bla bla bla.....EntCode:= :new.primary_enterprise_code;

BEGIN
select count(*) into existing from Item_table where trim(item_id) = :new.item_id and trim(primary_enterprise_code) = EntCode;

IF existing=0
THEN
INSERT INTO Item_table ( ITEM_HTS_KEY, ITEM_ID, ECCN, COUNTRY, ENTERPRISE_CODE)
VALUES (itemKey,:new.item_id, :new.eccn, :new.country, EntCode);
ELSE
UPDATE Item_table SET ENTERPRISE_CODE=nvl(trim(mappedEntCode),ENTERPRISE_CODE)
where item_id = :new.item_id and primary_enterprise_code = EntCode;
END IF;
Re: Query on 'AFTER INSERT ON ' [message #190424 is a reply to message #190419] Wed, 30 August 2006 11:45 Go to previous messageGo to next message
Todd Barry
Messages: 4819
Registered: August 2001
Senior Member
That 'select count(*)' is using TRIM in the WHERE clause which will cause a full-table scan. You are not using a TRIM anyway in the actual update.

If you are on 9i or later, eliminate the count(*) and just do a MERGE (with no TRIM).

Cross-posted on OTN [message #190426 is a reply to message #190424] Wed, 30 August 2006 11:49 Go to previous messageGo to next message
Todd Barry
Messages: 4819
Registered: August 2001
Senior Member
No Message Body
Re: Cross-posted on OTN [message #190507 is a reply to message #190426] Thu, 31 August 2006 02:21 Go to previous message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
If you're on 8i or earlier, you can still loose the count(*) and do
UPDATE Item_table SET ENTERPRISE_CODE=nvl(trim(mappedEntCode),ENTERPRISE_CODE)
where item_id = :new.item_id and primary_enterprise_code = EntCode;
IF sql%rowcount = 0 THEN -- no record updated, so insert instead
  INSERT INTO Item_table ( ITEM_HTS_KEY, ITEM_ID, ECCN, COUNTRY, ENTERPRISE_CODE)
  VALUES (itemKey,:new.item_id, :new.eccn, :new.country, EntCode);
END IF;
Previous Topic: Creating a view contains...
Next Topic: how to check return status and write to the log file?
Goto Forum:
  


Current Time: Sun Dec 11 02:31:38 CST 2016

Total time taken to generate the page: 0.08381 seconds