Need help with "Upsert" trigger in Oracle

I have an Oracle table that contains 30 million records of a document archive that is built similar to an EAV table. It contains in each row an id, a keyword, and a value. There may be as a many as 10 records that make up one "document" entry.

 Queries to determine how many "documents" a person has in their archive take well over 30 seconds if they have a lot of documents, some take almost a minute.

I would like a trigger to insert a new record in the LIC_DOCCCOUNT table when a record with the keyword of CLRID is inserted and to decrement the DOUCCOUNT in the LIC_DOCCCOUNT with the same CLRID value when deleting, and I only want it to happen for certain CLRID values.

I'm not sure exactly how to create this trigger. Not a lot but here is what I have so far.

CREATE OR REPLACE TRIGGER trLIC_DOCCOUNT
    AFTER
        INSERT OR
        DELETE
    FOR EACH ROW
    ON fkp_attib
BEGIN
    -- Structure of LIC_DOCCOUNT Table:
    --  CLRID    NUMBER(10)  NOT NULL,
    --  DOC_COUNT  INTEGER
    -- FKP_VALUE has the value that we need to check for
    IF :New.fkp_keyword = 'CLRID' AND :New.fkp_value in (12345, 67890, 654123, 987456) THEN
        CASE
        WHEN INSERTING THEN
            -- this was an insert so insert a new record in LIC_DOCCOUNT 
            -- if a record with the same CLRID doesn't exist, otherwise, 
            -- update the record with the CLRID
        WHEN DELETING THEN
            -- decrement the DOC_COUNT for the record with the CLRID
        END CASE;
    END IF;
END;
/

Open in new window


Here's code we use to obtain the count:
select count(*) as DocCount from (
    select distinct doc.FKP_FD_RID 
    from fkp_attib doc, fkp_attib cli
    where doc.FKP_FD_RID=cli.FKP_FD_RID 
      and cli.FKP_KEYWORD='CLRID' and cli.FKP_VALUE = :PID group by doc.FKP_FD_RID
    having sum(decode(doc.fkp_keyword||doc.fkp_value,'DISTRIBUTIONINTERNAL',1,0))=0
    )

Open in new window

LVL 27
Eddie ShipmanAll-around developerAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

slightwv (䄆 Netminder) Commented:
With the distinct and having sum in the query, I can see where it might be inefficient.

I'm not fully understanding what you are trying to do.

Can you provide some sample raw data, some insert and deletes and expected results?
Eddie ShipmanAll-around developerAuthor Commented:
Not sure how many records you need but here's 10 documents:
FKP_FD_RID	FKP_KEYWORD	FKP_VALUE
1621438	FILENAME	\1016713\770\Letters\CLDL20100302
1621438	CLRID		1016713
1621438	DOC_NAME	CLDL
1621438	PRINT_DATE	20100303
1621438	DESIGNATION	CISR
1621438	DISTRIBUTION	INTERNAL
1621439	FILENAME	\965699\603\Letters\CLDL20100302
1621439	CLI_RID		965699
1621439	DOC_NAME	CLDL
1621439	PRINT_DATE	20100303
1621439	DESIGNATION	CISR
1621439	DISTRIBUTION	INTERNAL
1621440	FILENAME	\1009723\759\Letters\CLDL20100302
1621440	CLI_RID		1009723
1621440	DOC_NAME	CLDL
1621440	PRINT_DATE	20100303
1621440	DESIGNATION	CISR
1621440	DISTRIBUTION	INTERNAL
1621441	FILENAME	\953310\178\Letters\CLDL20100302
1621441	CLI_RID		953310
1621441	DOC_NAME	CLDL
1621441	PRINT_DATE	20100303
1621441	DESIGNATION	CISR
1621441	DISTRIBUTION	INTERNAL
1621442	FILENAME	\1098748\54\Letters\CLDL20100302
1621442	CLI_RID		1098748
1621442	DOC_NAME	CLDL
1621442	PRINT_DATE	20100303
1621442	DESIGNATION	CISR
1621442	DISTRIBUTION	INTERNAL
1621443	FILENAME	\1088006\279\Letters\CLDL20100302
1621443	CLI_RID		1088006
1621443	DOC_NAME	CLDL
1621443	PRINT_DATE	20100303
1621443	DESIGNATION	CISR
1621443	DISTRIBUTION	INTERNAL
1621444	FILENAME	\996175\172\Letters\CLDL20100302
1621444	CLI_RID		996175
1621444	DOC_NAME	CLDL
1621444	PRINT_DATE	20100303
1621444	DESIGNATION	CISR
1621444	DISTRIBUTION	INTERNAL
1621445	FILENAME	\1092295\580\Letters\CLDL20100302
1621445	CLI_RID		1092295
1621445	DOC_NAME	CLDL
1621445	PRINT_DATE	20100303
1621445	DESIGNATION	CISR
1621445	DISTRIBUTION	INTERNAL
1621446	FILENAME	\900796\505\Letters\CLDL20100302
1621446	CLI_RID		900796
1621446	DOC_NAME	CLDL
1621446	PRINT_DATE	20100303
1621446	DESIGNATION	CISR
1621446	DISTRIBUTION	INTERNAL
1621447	FILENAME	\1091439\721\Letters\CLDL20100302
1621447	CLI_RID		1091439
1621447	DOC_NAME	CLDL
1621447	PRINT_DATE	20100303
1621447	DESIGNATION	CISR
1621447	DISTRIBUTION	INTERNAL
1621448	FILENAME	\921784\556\Letters\CLDL20100302
1621448	CLI_RID		921784
1621448	DOC_NAME	CLDL
1621448	PRINT_DATE	20100303
1621448	DESIGNATION	CISR
1621448	DISTRIBUTION	INTERNAL
1621449	FILENAME	\1113354\702\Letters\CLDL20100302
1621449	CLI_RID		1113354
1621449	DOC_NAME	CLDL
1621449	PRINT_DATE	20100303
1621449	DESIGNATION	CISR
1621449	DISTRIBUTION	INTERNAL

Open in new window



expected results would be just insert the number of documents for each CLRID in the list into the LIC_DOCCOUNT table.
awking00Information Technology SpecialistCommented:
Given your sample data, what would the LIC_DOCCOUNT table look like at that stage? Provide some data for, say, two inserts and select maybe three existing records for deletion, then show what the LIC_DOCCOUNT should look like after the trigger.
Your Guide to Achieving IT Business Success

The IT Service Excellence Tool Kit has best practices to keep your clients happy and business booming. Inside, you’ll find everything you need to increase client satisfaction and retention, become more competitive, and increase your overall success.

Download eBook now!
Eddie ShipmanAll-around developerAuthor Commented:
It is easy enough to write a insert and delete queries here, I don't need to provide those. The doc_ccount is based on the count of the CLRID value.

The contents of the LIC_DOCCOUNT table should look like this if my limiting CLRIDs are: 953310, 1092295, 1113354, 1016713:
CLRID       DOC_COUNT
1016713     1
953310      1
1092295     1
1113354     1

Open in new window


The other records that aren't in my limiting list do not get entries in the  table. The trigger only should work on the limiting list. However, the data provided includes the DISTRIBUTION keyword with a value of INTERNAL and those would be omitted from the count. Please use this revised data:
FKP_FD_RID	FKP_KEYWORD	FKP_VALUE
1621438	FILENAME	\1016713\770\Letters\CLDL20100302
1621438	CLRID		1016713
1621438	DOC_NAME	CLDL
1621438	PRINT_DATE	20100303
1621438	DESIGNATION	CISR
1621439	FILENAME	\965699\603\Letters\CLDL20100302
1621439	CLRID		965699
1621439	DOC_NAME	CLDL
1621439	PRINT_DATE	20100303
1621439	DESIGNATION	CISR
1621440	FILENAME	\1009723\759\Letters\CLDL20100302
1621440	CLRID		1009723
1621440	DOC_NAME	CLDL
1621440	PRINT_DATE	20100303
1621440	DESIGNATION	CISR
1621441	FILENAME	\953310\178\Letters\CLDL20100302
1621441	CLRID		953310
1621441	DOC_NAME	CLDL
1621441	PRINT_DATE	20100303
1621441	DESIGNATION	CISR
1621442	FILENAME	\1098748\54\Letters\CLDL20100302
1621442	CLRID		1098748
1621442	DOC_NAME	CLDL
1621442	PRINT_DATE	20100303
1621442	DESIGNATION	CISR
1621443	FILENAME	\1088006\279\Letters\CLDL20100302
1621443	CLRID		1088006
1621443	DOC_NAME	CLDL
1621443	PRINT_DATE	20100303
1621443	DESIGNATION	CISR
1621444	FILENAME	\996175\172\Letters\CLDL20100302
1621444	CLRID		996175
1621444	DOC_NAME	CLDL
1621444	PRINT_DATE	20100303
1621444	DESIGNATION	CISR
1621445	FILENAME	\1092295\580\Letters\CLDL20100302
1621445	CLRID		1092295
1621445	DOC_NAME	CLDL
1621445	PRINT_DATE	20100303
1621445	DESIGNATION	CISR
1621446	FILENAME	\900796\505\Letters\CLDL20100302
1621446	CLRID		900796
1621446	DOC_NAME	CLDL
1621446	PRINT_DATE	20100303
1621446	DESIGNATION	CISR
1621447	FILENAME	\1091439\721\Letters\CLDL20100302
1621447	CLRID		1091439
1621447	DOC_NAME	CLDL
1621447	PRINT_DATE	20100303
1621447	DESIGNATION	CISR
1621448	FILENAME	\921784\556\Letters\CLDL20100302
1621448	CLRID		921784
1621448	DOC_NAME	CLDL
1621448	PRINT_DATE	20100303
1621448	DESIGNATION	CISR
1621449	FILENAME	\1113354\702\Letters\CLDL20100302
1621449	CLRID		1113354
1621449	DOC_NAME	CLDL
1621449	PRINT_DATE	20100303
1621449	DESIGNATION	CISR

Open in new window

slightwv (䄆 Netminder) Commented:
In your sample data I only see one CLRID record.  The fkp_value for that one isn't in the pseudocode for the trigger you posted so I don't see how that row ends up in the LIC_DOCCOUNT table.

But, I sort of get the idea.  Instead of incrementing and decrementing in the trigger, can you not just compute the count every time?

The insert code should go something like (untested, just typed in):
            -- this was an insert so insert a new record in LIC_DOCCOUNT
            -- if a record with the same CLRID doesn't exist, otherwise,
            -- update the record with the CLRID
    update  LIC_DOCCOUNT set doc_count=doc_count+1 where clrid=:New.fkp_value
   if sql%rowcount=0 then
      insert into LIC_DOCCOUNT(clrid,doc_count) values(:New.fkp_value,1);
   end if;
   end;


delete code:
update  LIC_DOCCOUNT set doc_count=doc_count-1 where clrid=:New.fkp_value

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Eddie ShipmanAll-around developerAuthor Commented:
Yeah, the initial code post didn't have the "filters" that matched the data I posted.
Hmm, never thought of that one, but it looks like it might work, let me work on it.

I will have to get an initial value to load the table to begin with, though.
slightwv (䄆 Netminder) Commented:
>>I will have to get an initial value to load the table to begin with, though.

Should be a simple insert into as select statement.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Oracle Database

From novice to tech pro — start learning today.