My tables(indexed) are overflowing - Data is growing at a very fast rate. This slows down my queries from MS Access front end (ODBC route). What can I do to manage this? My users frequently need to access old data.
If this doesn't help then your indexes might not be getting used.
First I'd identify the sql statements that are causing problems and generate an explain plan for them to see if you are actually using the indexes. If you're not then you either need to modify your sql or change your indexes.
Once you have the explain plan check for the following:
Table scans on large tables.
Unnecessary sort operations (distinct and order by clauses that the application doesn't need).
Non selective index scans, if an index is not selective e.g. not many unique values, you might want to use a bitmap index.
Although I hate to say it (I like command lines) you might like to try to use the Enterprise Manager GUI tools for evaluating the sql as they're very good and make the whole process very intuative.
If your tables really are VERY big then you might want to partition them and allow parallel query plans but this is quite a complex option to implement properly and you'll need to spend a long time with the tuning manual before you do it.