Home » SQL & PL/SQL » SQL & PL/SQL » before insert trigger (Oracle)
before insert trigger [message #427197] Wed, 21 October 2009 09:19 Go to next message
thabo
Messages: 8
Registered: October 2009
Location: South Africa
Junior Member
please help me out, i constantly load files in a table in a database, in a given 24 hour circle i can get get 5000 files which translet into 6 million rows of data, to make any sense of this data i need to summarise the data into a daily table which i do by way of running a procedure as follows:-

CREATE OR REPLACE PROCEDURE IXC.CONT_IND_24_P
AS
BEGIN
INSERT INTO IXC.CONT_IND_24
SELECT
DISTINCT
TRUNC(sdate,'DD') sdate,
operate_type,
operate_result,
count(*) total_operate_result
FROM IXC.CONT_IND
where TRUNC(SDATE)=TRUNC(SYSDATE)-1
group by TRUNC(sdate,'DD'),operate_type,operate_result;
END;
/


this works fine as it gives me summarised daily stats for the previous day, i now need to summarise the info as i load it into a table by way of a before insert trigger, could someone help me with this
Re: before insert trigger [message #427202 is a reply to message #427197] Wed, 21 October 2009 09:35 Go to previous messageGo to next message
Its_me_ved
Messages: 979
Registered: October 2009
Location: India
Senior Member
Why you want to do it with a trigger while you can do this by a stored procedure?
Re: before insert trigger [message #427204 is a reply to message #427197] Wed, 21 October 2009 09:38 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
You can get rid of the DISTINCT on the SELECT - you're doing a group by, so the distinct is superfluous.

Why not just call this procedure from an After Insert trigger at statement level on the table?

Re: before insert trigger [message #427205 is a reply to message #427202] Wed, 21 October 2009 09:39 Go to previous messageGo to next message
thabo
Messages: 8
Registered: October 2009
Location: South Africa
Junior Member
thanks for your response, the thing is i get these files say every minute or so, running a procedure every hour will take a while hence i was hoping by a trigger i will be able to process the files as they come in.....by the way the files will still load normally on a base table but i will have to create another hourly table that processes the summarised hourly files....hope this makes sense
Re: before insert trigger [message #427211 is a reply to message #427205] Wed, 21 October 2009 09:49 Go to previous messageGo to next message
Its_me_ved
Messages: 979
Registered: October 2009
Location: India
Senior Member
Create a procedure that loads the data every hour ( by using
jobs/cron jobs). I dont think it will be a problem if the sql is
tunes properly.

Ofcourse this is my opinion. You need to wait for others opinion as well.
Re: before insert trigger [message #427213 is a reply to message #427197] Wed, 21 October 2009 09:52 Go to previous messageGo to next message
Michel Cadot
Messages: 63810
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
If you load 5000 files per day what is the purpose to get an immediate answer to a query when you know it is wrong when the query ends?

In clear, why do you want to it for each insert?

By the way, please read OraFAQ Forum Guide, especially "How to format your post?" section.
Make sure that lines of code do not exceed 80 characters when you format.
Indent the code, use code tags and align the columns in result.
Use the "Preview Message" button to verify.
Also always post your Oracle version with 4 decimals.

Regards
Michel

[Updated on: Wed, 21 October 2009 09:53]

Report message to a moderator

Re: before insert trigger [message #427217 is a reply to message #427213] Wed, 21 October 2009 09:57 Go to previous messageGo to next message
thabo
Messages: 8
Registered: October 2009
Location: South Africa
Junior Member
very true what you saying in terms of accuracy...but the thing is i have a live dashboad that monitors this, currently i only have values for the previous day hence i thought by using a trigger i could get to real time data as much as i can...i will also try to run an hourly proc
Re: before insert trigger [message #427218 is a reply to message #427217] Wed, 21 October 2009 10:09 Go to previous messageGo to next message
Its_me_ved
Messages: 979
Registered: October 2009
Location: India
Senior Member
< Comment removed >



[Updated on: Wed, 21 October 2009 10:14]

Report message to a moderator

Re: before insert trigger [message #427220 is a reply to message #427218] Wed, 21 October 2009 10:14 Go to previous messageGo to next message
thabo
Messages: 8
Registered: October 2009
Location: South Africa
Junior Member
for the current hour data is inaccurate but for the current hour minus one it is okay...by monitoring this on an hourly bases you likely to pick up issues quicker...by the way this is just on the database side, the same data is then manipulated on the application side and here one can determine any inconsistences
Re: before insert trigger [message #427221 is a reply to message #427217] Wed, 21 October 2009 10:14 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
Can't you base your dashboard on a view with the same definition as your select-clause for the insert, instead of on some intermediate table?

[Edit: just saw your last response. If you want to omit the running hour, simply exclude it from your view-data]

[Updated on: Wed, 21 October 2009 10:17]

Report message to a moderator

Re: before insert trigger [message #427224 is a reply to message #427221] Wed, 21 October 2009 10:23 Go to previous messageGo to next message
thabo
Messages: 8
Registered: October 2009
Location: South Africa
Junior Member
CREATE OR REPLACE PROCEDURE IXC.CONT_IND_H_P
AS
BEGIN
INSERT INTO IXC.CONT_IND_H
SELECT 
TRUNC(sdate,'HH') sdate,
operate_type,
operate_result,
count(*) total_operate_result
FROM IXC.CONT_IND
where TRUNC(SDATE,'HH')=TRUNC(SYSDATE,'HH')-1/24
group by TRUNC(sdate,'HH'),operate_type,operate_result;
END;
/


this is the hourly proc i have created, its been runing for more than 20 minutes now
Re: before insert trigger [message #427226 is a reply to message #427197] Wed, 21 October 2009 10:29 Go to previous messageGo to next message
cookiemonster
Messages: 12320
Registered: September 2008
Location: Rainy Manchester
Senior Member
Assuming there's an index on sdate repalcing this:
where TRUNC(SDATE,'HH')=TRUNC(SYSDATE,'HH')-1/24

with
WHERE sdate BETWEEN TRUNC(SYSDATE,'HH')-2/24 and (TRUNC(SYSDATE,'HH')-1/24) - 1/24/60/60

Would probably help.

Re: before insert trigger [message #427227 is a reply to message #427226] Wed, 21 October 2009 10:37 Go to previous messageGo to next message
thabo
Messages: 8
Registered: October 2009
Location: South Africa
Junior Member
thank you, it seems to run significently faster, i will do away with the trigger and run an hourly proc, please explain to a novice why it will run a lot quicker in this format
WHERE sdate BETWEEN TRUNC(SYSDATE,'HH')-2/24 and (TRUNC(SYSDATE,'HH')-1/24) - 1/24/60/60

and note in
where TRUNC(SDATE,'HH')=TRUNC(SYSDATE,'HH')-1/24


my sdate is indexed
Re: before insert trigger [message #427228 is a reply to message #427227] Wed, 21 October 2009 10:41 Go to previous messageGo to next message
Michel Cadot
Messages: 63810
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
The first one allows the usage of an (b-tree) index when the second does not.

Can't you think about using a materialized view instead? Based on only the last hour modifications it should be fast to refresh.

Regards
Michel

[Updated on: Wed, 21 October 2009 10:42]

Report message to a moderator

Re: before insert trigger [message #427235 is a reply to message #427228] Wed, 21 October 2009 10:51 Go to previous messageGo to next message
thabo
Messages: 8
Registered: October 2009
Location: South Africa
Junior Member
i am still a novice on oracle especialy PL/SQL, i am working really hard to master this as i am studying alone with the help of the free sites on the net Laughing as the courses are extremnly expensive here....all the help has been extremnly helpful and im extremnly grateful...thank you Laughing
Re: before insert trigger [message #427239 is a reply to message #427197] Wed, 21 October 2009 11:00 Go to previous message
cookiemonster
Messages: 12320
Registered: September 2008
Location: Rainy Manchester
Senior Member
To clarify Michel's first point:
Whenever you use a function on a column in a where clause it stops oracle from using any indexes on that column.
The only exception to that is if you create a function based index on that column that uses the exact same function call you're using in your query.

That's why I rewrote it to only apply functions to sysdate.
Previous Topic: Datawarehouse cleanup
Next Topic: BULK Collect Inside FOR LOOP?
Goto Forum:
  


Current Time: Thu Sep 29 12:25:12 CDT 2016

Total time taken to generate the page: 0.08331 seconds