Indexes and natural keys for managed tables
Use indexes and natural keys to improve rule engine performance and speed up table lookups.
CPQ supports the integration of natural keys and indexes for faster table lookups and heightened rules engine performance.
This enhancement to CPQ's table management system introduces compatibility with up to three natural keys per table and the addition of two indexed columns.
- A natural key provides a means of unique identification for data in a table.
- Indexed columns quickly locate data without having to search every row in the table every time the table is accessed.
Lookup functions that reference a column in their SELECT clause can benefit from the addition of indexing the column, as searches can be performed faster with no changes needed to the script.
These features can be added to a column by editing the table schema. (The schema editor opens automatically when you create a new table.)
Because all natural keys are also indexed, checking the column does not allow the user to also check the Indexed column.
When the Natural Key column is checked, a keys icon displays in the table.
Another icon indicates which columns are indexed.
Since natural keys act as unique identifiers in the table, internal validation makes sure that each column in the table is unique.
When adding a natural key to a column with duplicate rows, the table does not allow the schema to save.
When exporting a table, the schema file indicates whether a column is indexed or is a natural key, so when the user imports the table to another environment, the schema’s column settings are saved.
Reasons an import might fail
Some factors associated with natural keys and Indexes might hamper table import. These factors include:
- There are more than three columns set to be natural keys in the schema
- There are more than two columns set to be indexed in the schema
- A column set to be a natural key contains duplicate rows