We have a SQL Anywhere 12 (12.0.1.3798) that has a number of tables. We are doing a select that using two tables (lets call them Table A and Table B). Table A has a primary key containing 4 fields. When we do the select we join 2 of the columns from the primary key of Table A. The issue is that for some reason it wants to go sequential on table A even though we are joining 2 fields from the primary key. Am I missing something or would I have to create separate indexes for every combination of the 4 primary key fields?

Thanks!

asked 25 May '17, 11:18

Codecranker's gravatar image

Codecranker
506283238
accept rate: 20%

3

Are these two columns the first two of the PK definition? If not, the default PK index will not be of help here, as it is built as combined index of all four columns in the declared order.

(25 May '17, 12:35) Volker Barth

The query optimizer in SQL Anywhere will choose a query plan that best matches the conditions of the database + server at the time of the query. The optimizer uses the sizes of the tables, selectivity estimates, amount of table data already in the server cache, and many other pieces of information to decide how to get the information it needs to answer the query.

In your example query - a join between two tables - it could be that the tables are small enough that the optimizer has decided that it is best to just read all of the table data and join the data using an in-memory hash table? (just an example - there are many ways that two tables can be joined).

If you need more information then you will need to provide more information to us! For example, provide a query graphical plan with statistics.

You can read more about the query optimizer (v12) here.

HTH

permanent link

answered 25 May '17, 11:45

Mark%20Culp's gravatar image

Mark Culp
24.8k10139296
accept rate: 41%

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:

×68

question asked: 25 May '17, 11:18

question was seen: 1,718 times

last updated: 25 May '17, 12:35