SQLCODE=-727 unable to construct a valid access plan

[Breck_Carter]

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.

[Former Member]

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
FROM t WITH ( INDEX ( PRIMARY KEY t, xdata ) )

WHERE t.data = 'Kc7Prp2eUWsSg7Jy' and t.pkey = 2

ORDER BY t.pkey ASC;

[Breck_Carter]

Your reply raises more questions:

When you say "don't use hints", aren't you saying "don't use multiple index scans at all"? The Help seems to say the ONLY way to get a multiple index scan is to use a hint.

...I generally agree with "don't use hints", but how DOES someone make use of multiple index scans, anyway? If the optimizer automatically uses them if applicable, where is that discussed in the Help?

Actually, where is ANYTHING about multiple index scans discussed? 🙂

The query can (and does, see today's blog post) make use of a composite index on both pkey and data... it turns the WHERE and ORDER BY into an Index Only scan. Are you saying the multiple index scan feature does not apply to the optimization of the ORDER BY / TOP clauses?