-- попробуем создать индекс по выражению, который включает в 
-- своё выражение вычисляемое поле. Не случайно индекс по вычисляемому
-- полю запрещён, но проверим лишний раз

-- таблица, которую мы будем мучать
create table t1(
  fld_Value varchar(50),
  fld_Key computed by((
    -- PNum - номер символа
    -- PChr, PNextChr - текущий и следующий за ним символ
    with recursive tmp(PNum, PChr, PNextChr) as (
        select
            1,
            substring(fld_Value from 1 for 1),
            substring(fld_Value from 2 for 1)
        from rdb$database

        union all

        select
            PNum + 1,
            substring(fld_Value from PNum + 1 for 1),
            substring(fld_Value from PNum + 2 for 1)
        from tmp
        where
            -- рекурсия не прерывается:
            -- * пока не достигли конца строки
            PNum < char_length(fld_Value) and
            -- * пока не попали на первый фронт цифра -> не-цифра
            (
                not (PChr between '0' and '9') or
                (PNextChr between '0' and '9')
            )
    ) -- /with recursive
    select cast(list(PChr, '') as varchar(30))
    from tmp
    where
        -- в итоговый лист попадают только цифры
        PChr between '0' and '9'
  )) -- /computed by
) -- /create table

-- собственно, индекс, который пробует обойти запрет
-- на индексацию вычисляемых полей "в лоб"
create index idx1 on t1
computed by ((fld_Key));

-- пушечное "свежее мя-я-яско!"
insert into t1(fld_Value)
values('ABCDE123344556667ILIKEIT');
insert into t1(fld_Value)
values('KARKNULVORONNEVERMORE123');
insert into t1(fld_Value)
values('OBEAFINEGIRLKISSME!');
commit; -- должна перестроить индекс

-- проверяем, что вычисляемое поле считается правильно:
select *
from t1;

/*
вернулось:
FLD_VALUE                               FLD_KEY
----------------------------------------------
ABCDE123344556667ILIKEIT        123344556667
KARKNULVORONNEVERMORE123    123
OBEAFINEGIRLKISSME!                null

ок, ключевое поле нормальное
*/

-- смотрим статистику индекса
select rdb$statistics
from rdb$indices
where rdb$index_name = 'IDX1';
-- вернула 1,00

-- попробуем явно пересчитать статистику
SET STATISTICS INDEX idx1;

-- снова смотрим статистику
select rdb$statistics
from rdb$indices
where rdb$index_name = 'IDX1';
-- чудес не бывает, она снова вернула 1,00

-- естественно, индекс при такой статистике
-- никуда не подцепится:
select fld_Value
from t1
where (fld_key) starting with '1233';
/*
получившийся план запроса:

PLAN (RDB$DATABASE NATURAL)
PLAN JOIN ()
PLAN (T1 NATURAL)

как мы видим из плана, значения вычисляемого поля,
существовавшие в момент построения индекса,
не используются. Вместо них используются текущие 
(мгновенные) значения вычисляемого поля.
*/

-- давайте попробуем построить вычисляемый индекс
-- по вычисляемому полю, такой, чтобы в запросах
-- он всё ж подцепился.

alter table t1
  add fld_AltKey computed by(
    -- это, конечно, не первый блок цифр. Просто уберём,
    -- для иллюстрации, подзапросы из выражения:
    cast(
        substring(fld_Value from 1 for 1) ||
        substring(fld_Value from 3 for 1) ||
        substring(fld_Value from 5 for 3)
        as varchar(5)
    )
  )

select *
from t1;
/*
FLD_VALUE                               FLD_KEY           FLD_ALTKEY
--------------------------------------------------------
ABCDE123344556667ILIKEIT        123344556667   ACE12
KARKNULVORONNEVERMORE123    123                 KRNUL
OBEAFINEGIRLKISSME!                                      OEFIN

ок, всё прилично пока...
*/

-- альтернативный индекс
create index idx2 on t1
computed by ((fld_AltKey));

-- коммитим и смотрим статистику индекса
select rdb$statistics
from rdb$indices
where rdb$index_name = 'IDX2';
-- вернула 1,00. Статистика не пересчиталась... странно!

-- укажем явный пересчёт статистики
SET STATISTICS INDEX idx2;

-- снова смотрим статистику
select rdb$statistics
from rdb$indices
where rdb$index_name = 'IDX2';
-- селективность с какого-то перепоя становится отрицательной: -1.00!

-- ладно, пробуем подцепить индекс...
select fld_Value
from t1
where (fld_AltKey) starting with 'K';
/*
не, нифига. План запроса:
PLAN (T1 NATURAL)
То же, что и раньше, только поскольку  вычисляемое 
поле теперь без запросов (скалярные функции), то и 
join'а больше нет.
*/

-- хорошо, теперь создадим индекс по выражению так,
-- чтобы вычисляемые поля в нём вообще не светились.
-- Попытаемся подцепить его в запросе

-- копипастим выражение для второго вычисляемого поля:
create index idx3 on t1
computed by (
    cast(
        substring(fld_Value from 1 for 1) ||
        substring(fld_Value from 3 for 1) ||
        substring(fld_Value from 5 for 3)
        as varchar(5)
    )
);

select rdb$statistics
from rdb$indices
where rdb$index_name = 'IDX3';
-- получаем ожидаемую статистику - 0,33

-- подцепится?
select fld_Value
from t1
where (fld_AltKey) starting with 'K';
/* PLAN (T1 INDEX (IDX3))
индекс подцепился, логично - выражение для fld_AltKey 
попало в where clause (поле вычисляется же в момент 
вычитки), и совпало с для fld_AltKey.
*/

-- повторяем третий вариант, но теперь включаем в индекс 
-- целиком выражение для первого вычисляемого поля:
create index idx4 on t1
computed by((
    -- PNum - номер символа
    -- PChr, PNextChr - текущий и следующий за ним символ
    with recursive tmp(PNum, PChr, PNextChr) as (
        select
            1,
            substring(fld_Value from 1 for 1),
            substring(fld_Value from 2 for 1)
        from rdb$database

        union all

        select
            PNum + 1,
            substring(fld_Value from PNum + 1 for 1),
            substring(fld_Value from PNum + 2 for 1)
        from tmp
        where
            -- рекурсия не прерывается:
            -- * пока не достигли конца строки
            PNum < char_length(fld_Value) and
            -- * пока не попали на первый фронт цифра -> не-цифра
            (
                not (PChr between '0' and '9') or
                (PNextChr between '0' and '9')
            )
    ) -- /with recursive
    select cast(list(PChr, '') as varchar(30))
    from tmp
    where
        -- в итоговый лист попадают только цифры
        PChr between '0' and '9'
)) -- /computed by

-- что статистика, изменилась?
select rdb$statistics
from rdb$indices
where rdb$index_name = 'IDX4';
-- о, да! она 0,33.

-- ну, подцепит или нет?
select fld_Value
from t1
where fld_Key starting with '1233';
/* нет, не подцепит

PLAN (RDB$DATABASE NATURAL)
PLAN JOIN ()
PLAN (T1 NATURAL)

непонятно почему, возможно оптимизатор опустил руки на join'е.
По плану есть вопрос - в конце сообщения
*/