Semen Popov,
Оптимальность не бывает "подвешена в воздухе".
- Каковы вообще предполагаемые объёмы данных? Тысячи строк, миллионы, сотни миллионов, больше? Может вообще овчинка выделки не стоит и как ни делай, всё равно.
- Каковы относительные объёмы? Можно предположить, что таблицы NISx - "справочники" с относительно малым объёмом (единицы строк, тысячи, десятки, миллионы), а вот "REGISTER" - таблица фактов несравнимо большего объёма.
- Являются ли bigint значения в таблицах NISx первичными ключами ключами? Уникальны ли NAMx в рамках своих таблиц?
- Являются приведённые запросы - "профильной" нагрузкой? Т.е. надо ли строить всё преимущественно под них.
- Есть ли наиболее часто используемые группы в наборах (NAMx, NAMy, NAMz, ...) или это под какую-нибудь аналитику/дата-майнинг, прости господи, чтобы данные со всех сторон вертеть? (Они в имеющихся решениях чаще вертятся уже полностью выгруженные на клиент)
- Как обновляются таблицы?
Далее в предположении, что данных много.
Приведённые Вами способы выразить запрос приведут к полному построению ("развёртыванию") имеющейся конструкции (этакая денормализация в одну таблицу).
Второй способ не до конца ясен, пока Вы не привели код ISCOND(x) ф-и, но ситуфция, полагаю, будет схожей.
СУБД не настолько "умна", чтобы развернуть логику внутри SUM, чтобы исключить из построения часть строк ("понимая", что 0 не повлияет на сумму, да и вообще как-то соображая, как тот 0 может получиться).
Ситуация подобна выборке по предикату "where F(column_X) = ?". Сколько ни строй индексов по column_X, потребуется просканировать всю таблицу, построить все результаты и произвести сравнение. Ровно потому, что в общем случае (если Вы сталкивались с теорией алгоритмов) машинным образом построить обратный алгоритм и преобразовать предикат в "where column_X = F1(?)" невозможно (и не всегда такая обратная ф-я вообще есть).
/* Может быть не совсем так с INLINED SQL ф-ей, и есть костыль в виде функционального индекса */
Давайте теперь посмотрим, что будет происходить.
Это будет full scan по самой большой таблице (REGISTER?) с допустим миллионом записей с последующим объединением с NISx с пусть хотя бы по 1000 записей.
Так или иначе объединяя записи (просто проверяя условие объединения) потребуется перелопатить:
1000000*1000*1000*1000*1000*1000*1000*1000 строк, оставить тот же 1000000 и физически сформировать гораздо более широкую со строками вместо bigint, потом пройтись этому 1000000 строк для вычисления окончательного результата.
Это будет долго. Очень долго.
Вообще говоря, приведённая структура таблиц более менее стандартна для хранилищ (если верно предположение о характере и относительных объёмах таблиц). СУБД хорошо с ними работают. Только пишите более понятно для оптимизатора (предполагая, что NAMx уникальны в NSIx):
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
select
'P1' as NAME
,count(*)
from REGISTER
where IDNSI1 = (select IDNSI1 from NSI1 where NAM1='1VAL1')
AND IDNSI2 = (select IDNSI2 from NSI2 where NAM2='1VAL2')
AND IDNSI4 = (select IDNSI4 from NSI4 where NAM4='1VAL4')
union all
select
'P2' as NAME
,count(*)
from REGISTER
where IDNSI1 = (select IDNSI1 from NSI1 where NAM1='2VAL1')
AND IDNSI5 = (select IDNSI5 from NSI5 where NAM5='2VAL5')
AND IDNSI6 = (select IDNSI6 from NSI6 where NAM6='2VAL6')
AND IDNSI7 = (select IDNSI7 from NSI7 where NAM7='2VAL7')
select
'P3' as NAME
...
Или вообще выполняйте эти запросы по отдельности.
Все запросы к NISx выродятся в несколько коротких look-up'ов, далее зависит от индексов на REGISTER. В худшем случае просто full-scan таблицы. В лучшем - INDEX scan(s) и динамическое построение bitmap index'а или просто scan по региону индекса.
Можете оставить:
1.
2.
3.
4.
5.
6.
select 'P1' as NAME, count(*)
from REGISTER as r
join NSI1 as n1 on r.IDNSI1=n1.IDNSI1
join NSI2 as n2 on r.IDNSI2=n2.IDNSI2
join NSI4 as n4 on r.IDNSI4=n4.IDNSI4
where (NAM1, NAM2, NAM4)=('1VAL1', '1VAL2',' 1VAL4')
Оптимизатор развернёт эту констркцию как надо (но всё-таки всегда проверяйте получившийся план).
Только пожалуйста, используйте в данном случае просто join, чтобы как раз отфильтровать записи из REGISTER и выбросите лишние, чтобы отбросить ненужную фильтрацию по наличию записей в неиспользуемых в предикате "таблиц-справочников".
В качестве упражнения - оформить несколько запросов в виде Common Table Expression (CTE) и собрать результат в одну строку ;)
Как мне представляется - неверный посыл с Вашей стороны - попытка оформить всё в таком виде, чтобы при выполнении осущесвлялся только один проход по таблице.
PS Если данных _действительно_ много, то есть ещё такая вещь, как BLU Acceleration (но не в 9.7).
В общем, вам действительно надо или абстрактный интерес?
