Lookups are a powerful tool that can reduce both storage requirements and query times when used effectively.

Lookups replace a CHAR or VARCHAR value with an integer value for a column where the expected range of values is less than 10000 instances, and there is a high degree of repetition in values. This is particularly beneficial for fields like States and other fields that traditionally have a small range of acceptable values. Typically, the number of records to distinct values should be at a ratio of at least 10:1. So, if you had 100,000 values, and only 10,000 unique values, then you would have a good candidate for the lookup function.

The lookup function should not be used if this criteria is not met – over 10,000 unique values or a ratio less than 10:1 will result in slower queries and longer load times.

How do I create a Lookup?

A lookup must be created at table creation time as part of the DDL. Simply add the following to the table definition:

> <<column name>> <<column type>> … comment ‘lookup’ …

This cannot be done as part of a modify table statement, since Infobright generates the integer values on load.