On Tue, 13 Apr 2004 08:12:56 +0000 (UTC), Werner Cloete wrote:

> ...
> PLAN JOIN (MERGE (SORT (JOIN (JOIN (PROPERTY_AGENTS INDEX
> (PROPERTY_AGENTS_IDX1),JOIN (JOIN (JOIN (JOIN (AGENTS ENTITIES INDEX
> (RDB$PRIMARY1),AGENTS ENTITY_TYPES INDEX (RDB$PRIMARY2)),AGENTS PERSONS
> INDEX (RDB$PRIMARY4)),AGENTS COMPANIES INDEX (RDB$PRIMARY5)),AGENTS
> ENTITY_ROLES INDEX (RDB$PRIMARY3))),COMPANIES INDEX
> (RDB$PRIMARY5))),SORT (JOIN (PROPERTY_BUYERS INDEX
> (PROPERTY_BUYERS_IDX1),JOIN (JOIN (JOIN (JOIN (BUYERS ENTITIES INDEX
> (RDB$PRIMARY1),BUYERS ENTITY_TYPES INDEX (RDB$PRIMARY2)),BUYERS PERSONS
> INDEX (RDB$PRIMARY4)),BUYERS COMPANIES INDEX (RDB$PRIMARY5)),BUYERS
> ENTITY_ROLES INDEX (RDB$PRIMARY3)))),SORT (JOIN (PROPERTY_OWNERS
> NATURAL,JOIN (JOIN (JOIN (JOIN (OWNERS ENTITIES INDEX
> (RDB$PRIMARY1),OWNERS ENTITY_TYPES INDEX (RDB$PRIMARY2)),OWNERS PERSONS
> INDEX (RDB$PRIMARY4)),OWNERS COMPANIES INDEX (RDB$PRIMARY5)),OWNERS
> ENTITY_ROLES INDEX (RDB$PRIMARY3))))),JOIN (INSPECTIONS INDEX
> (INSPECTIONS_IDX2),PROPERTIES INDEX (FK_PROPERTIES)))

As Svein Erling wrote, I would first investigate the "PROPERTY_OWNERS
NATURAL" - this is a table scan so maybe an index will help.
Also, try to put all info on joining tables into the FROM clause. Maybe
the WHERE clause should only hold:
where INSPECTIONS.INSPECTION_ID = 23
and INSPECTIONS.SERVICE_TYPE = 1

The mere size of this plan makes it difficult to sort it out. Maybe a
trained eye (Arno - are you there?) can help you.


--
Aage J.