Hi Set,

OK, I've finally got everything sorted for testing the WITH idea, and here are the results:

I'm not quite sure what your query was attempting to do, but have adapted it as follows:

WITH INSTEMP(INCIDENTID) AS
(SELECT I.ID FROM customerincidents I WHERE I.CUSTOMERID = :ICustomerID)
SELECT max(C.communicationdate)
FROM customercommunications C
JOIN INSTEMP IT ON C.INCIDENTID = IT.INCIDENTID

PLAN JOIN (IT I INDEX (CUSTOMERINCIDENTS_IDX1), C INDEX (CUSTOMERCOMMUNICATIONS_IDX2))

Takes 63 milliseconds to run (local connection though!)
Still reads exactly the same number of records as my FB 1.5 solution though (ie. all incidents and comms for that customer)

PS. Your original query returned more than one record, and took several seconds to run

-----Original Message-----
From: firebird-support@yahoogroups.com [mailto:firebird-support@yahoogroups.com] On Behalf Of Svein Erling Tysvær
Sent: 04 November 2008 12:07 PM
To: 'firebird-support@yahoogroups.com'
Subject: RE: [firebird-support] Possible to have an index across 2 tables?

Another option could be (if interested mainly in Comms and one particular customer):

WITH INSTEMP(INCIDENTID) AS
(SELECT I.ID FROM INCIDENTS I WHERE I.CUSTOMER_NAME = :NAME)

SELECT C.<Whatever>
FROM COMMS C
JOIN INSTEMP IT ON C.INCIDENTID = IT.INCIDENTID
WHERE NOT EXISTS(
SELECT NULL FROM COMMS C2
JOIN INSTEMP IT2 ON C2.INCIDENTID = IT2.INCIDENTID
WHERE C.xDATE < C2.xDATE)

Now, I'm still on Firebird 1.5 and WITH is only available in Firebird 2.1 and above (I think). If the optimizer choose a plan similar to:

PLAN JOIN(IT2 I INDEX(IDX_CUSTOMERNAME), C2 INDEX(INCIDENTID))
PLAN JOIN(IT I INDEX(IDX_CUSTOMERNAME), C INDEX(INCIDENTID))

then this could be reasonably fast. Unfortunately, I have no idea whether the optimizer in Fb 2.1 or 2.5 choose something like this or not. It would be nice if you tried and reported back.

HTH,
Set

-----Original Message-----
From: firebird-support@yahoogroups.com [mailto:firebird-support@yahoogroups.com] On Behalf Of Helen Borrie
Sent: 4. november 2008 09:17
To: firebird-support@yahoogroups.com
Subject: RE: [firebird-support] Possible to have an index across 2 tables?

At 06:53 PM 4/11/2008, you wrote:

>Hi Set,
>
>>I don't quite grasp your structure, so it is difficult to give sound
>advice. How is COMMUNICATIONS and INCIDENT defined, what is their
>>relationship and approximately how many records are there within each
>table and for each customer?
>Just answered in reply to Helen's mail, but in short:
>
>Table Records
>Customers 15000 PK ID
>Incidents 150000 PK ID, CustomerID references Customers.ID
>Comms 1500000 PK ID, IncidentID references Incidents.ID
>
>Need to extract max date in Comms per Customer

So what you said before isn't quite correct. You have a 1:1 relationship between an Incident and a Customer, and a 1:1 relationship between a Comm and an Incident. That won't let your comms records get confused by customer.

select
i.CustomerID,
(select cus.Customer_name from Customers cus
where cus.CustomerID = i.CustomerID) as Customer,
max(c.xDate) Last_Comm_Date
from Incidents i
join Comms.c
on c.IncidentID = i.Incident_id
group by 1,2

./heLen

------------------------------------

++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++

Visit http://www.firebirdsql.org and click the Resources item
on the main (top) menu. Try Knowledgebase and FAQ links !

Also search the knowledgebases at http://www.ibphoenix.com

++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
Yahoo! Groups Links