All the queries uses the vendor product and thats why this column is a perfect fit as a partition column.My main table is big (10M+) (Product), but other tables can also be big(1M+)..
I assume you have query performance problems and are hoping partitioning will help? Are you read heavy, or write intensive, or both? 10 million rows, especially if they aren't super wide, doesn't seem like a huge number to me. Do you have example queries with explain plans that you think would benefit from the system being partitioned? I just know that as an engineer, sometimes I like to make use of new tools, even when it isn't the best solution for the problem I am actually experiencing. How confident are you that you NEED partitions is my real question.
