Apparently, ever since Version 11 SQL Anywhere can use up to four indexes for a single table, but that feature is not exactly well advertised; here's the only mention:
Support for multiple indexes scan - The optimizer has been enhanced to consider multiple indexes (up to four) to retrieve data from a base table based on multiple predicates on that table. Previously, you could only specify one index as an index hint for a query. A new index hint in the WITH clause of the SELECT statement allows you to specify that a multiple index scan can be used. See FROM clause.
Sadly, my attempts to demonstrate this feature have met with failure; for example...
CREATE TABLE t ( pkey INTEGER NOT NULL DEFAULT AUTOINCREMENT PRIMARY KEY, data VARCHAR ( 50 ) NOT NULL ); CREATE INDEX xdata ON t ( data ); BEGIN DECLARE @loop_counter INTEGER = 1; WHILE @loop_counter <= 10000 LOOP INSERT t ( data ) VALUES ( 'mxAn22qAlHI7PThX' ); INSERT t ( data ) VALUES ( '1CeqJK4mq1pU78k2' ); INSERT t ( data ) VALUES ( 'H27qdWa2x90j42xR' ); INSERT t ( data ) VALUES ( 'qr4NAIwGpsS3I8BX' ); INSERT t ( data ) VALUES ( 'Kc7Prp2eUWsSg7Jy' ); INSERT t ( data ) VALUES ( '2f2EVHC5yiemiGvL' ); INSERT t ( data ) VALUES ( 'dtHYKbaKWpVI4hCq' ); INSERT t ( data ) VALUES ( 'kZmpve2HROp1DfYB' ); INSERT t ( data ) VALUES ( 'mBUXUfbs7HthbMCf' ); INSERT t ( data ) VALUES ( 'JqstKsRh86fQ9nM9' ); SET @loop_counter = @loop_counter + 1; COMMIT; END LOOP; END; SELECT TOP 1 t.pkey FROM t WITH ( INDEX ( PRIMARY KEY t, xdata ) ) WHERE t.data = 'Kc7Prp2eUWsSg7Jy' ORDER BY t.pkey ASC; Could not execute statement. The optimizer was unable to construct a valid access plan SQLCODE=-727, ODBC 3 State="HY000" Line 1, column 1
The database server was unable to construct a valid access plan for the given request. This is a SQL Anywhere internal error. If it can be reproduced, it should be reported to SAP Sybase. You may be able to work around this problem by modifying the query statement.
It is not recommended to use hints in your query: SQL Anywhere Optimizer will use all the resources available (e.g., indexes) and will choose the best plan based on cost.
About your example: The error is correct. Your query doesn't follow the documentation which said "table based on multiple predicates on that table" - i.e.,each index used in the index hint must be used as a partial index scan, hence a predicate[s] on the prefix of that index must exist in the WHERE clause. The index "PRIMARY KEY t" doesn't have a sargable predicate covering its prefix. If a new predicate is added on the 'pkey' column, then the hint might work (note that the query plan obtained because of this hint is not the best/most optimal plan for this query).
SELECT TOP 1 t.pkey
WHERE t.data = 'Kc7Prp2eUWsSg7Jy' and t.pkey = 2
ORDER BY t.pkey ASC;
answered 15 Jan '14, 13:52