How to force an index

[Former Member]

I'm trying to force an index on a query, but I keep getting errors and can't find an actual example in the docs nor here, I must be doing something wrong (in ISQL, engine version 11.0.1.2436):

the following:

select id from vkreg with ( index (vkreg_ind_fk_artpres_schemes_id) index only on )

gives the following error:

Could not execute statement.
  The optimizer was unable to construct a valid access plan
  SQLCODE=-727, ODBC 3 State="HY000"



select id from vkreg with ( index (dba.vkreg_ind_fk_artpres_schemes_id) index only on )

gives this error (NOTE the qualifier "dba." in the above query)

Could not execute statement.
Cannot find index named 'dba.vkreg_ind_fk_artpres_schemes_id'
SQLCODE=-183, ODBC 3 State="42S12"

Leaving out the "index only on" portion gives no errors, but I think it then still has an opportunity not the use the index

what am I doing wrong, or can someone point me in the right direction?

TIA

[VolkerBarth · Accepted solution]

I think the error for the query hint with "INDEX ONLY ON" tells you that an index-only retrieval is not possible here. However, it does not mean the index is ignored, it just means the query will use the index and still need to access the table itself to access relevant data (i.e. data not stored in the index itself). I would omit that clause and give the simple index hint a try.

For the 2nd query, if you add the owner to the index name, you also have to add the table name. Both are unnecessary here, unless your query refers to several tables and those have indexes with identical names.

[Former Member · Accepted solution]

You're right, no need to add "INDEX ONLY ON" (this gives indeed no errors when I only fetch this foreign key column), problem solved (and I understand the owner problem too now)!