# Database Discussions > Oracle >  dummy table for inserts

## paul_adp

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!

----------


## jkoopmann

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

----------


## paul_adp

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.

----------


## doug.anderson

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;

----------


## jkoopmann

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.

----------


## paul_adp

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.

----------


## doug.anderson

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.

----------


## jkoopmann

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?

----------


## doug.anderson

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.

----------


## jkoopmann

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

----------

