Hi Ivan,

Thanks for your response. You're right and I was wrong, the indices are
used if the plan is 'ORDER' and not used if it's 'SORT'.

But there is still a problem. Can the optimizer use an index for sorting
joined tables? I couldn't enforce it to do this.

Look at the following simple query:

select a."Uid", a."Conn_Id", a."Type", b."Type", a."Line", a."Stamp",
a."Duration",
a."Int_Num", a."Ext_Num", a."Ext_Num_Is_Valid", a."Ext_Num_Validated",
b."Ext_Num", b."Ext_Num_Is_Valid", b."Ext_Num_Validated"
from "History" a, "History" b
where a."Conn_Id" = b."Conn_Id" and b."Type" <> 'T'
order by a."Stamp"

The plan is:

PLAN SORT (JOIN (B NATURAL,A INDEX (idx_History_Conn_Id)))

So the engine performs the external sorting of the result set, although I
have an ascending index for the "Stamp" field.

Let's rewrite the above query as:

select a."Uid", a."Conn_Id", a."Type",
(select "Type" from "History" where "Conn_Id" = a."Conn_Id" and "Type"
<> 'T'),
a."Line", a."Stamp", a."Duration",
a."Int_Num", a."Ext_Num", a."Ext_Num_Is_Valid", a."Ext_Num_Validated",
(select "Ext_Num" from "History" where "Conn_Id" = a."Conn_Id" and
"Type" <> 'T'),
(select "Ext_Num_Is_Valid" from "History" where "Conn_Id" =
a."Conn_Id" and "Type" <> 'T'),
(select "Ext_Num_Validated" from "History" where "Conn_Id" =
a."Conn_Id" and "Type" <> 'T')
from "History" a
order by a."Stamp"

And the plan is:

PLAN (History INDEX (idx_History_Conn_Id))
PLAN (History INDEX (idx_History_Conn_Id))
PLAN (History INDEX (idx_History_Conn_Id))
PLAN (History INDEX (idx_History_Conn_Id))
PLAN (A ORDER idx_History_Stamp)

As you can see, the existent index is used.

The trick is the second query executes up to 5 times faster than the first
one. In the case of descending sorting on the same field, the second query
is twice faster. And only for a sequential select without sorting, the
second query is about 20% slower.

It's just an example, maybe a bad one, but scanning through the index works
faster than the external sorting, even if this query contains a few nested
ones. Note, that the record size in this case is considerably less than the
page size.

BTW, are there any optimizations in the engine to perform such nested
selects, which are practically the same? If not, why isn't it possible for
me to use nested queries with multiple fields selected? IMHO, it would
increase the execution performance. And anyway, I think the absence of such
ability is the parser's problem, but not the engine's one.

Best regards,
Dmitry