dummy table for inserts

Here's an odd question. There is an external (legacy) process that we're not big on changing. It inserts data into many tables for our process, but most of these tables are not used anymore. The volume of data is fairly large, so we would like to not populate the unnecessary data. Is there a way to define a DUMMY table that externally would look like a 'real' table to a process and accept the insert statements, but wouldn't represent a real table and wouldn't persist the data? Then we would accomplish the task of not storing the unneeded data without changing the external process.

Thanks!

you could use a view ontop of a table and then put an instead of trigger that would do nothing for inserts.
here is an example :

CREATE TABLE TESTER_TB
(CNOTNULL NUMBER,
CNULL NUMBER)
/
CREATE OR REPLACE VIEW TESTER_VW AS
SELECT decode(CNOTNULL,NULL,999,CNOTNULL) CNOTNULL, CNULL FROM TESTER_TB
/
CREATE OR REPLACE PROCEDURE tester_insert (in_cnotnull IN NUMBER,
in_cnull IN NUMBER) AS
vdummy number;
BEGIN
vdummy := 0;
END tester_insert;
/
CREATE OR REPLACE TRIGGER TESTER_TG
INSTEAD OF INSERT ON TESTER_VW
FOR EACH ROW
BEGIN
tester_insert(:new.cnotnull,:new.cnull);
END;
/
SQL> insert into TESTER_VW (CNOTNULL, CNULL) values (1,1);
1 row created.

SQL> commit;
Commit complete.

SQL>select * from tester_vw;
no rows selected

SQL>select * from tester_tb;
no rows selected

jkoopmann,
Thanks - that's a great idea.
Actually by doing this:
CREATE OR REPLACE TRIGGER TESTER_TG
INSTEAD OF INSERT ON TESTER_VW
FOR EACH ROW
BEGIN
NULL;
END;

you don't even need the stored proc.
For anyone who might be curious Oracle wont let you have an 'INSTEAD OF...' clause for a trigger on a TABLE. (I tried it). That's why the view is needed.

How about using a Global Temporary Table? It only contains the data until either the end of the transaction or the end of the session based on how you define it.

example:

CREATE GLOBAL TEMPORARY TABLE duh
(col1 datatype1,
col2 datatype2, ...
) ON COMMIT DELETE ROWS;

duh will have a higher performance impact as inserts will actually happen, and then, duh, will have to delete the rows again.

by putting a "smart" procedure in the middle that just throws away the data, there is only logic code that will be executed (very small overhead) compared with the disk i/o that would happen in duh.

Is there any advantage to using the empty procedure (tester_insert) as opposed to no command at all in the trigger? Running some tests I could not find much detectable difference.

Concerning Jkoopman's note...

"duh will have a higher performance impact as inserts will actually happen, and then, duh, will have to delete the rows again."

The point of the global-temporary is that it doesn't persist data. You don't have to delete the data, it will be removed for you. Also, there is not any disk access provided the amount of data is low.

Global temporaries seem to be very efficient in my environment.

doug, agreed data does not get deleted at the end of the persistance, more like a truncate. but the user in the session can delete.

also, agreed, if data is low you will not experience issues with the DML.

but these temporary tables do create temp segments in the temporary tablespace or system tablespace if not defined. and if the volume is large (as paul said there would be) they will go to disk and thus more i/o.

my issue was to not incure any i/o weather in the buffer or disk and also no other internal oracle locking/latching for the allocation/deallocation of resources associated with the segments needed.

do you agree with this?

You will incur I/O by simply having a transaction... Redo-logs will be written. If you want to reduce the amount of temporary storage you could add a trigger to the Temp table that simply sets each column to NULL.

also wanted to add that while redo is not generated for the DML on these temporary tables, ie..no i/o
but
undo is generated for transaction based recovery, ie..i/o cost