Статья
09/28/2010

Оптимизация таблицы NewOrg

Таблица HumanResources.NewOrd, созданная в задании Заполнение таблицы существующими иерархическими данными, содержит все сведения о сотрудниках и представляет иерархическую структуру с помощью типа данных hierarchyid. Эта задача добавляет новые индексы для поддержки поиска по столбцу hierarchyid.

Кластеризованный индекс

Столбец hierarchyid (OrgNode) является первичным ключом таблицы NewOrg. После создания таблицы в ней содержался кластеризованный индекс PK_NewOrg_OrgNode, обеспечивающий уникальность значений в столбце OrgNode. Кластеризованный индекс также поддерживает поиск по таблице по глубине.

Некластеризованный индекс

Во время этого шага создаются два некластеризованных индекса для поддержки работы обычных видов поиска.

Индексация таблицы NewOrg для обеспечения эффективного поиска

Чтобы обеспечить функционирование запросов, работающих на одном и том же уровне иерархии, следует использовать метод GetLevel для создания вычисляемого столбца, содержащего уровень иерархии. Затем следует создать составной индекс, основанный на уровне и Hierarchyid. Запустите следующий код для создания вычисляемого столбца и индекса преимущественно в ширину:
```
ALTER TABLE HumanResources.NewOrg 
ADD H_level AS OrgNode.GetLevel() ;
CREATE UNIQUE INDEX EmpBFInd 
   ON HumanResources.NewOrg(H_level, OrgNode) ;
GO
```
Создайте уникальный индекс для столбца EmployeeID. Это стандартный единичный уточняющий запрос относительно одного сотрудника по номеру EmployeeID. Запустите следующий код, чтобы создать индекс для столбца EmployeeID:
```
CREATE UNIQUE INDEX EmpIDs_unq ON HumanResources.NewOrg(EmployeeID) ;
GO
```

Запустите следующий код, чтобы получить данные из таблицы, упорядоченные относительно каждого из этих трех индексов:

SELECT OrgNode.ToString() AS LogicalNode,
OrgNode, H_Level, EmployeeID, LoginID, Title
FROM HumanResources.NewOrg 
ORDER BY OrgNode;

SELECT OrgNode.ToString() AS LogicalNode,
OrgNode, H_Level, EmployeeID, LoginID, Title
FROM HumanResources.NewOrg 
ORDER BY H_Level, OrgNode;

SELECT OrgNode.ToString() AS LogicalNode,
OrgNode, H_Level, EmployeeID, LoginID, Title
FROM HumanResources.NewOrg 
ORDER BY EmployeeID;
GO

Сравните результирующие наборы, чтобы понять порядок хранения для каждого типа индекса. Далее приводятся только первые четыре строки из каждого выходного набора.

Ниже приводится результирующий набор.

Индекс преимущественно в глубину: записи сотрудников хранятся рядом с записью их менеджера.

LogicalNode OrgNode    H_Level EmployeeID LoginID                  Title
/           0x         0       109        adventure-works\ken0     Chief Executive Officer
/1/         0x58       1        12        adventure-works\terri0   Vice President of Engineering
/1/1/       0x5AC0     2         3        adventure-works\roberto0 Engineering Manager
/1/1/1/     0x5AD6     3         4        adventure-works\rob0     Senior Tool Designer

Индекс преимущественно в ширину: уровни управления хранятся вместе.

LogicalNode OrgNode    H_Level EmployeeID LoginID                  Title
/           0x         0       109        adventure-works\ken0     Chief Executive Officer
/1/         0x58       1        12        adventure-works\terri0   Vice President of Engineering
/2/         0x68       1         6        adventure-works\david0   Marketing Manager
/3/         0x78       1        42        adventure-works\jean0    Information Services Manager
...

Индекс с приоритетом значений в столбце EmployeeID: строки хранятся в соответствии с последовательностью значений EmployeeID.

LogicalNode OrgNode    H_Level EmployeeID LoginID                  Title
/6/4/13/12/ 0x961B7640 4       1          adventure-works\guy1     Production Technician - WC60
/2/5/       0x6C60     2       2          adventure-works\kevin0   Marketing Assistant
/1/1/       0x5AC0     2       3          adventure-works\roberto0 Engineering Manager
/1/1/1/     0x5AD6     3       4          adventure-works\rob0     Senior Tool Designer

Примечание
Диаграммы, на которых показывается разница между индексом преимущественно в глубину и индексом преимущественно в ширину, см. в разделе Использование типов данных hierarchyid (компонент Database Engine).

Удаление ненужных столбцов

Столбец ManagerID представляет связь сотрудник-менеджер, которая на данный момент представлена столбцом OrgNode. Если другим приложениям столбец ManagerID не нужен, то, возможно, стоит его удалить с помощью следующей инструкции:
```
ALTER TABLE HumanResources.NewOrg DROP COLUMN ManagerID ;
GO
```
Столбец EmployeeID также является избыточным. Столбец OrgNode уникально идентифицирует каждого сотрудника. Если другим приложениям столбец EmployeeID не нужен, то, возможно, стоит его удалить с помощью следующей инструкции:
```
DROP INDEX EmpIDs_unq ON HumanResources.NewOrg ;
ALTER TABLE HumanResources.NewOrg DROP COLUMN EmployeeID ;
GO
```

Замена исходной таблицы на новую

Если в исходной таблице содержались дополнительные индексы или ограничения, добавьте их в таблицу NewOrg.
Замените старую таблицу EmployeeDemo новой. Запустите следующий код, чтобы удалить старую таблицу и присвоить новой таблице имя старой:
```
DROP TABLE HumanResources.EmployeeDemo ;
GO
sp_rename 'HumanResources.NewOrg', EmployeeDemo ;
GO
```
Запустите следующий код, чтобы изучить окончательную таблицу:
```
SELECT * FROM HumanResources.EmployeeDemo ;
```

Следующая задача занятия

Сводка. Преобразование таблицы в иерархическую структуру

Поделиться через