Using IBM i? Need to create Excel, CSV, HTML, JSON, PDF, SPOOL reports? Learn more about the fastest and least expensive tool for the job: SQL iQuery.
On IBM i, the only way to build an index over an existing table and have that index be a VECTOR INDEX (must faster than regular indexes) is to create it using SQL.
The SQL statement to create this type of index, is "CREATE ENCODED VECTOR INDEX".
The first parameter is the name of the new index object (similar to a logical file) that is being created.
That index object name is followed by the "ON" keyword which is followed by the table (physical file) name over which the index is built.
Last, you enclose the key fields for the index in parens and separate them with commas.
Here's a simple example:
CREATE ENCODED VECTOR INDEX lgl_view ON phy_table (fld1,fld2,fld3);
In this example, the Vector Index named LGL_VIEW is being created over the file named PHY_TABLE. You would of course modify these names to meet your own needs.
The key fields in this index (there are 3 of them) are FLD1, FLD2 and FLD3. These are obviously made-up names and your own field names would go in place of them.
If processing this SQL statement with the RUNSQLSTM command, then the semicolon is required at the end of the statement. If running this CREATE ENCODED VECTOR INDEX statement in Interactive SQL (i.e., STRSQL) the semicolon is not specified.
MORE INFO: Only use EVI's on tables where the number of distinct key fields is not a "large" amount. The US State codes would be a good candidate for an EVI. But a timestamp would be a terrible candidate for an EVI. Also, SQL is smart enough to join existing EVI's, so if it makes more sense to create 2 EVI's with one key field instead of one EVI with 2 key fields, do that.
Ringer
Can you expand on why large key fields would be something to avoid?
Two questions. 1) If I create an EVI with SQL and have a DDS define Logical File (*LF) with the same key will access path sharing use the EVI index with the key? 2) If I use the iNavigator Index Advisor and tell it to create the index, does it use CREATE ENCODED VECTOR INDEX?
Cardinality is what it's called... number of distinct values in the column. Low cardinality... think 30,000 cars in 5 different colors, color has low cardinality. High cardinality... think... primary key... each record has a distinct, unique value, high cardinality.
EVI is good for low to medium cardinality. When a low percentage of rows are expected to be selected, then binary radix indexes are preferred by the optimizer.
High cardinality is better served by binary radix. When a high percentage of rows are expected to be selected, the optimizer leans toward a table scan opearation. EVI's can optimize that scan by reducing the scan to a bit mapping scan of the rows.
Yes, create EVI's over a single field. The optimizer will combine those EVIs that it can use based on the columns in the predicates. If you create one EVI over multiple fields, then the optimizer is extremely restricted in using that EVI.
On the sharing.. no. The ONLY way to create an EVI is in SQL. ONLY SQL can USE an EVI. That is, only the SQL database engine can use them. They produce bit-mapped indexes during optimization. They provide no ordering.
*LF files are binary radix indexes only and the sharing of access paths is very different from the sharing of SQL indexes.
If the Index advisor in i Navigator advises an EVI, then yes, it will create an EVI. But if it advises a binary radix index, then that's what it creates.
From the preceding, it should be apparent that they serve two different purposes.