Both tables are huge. 3+ million records right now and growing fast.
TableA would be the largest since it is growing faster.
TableB is a list of values thay may or may not be in TableA. Quite often
(as TableB is updated) I will need to check TableB
against TableA and update some flags on TableA. They both have constraints
applied to the double precision field, so no duplicate
values exist.

Your explanation makes a lot of sense though. It does sound like the best
plan you get now.
As for the speed of the query, I intend to loop through the results in a
stored procedure. I don't know if the stored
procedure has to wait for full fetch. I wouldn't think so. I would think
it would begin looping as soon as there were records
found. Thanks for the help.





-----Original Message-----
From: Svein Erling [mailto:svein.erling.tysvaer@...]
Sent: Friday, March 19, 2004 12:14 AM
To: firebird-support@yahoogroups.com
Subject: [firebird-support] Re: Indexes and Plans

--- In firebird-support@yahoogroups.com, "Epstein, Ed" wrote:
> I have 2 different tables with a double precision field in each one.
> Both are indexed. I am trying to perform a query of the values that
> are in both tables.
>
> I have tried many variations, but this is the most logical one to
> me:
>
> SELECT * FROM TABLEA JOIN TABLEB ON TABLEA.FIELD1 = TABLEB.FIELD1
>
> The Plan that is generated for that is
> (TableA Natural,TableB (Index TableB_Field1_A))
>
> Wouldn't the best plan be to use both indexes?

No, Firebird/Interbase needs a where clause to be able to use an index
on the first table in the plan. Basically, the question is: "Which
records shall I check in TableA? The answer is 'all of them'. The next
question is, what shall I check for in TableB, with the answer
'records that have the same value of Field1 as in TableA. It is the
best plan you could possibly get for this select.

> Basically I am trying to create the fastest query to return values
> from both tables that are the same. So far it takes at least 2-5
> minutes which is not very good.

Does that mean there are lots of records returned and that the tables
are huge? Or just lots of duplicates for Field1? Any other bottlenecks
(RAM, slow lines etc.)?

Set




Yahoo! Groups Links