ASE 12.5.3. Необходимо создать составной индекс покрывающий запрос из 5 полей, одно из них (предпоследнее) уникальная id-шка. Т.е. индекс всегда будет уникальным. С точки зрения производительности (и вставки, и выборки из таблицы) стоит указывать для индекса уникальность или лучше не указывать?

[quot White Owl]эээ... а зачем класть уникальное поле в индекс да еще и предпоследним?
Так надо :)
Оно необходимо для связки по id-шке с другой таблицей в этом запросе. Первые три поля индекса используются в условии where для выборки из этой таблицы, четвертое для связки с другой таблицей. Пятое поле вообще-то результат выборки в select. Я точно не помню текст запроса, но что-то типа:
select sum(summa) from table1, table2
where table1.tabn=@tabn and table1.code=@code and table1.period=@period and table1.id=table2.id and table2.code=@num_code

Спасибо за проявленный интерес. Я и ждал отклика от модераторов этой ветки.

MasterZiv Такой индекс из пяти полей не стоит создавать (вообще индекс из пяти полей- это
уже подозрительно). Тем более с уникальным полем.
Я не маньяк, но что поделать, грешу этим :)
MasterZiv Покрывающий индекс хорош только, если он уже и так есть в БД, специально под
запросы его создавать/подгонять не надо - только будете тормозить UPDATE-ы.
А если его нет, забыли создать? А он реально ускоряет выборку почти на порядок. На реальной базе сейчас задержка порядка 0.5 с. Да еще выборка выполняется в цикле по полю tabn (см. выше пример запроса).
MasterZiv Ничего особо страшного в отсутствии покрывающего индекса нет - всего лишь +1 IO,
если индекс реально работает, то это не страшно. Индекс нужен только для этого запроса.
То есть вы предлагаете сделать индекс не покрывающий запрос, а только из трех полей, используемых в условии where? Но таблица table1 порядка 10 млн. записей, выборка затрагивает 20-50 записей, которые находятся друг от друга "на расстоянии" порядка 30 тыс.записей. И планировщик выбирает стратегию выборки данных по второй таблице, а затем выбирает данные из первой таблицы по индексу - id-шке!
MasterZiv
Если все же будете, уникальное поле надо сделать первым и индекс пометить
как уникальный.
Это отпадает, см. выше.

Вы не убедили меня отказаться от индекса, покрывающего запрос.
В свете того, что "специально под запросы его создавать/подгонять не надо - только будете тормозить UPDATE-ы", хотелось бы уточнить. UPDATE в этой таблице будет крайне редким явлением, в основном SELECT,INSERT, DELETE. С точки зрения сервера, как ему легче (быстрее) обработать INSERT и UPDATE? Для уникального индекса ему нужно проделать дополнительно проверку на уникальность (или что там еще, я не знаю :) вот и хотел узнать у вас).
С другой стороны, указывая уникальность, я даю какую-то подсказку серверу при выборках данных из таблицы, или нет? Хотелось бы найти ответы на эти вопросы, чтобы знать, как поступать дальше.

Спасибо, за ответы.
Я немного ошибся с запросом:
select sum(summa) from table1, table2
where table1.tabn=@tabn and table1.code=@code and table1.period=>@period1 and table1.period<=@period2 and table1.id=table2.id and table2.code=@num_code
Под рукой описания таблиц, индесов сейчас нет. Могу дать только в понедельник.
MasterZivНа порядок относительно отсутствия этого индекса или относительно такого
же индекса, для этого же запроса, но без лишних колонок, делающих этот
индекс покрывающим ? Относительно второго не должно быть такой разницы. Попробую. Хотя, при наличии индекса по id-шке в первой таблице, и индекса по (code,id) во второй, планировщик выбирает стратегию выборки данных сначала по второй таблице, а затем выбирает данные из первой таблицы по индексу - id-шке (так как выборка из второй таблицы выбирает меньше записей, и вторая таблица всего из двух полей и содержит меньше записей)! MasterZiv Это все равно. Это только лишь + 1 IO на каждую запись, уже найденную в индексе. Это + 20-50 IO. Это немного. Тогда почему задержка 0.5 c. Неужели поиск по индексу id-шке в первой таблице может занимать такое время?
MasterZivА вот если вы на 10 млн будете создавать индекс из пол-таблицы, места он занимать будет значительно. И в итоге потом (в конечном итоге) все будет упираться в то, что
он не будет влезать в кэш, и изменения таблицы будут сильно тормозится. Особой задержки на отладочном сервере я не заметил. А вот индекс действительно большой, но прирост в скорости выполнения стоит этого. Тем более, что количество таких запросов, я прогнозирую, увеличится.
MasterZiv Я рассматривал с точки зрения селективности индекса. Если первое
поле уникальное, селективность высокая. Если нет - может быть хуже.
А maintanance индекса мало зависит от его уникальности. Тут длина
важнее. Т.е. если уникальность индекса зависит от 4-го поля в индексе, указывать уникальность смысла не имеет. На время выборок это не повлияет? А время обслуживания записей уникального индекса не отличается от неуникального?
MasterZiv> С другой стороны, указывая уникальность, я даю какую-то подсказку
> серверу при выборках данных из таблицы, или нет?
Конечно даете.
Если уникальность задает первое поле или первые поля, это понятно. А если первые три поля не дают уникальность, а только четвертое.

И еще вопрос. Как лучше располагать поля в таблице?
Стоит ли в начале перечислять поля, которые используются в индексах и наиболее частых запросах. Или стоит сначала перечислить поля, которые не могут быть нулом и Varchar?
Для меня это вопрос на засыпку :)

to MasterZiv
Попробовал с индексом не покрывающем запрос (только три поля в индексе). В зависимости от параметров, передаваемых в select оптимизатор выбирает три разных стратегии выборки.
При большом периоде выборки, он вообще игнорирует этот индекс!
При каком-то условии, он идет сначала по индексу во второй таблице, а потом по новому индексу
в первой.
При малом периоде выборки, он идет по новому индексу в первой, а затем по второй таблице.
В результате время выборки сократилось в среднем в 3 раза по отношению к времени, когда индекс отсутствовал. Прекрасно. Но все же хуже по сравнению с индексом, покрывающим запрос, в 1.5 раза.
Сейчас проверяю, насколько улучшит (или ухудшит :) ситуацию принудительное указание нового индекса?

>> И еще вопрос. Как лучше располагать поля в таблице?
>> Стоит ли в начале перечислять поля, которые используются в индексах и
>> наиболее частых запросах. Или стоит сначала перечислить поля, которые не
>> могут быть нулом и Varchar?

> Абсолютно все равно. Физически все равно порядок полей другой.

Если абсолютно другой, как тогда вычислить предполагаемую длину записи для подсказки оптимизатору? Как он выбирает физический порядок? Если я для поля null пропишу значение по умолчанию и укажу, что теперь оно не null, сервер поменяет физический порядок полей в записи?

И еще вопрос. Если какая-то разница, если я укажу для таблицы, где поле id1 - это нарастающий номер:
1) CONSTRAINT chrges PRIMARY KEY NONCLUSTERED ( id1 )
или
2) CONSTRAINT chrges UNIQUE NONCLUSTERED ( id1 )
или
3) unique nonclustered index chrges ( id1 )
Чем они отличаются, скажем так, физически для сервера?
И с точки зрения выполнения selecta c условием по этому полю, они равнозначны?

MasterZiv
Eugeny_M пишет:
> При большом периоде выборки, он вообще игнорирует этот индекс!

Ну и правильно делает ! CBO он и есть CBO.
СВО - это что такое? MasterZiv
Временем скорость работы запроса мерить не хорошо, надо мерить IO.
IO - соответствует времени. Принудительное указание индекса ничего не меняет.
MasterZiv
А где вы подсказываете оптимизатору длину записи ?
Определить- есть кажется спец. процедура. И формулы должны быть в P&T.
Где-то видел, уже не помню где :) Это я в порядке самообразования спрашиваю.
MasterZiv
Зачем вам, ей богу не пойму ...
Вы опять ерундой занимаетесь. Вам нужно прислать определения таблиц, индексов,
запрос и планы. Все равно то, что я вам отвечаю, может запросто даже быть
неправдой (я старался, но все же).

select sum(summa)
from zt_register_charges cr, zt_doc_basis
where zt_doc_basis.rgnom_doc =1484684
and cr.cr_record = zt_doc_basis.cr_record and cr.tn =76788
and cr.period > 200502 and cr.period <= 200705 and article_code=953

План для индекса не покрывающего запрос:
unique nonclustered index ztui_register_charges_tn ( tn, article_code, period)
QUERY PLAN FOR STATEMENT 1 (at line 1).

STEP 1
The type of query is SELECT.
Evaluate Ungrouped SUM OR AVERAGE AGGREGATE.


FROM TABLE
zt_doc_basis
Nested iteration.
Index : ztui_doc_basis
Forward scan.
Positioning by key.
Index contains all needed columns. Base table will not be read.
Keys are:
rgnom_doc ASC
Using I/O Size 2 Kbytes for index leaf pages.
With LRU Buffer Replacement Strategy for index leaf pages.


FROM TABLE
zt_register_charges
cr
Nested iteration.
Index : zpk_register_chrges
Forward scan.
Positioning by key.
Keys are:
cr_record ASC
Using I/O Size 2 Kbytes for index leaf pages.
With LRU Buffer Replacement Strategy for index leaf pages.
Using I/O Size 2 Kbytes for data pages.
With LRU Buffer Replacement Strategy for data pages.


STEP 2
The type of query is SELECT.


Server Message: Number 3630, Severity 10
Server 'gnome_ds', Line 1:
Total estimated I/O cost for statement 1 (at line 1): 180.


Parse and Compile Time 0.
SQL Server cpu time: 0 ms.
Table: zt_register_charges scan count 28, logical reads: (regular=140 apf=0 total=140), physical reads: (regular=63 apf=0 total=63), apf IOs used=0
Table: zt_doc_basis scan count 1, logical reads: (regular=4 apf=0 total=4), physical reads: (regular=2 apf=0 total=2), apf IOs used=0
Server Message: Number 3631, Severity 10
Server 'gnome_ds', Line 1:
Total actual I/O cost for this command: 1458.
Total writes for this command: 0

Execution Time 4.
SQL Server cpu time: 400 ms. SQL Server elapsed time: 360 ms.
(1 row affected)

План для индекса покрывающего запрос:
unique nonclustered index ztui_register_charges_tn ( tn, article_code, period, cr_record, summa)
QUERY PLAN FOR STATEMENT 1 (at line 1).

STEP 1
The type of query is SELECT.
Evaluate Ungrouped SUM OR AVERAGE AGGREGATE.


FROM TABLE
zt_register_charges
cr
Nested iteration.
Index : ztui_register_charges_tn
Forward scan.
Positioning by key.
Index contains all needed columns. Base table will not be read.
Keys are:
tn ASC
article_code ASC
period ASC
Using I/O Size 2 Kbytes for index leaf pages.
With LRU Buffer Replacement Strategy for index leaf pages.


FROM TABLE
zt_doc_basis
Nested iteration.
Using Clustered Index.
Index : zpk_doc_basis
Forward scan.
Positioning by key.
Index contains all needed columns. Base table will not be read.
Keys are:
cr_record ASC
rgnom_doc ASC
Using I/O Size 2 Kbytes for index leaf pages.
With LRU Buffer Replacement Strategy for index leaf pages.


STEP 2
The type of query is SELECT.


Server Message: Number 3630, Severity 10
Server 'briz_ds', Line 1:
Total estimated I/O cost for statement 1 (at line 1): 160.


Parse and Compile Time 0.
SQL Server cpu time: 0 ms.
Table: zt_register_charges scan count 1, logical reads: (regular=5 apf=0 total=5), physical reads: (regular=3 apf=0 total=3), apf IOs used=0
Table: zt_doc_basis scan count 27, logical reads: (regular=108 apf=0 total=108), physical reads: (regular=27 apf=0 total=27), apf IOs used=0
Server Message: Number 3631, Severity 10
Server 'briz_ds', Line 1:
Total actual I/O cost for this command: 766.
Total writes for this command: 0

Execution Time 2.
SQL Server cpu time: 200 ms. SQL Server elapsed time: 146 ms.
(1 row affected)

Или вот еще два плана для непокрывающего индекса.
Условия выборки были разные, чтобы продемонстрировать разные планы.
***********************************************************************
QUERY PLAN FOR STATEMENT 1 (at line 1).

STEP 1
The type of query is SELECT.
Evaluate Ungrouped SUM OR AVERAGE AGGREGATE.


FROM TABLE
zt_doc_basis
Nested iteration.
Index : ztui_doc_basis
Forward scan.
Positioning by key.
Index contains all needed columns. Base table will not be read.
Keys are:
rgnom_doc ASC
Using I/O Size 2 Kbytes for index leaf pages.
With LRU Buffer Replacement Strategy for index leaf pages.


FROM TABLE
zt_register_charges
cr
Nested iteration.
Index : zti_register_charges_tn
Forward scan.
Positioning by key.
Keys are:
tn ASC
article_code ASC
period ASC
Using I/O Size 16 Kbytes for index leaf pages.
With LRU Buffer Replacement Strategy for index leaf pages.
Using I/O Size 16 Kbytes for data pages.
With LRU Buffer Replacement Strategy for data pages.


STEP 2
The type of query is SELECT.


Server Message: Number 3630, Severity 10
Server 'gnome_ds', Line 1:
Total estimated I/O cost for statement 1 (at line 1): 148.


Parse and Compile Time 0.
SQL Server cpu time: 0 ms.
Table: zt_register_charges scan count 7, logical reads: (regular=61 apf=0 total=61), physical reads: (regular=6 apf=0 total=6), apf IOs used=0
Table: zt_doc_basis scan count 1, logical reads: (regular=4 apf=0 total=4), physical reads: (regular=0 apf=0 total=0), apf IOs used=0
Server Message: Number 3631, Severity 10
Server 'gnome_ds', Line 1:
Total actual I/O cost for this command: 238.
Total writes for this command: 0

Execution Time 0.
SQL Server cpu time: 0 ms. SQL Server elapsed time: 36 ms.
(1 row affected)
***********************************************************************
QUERY PLAN FOR STATEMENT 1 (at line 1).

STEP 1
The type of query is SELECT.
Evaluate Ungrouped SUM OR AVERAGE AGGREGATE.


FROM TABLE
zt_register_charges
cr
Nested iteration.
Index : zti_register_charges_tn
Forward scan.
Positioning by key.
Keys are:
tn ASC
article_code ASC
period ASC
Using I/O Size 2 Kbytes for index leaf pages.
With LRU Buffer Replacement Strategy for index leaf pages.
Using I/O Size 2 Kbytes for data pages.
With LRU Buffer Replacement Strategy for data pages.


FROM TABLE
zt_doc_basis
Nested iteration.
Using Clustered Index.
Index : zpk_doc_basis
Forward scan.
Positioning by key.
Index contains all needed columns. Base table will not be read.
Keys are:
cr_record ASC
rgnom_doc ASC
Using I/O Size 2 Kbytes for index leaf pages.
With LRU Buffer Replacement Strategy for index leaf pages.


STEP 2
The type of query is SELECT.


Server Message: Number 3630, Severity 10
Server 'gnome_ds', Line 1:
Total estimated I/O cost for statement 1 (at line 1): 180.


Parse and Compile Time 0.
SQL Server cpu time: 0 ms.
Table: zt_register_charges scan count 1, logical reads: (regular=7 apf=0 total=7), physical reads: (regular=4 apf=0 total=4), apf IOs used=0
Table: zt_doc_basis scan count 3, logical reads: (regular=12 apf=0 total=12), physical reads: (regular=3 apf=0 total=3), apf IOs used=0
Server Message: Number 3631, Severity 10
Server 'gnome_ds', Line 1:
Total actual I/O cost for this command: 164.
Total writes for this command: 0

Execution Time 0.
SQL Server cpu time: 0 ms. SQL Server elapsed time: 33 ms.
(1 row affected)

В общем я решил остановится на индексе покрывающем запрос, несмотря на то, что размер индекса в этом случае на 100 Мб больше :( Т.к. со временем кол-во записей выбираемых за период будет увеличиваться, и кол-во случаев игнорирования индекса будет расти.
Спасибо за ваши ответы. Я как-то не подумал, что не покрывающий индекс так ускорит выборку. Если бы выборка была единичной, я бы остановился на индексе из трех полей, но так как технологически в данном случае выборка идет в цикле, то лучше покрывающий индекс. Имхо.

При большом периоде выборки он вообще игнорируется этот индекс! И привел пример.
Я же уже писал. При малых периодах он используется и время почти идентичное с покрывающим.
Кластерным сделать его не могу. Поэтому сделал покрывающий индекс.