Hi All, @hanson
Context
I have table like below.
I have created Shard key on Primary_key_column for equal distribution among 16 partitions
We will be running frequent queries like below.
Select Dimension, SUM(Measure) from Table_1 where Attribute1=‘A’ and Attribute2=‘B’ group by Dimension.
Since I will be using Attribute1 and Attribute2 in filter clause, I decided to chose SORT_KEY (Attribute1, Attribute2)
Problem Statement.
Our DBAs has defined Segment rows variable as 1000K records. See screenshot below
However, In Table_1 , we have total 900K records. that are distributed among 16 partitions, one partition keeps almost 60k records only… All 60k records get fit into a single segment of that partition.
Hence Sort_key is not giving any benefits as all 16 segments in 16 partitions are being scanned and we are not getting advantage of Segment elimination.
As I checked with DBAs, They can not reduce segment rows because it is defined at database level and not schema level.
Every team is using same database with their own schemas.
Question
Can you please suggest what is the better solution for us. we will always have Attribute1 and Attribute2 in filter clause. How can we improve performance of queries?
Thanks
Himanshu