I need to implement something rather strange. My application creates internal application sessions for interacting with the client. Session is basically a db record with a unique ID.
The session needs to be stored forever, even after the client had disconnected.

The problem is that ACTIVE sessions need to be checked very often, while FINISHED sessions are used only by reporting.

If I put them both in the same table, then finding ACTIVE session will become progressively slower as the number of old sessions grows. Indexing on “active” flag will not help as it will have very low selectivity.

If I put them in separate tables then I need to create two identical entities in the application.

I’m wondering if there is some elegant solution to this.

Thanks,
Philopator.

I'm not agree with this.
If you have for example 10 sessions with 'active' flag and 100.000 sessions with flag set to 'finished',
then a query like "from Session where flag='active'" should indeed profit extremely from the existence the index on the flag column,
because In this case you have a extremely high selectivity (1:10.000).
You have only low selectivity if you query "from Session where flag='finished'" (selectivity ~ 1:1)
but this query is not so relevant to you.
You must distinct GENERAL selectivity of boolean values (which is very low as we know),
from SPECIFIC selectivity of boolean values (=for a determinate boolean value),
which in your specific case is extremely high.

regards
Guenther D.

You're probably right - we'll check it out.

Thanks for the reply.