ASA6. К примеру, есть поле в некой таблице, по которому необходимо сделать полнотекстовый индекс для решения задачи контектного поиска.
Упрощенно это выглядит следующим образом:
main_table( id integer,data char(200)) - таблица с тектовым полем
words( id integer,txt char(50)) - таблица со словами
tw( main_table_id , words_id ) - таблица пересечений "одних" с "другими"
У таблицы tw два внешних ключа на другие таблицы. Т.е. между таблицами main_table и words реализовано отношение многие-ко-многим.
Самое интересное начинается при выборках.
Реализуем поиск всех заданных слов. Первое что приходит на ум (понятно, что динамическим SQL решаем вопрос с разным кол-вом слов):

select main_table.*
from
(select main_table_id from tw key join words where txt='слово1') as v1(i)
inner join (select main_table_id from tw key join words where txt='слово2') as v2(i) on v1.i=v2.i
inner join main_table on main_table.id=v1.i;

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

Развиваем тему:
select id
into id_1
from words
where txt='слово1'

select id
into id_2
from words
where txt='слово2'

select main_table.*
from
tw as c1,tw as c2,main_table
where c1.main_table_id=c2.main_table_id and c1.word_id = i_1 and c2.word_id = i_2 and c1.main_table_id=main_table.id;

В данном случае все хорошо. И оптимизатор в зависимости от данных выбирает как лучше делать, то ли c1->c2->main_table, то ли c2->c1->main_table. Таким образом выбираем все записи в которые входят все заданные слова. Но бывает случаи, когда одно слово встречается в справочнике слов более одного раза (как следствие использования словарей словоформ). Тогда необходимо реализовать локальную функцию "ИЛИ". Пробуем:

select main_table.*
from
tw as c1,tw as c2,main_table
where c1.main_table_id=c2.main_table_id and c1.word_id = (select id from words where txt='слово1') and c2.word_id = (select id from words where txt='слово2') and c1.main_table_id=main_table.id;

Но тут опять оптимизатор начинает "тупить", и не обращает внимания на статистику, т.е. план выполнения не меняется от исходных данных. Делаем через временные таблицы:

insert into t1
select id from
words where
txt = 'слово1';

insert into t2
select id from
words where
txt = 'слово2';

select main_table.*
from
tw as c1,tw as c2,t1,t2,main_table
where c1.main_table_id=c2.main_table_id and c1.word_id = t1.id and c2.word_id =t2.id and c1.main_table_id=main_table.id;

Так не тупит, но сервер делает лишнее перемножение(t1*t2), которое требует времени, т.е. опять не то... Все тесты проводил и на 6.04 и на АСА9.02. Результаты одинаковые. Также не нравится то, что АСА иногда забывает свою статистику или не может правильно предсказать и может начать совсем с другого конца(main_table->c2->c1), что вообще не катит.

Склоняюсь к мысли, что надо для каждого слова добавить статистику в виде поля. И потом при выборках "хранимой" делать по циклу селекты начиная с самого редкого пересекая с остаточным набором, постоянно его уменьшая. Но сдается мне, что сервер на то и нужен чтоб взять такую оптимизацию на себя?!...Вот только как ему это передать.

Вопросы:
1) Какие еще пути организации полнотекстового поиска для АСА есть?
2) Какие еще мысли есть по организации выборок из приведенной схемы таблиц с реализацией поиска (S1|S2)&(S4|S5|S6)
3) Может я делаю не то, не там и не так?!

main_table - список статей
tw наполняет кто-то. Триггер на main_table или хранимая процедура по расписанию - для вопросов не важно.
Представьте себе матрицу, по горизонтали коды статей, по вертикали - коды слов. tw - пересечение одних с другими.
В моем понимании, таким образом я реализовал полнотекстовый поиск собственными средствами а не средствами СУБД. tw - называется полнотекстовым индексом.

Именно, что в t1 и t2 не по одной строке. Читайте внимательней, эти таблицы сделаны как раз из-за того, чтобы реализовать поиск по "синонимам". Т.е. вопрос в следующем как нужно найти такую статью, в которой упоминалось бы ((слово1 или ... или словоX) и ... и (слово2 или ... или словоY)
Таблица t1 - набор кодов "слово1",...,"словоX"
Таблица t2 - набор кодов "слово2",...,"словоY"

Кол-во синонимов у каждого слово свое, от 0 до бесконечности в теории.
Примеры написаны исходя из того, что мы ищем два разных слова, кол-во синонимов этих слов не определено.
Ладно, плюньте на предметную область. Всех только запутал.
Перевожу вопрос в более узкое русло:
Есть таблица
к1 к2
1 1
1 3
1 4
2 2
2 4
3 1
3 2
3 3
3 5

Вот вопрос в следующем: как найти все значения к1 для которых в этой таблице есть соответствие на к2 по формуле (к2=1 или к2=4) и (к2=2) .
Понятно, что для этого условия подходит к1=2 или к1=3. Вот как написать селект для АСА, чтоб оптимизатор мог определить необходимый план выполнения в зависимости от статистики к2.
Т.е. если АСА сначала попытается выбрать все записи при которых к2=2, то сразу станет понятно, что набор с к1=1 выпадает из результата, и нет необходимости его просматривать. Вот когда мы делаем выборки по типу (к2=1) и (к2=5) , то АСА этот момент ловит и делает как быстрее, т.е. сначала к2=5, потом среди этого к2=1... Проблема в реализации принципа "или".

А вот union all задействовать я как-то и не подумал. Щас попробую.

Получилось без union'ов:

select c1.k1,c1.k2,c2.k2
from t as c1,t as c2
where c2.k2 in (65768,67601) and c1.k2 in (70442,72871) and c1.k1 = c2.k1;

Так оптимизатор нормально выбирает план исходя из статистики значений. Такой вариант подходит, но остались вопросы:
1) Как С127 сделал поиск статическим SQL, пускай без синонимов, но с заранее неизвестным кол-вом требуемых слов? И на каком сервере?
2) Сервер АСА6/9. После старта в кэше пусто, поэтому при поиске часто возникает дисковое чтение, пока он не возьмет себе в кэш всю таблицу t с индексами. Закрепить таблицу к кэшу, как я понимаю в АСА низя? И не дай бог АСА выбросит у себя из кэша таблицу - опять все заново накачивать придется. А из-за этого дискового чтения идут секунды, или десятки секунд. Как избежать этого чтения нормальным путем(сейчас выкручиваюсь путем выкладывания этого спейса с этой таблицей на виртуальный диск, и на дисковое чтение мне положить)? Может я неправильно сделал структуру? Может на АСА такие задачи не делаются? Или я неправильные селекты пишу?

Вот и не понятно как это иннер джоин!
Есть временная табличка "T" искомых слов:
ID_word
1
3

Есть таблица "WA" связей статей со словами:
ID_word ID_Article
1 1
2 1
4 1
6 1
3 2
4 2
1 2

Дальше напишите плиз как джойните? Ну в смысле приджойнить - это полдела, чего дальше-то с этим делать? Как отсечь лишнее?
Пересечение T с WA:
ID_word ID_Article
1 1
3 2
1 2
Нужны же только те статьи, в которых есть все искомые слова! Если по этому делать GROUP BY и HAVING count=2, то получается много лишней работы для СУБД.