frequently deleted table

frequently deleted table

Post by stefanss » Thu, 06 Feb 2003 00:41:37



Hello

i would like to know how to get rid of this problem,
some tables in our database get populated and deleted many times
therefore after a while even when the table is empty a select * from
that
table  takes about 10 seconds
however after dropping and recreating the table the result of  select
* from ...  is of course immediate.

that table as a heap structure.

It seems like , like in oracle we have the watermark problem
therefore in oracle i just do a truncate table
but here ingres the result of a modify to truncated is that you lose
the indexes
and datas
so is this problem something to do with the "watermark" and how can i
fixed this
in a script that will be run every 2 weeks or so.

regards

 
 
 

frequently deleted table

Post by Karl & Betty Schend » Thu, 06 Feb 2003 01:58:01



Quote:>some tables in our database get populated and deleted many times
>therefore after a while even when the table is empty a select * from
>that
>table  takes about 10 seconds
>however after dropping and recreating the table the result of  select
>* from ...  is of course immediate.

>that table as a heap structure.

>It seems like , like in oracle we have the watermark problem
>therefore in oracle i just do a truncate table
>but here ingres the result of a modify to truncated is that you lose
>the indexes
>and datas

If I understand correctly, what you want to do is simply a
modify <tablename> to heap
which will rebuild the heap table and reclaim lost disk space.
It will drop any (non-persistent) secondary indexes, but it will not
delete existing data in the table.  You can simply recreate any secondary
indexes.  (I am not a big fan of heap tables with secondary indexes
in any case.)

Heap may not be an appropriate base storage structure for your situation.
You might want to look into using a hash structure, for instance.

--

K/B Computer Associates       www.kbcomputer.com
Ingres, Unix, VMS             Consulting and Training

 
 
 

frequently deleted table

Post by Roy Han » Thu, 06 Feb 2003 02:49:23



Quote:> i would like to know how to get rid of this problem,
> some tables in our database get populated and deleted many times
> therefore after a while even when the table is empty a select * from
> that
> table  takes about 10 seconds
> however after dropping and recreating the table the result of  select
> * from ...  is of course immediate.

> that table as a heap structure.

> It seems like , like in oracle we have the watermark problem
> therefore in oracle i just do a truncate table
> but here ingres the result of a modify to truncated is that you lose
> the indexes
> and datas
> so is this problem something to do with the "watermark" and how can i
> fixed this
> in a script that will be run every 2 weeks or so.

Ingres will not re-use space left by deleted rows in a heap unless the space
is in the "last" page of the heap.  The delay you are experiencing on
unrestriced queries is due to having to read a long chain of empty pages to
find the little remaining data at the end.

A couple of possible solutions suggest themselves.

(1) See if you can avoid using a heap.  Space left by deleted rows is reused
automatically in hash and ISAM tables.  Space left by deleted rows is also
managed more intelligently in b-trees.  I suppose it is possible that this
might also entirely eliminate the need for any secondary index.
(2) Recreate your secondary indices WITH PERSISTENCE just once, and
thereafter MODIFY table TO HEAP periodically.  Effectively this will
eliminate empty pages in the heap, and the secondary indices will be rebuilt
automatically.

Roy Hann
Rational Commerce Ltd.
www.rationalcommerce.com
"Ingres development, tuning, and training experts"

 
 
 

1. instead of delete trigger delete data from table using execute and temporary table for deleted

Hello,

SQL Server 2k has got me confused...below the instead of trigger that
causes all my problems...below the trigger the explanation of my
problem

===============================================================================
create trigger [triggerConcurrenten] on [dbo].[concurrenten]
instead of delete
as
begin
        -- ***** Declare local vars




        begin transaction

        -- ***** Initialize



        select deleted.* into #deleted from deleted

        /*
        Delete data from concurrenten table using the below sql
statement
        works perfectly.
        =============
        delete
        from concurrenten
        where exists(
        select * from #deleted d
        where concurrenten.[id] = d.[id])
        */

        /* Delete using the execute function fails! */







        begin
                raiserror('[triggerConcurrenten]: Error occured!', 16, 1)
                rollback transaction
        end

        commit transaction      
end

===============================================================================

In the trigger I've tried a 'direct' sql statement to delete data from
the concurrenten table. No problem at all...but when I use the execute
function the exact same delete statement fails...what am I doing
wrong?

Thanx in advance for the answer!

Best regards,

Peter

2. Opinion Poll

3. How to identify frequently used tables

4. FM plugin & timecode?

5. FW: What tables the database access most frequently

6. dbcombo - can't pick off the list

7. What tables the database access most frequently

8. PROBLEM: Accessing Foxpro table(Deleting, update, insert) READ ONLY

9. Delete trigger shows nothing in Deleted table

10. No INSTEAD OF DELETE triggers for tables with ON DELETE CASCADE constraints

11. Deleted records when text file is linked as table (#deleted)

12. .Delete Deletes From BOTH Joind Tables - Help!

13. Can't delete table using Utility|delete