INDEX Object Design (Prioritized)

Impact of rule violation:

• Redundant indexes consume unnecessary space and can impact data import efficiency.

• The column sequence in the composite index is incorrect, affecting the query efficiency.

Best practices:

The following conditions must be met when indexes are used:

• The index column should be a column commonly used for filtering or joining conditions.

• The index column should have more distinct values.
• When creating a multi-column combination index, prioritize columns with more distinct values.
• The number of indexes in a single table should be limited to less than five. You can control the number of indexes by combining them.
• In scenarios where data is added, deleted, or modified in batches, delete the index first and then add it back after the batch operation is complete to improve performance (real-time access may be affected).

Impact of rule violation:

• Incorrect indexes do not improve column-store access and can negatively affect query performance.

Solution:

1. Specify the appropriate index type when creating indexes, avoiding the default psort index.
2. In point queries where small amounts of data need to be retrieved from mass datasets, consider creating a B-tree index.
3. For high range query performance, create a partial cluster key (PCK) to quickly filter and scan fact tables using the min/max sparse index. Comply with the following rules to create a PCK:
   • [Notice] Only one PCK can be created in a table. A PCK can contain multiple columns, preferably no more than two columns.
   • [Suggestion] Create a PCK for the filter condition column of the expression (e.g., col op const, where op is the operator =, >, >=, <=, and <, and const is a constant value).