Given that your number of users is huge ("Active users nearly 6 Million") per your comment, any one query with a predicate on user_id is going to go after a very tiny percent of your data. So, your best strategy for performance is simply to index (user_id,timestampcol) and not try to use partition pruning for performance purposes. Partition pruning to reduce I/O is useful when your queries are after a significant portion of a partition and therefore ill-suited to index use and more geared toward full scans, but that is not your situation.
For maintenance purposes that index should be local, not global, so make sure to add the LOCAL keyword.
As for the partitioning, your only aim is maintenance and space management. So, go with the simplest method and partition by range on timestampcol (whatever the real name is) at an interval of 1 month (or maybe 1 day, see below for discussion). At 3 years retention, monthly gives you a quite manageable 36 partitions which you can roll off the back end and is the most common partitioning strategy I see. Each time you advance to a new month, you can have a scheduled job compress the month partition just completed for significant space savings.
Here's why I would not suggest the other options:
Daily partitioning: generally in most cases this is too many partitions (1095 for 3 years) and provides no benefit if you are using indexes (actually a penalty for having more index segments to seek when you request a large range for timestampcol that spans many partitions). However, given that you will be going after thousands of rows and not one row or a few rows, the overhead of these extra seeks will be minimal and you may barely notice the speed drop. Also, 1 billion rows per month is a quite hefty table - monthly partitions would give you 1 billion rows per partition, which you might find is unwieldy (e.g. requiring excessive temp space to create or rebuild index segments, compression, etc.). So, for that reason alone it might be worth going with daily rather than monthly partitions. We do tend to see daily being chosen when the table is truly massive.
Yearly partitioning: partitions too few and large, making them less manageable and postponing when you can safely compress old data.
Composite range/hash (on user_id): while your queries would prune, there's no benefit if you are using the index, and the downside is a vast increase in # of partitions. If you had daily partitions and hash 100 on the subpartitions, you'd have 109,500 segments, which is ridiculous. Even at monthly partitions, that's still 12*100 = 1,200 segments. That could be doable if it gained you something important, but it doesn't.
The other options are all variations on these options.
For others who might read this, the above advice only applies to OP's situation where all queries apply a predicate on a high cardinality column (user_id) which is therefore an index candidate. The above advice would have to be adjusted in other situations.
I'll now go through your individual questions.
Range partitioning (interval = every 1 day):
Pros: Easy maintenance as Data purging is easier Cons: Since the query
can have a date range for a week (7 partitions), a month (30
partitions) or a year (365 partitions), Will this impact the query
performance as I need to aggregate over multiple partitions?
Yes, if you query a year you will have to do 365 separate index lookups which means more block reads for the top of each index segment. But as I explained above, because you're going after thousands of rows your overall time will be mostly table block reads and not index top-level block reads, so this won't be significant in comparison. It would be very significant if you were going after 1 row per query execution and wanted to do this in a loop a million times, but that's not your case.
Hash partitioning (partition key by user_id, num partitions = 128
partitions):
Pros: All data for a specific user will be in the same partition and
hence can query performance would be better Cons: Might result in
skewed data as the partitioning is based on user_id and not
transaction_id Difficulty in purging as the old data is spread across
multiple partitions.
No, performance would not be better. While you'd do a seek on only one index segment, the # of table blocks would not be appreciably less, because one user represents such a tiny percent of each segment that having all the rows for a user in one segment is unlikely to bring those blocks together enough to reduce I/O. It would not skew it, however, because it applies a hash function to user_id so would group together an even # of user_id values in each bucket. You are correct about the negative impact on manageability. You'd never be able to archive or compress.
Range-Hash partitioning (interval = every 1 month, sub-partition by
user id with 32 partitions)
Pros: Benefits of range partitioning (easier maintenance) Most of my
queries will be for weekly or monthly and hence most of the time the
query should find the data in the same segment Cons: Will it have any
impact if I have to query for a year and aggregate monthly?
As I explained above, finding your data in the same segment is not much of a benefit if using an index when you are after such a tiny percent of the segment, as the blocks you need are likely scattered all over, not reducing your block read count by that much over finding them in different partitions.
Hash-Range partitioning (partition by user with 32 partitions,
sub-partition by range (interval = every 1 month))
I have no clue how this can be different from Range-Hash Partitioning.
I've never tried using range partitioning at the sub-partition level, as it doesn't make a lot of sense (you can't easily archive old data). But if you did, the performance should be the same.
Conclusion: Partitioning is performance-critical for situations where queries need to hit a significant portion of the data (more than 1% or so) and so no index is used. Then the size of the segment(s) you have to scan is everything and the above partitioning choices far more interesting. But when your queries are going after a tiny percent of the data and you'll be using an index, really your only reason for using partitioning is maintenance/archival (which is good enough of a reason, of course).
USER_ID