Results 1 to 10 of 10

Thread: dummy table for inserts

  1. #1
    Join Date
    Jun 2004
    Posts
    3

    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!

  2. #2
    Join Date
    Mar 2003
    Posts
    468
    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

  3. #3
    Join Date
    Jun 2004
    Posts
    3
    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.

  4. #4
    Join Date
    Jun 2004
    Posts
    3
    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;

  5. #5
    Join Date
    Mar 2003
    Posts
    468
    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.

  6. #6
    Join Date
    Jun 2004
    Posts
    3
    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.

  7. #7
    Join Date
    Jun 2004
    Posts
    3
    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.

  8. #8
    Join Date
    Mar 2003
    Posts
    468
    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?

  9. #9
    Join Date
    Jun 2004
    Posts
    3
    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.

  10. #10
    Join Date
    Mar 2003
    Posts
    468
    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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •