One of the advantages of semantic partitioning is that the query processor may be able to take advantage of this and be able to disqualify range, hash, and list partitions at compile time. With hash partitions, only equality predicates can be used, whereas for range and list partitions, equality and in-equality predicates can be used to eliminate partitions. For example, consider table RA2 with its semantic partitioning defined on columns a1, a2 where (p1 values <= (500,100) and p2 values <= (1000, 2000)). If there are predicates on columns a1 or columns a1, a2, then it would be possible to do some partition elimination. For example, this statement does not qualify any data:
select * from RA2 where a1 > 1500
You can see this in the showplan output.
QUERY PLAN FOR STATEMENT 1 (at line 1). ................................ | | |SCAN Operator | | | FROM TABLE | | | RA2 | | | [ Eliminated Partitions : 1 2 ] | | | Index : RA2_NC2L
The phrase Eliminated Partitions identifies
the partition in accordance with how it was created and assigns
an ordinal number for identification. For table RA2,
the partition represented by p1 where (a1, a2)
<= (500, 100) is considered to be partition number one
and p2 where (a1, a2)
> (500, 100) and <= (1000, 2000) is identified as partition
number two.
Consider an equality query on a hash-partitioned table where all keys in the hash partitioning have an equality clause. This can be shown by taking table HA2, which is hash-partitioned two ways on columns (a1, a2). The ordinal numbers refer to the order in which partitions are listed in the output of sp_help.
select * from HA2 where a1 = 10 and a2 = 20 QUERY PLAN FOR STATEMENT 1 (at line 1). ................................ |SCAN Operator | FROM TABLE | HA2 | [ Eliminated Partitions : 1 ] | Table Scan.
