Force optimizer to choose indexscans

[former_SQLA_member1694875]

Hello,

We have a query which selects from about 20 tables. Lets say, T is one of these tables (and T.id 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:

T.id 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

T.id 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.

[Former Member · Accepted solution]

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

[former_SQLA_member1694875 · Accepted solution]

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.

[Former Member]

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

[former_SQLA_member1694875]

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.

[jeff_albion]

Specifically, see "SELECT ... FROM Table WITH INDEX(index_name)" (or "FORCE INDEX(index_name)") in the help: http://dcx.sybase.com/index.html#1201/en/dbreference/from-statement.html

[MCMartin]

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

[former_SQLA_member1694875]

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