Possiamo facilmente immaginare l’inefficienza introdotta dalla doppia indicizzazione (ugualmente ordinata) di un attributo: SQL Server dovrà mantenere aggiornati due indici, organizzati in strutture B-Tree letteralmente identiche, senza trarre alcun beneficio da una delle due.
use [AdventureWorks]
go
if object_id('usp_drop_double_more_index') is not null
drop procedure dbo.usp_drop_double_more_index
go
create procedure dbo.usp_drop_double_more_index
(
@mode tinyint,
@object_name varchar(4000),
@table_type varchar(10),
@index_name_prefix_order varchar(8),
@debug tinyint
)
as
begin
/*
Descrizione:
Individua ed elimina i casi di doppia (tripla, ecc..)
indicizzazione sui medesimi attributi
Parametri:
- @mode
Modalità di utilizzo
Può assumere i valori: 1= Exec Mode, 0= Print Mode.
In exec-mode esegue la cancellazione degli indici
doppi, in print-mode stampa i comandi T-SQL
- @object_name
Nome di una tabella o lista di nomi di tabelle
nel formato <schema_name>.<table_name> separati da ","
per le quali di desidera verificare
ed eventualmente cancellare gli indici doppi
(esempio <schema_name_1>.<table_name_1>, <schema_name_2>.<table_name_2>, ..., <schema_name_N>.<table_name_N>
- @table_type
Tipo di tabella
Può assumere: "VIEW" o "BASE TABLE"
- @index_name_prefix_order
Determina, attraverso ordinamento, quale indice
mantenere tra quelli doppi. Qualora si desideri
mantenere un particolare indice o una particolare
famiglia di indici il cui nome inizia ad esempio
con "IX_", sarà sufficiente specificare il
prefisso desiderato in questo parametro
- @debug
Modalità di debug
Può assumere i valori: 1= Debug ON, 0= Debug OFF
*/
declare
@msg varchar(256),
@cmd varchar(max),
@index_name_prefix_len int
-- Controllo parametri di input
-- @mode
if (@mode not in (0, 1))
begin
set @msg = object_name(@@procid) +
' : Modalità di esecuzione non supportata, ' +
'@mode può assumere i seguenti valori: ' +
'1= Modalità Exec, 0= Modalità Print. ' +
'Il valore di @mode passato è: ' + ltrim(str(@mode))
raiserror(@msg, 16, 1)
return
end
-- @debug
if (@debug not in (0, 1))
begin
set @msg = object_name(@@procid) +
' : Modalità di debug non supportata, ' +
'@debug può assumere i seguenti valori: ' +
'1= Debug ON, 0= Debug OFF. ' +
'Il valore di @debug passato è: ' + ltrim(str(@debug))
raiserror(@msg, 16, 1)
return
end
set nocount on
-- Setup tabelle
-- tmphelpindex
if object_id('tmphelpindex') is not null
drop table tmphelpindex
create table tmphelpindex
(id int identity(1, 1) not null,
index_name sysname,
index_description varchar(210),
index_keys varchar(4000))
-- tmpindexes
if object_id('tmpindexes') is not null
drop table tmpindexes
create table tmpindexes
(id int identity(1, 1) not null,
table_schema varchar(128),
table_name sysname,
index_name sysname,
index_description varchar(210),
index_keys varchar(4000))
-- tmpindexes1
if object_id('tmpindexes1') is not null
drop table tmpindexes1
create table tmpindexes1
(id int identity(1, 1) not null,
table_schema varchar(128),
table_name sysname,
index_name sysname,
index_description varchar(210),
index_keys varchar(4000))
set @cmd = ''
set @index_name_prefix_len = len(ltrim(rtrim(@index_name_prefix_order)))
if (@debug = 1)
begin
print ('@index_name_prefix_order= "' + ltrim(rtrim(@index_name_prefix_order)) + '"')
print ('@index_name_prefix_len= ' + ltrim(str(@index_name_prefix_len)))
end
select
@cmd = @cmd + 'insert into tmphelpindex ' +
'exec sp_helpindex ''' + i.table_schema + '.' + i.table_name + '''; ' +
'delete ' +
't ' +
'from ' +
'tmphelpindex t ' +
'join ' +
'(select ' +
'min(t1.id) as id ' +
'from ' +
'tmphelpindex t1 ' +
'where ' +
'exists (select ' +
'id ' +
'from ' +
'tmphelpindex t2 ' +
'where ' +
'(t1.index_keys=t2.index_keys) and ' +
'(t2.index_description like ''clustered%'')) and ' +
'(t1.index_description like ''nonclustered%'')) tx on tx.id=t.id; ' +
'insert into tmpindexes ' +
'(table_schema, ' +
'table_name, ' +
'index_name, ' +
'index_description, ' +
'index_keys) ' +
'select ' +
'''' + i.table_schema + ''',' +
'''' + i.table_name + ''', ' +
'index_name, ' +
'index_description, ' +
'index_keys ' +
'from ' +
'tmphelpindex; ' +
'delete from tmphelpindex; '
from
information_schema.tables as i
where
((charindex((',' + i.table_schema + '.' + i.table_name + ','),
(',' + replace(@object_name, ' ', '') + ',')) > 0) OR (@object_name='')) and
(i.table_type=@table_type)
order by
i.table_schema,
i.table_name
if (@debug = 1)
print(@cmd)
exec(@cmd)
if (ltrim(rtrim(@index_name_prefix_order)) <> '')
begin
insert into
tmpindexes1
(index_name,
index_description,
index_keys,
table_schema,
table_name)
select
index_name,
index_description,
index_keys,
table_schema,
table_name
from
tmpindexes
where
(left(index_name, @index_name_prefix_len) <> @index_name_prefix_order)
insert into
tmpindexes1
(index_name,
index_description,
index_keys,
table_schema,
table_name)
select
index_name,
index_description,
index_keys,
table_schema,
table_name
from
tmpindexes
where
(left(index_name, @index_name_prefix_len) = @index_name_prefix_order)
end
else
insert into
tmpindexes1
(index_name,
index_description,
index_keys,
table_schema,
table_name)
select
index_name,
index_description,
index_keys,
table_schema,
table_name
from
tmpindexes
-- Select double index on the same columns
set @cmd = ''
select
@cmd = @cmd +
'drop index [' + t.table_schema + '].[' + t.table_name + '].[' + t.index_name + ']; ' + char(13) + char(10)
from
tmpindexes1 t
join
(select
max(id) as max_id,
count(index_keys) as index_count,
table_schema,
table_name,
index_keys,
index_description
from
tmpindexes1
group by
table_schema,
table_name,
index_keys,
index_description
having
(count(index_keys) > 1)
) t1 on (t.table_schema = t1.table_schema) and
(t.table_name = t1.table_name) and
(t.index_keys = t1.index_keys) and
(t.index_description = t1.index_description) and
(t.id < t1.max_id)
order by
t.index_name
if (@cmd = '')
begin
print ('')
print ('+-+-+-+-+-+-+-+-+-+-+-+-+')
print (object_name(@@procid) + ': No double or more index found!')
end
else begin
print ('')
print ('+-+-+-+-+-+-+-+-+-+-+-+-+')
if (@mode = 0)
print (object_name(@@procid) + ': Scrip drop index...')
else
print (object_name(@@procid) + ': Start drop index...')
print ('')
if (@mode = 0)
print (@cmd)
else begin
exec (@cmd)
if (@debug = 1)
print (@cmd)
print ('')
if (@@error = 0)
print ('Commands: ' +
char(13) + char(10) +
@cmd + ' completed successfully.')
else
print ('Commands: ' +
char(13) + char(10) +
@cmd + ' ended with errors.')
print ('')
end
end
print ('')
print (object_name(@@procid) + ': ...Execution end.')
print ('+-+-+-+-+-+-+-+-+-+-+-+-+')
-- Pulizia tabelle
-- tmphelpindex
if object_id('tmphelpindex') is not null
drop table tmphelpindex
-- tmpindexes
if object_id('tmpindexes') is not null
drop table tmpindexes
-- tmpindexes1
if object_id('tmpindexes1') is not null
drop table tmpindexes1
set nocount off
end
Ipotizziamo la situazione illustrata nella figura seguente per la tabella Person.Contact del database di esempio AdventureWorks.
L’attributo FirstName è caratterizzato da una tripla (identica) indicizzazione, riproducibile con i seguenti comandi CREATE INDEX:
Ci aspettiamo che rilevi la tripla indicizzazione della colonna FirstName nella tabella Person.Contact.
L’output ottenuto propone la cancellazione di due, dei tre indici definiti per l’attributo FieldName della tabella Person.Contact:
Il comando precedente invocato in modalità Exec (@mode = 1) eseguirà la cancellazione fisica dei due indici: