Is partitioning likely useful for this application
Let's say I have a table of products with lots of data in it, 15GB. Just for the sake of argument, say 12,000,000 rows. This table has built up over the years as new products come and go. Products are never removed for a variety of reasons. So, say today half of those are active products, the rest are inactive, and in rare cases, inactive products can be re-activated. Products are activated and inactivated during the day.
Let's further say there this table ends up feeding a variety of systems, each with their own unique data requirements. So, some data might go to system A, some data might go to system B after joining with various other tables, etc.
In the end, the problem has become the time to serial read the table to pick out active items to be processed in 100 or so different ways and purposes. Not that any one particular use by itself is overly slow, but, when you have so many different systems needing the data, each in different ways with many different tables potentially needing joined, it can get lengthy when all added up, and, if there was a simple way to halve this time, that would be a gold mine.
So, do not worry about mixing such queries with online purposes, not the case here. I realize there are many potential ways to tackle this, but for various reasons, most of them are just not feasible. So, let's stay OT here and only deal with partitioning as a potential solution as there is no need to discuss the myriad of other potential ways to deal with it and I am not asking about those.
Would partitioning on a single field, active or inactive flag, likely be a good way to deal with this, i.e., we would then easily read only the active products for 99% of our processes that need only active items. Anything else that also needs inactive products can get those also by reading the entire table. Seems like if 50% of the items are active, then, the time will be around 50% to retrieve those over the table as is.
Is there any potential downside to this method, or, will the performance not likely be as good as it may seem to me?