At 08:17 AM 19-07-02 +0930, Raymond Kennington wrote:

>The problem with using RDB$FOREIGN66 or RDB$Primary47 as indexes in PLANs
>is that their
>numbers could change with the insertion of constraints between existing
>constraints when a
>database is recreated.
>
>Is it now possible to name these when created and use those
>developer-defined names for use in PLANs?

No. And the system-generated indexes will have their names changed by a
backup and restore. For this reason, it is somewhat less than useless to
store plans in your applications.

Use the PLAN statement as a development tool, to test your assumptions
agains the optimizer's choices. The "right" approach to getting the
optimizer to use your preferred plan is to test your queries using an SQL
Monitor which will display the optimizer's plan at Prepare time (IB_SQL has
a good one). You can influence the plan which the optimizer will use by
some tweaking in your SQL to force it to ignore a troublesome index.

Of course, it goes without saying that the better your indexing scheme, the
higher the likelihood that the optimizer will get the plan right every
time. Certainly avoid **completely** creating indexes which duplicate
those created by the system for keys (primary, unique, foreign); and (for
now, Firebird and InterBase alike) avoid creating a single-column index on
a column of low selectivity (i.e. where large blocks of rows contain the
same value, from a small set of possible values); and don't place foreign
keys on such single columns. Avoid having foreign keys that "step on" the
primary key (a common source of optimizer problems for people who convert
Paradox databases to IB/FB without fixing up keys). Create descending
indexes for places where you need to call Max() or to sort from the top - a
DESC index won't interfere with an ASC index over the same column(s).

heLen

All for Open and Open for All
Firebird Open SQL Database · http://firebirdsql.org ·
http://users.tpg.com.au/helebor/
_______________________________________________________