this one had a little bit strange plan:

create main table test1

CREATE TABLE TEST1 (
ID INTEGER NOT NULL,
CAPTION VARCHAR(10) CHARACTER SET NONE
);
ALTER TABLE TEST1 ADD PRIMARY KEY (ID);
CREATE INDEX TEST1_IDX ON TEST1 (CAPTION);


and 9 tables, called test2 ... test10, referencing the table test1...test9

CREATE TABLE TEST2 (
ID INTEGER NOT NULL,
CAPTION VARCHAR(10) CHARACTER SET NONE,
TEST1_ID INTEGER
);
ALTER TABLE TEST2 ADD PRIMARY KEY (ID);
ALTER TABLE TEST2 ADD FOREIGN KEY (TEST1_ID) REFERENCES TEST1 (ID);
CREATE INDEX TEST2_IDX ON TEST2 (CAPTION);


all have the same data (100 records, id from 1 to 100 and each foreign key
is the same as the id)

when starting the following command with the like on test2..test10 gives the
really nice result

select test1.caption, test2.caption, test3.caption, test4.caption,
test5.caption, test6.caption, test7.caption, test8.caption, test9.caption,
test10.caption
from test1, test2, test3, test4, test5, test6, test7, test8, test9, test10
where test2.test1_id=test1.id and test3.test2_id=test2.id and
test4.test3_id=test3.id and test5.test4_id=test4.id and
test6.test5_id=test5.id and
test7.test6_id=test6.id and test8.test7_id=test7.id and
test9.test8_id=test8.id and test10.test9_id=test9.id and test10.caption like
'A%'


Enchanced Info from ibexpert
+--------------------------+-----------+-----------+---------+---------+----
-----+
| Table Name | Index | Non-Index | Updates | Deletes |
Inserts |
| | reads | reads | | |
|
+--------------------------+-----------+-----------+---------+---------+----
-----+
| TEST1| 1 | 0 | 0 | 0 |
0 |
| TEST2| 1 | 0 | 0 | 0 |
0 |
| TEST3| 1 | 0 | 0 | 0 |
0 |
| TEST4| 1 | 0 | 0 | 0 |
0 |
| TEST5| 1 | 0 | 0 | 0 |
0 |
| TEST6| 1 | 0 | 0 | 0 |
0 |
| TEST7| 1 | 0 | 0 | 0 |
0 |
| TEST8| 1 | 0 | 0 | 0 |
0 |
| TEST9| 1 | 0 | 0 | 0 |
0 |
| TEST10| 1 | 0 | 0 | 0 |
0 |
+--------------------------+-----------+-----------+---------+---------+----
-----+
PLAN JOIN (TEST10 INDEX (TEST10_IDX),TEST9 INDEX (RDB$PRIMARY28),TEST8 INDEX
(RDB$PRIMARY25),TEST7 INDEX (RDB$PRIMARY23),TEST6 INDEX
(RDB$PRIMARY21),TEST5 INDEX (RDB$PRIMARY18),TEST4 INDEX
(RDB$PRIMARY15),TEST3 INDEX (RDB$PRIMARY13),TEST2 INDEX (RDB$PRIMARY9),TEST1
INDEX (RDB$PRIMARY8))

i would say: perfect (other than in older ib versions)


but:

when starting the command with the like on test1 it gives this result

select test1.caption, test2.caption, test3.caption, test4.caption,
test5.caption, test6.caption, test7.caption, test8.caption, test9.caption,
test10.caption
from test1, test2, test3, test4, test5, test6, test7, test8, test9, test10
where test2.test1_id=test1.id and test3.test2_id=test2.id and
test4.test3_id=test3.id and test5.test4_id=test4.id and
test6.test5_id=test5.id and
test7.test6_id=test6.id and test8.test7_id=test7.id and
test9.test8_id=test8.id and test10.test9_id=test9.id and test1.caption like
'A%'

Enchanced Info from ibexpert:
+--------------------------+-----------+-----------+---------+---------+----
-----+
| Table Name | Index | Non-Index | Updates | Deletes |
Inserts |
| | reads | reads | | |
|
+--------------------------+-----------+-----------+---------+---------+----
-----+
| TEST1| 1 | 0 | 0 | 0 |
0 |
| TEST2| 100 | 0 | 0 | 0 |
0 |
| TEST3| 100 | 0 | 0 | 0 |
0 |
| TEST4| 100 | 0 | 0 | 0 |
0 |
| TEST5| 0 | 100 | 0 | 0 |
0 |
| TEST6| 1 | 0 | 0 | 0 |
0 |
| TEST7| 1 | 0 | 0 | 0 |
0 |
| TEST8| 1 | 0 | 0 | 0 |
0 |
| TEST9| 1 | 0 | 0 | 0 |
0 |
| TEST10| 1 | 0 | 0 | 0 |
0 |
+--------------------------+-----------+-----------+---------+---------+----
-----+

plan:
PLAN JOIN (TEST5 NATURAL,TEST4 INDEX (RDB$PRIMARY15),TEST3 INDEX
(RDB$PRIMARY13),TEST2 INDEX (RDB$PRIMARY9),TEST1 INDEX
(TEST1_IDX,RDB$PRIMARY8),TEST6 INDEX (RDB$FOREIGN22),TEST7 INDEX
(RDB$FOREIGN24),TEST8 INDEX (RDB$FOREIGN27),TEST9 INDEX
(RDB$FOREIGN29),TEST10 INDEX (RDB$FOREIGN31))

when somebody want to test it, i uploaded the db script on
www.ibexpert.com/test/test.zip




--
See you on First European Firebird Conference in May in Fulda, Germany
http://www.firebird-conference.com

IBExpert - The most Expert for InterBase and Firebird --- www.ibexpert.com
HK Software - Holger Klemt - Huntestrasse 15 - D-26135 Oldenburg
Telefon Telefax +49 700 IBEXPERT (42397378) www.h-k.de
Schulungen - Projektunterst�tzung - Delphi - InterBase - AS/400