Доброго дня. Такая же задача - есть объекты из множества таблиц с тучей полей, в таблицах млн записей. Выборки нельзя назвать ad-hoc, однако они разнообразны и будут определяться пользователями. СУБД не позволяет использовать множество индексов на одну таблицу в запросе, хотя даже если бы и позволяла, ни к чему хорошему это не привело при таких кол-вах полей и записей. Естественно при такой постановке задачи игра в угадайку по типа "Наделаю составных индексов, авось пригодится", тоже не прошла бы.

В итоге на чем остановился:
Вынес в отдельную таблицу поля, которые железно будут участвовать в выборках (например Код-натуральный ключ или Наименование). Основные по ним выборки - поисковые операции нужных объектов (то есть единичных).

Ввел в систему понятие именованные выборки. Пользователь имеет возможность задать различные условия выборки и сохранить описание этих условий в БД под именем. При сохранении условий в БД, так же автоматически по ним формируется SQL запрос, возвращающий список id объектов, которые соответствуют запросу. Текст запроса тоже сохраняется в БД. После сохранения по этому фильтру в постоянную таблицу заносятся все id объектов, подходящих под выборку, то есть идет долгий и неприятный table scan. Таким образом, пользователь теперь может просто указав нужную выборку, моментально получить все нужные объекты, где в запросе вместо условия выборки будет достаточно подставить WHERE id IN (SELECT object_id FROM Выборка WHERE КодВыборки = <Код>). Для того, чтобы не плодить хранение результатов выборок, пользователю дана возможность на базе именованной выборки писать свои приватные условия. То есть он может создать именованную выборку "Все у кого Поле1 между 500 и 1000", а затем используя ее в запросах, добавлять в запрос к вышеуказанному запросу в WHERE еще свое условие "AND Поле1 BETWEEN 400 AND 600", что будет моментально отрабатывать на объемах в миллионы записей. Естественно, для того, чтобы выборки были актуальные, их надо перестраивать - динамически при изменении в таблицах на триггерах или периодически - это зависит от способов пополнения информации и более выгодной логики. У меня например записи вливаются ручным вводом, поэтому выгоднее динамически, если бы я грузил записи большими порциями с внешних источников, то использовал бы периодически. Идея взята по мотивам материализованных представлений.

Конечно, если бы у меня в этом проекте использовалась СУБД та же ASA 11, мне бы вообще мало что пришлось делать - генерил бы по выборкам мат представления и не о чем не думал, они бы сами даже обновлялись при изменении записей в таблицах, на которые завязан запрос. К сожалению используется другая СУБД, поэтому все придется делать руками. Но считаю данное решение оптимальным для поставленной задачи, во всяком случае лучше ничего придумать не удалось ;)

KREDASCRUS,

Честно слово .... сложно это и по большей части я очень подумаю или пойти на такой вариант.
Такие самописные матерчатые вьюхи пока не хочется даже планировать ... Буду пытаться сделать на основе одной таблички... не получиться так начнём этим заморачиваться.
Не спорю. Все будет зависеть от сложности выборок, кол-ва записей и временем отклика системы, которое устроит пользователя. Делать конечно же нужно как можно проще, усложнять в крайних случаях, когда простых решений не было найдено.