Hello firebird-support,

I constructed the following view, which combines elements and their
categories (two tables) into one elements tree (FB 1.5, new empty DB):
-----
create view ElsTreeView (ID, ParentID, Code, Name, BgColorCode,
Article, Collection, Manufacturer, Standard,
SubCateg, ElsCount, IsActive)
as
/* elements without parents: categories are parents (ID>0,
CategID<0) */
select e.ID, e.CategID, e.Code, e.Name, cast(null as integer),
e.Article, e.Collection, a1.Name, e.Standard,
a2.Name, e.SubElsCount, e.IsActive from Elements e
left outer join Attributes a1 on a1.ID=e.ManufactID
left outer join Attributes a2 on a2.ID=e.SubCategID
where e.ParentID is null
union
/* elements with parents */
select e.ID, e.ParentID, e.Code, e.Name, cast(null as integer),
e.Article, e.Collection, a1.Name, e.Standard,
a2.Name, e.SubElsCount, e.IsActive from Elements e
left outer join Attributes a1 on a1.ID=e.ManufactID
left outer join Attributes a2 on a2.ID=e.SubCategID
where e.ParentID is not null
union
/* and categories as "root elements" without parents */
select ID, cast(null as integer), Code, cast(Name as varchar(50)),
cast(BgColorCode as integer),
cast(null as varchar(20)), cast(null as varchar(30)),
cast(null as varchar(30)), cast(null as varchar(30)),
cast(null as varchar(30)), ElsCount,
cast(1 as smallint) from Categories;
-----

IBExpert plan analizer shows the following for that view:
-----
PLAN JOIN (JOIN (E INDEX (ELEMENTS_PARENTID_FK),A1 INDEX
(ATTRIBUTES_PK)),A2 INDEX (ATTRIBUTES_PK))
PLAN JOIN (JOIN (E NATURAL,A1 INDEX (ATTRIBUTES_PK)),A2 INDEX
(ATTRIBUTES_PK))
PLAN (CATEGORIES NATURAL)
-----

At present I have no additional indices on Elements(ParentID) except
FK index.

Questions:
Is it OK that the second SELECT doesn't use index (as first SELECT
does)? What index should I create/use to fix the problem (if it's the
problem :)?


Thanks in advices.

Best regards,
Pavel Menshchikov.