Re: Growth of Index

You say that you have a table indexed by timestamp. It is five million rows in size. Every day you add a million rows and every day you delete the million oldest rows. You are concerned about the growth of your index.

I can tell you how to solve the problem of a continually growing index. My solution will also reduce the amount of time you must be spending to delete the million rows.

The solution is to create a new table and index for each day. Use a naming convention that makes the date part of the table name. Create a view with the same name as your current table. The definition of this table would look something like this:

    CREATE VIEW XXX AS

        SELECT * FROM XXX20_JUN-95
      UNION
        SELECT * FROM XXX21_JUN-95
      UNION
        SELECT * FROM XXX22_JUN-95
      UNION
        SELECT * FROM XXX23_JUN-95
      UNION
        SELECT * FROM XXX24_JUN-95
      UNION
        SELECT * FROM XXX25_JUN-95

Also create a writable view that serves as an alias for the table that corresponds to the current day.

Every night run a job that creates a new table, redefines the views and drops the oldest table.

-- 
==============================
Mark Grand