use indeksi

create nonclustered index idx_first
on person(firstname)

drop index person.idx_first

alter table person
drop constraint PK_BusinessEntityID

alter table person
add constraint PK_BusinessEntityID primary key(BusinessEntityID)

-------ORDER BY---------------------------------

select * from person
order by firstname
-- bez indeksa 1.49
-- table scan - prolazak kroz tabelu 12% - 0.1726
-- sort - sortiranje podataka po firstname 88% - 1.32
-- samo sa klasterovanim PK 1.49
-- clustered index scan 12%-0.1726, citanje cele tabele
-- sort 88% - 1.32, sortiranje po koloni firstname
-- sa neklasterovanim idx_first isto
-- zapisi u indeksu jesu sortirani po koloni firstname,
-- ali za svaki zapis bi moralo da se pristupa tabeli da bi se ucitale ostale kolone
-- neke memorijske stranice bi se ucitavale po vise puta,
-- i zato je efikasnije uopste ne iskoristiti neklasterovani indeks

select firstname from person
order by firstname
-- bez neklasterovanog 1.49
-- sa neklasterovanim 0.0719 (21 puta brze)
-- index scan 100%, uopste ne pristupa tabeli
-- sve sto je potrebno upitu se nalazi u indeksu, a tamo su zapisi vec sortirani po imenu

select businessEntityID, firstname --, lastname
from person
order by firstname
-- bez neklasterovanog 1.49
-- sa neklasterovanim 0.0719
-- kljuc klasterovanog se nalazi u indeksu, ne pristupa se tabeli
-- ovde jedan indeks bez drugog nema nekog smisla
-- sa lastname 1.49, ova kolona se ne nalazi u indeksu, pa mora da se pristupa tabeli,
-- i onda je efikasnije uopste ne koristiti neklasterovani indeks

-- uklonimo klasterovani PK
select firstname, businessEntityID
from person
order by firstname
-- bez klasterovanog PK, sa neklasterovanim idx_first 1.49
-- ID vise nije PK, pa ne ulazi u sastav neklasterovanih indeksa,
-- vec mora da se ucita iz tabele, pa se neklasterovani uopste i ne koristi

select BusinessEntityID 
from person
order by BusinessEntityID
-- bez klasterovanog 1.49
-- sa klasterovanim PK nad ID 0.1726
-- tabela je vec sortirana po primarnom kljucu, samo je treba ucitati

select * from person
order by BusinessEntityID
-- bez klasterovanog 1.49
-- sa klasterovanim 0.1726
-- klasterovani indeks je zapravo cela tabela, samo fizicki sortirana,
-- pa su i kolone van kljuca na neki nacin deo indeksa

--zakljucak: klasterovani bolje sortira za sve kolone
--neklasterovani bolje sortira za kolone svog i klasterovanog kljuca pretrage

select top 5 * from Person
order by FirstName
-- samo sa klasterovanim PK 1.49
-- sa neklasterovanim idx_first 0.022
-- index scan 14%, ucitavanje prvih 5 zapisa iz indeksa
-- key lookup 86%, ucitavanje ostalih kolona iz tabele

-- nece uvek ncl da donese poboljsanje
select top 5000 * from Person
order by FirstName
-- samo sa klasterovanim PK 1.49
-- sa neklasterovanim idx_first 1.457
-- index scan 1%, ucitavanje prvih 5000 zapisa iz indeksa
-- key lookup 97%, ucitavanje ostalih kolona iz tabele za tih 5000 vrsta

select top 6000 * from Person
order by FirstName
-- samo sa klasterovanim PK 1.49
-- sa neklasterovanim idx_first isto

--5000/20000 koristi ncl indeks
--6000/20000 ne koristi ncl indeks
-----------------------------------------------

create nonclustered index idx_first
on person(firstname)

drop index person.idx_first

alter table person
drop constraint PK_id

alter table person
add constraint PK_id primary key(BusinessEntityID)

----------KOMPOZITNI INDEKSI---------------------

-- Kompozitni indeks je indeks nad vise kolona, indeks koji ima slozeni kljuc pretrage

create nonclustered index idx_flm
on person(firstname, lastname, middlename)
-- zapisi su u ovom indeksu sortirani po koloni firstname
-- oni sa istim imenom su sortirani po koloni lastname
-- oni sa istim imenom i prezimenom su sortirani po koloni middlename

drop index idx_flm on person

-- vratimo PK
select BusinessEntityID, FirstName
from Person
order by FirstName
-- sa PK 1.49
-- sa neklasterovanim nad firstname (idx_first) 0.072
-- sve select-ovane kolone se nalaze u indeksu
-- treba samo ucitati indeks (index scan)
-- sa kompozitnim nad firstname, lastname, middlename (idx_flm) 0.108
-- i ponovo treba samo ucitati indeks (index scan)
-- ali je indeks sada veci, ima vise kolona, zauzima vise memorije
-- vise vremena je potrebno za pristup ovom indeksu i ucitavanje podataka iz njega

select BusinessEntityID, FirstName, LastName
from Person
order by FirstName
-- sa neklasterovanim idx_first 1.49
-- kolona lastname se ne nalazi u indeksu, pa se indeks uopste ne koristi
-- sa kompozitnim idx_flm 0.108
-- u kompozitnom se nalaze sve select-ovane kolone, nema pristupa tabeli
-- isto vreme izvrsavanja kao prethodni upit, broj select-ovanih kolona ne pravi razliku

select BusinessEntityID, FirstName--, LastName
from Person
order by LastName
-- sa neklasterovanim idx_first 1.49
-- clustered index scan (ucitavanje cele tabele) 12% - 0.1726, sortiranje po lastname 88% - 1.32
-- sa kompozitnim idx_flm 1.429
-- sve potrebne kolone se nalaze u indeksu pa nema pristupa tabeli (index scan 8% - 0.108)
-- upit treba da vrati sortirano po lastname, pa se vrsi sortiranje indeksa (sort 92% - 1.32)
-- sa LastName u select-u sve isto

select BusinessEntityID, FirstName--, LastName
from Person
order by firstname, LastName
-- bez kompozitnog idx_flm 1.49
-- sa kompozitnim 0.108
-- samo citanje iz indeksa (index scan 100%)
-- u indeksu je vec sve sortirano onako kako je trazeno u upitu

select firstname, lastname, count(*)
from Person
group by firstname, LastName
-- bez kompozitnog indeksa 1.51
-- clustered index scan - citanje cele tabele 11% - 0.1726
-- sortiranje po firstname i lastname 87% - 1.32
-- stream aggregate - prolazak kroz sortirane zapise i prebrajanje 1%
-- sa kompozitnim idx_flm 0.129
-- index scan 83% - 0.108, ucitava se samo indeks, nema pristupa tabeli
-- indeks je vec sortiran po imenu i prezimenu
-- stream aggregate - prolazak kroz sortirane zapise i prebrajanje 17%

select lastname, count(*)
from Person
group by LastName
-- bez indeksa 0.38
-- clustered index scan 45% - 0.1726
-- hash match 55% - 0.208
-- sa idx_first isto
-- sa kompozitnim idx_flm 0.316
-- index scan umesto citanja cele tabele 34% - 0.108
-- hash match 66% - 0.208, isto kao i bez kompozitnog
-- malo ubrzanje je postignuto jer se ucitava samo kompozitni indeks

---------------------------------------------------------------------

alter table person
add constraint PK_BusinessEntityID primary key(BusinessEntityID)

alter table person
drop constraint PK_BusinessEntityID

create nonclustered index idx_first
on person(firstname)

drop index person.idx_first

create nonclustered index idx_flm
on person(firstname, lastname, middlename)

drop index idx_flm on person

----------POMOCNE PROCEDURE------------------------------------------

exec sp_helpindex person
-- daje listu svih indeksa nad tabelom uz kratak opis i prikaz kljuca

exec sp_spaceused person
-- prikazuje koliko memorije zauzima prosledjena tabela
-- koliko zauzimaju sami podaci, a koliko indeks

-- poredjene zauzeca memorije pri upotrebi razlicitih kombinacija indeksa
exec sp_spaceused person
-- bez ikakvih indeksa:				data 1600KB
-- sa klasterovanim PK:				data 1600KB, index 16KB
-- sa cl PK i ncl idx_first:		data 1600KB, index 544KB
-- sa cl PK i kompozitnim idx_flm:  data 1600KB, index 936KB
-- sa ncl idx_first (bez cl PK):	data 1600KB, index 616KB!
-- vise memorije zauzima samo neklasterovani nego kad ide u kombinaciji sa klasterovanim
-- razlog: neklasterovani ima jednu kolonu za PK, odnosno za klasterovani indeks, tip int, zauzima 4KB po vrsti
-- kada nema klasterovanog PK, neklasterovani ima jednu kolonu za RID (Row ID), zauzima 8KB po vrsti

use indeksi

------------INCLUDE------------------------------

-- Naredbom INCLUDE se u indeks dodaju kolone iz tabele koje nisu deo kljuca pretrage.
-- Ove kolone su pridruzene indeksu kao sto je pridruzena npr. kolona klasterovanog kljuca.
-- Od vrednosti pridruzenih kolona ne zavisi raspored zapisa u indeksu.

-- Kreiranje indeksa nad kolonom FirstName sa pridruzenom kolonom LastName:

create nonclustered index idx_first_incl
on person(firstname) include (lastname)

-- Neklasterovanom indeksu se moze pridruziti i vise kolona

create nonclustered index idx_incl_sve
on person(firstname) include (lastname, middlename, suffix, ModifiedDate, PersonType, Title)

-- Nema potrebe pridruzivati i primarni kljuc jer je on vec pridruzen automatski.

-- Kakva je razlika izmedju indeksa sa include idx_first_incl i sledeceg kompozitnog indeksa:
create nonclustered index idx_first_last
on person(firstname, lastname)

-- I jedan i drugi indeks sadrze kolone firstname i lastname. 
-- Razlika je u tome sto su kod kompozitnih svi zapisi sortirani po celom kljucu pretrage,
-- po FirstName, pa po LastName.
-- Kod idx_first_incl su zapisi sortirani samo po FirstName, po LastName nisu,
-- LastName kolona je samo pridruzena.

-- Zasto bismo onda uopste koristili indekse sa include?

-- 1. razlog - SQL server dopusta duzinu kljuca od 16 kolona ili 900 bajtova. 
-- Medjutim, sa include mozemo da dodamo proizvoljan broj non-key kolona u indeks.  

-- 2. razlog - Include-ovane kolone se cuvaju samo u listovima, ne koriste se za pretragu,
-- nisu sortirane, pa ih nema u cvorovima stabla. Samim tim, zauzimaju manje memorije
-- nego dodatna kolona u kljucu pretrage, koja se nalazi i u listovima i u stablu.

alter table person
add constraint PK_id
primary key (BusinessEntityID)

create nonclustered index idx_first
on person(firstname)
drop index person.idx_first

create nonclustered index idx_first_last
on person(firstname, lastname)
drop index person.idx_first_last

create nonclustered index idx_first_incl
on person(firstname) include (lastname)
drop index person.idx_first_incl

exec sp_spaceused person

select firstname, lastname
from person
where firstname like 'Martin'
--samp PK 0.17, data 1600KB, index 16KB
--sa idx_first 0.063, data 1600KB, index 544KB
--sa idx_first_last 0.0033, data 1600KB, index 808KB
--sa idx_first_incl 0.0033, data 1600KB, index 808KB

-- Kompozitni indeks i indeks sa include su dali isto vreme izvrsavanja.
-- Razlika u memoriji je trenutno zanemarljiva, nije cak ni na nivou KB.
-- Ova razlika ce doci do izrazaja kod vecih indeksa.

create nonclustered index idx_sve
on person(firstname, lastname, middlename, suffix, ModifiedDate, PersonType, Title)
drop index person.idx_sve

create nonclustered index idx_sve_incl
on person(firstname) include (lastname, middlename, suffix, ModifiedDate, PersonType, Title)
drop index person.idx_sve_incl

exec sp_spaceused person

select * from person
where firstname like 'Martin'
-- sa idx_sve 0.0033, data 1600KB, index 1600KB
-- sa idx_sve_incl 0.0033, data 1600KB, index 1576KB

-- Vreme izvrsavanja je i dalje isto, 
-- ali je sada razlika u memoriji dosla do izrazaja

-- ZAKLJUCAK: Kolone koje se koriste za pretragu, grupisanje ili sortiranje
-- treba staviti u kompozitni indeks, 
-- a one koje se nalaze samo u SELECT delu treba pridruziti indeksu
-- jer nam za pretragu nisu potrebne, pa ne moraju biti deo kljuca pretrage,
-- a ovako mogu da ustede memoriju.

---------KLASTEROVANI INDEKSI-------------------

-- Klasterovani indeksi ne moraju da budu samo primarni kljucevi.
-- Npr. moguce je napraviti klasterovani indeks nad kolonom LastName.
-- Ali prvo treba ukloniti postojeci klasterovani indeks.
-- Jedna tabela moze imati samo jedan kl. indeks koji fizicki sortira podatke.

alter table person
drop constraint PK_id

-- Sada moze da se napravi novi klasterovani indeks

create clustered index cl_last
on person(lastname)

exec sp_spaceused person
--sa PK_id, data 1600KB, index 16KB
--sa cl_last, data 1736KB, index 16KB

-- Sama tabela zauzima vise memorije sa cl_last nego sa primarnim kljucem.
-- Razlog - Kada je klasterovani kljuc PK, i uz to jos auto-increment,
-- memorijske stranice tabele se popunjavaju bez ostavljanja slobodnog prostora,
-- jer se racuna da nece biti INSERT operacija koje ubacuju vrste u sredinu tabele.
-- Kada klasterovani kljuc nije PK, a nije ni UNIQUE, postoji realna mogucnost
-- da se cesto ubacuju nove vrste u svaki deo tabele. Zato se u svakoj memorijskoj stranici
-- ostavlja dodatni prazan prostor za buduce nove vrste. Samim tim, cela tabela zauzima
-- vise memorijskih stranica.

select *
from person
where lastname like 'Adams'
-- sa cl_last 0.0033
-- nema potrebe za include-ovanjem svih select-ovanih kolona,
-- jer se sve vec nalaze u klasterovanom indeksu,
-- jer je klasterovani indeks zapravo sama tabela, sortirana po klasterovanom kljucu.
-- Poprilicna usteda memorije u odnosu na resenje sa neklasterovanim indeksom.  

-- Koriscenje INCLUDE naredbe sa klasterovanim indeksom nema smisla, 
-- sve kolone van kljuca pretrage su vec pridruzene indeksu.
-- Sa druge strane, moguce je napraviti klasterovani kompozitni indeks:   

drop index person.cl_last

create clustered index cl_last_first
on person(lastname, firstname)
-- data 1664KB, index 32KB
-- data je manji nego kod cl_last jer ima manje ponavljanja vrednosti kljuca,
-- pa je ostavljeno manje slobodnog prostora u memorijskim stranicama
-- indeks zauzima duplo vise nego kod cl_last zbog duplo veceg kljuca.

select *
from person
order by lastname, firstname
-- bez indeksa 1.49
-- sa cl_last_first 0.178

select lastname, firstname, count(*)
from person
group by lastname, firstname
-- bez indeksa 1.5
-- sa cl_last_first 0.2

-- Ako zelimo da klasterovani indeks ostane nad kolonom LastName,
-- a da ipak napravimo primarni kljuc nad ID kolonom,
-- moguce je napraviti neklasterovani primarni kljuc:

alter table person
add constraint PK_id
primary key nonclustered (BusinessEntityID)

-------------------------------------------------------------------------

alter table person drop constraint PK_id

drop index person.cl_last_first

-- Indeksi mogu i da uspore izvrsavanje nekih upita.
-- Takav ja slucaj sa INSERT, UPDATE i DELETE naredbama. 

-------------INSERT-------------------------------

insert into person(BusinessEntityID, PersonType, 
FirstName, LastName, ModifiedDate)
values (30000,'EM','Pera','Peric','2015-12-3')

-- bez indeksa 0.01
-- table insert 100% - 0.01, ubacivanje nove vrste u tabelu
-- assert 0% - provera da li nova vrsta narusava ogranicenja nad tabelom
-- sa PK_id 0.01
-- clustered index insert - isto sto i table insert, 100% - 0.01
-- assert 0%
-- klasterovani indeks ne usporava INSERT naredbu 

-- sa 1 ncl (idx_last), 0.02
-- clustered index insert 0.02, duplo vise nego bez neklasterovanog
-- razlog: nova vrsta, osim u samu tabelu, sada mora da se ubaci i u ncl indeks

-- sa 2 ncl (idx_last i idx_first), 0.03
-- jos jedan ncl indeks, i u njega mora da se ubaci nova vrsta

alter table person
add constraint PK_id
primary key (BusinessEntityID)

create nonclustered index idx_last
on person(lastname)

create nonclustered index idx_first
on person(firstname)

drop index person.idx_last
drop index person.idx_first

-- Neklasterovani indeksi usporavaju naredbe modifikacije,
-- jer svaka promena tabele mora da se odrazi i u indeksima.

-------------UPDATE-----------------------------------------

update person
set lastname = 'Jovanovic'
where firstname = 'Pera'
-- samo klasterovani nad id 0.1862
-- clustered index scan 93%, 0.1726
-- compute scalar 0.003, racunanje izraza pod set (pretpostavlja da moze postojati neki izraz)      
-- clustered index update 0.01, promena vrednosti u jednoj vrsti tabele                                   

create nonclustered index idx_last
on person(lastname)
-- sa idx_last 0.2
-- clustered index update 10% - 0.02
-- update traje duplo duze jer treba azurirati vrstu i u tabeli i u neklasterovanom indeksu

-- Za razliku od INSERT naredbe, UPDATE naredbu je moguce i ubrzati indeksom, jer ima WHERE deo

drop index person.idx_last
create nonclustered index idx_first
on person(firstname)
-- sa idx_first 0.013
-- index seek 25%-0.003, pretraga kroz indeks umesto citanja cele tabele, znatno ubrzanje
-- clustered index update 75%-0.01
-- mora da azurira tabelu, ali ne mora i neklasterovani indeks
-- jer se azurira samo kolona LastName, a ona nije deo indeksa idx_first

-- DELETE ima slicno ponasanje kao UPDATE