Any suggestions?

[sbix]

A customer of mine has the following procedure:
Once someone needs to send a mail from the application system, writes a record in an Oracle table, with the message and a "NEW RECORD" flag in a field of the row.
Periodically a script scans this table and when it finds a a row with this "NEW RECORD" flag, sends the e-mail and update the field with a "ELABORATED RECORD" flag.
This means a periodical full table scan on an always growing table.
I would like to write a trigger which sends the e-mail each time a new row is appended in the table, is it possible?

 

[SantaMufasa]

Sbix,

It's absolutely possible. (Now, do you want me to write the trigger for you, or will you write it?) Since it is about 5:00 a.m. here and I haven't been to bed yet tonight, if you want me to write the trigger, it will need to wait until I've been to sleep for a little while...or someone else that is awake, such as Sem/Dima (who is 9 hours ahead of me) can help you if he wishes.

Mufasa
(aka Dave of Sandy, Utah, USA)
@ 11:56 (25Oct04) UTC (aka "GMT" and "Zulu"),
@ 04:56 (25Oct04) Mountain Time

 

[sbix]

Hi Muf,
thanks for your valuable help (as usual).
I found something here in the FAQ section, related to sending an e-mail from the inside of a stored procedure.
Is it feaseble also by a trigger?
Further question:
Probably I have to think something about a delayed retry, just in case of problems on the mail server, this would mean I am back on the timed script again.
Just to speed up the operation, do you think a bitmap index built upon the flag will be useful?

 

[sem]

I think that [bitmap] index on flag is a solution. In fact even B-tree index may help, because the amount of not yet sent messages is expected to be relatively small. Nullifying flag on successfully sent messages may help to decrease B-tree index size (in case you can not use bitmap indexes e.g. in standard edition)

Regards, Dima

http://seminihin.narod.ru

 

[sbix]

Thanks Dima!!!!

 

[Beilstwh]

Using a bitmap index on a table that is being updated frequently is NOT a good idea, it would have to rebuild the entire bitmap everytime. A much better idea to to create a btree index on the flag column. You will never have to do a full table scan again and the email code will go very fast.

Bill
Oracle DBA/Developer
New York State, USA

 

[sem]

A good catch, Bill.
Yes, bitmap indexes in OLTP environment may be a nightmare, but mainly due to locking issues as adding rows with already "known" values is quite fast (in fact much faster then that for B-tree) and doesn't require full rebuild. You may test it yourself (not quite clean but descriptive):

SQL> create table bmp_test(id number, flag char(1));
SQL> create bitmap index bmp_flag_ind on bmp_test(flag);
SQL> set timing on
SQL> insert into bmp_test select rownum, mod(rownum,3) from dba_objects where rownum<10000;
Elapsed: 00:00:00.07
SQL> set timing off
SQL> rollback;
SQL> drop index bmp_flag_ind;
SQL> create index bmp_flag_ind  on bmp_test(flag);
SQL> set timing on
SQL> insert into bmp_test select rownum, mod(rownum,3) from dba_objects where rownum<10000;
Elapsed: 00:00:04.04

Regards, Dima

http://seminihin.narod.ru

 

[Beilstwh]

Sorry, when I said it had to rebuild, I meant it had to rebuild the map for the particular key, not the entire table.

Bill
Oracle DBA/Developer
New York State, USA