Please be aware that the content in SAP SQL Anywhere Forum will be migrated to the SAP Community in August and this forum will be retired.


We have a query which selects from about 20 tables. Lets say, T is one of these tables (and is a primary key). Let's say, we have a string @S with some T.ids, delimited by some delimiter, let it be a semicolon (e. g.: "1;5;10"). We want to filter a query using this string. We have tried this approach: IN (select row_value from sa_split_list(@S, ';'))

But the problem is that query optimizer always chooses tablescan. There would not be problems (InList would be chosen) if we used IN (1, 5, 10)

but it is not possible (we do not know these values). Is it possible to force optimizer to use something like "InList" and to start joining with table T?

We also tried to create a global temporary table and insert these IDs into it before executing the query (and then join that table). Unfortunately, optimizer chooses good plan only when we set optimization_level option to 15 (by default it is 9). Good plan starts with

( IndexOnlyScan tmp_ids tmp_ids** )
but then optimization costs ~30 seconds instead of less than 1 second.

Is there a way to force optimizer to always chose to join that global temporary table (index scan) with table T firstly?

SA versions: 11 or 12.

asked 26 Oct '11, 09:27

Arthoor's gravatar image

accept rate: 11%

EXECUTE IMMEDIATE WITH RESULT SET string('select ... IN (', @S, ')')

permanent link

answered 26 Oct '11, 09:51

Marsel's gravatar image

accept rate: 33%


This approach seems to be the most applicable till now and solves our problem. But the second question still remains (for the future): is there a way to force optimizer to always start scanning from a concrete table (concrete index)? That could reduce optimization cost, methinks.

(27 Oct '11, 01:26) Arthoor

SQL Anywhere supports index hints on table tables in the FROM clause, using the WITH keyword. See the help.

permanent link

answered 26 Oct '11, 11:23

Glenn%20Paulley's gravatar image

Glenn Paulley
accept rate: 43%

This could not help in our situation because optimizer always chooses IndexOnlyScan but not at first place. Firstly (when optimization_level=9) it chooses other tables (tablescans) and then (almost always at 3rd place) that global temporary table.

(27 Oct '11, 01:05) Arthoor

Specifically, see "SELECT ... FROM Table WITH INDEX(index_name)" (or "FORCE INDEX(index_name)") in the help:

(27 Oct '11, 14:49) Jeff Albion

You could try a user estimate like ( in ... ,0.00001) to influence the optimizer

permanent link

answered 26 Oct '11, 10:52

Martin's gravatar image

accept rate: 14%

We have tried this but with no success too - tablescans are at first place (even with OPTION(user_estimates='Enabled')).

(27 Oct '11, 01:10) Arthoor
Your answer
toggle preview

Follow this question

By Email:

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



Answers and Comments

Markdown Basics

  • *italic* or _italic_
  • **bold** or __bold__
  • link:[text]( "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:


question asked: 26 Oct '11, 09:27

question was seen: 2,689 times

last updated: 27 Oct '11, 14:49