I have a table with no primary key defined, partitioned on a column, using List Partitioning method. It has huge data in one partition. Though partitioned column is used in WHERE clause while querying against this table, performance is very slow.
Is it suggestible to create an index that includes the partitioned column? Here's my sample table structure,
CREATE TABLE inventory
(
AccountId BIGINT,
AccountNumber VARCHAR(250),
DeviceId BIGINT,
DeviceNumber VARCHAR(250),
ClientId SMALLINT
CreatedDate DATETIME,
CreatedUser VARCHAR(50)
)
PARTITION BY LIST (ClientId)
(PARTITION p_1 VALUES IN (1),
PARTITION p_2 VALUES IN (2),
PARTITION p_3 VALUES IN (3),
PARTITION p_4 VALUES IN (4),
PARTITION p_5 VALUES IN (5),
PARTITION p_6 VALUES IN (6),
PARTITION p_7 VALUES IN (7),
PARTITION p_8 VALUES IN (8),
PARTITION p_9 VALUES IN (9),
PARTITION p_10 VALUES IN (10),
PARTITION p_11 VALUES IN (11),
PARTITION p_12 VALUES IN (12),
PARTITION p_13 VALUES IN (13),
PARTITION p_14 VALUES IN (14),
PARTITION p_15 VALUES IN (15));
Table is partitioned by "ClientId" and I had to make 1024 partitions to this table.