Midrange News for the IBM i Community


Posted by: Bob Cozzi
Rogue Programmer
Cozzi Productions, Inc.
Chicagoland
How to Create an Encoded Vector Index on iSeries IBMi
has no ratings.
Published: 24 Aug 2011
Revised: 23 Jan 2013 - 4082 days ago
Last viewed on: 28 Mar 2024 (12746 views) 

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.

Using SQL to build an encoded vector index Published by: Bob Cozzi on 24 Aug 2011 view comments(4)

Encoded Vector Index

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.

Return to midrangenews.com home page.
Sort Ascend | Descend

COMMENTS

(Sign in to Post a Comment)
Posted by: Ringer
Premium member *
Comment on: How to Create an Encoded Vector Index on iSeries IBMi
Posted: 12 years 6 months 14 days 7 hours 25 minutes ago

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

Posted by: bobcozzi
Site Admin ****
Chicagoland
Comment on: How to Create an Encoded Vector Index on iSeries IBMi
Posted: 12 years 6 months 11 days 2 hours 55 minutes ago
Edited: Thu, 20 Oct, 2011 at 12:09:36 (4543 days ago)

Can you expand on why large key fields would be something to avoid?

Posted by: mcunning
Premium member *
Comment on: How to Create an Encoded Vector Index on iSeries IBMi
Posted: 12 years 5 months 22 days 16 hours 41 minutes ago

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?   

Posted by: hoffmanb
QSysOpr member ***
Comment on: How to Create an Encoded Vector Index on iSeries IBMi
Posted: 12 years 3 months 29 days 14 hours 4 minutes ago

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.