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.

asked 15 Jan '14, 13:28

Breck%20Carter's gravatar image

Breck Carter
25.7k427587847
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 t.data = '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
866722
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

By RSS:

Answers

Answers and Comments

Markdown Basics

  • *italic* or _italic_
  • **bold** or __bold__
  • link:[text](http://url.com/ "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:

×102

question asked: 15 Jan '14, 13:28

question was seen: 2,513 times

last updated: 15 Jan '14, 14:50