Index on Columns with only 'Y' and 'N'
914856Feb 2 2012 — edited Feb 6 2012Our transaction table has roughly 100 mn rows. Out of these about 60mn rows are not actively used.
We have composite indexes on several columns including dates, and these are used for regular queries.
Will the addition of a status ='y' or 'n' column, and creating an index on such a column be useful for increasing query performance?
For example, will adding a condition such as
WHERE .... ... ... AND ACTIVE_FLAG = 'Y'
help in increasing query performance?
Does creating such a column and adding an index help?
What type of index do you recommend?
The alternative some staff are advocating is archiving of INACTIVE rows in a separate table. This will have a big impact on business process. But, we may be able to pull it off.
Is there any other solution.
I am a complete newbie to performance tuning although i am familiar with Queries and PL/SQL.
I am on very tight time constraints for first level revert on this. I will have more time once i know the direction in which to proceed.
Please excuse if I have broken some forum etiquette. I am new to the forum also.