Partition, Index in SQL

Partition:

The most widely used partition for improving performance is Range by partition. The data in tables are physically partitioned based on the parameters we choose. It can be daily, weekly, monthly or quarterly based on the volume of data we are processing. Say quarters, the data will be partitioned into four quarters.

CREATE TABLE t1(c1 INT) IN tbsp1, tbsp2, tbsp3

PARTITION BY RANGE (c1)

(STARTING (1) ENDING (99) EVERY (33))

Index:

It is primarily used for better query performance for searching. We can create index for a field or a set of fields. It’s like a primary key index. But it may cause performance degradation. Either we can create clustered index or else unclustered index. Creating clustered index degrades performance as it sorts the data in key order and every time tables will be sorted resulting in longer query execution times.

Best practice:

Create partition and index on same set of columns like below,

……partition by range (Region) create index <idx_name>(Region)