At 04:28 PM 22-02-02 +0100, you wrote:

>Helen,
>
> > Because of the ambiguity of two identical indexes, it uses *no* index.
> > So it will join, sort, etc. by organising the column in natural order.
>
>Never been able to reproduce it. My tests show that one of the indexes
>is always used (IB4.2 tends to use them both). Out of curiosity, have
>you got a suitable example?

Not without reconstructing one (as I did back at 5.1 stage as
proof-of-concept when this problem was first explained by Ann Harrison).

If you like, you can reconstruct it.

create and populate (or use) a large-ish table ( > 100,000 rows) with a
primary key
create a second table with a foreign key to the PK of the first
table. Populate it with approximately 2 matches (or more) to the master table.

Run a query across these two tables consisting of an inner join.
Inspect the plan and time the query.

Run the same query but add an 'order by' involving the primary key columns
of both tables.
Inspect the plan and time the query.

Now, create an ascending index on the primary key column(s) of the first table.

Run both queries, inspecting the plans and timing them.

Then, if you are really masochistic, add an index to the FK in the detail
table and repeat the dose.

Since the optimiser hasn't been changed significantly since 5.1, I would
not anticipate that your results will be any less interesting with IB 6 or
Firebird...for maximum fun, use composite varchar keys, Paradox-style, with
foreign key columns overlapping PK columns.

btw, your comment "IB 4.2 tends to use them both" is a bit contradictory to
"Never been able to reproduce it". To have the optimiser choose two
identical indexes would be rather defeating, wouldn't it? (I've never seen
that, though, but it's a long time since I used 4.x).

Kia ora!

regards,
Helen Borrie (TeamIBO Support)

** Please don't email your support questions privately **
Ask on the list and everyone benefits
Don't forget the IB Objects online FAQ - link from any page at
www.ibobjects.com