I was told that partitioned table indexed must always start with the partition key columns.
Is this always the case or does it depend on use case? When would you want to create indexes in this way?
The documentation just mentions that it is strictly unnecessary but can be helpful. My understanding is partitions behave like normal tables. Each gets their own index. So, I'd expect the reasoning behind creating the index on the partition should be the same as if it were just a normal table (assuming it has the same subset of data as the individual partition). Is this a correct understanding?
Any other performance considerations when it comes to partitioned table indexing? Specifically, partitioning by range where the range is a single value.