I'm new to Sybase IQ and am designing a data warehouse using Sybase IQ. It's been stressed to me that deleting individual rows out of a Sybase IQ table is a bad thing to do because the resulting dead space plays havoc with subsequent query response. My question is how the heck do you age data out of a table?? Periodically, we need to roll off the oldest stuff in order to make room for new data. I looked at doing a data extract, but that method does not support going to tape. My management is unwilling to pay for double the disk space in order to leave half empty in order to extract to disk. Any ideas???
Individual transactions (deletes, updates, inserts) are slow in IQ because of its Architecture.
How many transaction tables do you have?
What you can look at is splitting the transaction data into tables (maybe on date ranges) and creating a view over these tables. This gives you a few advantages.
1) Load is faster.
2) Queries on views run faster because IQ will use multiple CPUs since the data is split into multiple tables.
3) You can extract / drop the entire table when its aged. You should be able to copy a export file onto tape.