Hi Jason,

i have used IBO Monitor on my example and i have extracted some parts:


[ 01/06/2005 18.00.12 ]
PREPARE STATEMENT
TR_HANDLE = 16871616
STMT_HANDLE = 16871672

SELECT RDB$FIELD_NAME
FROM RDB$INDEX_SEGMENTS
WHERE RDB$INDEX_NAME = ? /* Name */
ORDER BY RDB$FIELD_POSITION ASC

PLAN SORT ((RDB$INDEX_SEGMENTS INDEX (RDB$INDEX_6)))

====>> This query seem to be ok. <<====

% % % % % % % % % % % % % % % % % % % % % % % % % % % % % % % % % % % %
% % % % % % % % %

/*---
[ 01/06/2005 18.00.12 ]
PREPARE STATEMENT
TR_HANDLE = 16871616
STMT_HANDLE = 16871428

SELECT I.RDB$RELATION_NAME
, I.RDB$INDEX_NAME
, I.RDB$UNIQUE_FLAG
, I.RDB$INDEX_TYPE
, I.RDB$FOREIGN_KEY
, ( SELECT C.RDB$CONSTRAINT_NAME
FROM RDB$RELATION_CONSTRAINTS C
WHERE C.RDB$INDEX_NAME = I.RDB$INDEX_NAME ) AS CNST
, ( SELECT R.RDB$UPDATE_RULE
FROM RDB$REF_CONSTRAINTS R
WHERE R.RDB$CONSTRAINT_NAME =
( SELECT C.RDB$CONSTRAINT_NAME
FROM RDB$RELATION_CONSTRAINTS C
WHERE C.RDB$INDEX_NAME = I.RDB$INDEX_NAME )) AS UR
, ( SELECT R.RDB$DELETE_RULE
FROM RDB$REF_CONSTRAINTS R
WHERE R.RDB$CONSTRAINT_NAME =
( SELECT C.RDB$CONSTRAINT_NAME
FROM RDB$RELATION_CONSTRAINTS C
WHERE C.RDB$INDEX_NAME = I.RDB$INDEX_NAME )) AS DR
FROM RDB$INDICES I
WHERE NOT I.RDB$RELATION_NAME STARTING WITH 'RDB$'

PLAN (C INDEX (RDB$INDEX_43))
PLAN (R INDEX (RDB$INDEX_13))
PLAN (C INDEX (RDB$INDEX_43))
PLAN (R INDEX (RDB$INDEX_13))
PLAN (C INDEX (RDB$INDEX_43))
PLAN (I NATURAL)

====>> This query seem to be the problem !!! <<====

PLAN (I NATURAL) here the index are not used because the expression are
where NOT I.RDB$RELATION_NAME starting with 'RDB$'

% % % % % % % % % % % % % % % % % % % % % % % % % % % % % % % % % % % %
% % % % % % % % %

/*---
[ 01/06/2005 18.00.17 ]
PREPARE STATEMENT
TR_HANDLE = 16871616
STMT_HANDLE = 16871672

SELECT RDB$PROCEDURE_NAME
FROM RDB$PROCEDURES
ORDER BY RDB$PROCEDURE_NAME ASC

PLAN (RDB$PROCEDURES ORDER RDB$INDEX_21)

====>> This query seem to be ok <<====

% % % % % % % % % % % % % % % % % % % % % % % % % % % % % % % % % % % %
% % % % % % % % %

/*---
[ 01/06/2005 18.00.17 ]
PREPARE STATEMENT
TR_HANDLE = 16871616
STMT_HANDLE = 16871672

SELECT RDB$RELATION_NAME, RDB$FIELD_NAME, RDB$FIELD_SOURCE
FROM RDB$RELATION_FIELDS R
WHERE ( RDB$SYSTEM_FLAG <> 1 OR RDB$SYSTEM_FLAG IS NULL )
AND NOT RDB$RELATION_NAME STARTING 'RDB$'
AND NOT RDB$FIELD_NAME STARTING 'RDB$'
AND NOT RDB$FIELD_SOURCE STARTING 'RDB$'
ORDER BY 1 ASC
, 2 ASC

PLAN SORT ((R NATURAL))

====>> This query are not optimized but don't seem to be a big problem.
<<====

% % % % % % % % % % % % % % % % % % % % % % % % % % % % % % % % % % % %
% % % % % % % % %

/*---
[ 01/06/2005 18.00.18 ]
PREPARE STATEMENT
TR_HANDLE = 16871616
STMT_HANDLE = 16871672

SELECT R.RDB$FIELD_NAME
, R.RDB$RELATION_NAME
FROM RDB$RELATION_FIELDS R, RDB$FIELDS F
WHERE R.RDB$FIELD_SOURCE = F.RDB$FIELD_NAME
AND F.RDB$COMPUTED_SOURCE IS NOT NULL
AND NOT R.RDB$RELATION_NAME STARTING WITH 'RDB$'
ORDER BY 1 ASC

PLAN SORT (JOIN (F NATURAL,R INDEX (RDB$INDEX_3)))

====>> This query are not optimized but don't seem to be a big problem.
<<====

% % % % % % % % % % % % % % % % % % % % % % % % % % % % % % % % % % % %
% % % % % % % % %

/*---
[ 01/06/2005 18.00.18 ]
PREPARE STATEMENT
TR_HANDLE = 16871616
STMT_HANDLE = 24190644

SELECT RDB$FIELD_SOURCE
FROM RDB$RELATION_FIELDS
WHERE RDB$RELATION_NAME = ? /* RDB$RELATION_NAME */
AND RDB$FIELD_NAME = ? /* RDB$FIELD_NAME */

PLAN (RDB$RELATION_FIELDS INDEX (RDB$INDEX_15))

FIELDS = [ Version 1 SQLd 1 SQLn 30
RDB$RELATION_FIELDS.RDB$FIELD_SOURCE = <NIL> ]
----*/

====>> This query seem to be ok <<====

% % % % % % % % % % % % % % % % % % % % % % % % % % % % % % % % % % % %
% % % % % % % % %

But what i can do defining some index on the server.
The problem are in the query expression then i can nothing with index or
not ?

Best Regards.

Enrico Raviglione

IDEA Sistemi s.n.c.
Via Burolo, 30
10015 IVREA ( TO )
ITALY
TEL. +39 0125 253235
FAX +39 0125 253271
www.ideasistemi.it
e.raviglione@...