Please be aware that the SAP SQL Anywhere Forum will be shut down on August 29th, 2024 when all it's content will be migrated to the SAP Community.

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

   data VARCHAR ( 50 ) NOT NULL );
CREATE INDEX xdata ON t ( data );

   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;

SELECT TOP 1 t.pkey       
  FROM t WITH ( INDEX ( PRIMARY KEY t, xdata ) )
 WHERE = '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.

asked 15 Jan '14, 13:28

Breck%20Carter's gravatar image

Breck Carter
accept rate: 20%

edited 15 Jan '14, 13:35

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 = 'Kc7Prp2eUWsSg7Jy' and t.pkey = 2

ORDER BY t.pkey ASC;

permanent link

answered 15 Jan '14, 13:52

Nica%20_SAP's gravatar image

Nica _SAP
accept rate: 3%

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?

(15 Jan '14, 14:39) Breck Carter
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: 15 Jan '14, 13:28

question was seen: 4,504 times

last updated: 15 Jan '14, 14:50