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.
asked 26 Oct '11, 09:27
EXECUTE IMMEDIATE WITH RESULT SET string('select ... T.id IN (', @S, ')')
answered 26 Oct '11, 09:51
SQL Anywhere supports index hints on table tables in the FROM clause, using the WITH keyword. See the help.
answered 26 Oct '11, 11:23
You could try a user estimate like (T.id in ... ,0.00001) to influence the optimizer
answered 26 Oct '11, 10:52