Hello,
ASA 9.0.2. 2451
1) We have master and slave tables (assume: Mas and Sla) and
want a simple join by primary key which consists of 2
columns: X and Y.
2) So the select is:
select * from Mas, Sla
where Mas.x = Sla.x and Mas.y = Sla.y and mas.column1 = 0
Very simple, isn't it ?
3) We have Primary key on table Mas (x, y), and
Primary Key on table Sla (x, y, w, z)
4) We also have Foreign key between Mas and Sla on columns
X, Y.
5) Both tables have between 500 and 1000 rows (so really not
so much of data)
Problem:
This select lasts for about 90 seconds only on that
database?!
We tried it on all other databases, and select lasts for
only 0.5 seconds (which should be normal)!

Solution already tried:
1) Dropping Primary and Foreign keys and recreating them.
Does not help at all ?!
2)Dropping and creating tables Mas and Sla again, reloading
data from file. Does not help at all !?
3) Dropping database and creating from scratch, with
reloading data. Does not help at all!?
4) We made additional indexes on both tables. Does not help
at all!

We analized SQL plan in ISQL, and found that SQL optimizer
does not want to use index by primary keys ?! He is working
sequentially, even we have index on Mas and index on Sla
table (primary keys)
Question:
How to make sql optimizer use natural, normal PRIMARY KEY
for matching the data ?

Version 9.0.2 (2451) is the 9.0.2 GA version. We did a large number of
changes and fixes. So I strongly recommend using a recent EBF.

You may also
- create statistics on all columns "x", "y", and "column1" so the
optimizer can better estimate the join result
- create the PK as clustered index
- or force the optimizer to use the indexes you what
(FROM Mas FORCE INDEX(Mas), Sal FORCE INDEX(Sal)

cheers

Hartmut Branz
iAnywhere International and
Sustaining Engineering

Vladislav Cale wrote: