Most Sybase IQ indexes use a bitmap to store data compared to other indexes like B-trees .e.t.c, Bitmap indexes can be built very fast in IQ and even faster when using the “Parallel IQ” function” which I posted the syntax on a previous blog. This also speeds up data loading and extraction as you don’t have to drop, disable indexes and even Sybase highly recommends leaving the indexes in place. You can also place multiple indexes on a column. Another plus is when data is added, deleted or updated the index does not need to be rebuilt like traditional RDBMS indexes. In simple terms it’s faster to search/retrieved a zero or one than searching for a char value and only one column needs to be searched versus the whole row. This is what makes IQ unique and perhaps a leader in the new world of “Big Data”.
This overview is for IQ 15.x only, so lets talk about the FP index in some detail.
Sybase IQ has 9 types of indexes but only three are highly used and a couple are considered useless in 15x. In this Blog, I will only discuss the most frequently used which is the FP and explain it’s usages.
—FAST PROJECTION (FP)
When creating a table, IQ creates this type for free (By default) but you can optimize them accordingly. There are 4 types of FP indexes, 1 byte, 2 byte, 3 byte and flat. You want to avoid going flat because that means the index is not optimized. These types are dependent on 2 factors
— The cardinality of the data
— The value of IQ unique given in the ‘create table ‘ statement or if the, set option MINIMIZE_STORAGE=’ON’ which is like declaring IQ UNIQUE (255) on every column. Many times you will see this option hint if you use the INDEX_ADVISOR. If you see this recommendation when you analyze the query output html, you know your flat (No FP optimization) I’ll explain
The space used to store the index is dependent on the type of the FP index used and the data type being stored.
A flat index stores the data as it’s being loaded with no storage optimization. For example, a char(20) will store 20 bytes of storage per value. As mentioned before, we want to use the 1, 2 or 3 byte FP index and logic says the lower the byte the faster.
The 1 and 2 byte FP indexes are known as optimized FP indexes because they read faster from disk, use less disk space and are used by the query optimizer to obtain information about the data being stored in the column.
The 3 byte is the same as 1 and 2 byte except it holds between 65k and 16 million unique values. Again, It’s easier to sort through zeros and ones than a bunch of characters.
In Order to create an Optimized index you must either specify a value for IQ UNIQUE in a create table statement or set the MINIMIZE_STORAGE database option to ON. MINIMIZE_STORAGE = Guess, compression which another feature IQ uses to speed things up. If you intend on setting the MINIMIZE_STORAGE option, you must do this prior to creating the table where you want the IQ UNIQUE value to defaulted to.
Example;
CREATE TABLE order_entry
(
order_id int IDENTITY,
customer_id int IQ UNIQUE (255) NOT NULL,
sales_id int IQ UNIQUE (10000) NOT NULL,
product_id int IQ UNIQUE (5000) NOT NULL
)
SET OPTION MINIMIZE_STORAGE='ON';
CREATE TABLE statement
In a flat FP index a column of raw data is compressed but their is no storage optimization used. 1,2 and 3 byte indexes convert via look up tables.. ) zeros and ones. Minus pictures I hope I explained how IQ utilizes FP indexes and the DBA control over their optimization.