FB 2.0, индексы по строкам

Wed, 29 Nov 2006 05:12:57 -0800 

Есть БД, изначально создана на FB 1.5
Вчера попробовал перевести на 2.0.
Возникла ошибка при индексном поиске
по VARCHAR

Упростил БД до минимума

CREATE DOMAIN DID AS INTEGER NOT NULL;
CREATE DOMAIN DSTRING10N AS VARCHAR(10) DEFAULT '' COLLATE WIN1251_UA;

CREATE TABLE DOC (
    ID      DID NOT NULL,
    NUM_PP  DSTRING10N COLLATE WIN1251_UA
);

ALTER TABLE DOC ADD CONSTRAINT PK_DOC PRIMARY KEY (ID);
CREATE INDEX DOC_NUMPP ON DOC (NUM_PP);

-------------
Статистика БД

Database header page information:
        Flags                   0
        Checksum                12345
        Generation              71
        Page size               16384
        ODS version             11.0
        Oldest transaction      63
        Oldest active           64
        Oldest snapshot         64
        Next transaction        65
        Bumped transaction      1
        Sequence number         0
        Next attachment ID      0
        Implementation ID       16
        Shadow count            0
        Page buffers            0
        Next header page        0
        Database dialect        3
        Creation date           Nov 29, 2006 14:34:41
        Attributes              force write

    Variable header data:
        Sweep interval:         20000
        *END*


Database file sequence:
File D:\GDB\LLK3_1.FDB is the only file

Analyzing database pages ...
DOC (128)
    Primary pointer page: 135, Index root page: 136
    Average record length: 11.14, total records: 30119
    Average version length: 0.00, total versions: 0, max versions: 0
    Data pages: 94, data page slots: 94, average fill: 55%
    Fill distribution:
         0 - 19% = 0
        20 - 39% = 1
        40 - 59% = 93
        60 - 79% = 0
        80 - 99% = 0

    Index DOC_NUMPP (1)
        Depth: 2, leaf buckets: 7, nodes: 30119
        Average data length: 0.16, total dup: 25509, max dup: 254
        Fill distribution:
             0 - 19% = 1
            20 - 39% = 0
            40 - 59% = 0
            60 - 79% = 0
            80 - 99% = 6

    Index PK_DOC (0)
        Depth: 2, leaf buckets: 12, nodes: 30119
        Average data length: 1.77, total dup: 0, max dup: 0
        Fill distribution:
             0 - 19% = 0
            20 - 39% = 0
            40 - 59% = 0
            60 - 79% = 0
            80 - 99% = 12
------------------
Теперь собственно запрос

select * from doc where (NUM_PP = '') or (NUM_PP is NULL)

Возвращает, в зависимости от размера
страницы от 32 записи для (16K) до ~762 для 1K

Запрос (без использования индексов)
select * from doc where coalesce(NUM_PP,'') = ''
Возвращает, 26819.

select * from doc where (NUM_PP||'' = '') or (NUM_PP||'' is NULL) -
так тоже 26819

Есть архив fbk (rar) 116K 
P.S. FB 2.0 Release, WinXP + SP2

Ответить