At 12:20 AM 22/01/2004 +0000, you wrote:

>HUD_TRACS_FILES (175)
>
> Index IDX_CONTRACT_SYSID_HTF (2)
> Depth: 2, leaf buckets: 108, nodes: 69430
> Average data length: 0.00, total dup: 68880, max dup: 809
> Fill distribution:
> 0 - 19% = 1
> 20 - 39% = 0
> 40 - 59% = 6
> 60 - 79% = 0
> 80 - 99% = 101

Selectivity is OK.

> Index IDX_DATE_RECEIVED_HTF (5)
> Depth: 2, leaf buckets: 266, nodes: 69430
> Average data length: 1.00, total dup: 27949, max dup: 5
> Fill distribution:
> 0 - 19% = 0
> 20 - 39% = 0
> 40 - 59% = 265
> 60 - 79% = 1
> 80 - 99% = 0

Selectivity is fine but fill distribution is not great. This index would
benefit from rebuilding. (alter...set inactive..commit; alter...set
active...commit;)

> Index IDX_HTF_DATE_RECEIVED (1)
> Depth: 2, leaf buckets: 132, nodes: 69430
> Average data length: 1.00, total dup: 27949, max dup: 5
> Fill distribution:
> 0 - 19% = 1
> 20 - 39% = 0
> 40 - 59% = 0
> 60 - 79% = 0
> 80 - 99% = 131

OK.

> Index IDX_HTF_DATE_RECEIVED_DESC (4)
> Depth: 2, leaf buckets: 132, nodes: 69430
> Average data length: 1.00, total dup: 27949, max dup: 5
> Fill distribution:
> 0 - 19% = 1
> 20 - 39% = 0
> 40 - 59% = 0
> 60 - 79% = 0
> 80 - 99% = 131

Same as above.

> Index IDX_HTF_DATE_TIMESTAMP (7)
> Depth: 2, leaf buckets: 105, nodes: 69430
> Average data length: 0.00, total dup: 69419, max dup: 69417
> Fill distribution:
> 0 - 19% = 1
> 20 - 39% = 0
> 40 - 59% = 2
> 60 - 79% = 0
> 80 - 99% = 102

Horrible. Virtually all nodes are duplicates (expected) but all but two of
those duplicates are in one chain!! It has to go!

> Index IDX_HTF_FILE_TYPE_AND_KEY (6)
> Depth: 2, leaf buckets: 248, nodes: 69430
> Average data length: 8.00, total dup: 0, max dup: 0
> Fill distribution:
> 0 - 19% = 0
> 20 - 39% = 0
> 40 - 59% = 0
> 60 - 79% = 0
> 80 - 99% = 248

As expected (new index, unique)

> Index IDX_HTF_MADE_BY_AND_KEY (9)
> Depth: 2, leaf buckets: 249, nodes: 69430
> Average data length: 8.00, total dup: 0, max dup: 0
> Fill distribution:
> 0 - 19% = 0
> 20 - 39% = 0
> 40 - 59% = 0
> 60 - 79% = 0
> 80 - 99% = 249

As above.

> Index IDX_HTF_SUBSIDY_TYPE_AND_KEY (8)
> Depth: 2, leaf buckets: 247, nodes: 69430
> Average data length: 8.00, total dup: 0, max dup: 0
> Fill distribution:
> 0 - 19% = 0
> 20 - 39% = 0
> 40 - 59% = 1
> 60 - 79% = 0
> 80 - 99% = 246

As above

> Index IDX_PROPERTY_SYSID_HTF (3)
> Depth: 2, leaf buckets: 108, nodes: 69430
> Average data length: 0.00, total dup: 68886, max dup: 809
> Fill distribution:
> 0 - 19% = 1
> 20 - 39% = 0
> 40 - 59% = 6
> 60 - 79% = 0
> 80 - 99% = 101

OK.

> Index RDB$PRIMARY49 (0)
> Depth: 2, leaf buckets: 292, nodes: 69430
> Average data length: 6.00, total dup: 0, max dup: 0
> Fill distribution:
> 0 - 19% = 1
> 20 - 39% = 0
> 40 - 59% = 157
> 60 - 79% = 0
> 80 - 99% = 134

As expected re selectivity (it's unique). More than half of the pages are
less than 60% filled, so it could do with a rebuild, but not
desperately. Rebuilding is not so easy with a PK index, as you need to do
alter table...drop constraint..commit; alter table add constraint...commit;
and it will be complicated if there are dependencies on that PK. Regular
backup and restore will right it.

> Ploy A: Force the optimizer to ignore the PK of LOOKUP_CODES by
>modifying
> > the WHERE clause:
> >
> > WHERE CHA.User_Sysid = 1007
> > AND HTF.SubsidyType + 1 = HTF.SubsidyType + 1
> > AND HTF.FileType + 1 = HTF.FileType + 1
> > AND HTF.Made_By + 1 = HTF.Made_By + 1
> >
>
>This is REALLY BAD. A WHOLE LOT worse!

With the new "_KEY" indexes it will be.

> Ploy C: Create a highly selective index for each of the lookup
>columns,
> > involving the column + the PK in each case, in that order...let's
>say your
> > PK is P_Key:
> >
> > create index ix_SubsidyType on HUD_TRACS_FILES (SubsidyType, P_Key);
> > create index ix_FileType on HUD_TRACS_FILES (FileType, P_Key);
> > create index ix_Made_By on HUD_TRACS_FILES (Made_by, P_Key);
> >
>
>I did this and the plan does not show them being used.

That's because you used Ploy A *as well*, which caused the new indexes to
be ignored!!!

>What do you think???

I think that, with your new indexes and dropping IDX_HTF_DATE_TIMESTAMP,
your original query should be a lot better.

/heLen