CREATE TABLE INDEXER_WORDS (
    WORD_ID  INTEGER NOT NULL,
    WORD     VARCHAR(50)
);
ALTER TABLE INDEXER_WORDS ADD CONSTRAINT PK_INDEXER_WORDS PRIMARY KEY (WORD_ID);
CREATE INDEX INDEXER_WORDS_IDX1 ON INDEXER_WORDS (WORD);

CREATE TABLE INDEXER_FILES (
    ID        INTEGER NOT NULL,
    WORD_ID   INTEGER,
    FILE_ID   INTEGER,
    WORD_NUM  INTEGER
);

ALTER TABLE INDEXER_FILES ADD CONSTRAINT PK_INDEXER_FILES PRIMARY KEY (ID);
ALTER TABLE INDEXER_FILES ADD CONSTRAINT FK_INDEXER_FILES_1 FOREIGN KEY (WORD_ID) REFERENCES INDEXER_WORDS (WORD_ID) ON DELETE CASCADE;

select distinct
ifl1.file_id
from indexer_files ifl1
join indexer_files ifl2 on ifl2.file_id=ifl1.file_id
join indexer_files ifl3 on ifl3.file_id=ifl1.file_id
join indexer_words iw on ifl1.word_id=iw.word_id
join indexer_words iw2 on ifl2.word_id=iw2.word_id
join indexer_words iw3 on ifl3.word_id=iw3.word_id
where iw.word like 'ивано%'
and iw2.word like 'петр'
and iw3.word like 'михайлович'

select distinct
ifl1.file_id
from indexer_files ifl1
join indexer_words iw1 on ifl1.word_id=iw1.word_id
where iw1.word like 'ивано%'
and
exists(select ifl2.word_id
       from indexer_files ifl2
       join indexer_words iw2 on iw2.word_id=ifl2.word_id
       where ifl2.file_id=ifl1.file_id
       and iw2.word like 'петр')
and
exists(select ifl3.word_id
       from indexer_files ifl3
       join indexer_words iw3 on iw3.word_id=ifl3.word_id
       where ifl3.file_id=ifl1.file_id
       and iw3.word like 'михайлович')

EXECUTE BLOCK returns (
file_id INTEGER)
AS
declare variable ex INTEGER;
declare variable ex2 INTEGER;
BEGIN
  FOR select distinct ifl1.file_id
  from indexer_files ifl1
  join indexer_words iw1
  on ifl1.word_id=iw1.word_id
  where iw1.word like 'ивано%'
  INTO :file_id do
  BEGIN
    ex=null;
    ex2=null;

    select first 1 ifl2.word_id
    from indexer_files ifl2
    join indexer_words iw2 on iw2.word_id=ifl2.word_id
    where ifl2.file_id=:file_id
    and iw2.word like 'петр' into :ex;

    if (:ex is not null) then
    begin
      select first 1 ifl3.word_id
      from indexer_files ifl3
      join indexer_words iw3 on iw3.word_id=ifl3.word_id
      where ifl3.file_id=:file_id
      and iw3.word like 'михайлович' into :ex2;
    end

    if ((:ex is not null) and (:ex2 is not null)) then SUSPEND;
  END
END