We have three tables : Documents, DocItems, Doctypes
Documents has pkey with two columns : Doctype, Docnumber
Documents has foreign key to Doctypes : Doctype = Doctype
DocItems has pkey with three columns : Doctype, Docnumber,
DocItems has foreign key to Documents : Doctype = Doctype
and Docnumber and Docnumber
Our sql is like :
select Docitems.* , Documents.datedoc
from Docitems join Documents
where Documents.datedoc between date1 and date2
We found in iSql query plan that query optimezer use
Documents foreign key instead primary key to scroll through
If we change join clause and add text : .. on
Docitems.Doctype = Documents.Doctype and Docitems.Docnumber
than query optimezer work fine.
Ok we could use sintax with "join .. on " but it was work
fine on version Asa 6.x.
Also we found in 3396ebf readme.txt :
=================(Build #3299 - Engineering Case
The optimizer could have chosen a query plan that was less
if it used unique indexes, for which one or two columns had
predicates This was due to an incorrect computation of the
number of rows for an intermediate result. This has now been
Is it in some corelation ?
This is not only one situation where we detect this problem.
In several databases and at differnt users. Above
construction is simple version of database model.
From: Sprint NT
Subject: ASA join do not use foreign key and primary key between two tables
X-Mailer: WebNews to Mail Gateway v1.1s
Date: 15 Sep 2003 01:03:50 -0700
X-Trace: forums-1-dub 1063613030 10.22.241.41 (15 Sep 2003 01:03:50 -0700)
X-Original-Trace: 15 Sep 2003 01:03:50 -0700, 10.22.241.41
Xref: forums-1-dub ianywhere.public.general:1749
Article PK: 4040