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

asked 20 Sep '17, 05:22

Franky's gravatar image

Franky
116449
accept rate: 0%


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.

permanent link

answered 20 Sep '17, 05:45

Volker%20Barth's gravatar image

Volker Barth
39.7k357545815
accept rate: 34%

edited 20 Sep '17, 05:51

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)!

(20 Sep '17, 08:13) Franky
Your answer
toggle preview

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

Markdown Basics

  • *italic* or _italic_
  • **bold** or __bold__
  • link:[text](http://url.com/ "title")
  • image?![alt text](/path/img.jpg "title")
  • numbered list: 1. Foo 2. Bar
  • to add a line break simply add two spaces to where you would like the new line to be.
  • basic HTML tags are also supported

Question tags:

×31

question asked: 20 Sep '17, 05:22

question was seen: 1,866 times

last updated: 27 Nov '17, 04:41