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