171
6.1 Principes de l’indexation
6.1.4 Optimisation de la taille de l’index
Les index sont stockés dans des pages, tout comme les données. L’arbre B-Tree
possède une profondeur et une étendue. Le principe de l’index est de limiter
l’étendue par la profondeur : un niveau d’index trop large devient inefficace, il faut
alors placer un niveau supplémentaire pour en diminuer la surface. Corollairement,
plus la clé de l’index est petite, plus le nombre de clé par page est important, moins
l’index doit être profond, et plus son parcours est optimisé.
Prenons un exemple pratique. Voici le code, nous le détaillerons ensuite :
CREATE DATABASE testdb
GO
ALTER DATABASE testdb SET RECOVERY SIMPLE
GO
USE testdb
CREATE TABLE dbo.testIndex (
codeLong char(900) NOT NULL PRIMARY KEY NONCLUSTERED,
codeCourt smallint NOT NULL,
texte char(7150) NOT NULL DEFAULT ('O')
)
GO
DECLARE @v int
SET @v = 1
WHILE (@v <= 8000) BEGIN
INSERT INTO dbo.testIndex (codeLong, codeCourt)
SELECT CAST(@v as char(10)), @v
SET @v = @v + 1
END
GO
CREATE UNIQUE INDEX uq$testIndex$codeCourt ON dbo.testIndex (codeCourt)
SELECT name, index_id, type_desc
FROM sys.indexes
WHERE object_id = OBJECT_ID('dbo.testIndex')
/* -- résultat
name
index_id
type_desc
-------------------------- ----------- -----------NULL
0
HEAP
PK__testIndex__0425A276 2
NONCLUSTERED
uq$testIndex$codeCourt
3
NONCLUSTERED
*/
SELECT index_depth, index_level, page_count, record_count
FROM sys.dm_db_index_physical_stats(
DB_ID(), OBJECT_ID('dbo.testIndex'), 2, NULL, 'DETAILED')