BP2

-- Kreiranje klasterovanog indeksa podrazumeva fizičko sortiranje tabele po ključu pretrage, a zatim
-- izgradnju B-stabla nad stranicama tabele.
-- Najcesce PK
-- Primer, telefonski imenik

-- Neklasterovani indeksi ne utiču na fizički raspored podataka. Zbog toga ih može biti više nad
-- jednom tabelom.
-- Primer indeks pojmova u knjizi

-- http://prntscr.com/luc5lx

-- Indeksi se najčešće prave nad kolonama koje se pojavljuju kao argumenti pretrage u WHERE
-- naredbi, ili kao spoljašnji ključ u JOIN naredbi.

-- Klasterovani indeksi se obično postavljaju nad
-- nepromenljivim kolonama, a neklasterovani tamo gde se bira mali procenat podataka iz tabele.

-- Klasterovani bolje sortira za sve kolone
-- Neklasterovani bolje sortira za kolone svog i klasterovanog kljuca pretrage

-- Kreiranje neklasterovanog indeksa
CREATE NONCLUSTERED INDEX ncl_first
ON [dbo].[Person](Firstname)

-- Brisanje neklasterovanog indeksa
DROP INDEX ncl_first ON [dbo].[Person]

-- Dodavanje PK
ALTER TABLE [dbo].[Person]
ADD CONSTRAINT PK_ID
PRIMARY KEY(BusinessEntityID)

-- Uklanjanje PK
ALTER TABLE [dbo].[Person]
DROP CONSTRAINT PK_ID

-- Kompozitni indeks
CREATE NONCLUSTERED INDEX ncl_flm
ON person(firstname, lastname, middlename)
-- Zapisi su ovde sortirani prvo po firstname-u, pa lastname-u i na kraju ide middlenam

-- kompozitni indeksi su od najvece koristi za ubrzanje upita koji se
-- sortiraju ili grupisu vise kolona

DROP INDEX ncl_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


-----------------------------------------------
SELECT * FROM [dbo].[Person]

-- Bez indeksa: 0.17 (operacija TABLE SCAN)
-- Sa indeksom: 0.17 (operacija Clustered Index Scan) --> Isto sto i TABLE SCAN

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

-------------------- WHERE --------------------

SELECT *
FROM [dbo].[Person]
WHERE FirstName LIKE 'Diane'

-- Bez indeksa: 0.17 (TABLE SCAN)
-- Sa klasterovanim: 0.17 (CLUSTERED INDEX SCAN)
-- Sa neklasterovanim: 0.06 (Key LookUp & Index Seek -> Nasted Loops)

-- Posto imamo ncl_first znaci da ce se upit ubrzati posto
-- je uslov pretrage baziran na FirstName-u.

-- IndexSeek - prolazi kroz ncl_first i gleda na koje stranice zadovoljavaju WHERE uslov
    -- Uparuje ih sa odgovarajucim PK (Key Lookup ili RID ukoliko nema PK, razlika je sto RID zauzima vise memorije)
-- Nasted Loops -> JOIN, uparuje ova dva i vraca rezultate

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

SELECT *
FROM [dbo].[Person]
WHERE FirstName = 'Diane'

-- Bez ncl: 0.17
-- Sa ncl: 0.038

-- Razlog zasto je skoro duplo brza procena lezi u pretpostavci
-- da LIKE vraca vise redova od operatora =

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

SELECT FirstName --, BusinessEntityID
FROM [dbo].[Person]
WHERE FirstName = 'Diane'

-- Bez ncl: 0.17
-- Sa ncl: 0.003 (Index Seek)

-- Razlog, nema potrebe da ulazimo u tabelu
-- Svi podaci su nam vec u indeksu
-- Pored Firstname-a neklasterovani indeks sadrzi i PK/RID
    -- Tako da i njega mozemo vratiti sa istim performansama

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

SELECT COUNT(*)
FROM [dbo].[Person]

-- Bez ncl: 0.17
-- Sa ncl: 0.08

-- Razlog, nema potrebe da se prolazi kroz tabelu, svi podaci su u indeksu

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

SELECT COUNT(*)
FROM [dbo].[Person]
WHERE FirstName = 'Diane'

-- Bez ncl: 0.17
-- Sa ncl: 0.0033029 (Index Seek -> Stream Aggregate -> Compute Scalar)

-- Ponovo nema potrebe da ulazimo u tabelu, prebrojavanje vrsimo nad indeksom
    -- Stream Aggregate -> Prolazimo liniju po liniju indeksa i brojimo sa Compute Scalar

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

SELECT COUNT(*)
FROM [dbo].[Person]
WHERE FirstName LIKE 'Diane'

-- Bez ncl: 0.17
-- Sa ncl: 0.0033198

-- Sve isto kao iznad, malo sporija procena zbog LIKE.
-----------------------------------------------

SELECT BusinessEntityID --, FirstName
FROM [dbo].[Person]
WHERE Firstname LIKE 'Diane'

-- Bez ncl: 0.17
-- Sa ncl: 0.0033038

-- BusinessEntityID nalazi se u neklasterovanom indeksu

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

SELECT lastname
FROM [dbo].[Person]

-- Bez ncl: 0.17 (TABLE SCAN)
-- Sa ncl: 0.17 (Clustered Index Scan)

-- Razlog, lastname se ne nalazi u ncl, zato se pristupa tabeli

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

SELECT lastname
FROM [dbo].[Person]
WHERE FirstName LIKE 'Diane'

-- Bez ncl: 0.17
-- Sa ncl: 0.06

-- Razlog, WHERE se odnosi na stavku iz ncl-a, zato prvo mogu da saznam koje sve stranice
-- su mi potrebne iz tabele, a zatim da iste dovucem

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

SELECT *
FROM [dbo].[Person]
WHERE FirstName LIKE 'Richard'

-- Bez ncl: 0.17
-- Sa ncl: 0.17

-- Razlog, query optimizer je procenio da je jeftinije da procita celu tabelu
-- nego da vise puta pristupa odredjenim stranicama
-- posto se ime Richard pojavljuje dosta cesto u tabelama

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

SELECT BusinessEntityID
FROM [dbo].[Person]

-- Bez indeksa: 0.17
-- Sa klasterovanim (PK) indeksom: 0.17
-- Sa neklasterovanim indeksom: 0.07

-- Razlog, klasterovani indeks pokazuje na tabelu, i mora kroz celu da prodje,
-- dok neklasterovani ima radi sa kopijom iste a brze je kretati se kroz indeks nego dovlaciti tabelu

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

SELECT BusinessEntityID
FROM [dbo].[Person]
WHERE BusinessEntityID = 1000

-- Bez indeksa: 0.17
-- Sa klasterovanim (PK) indeksom: 0.003

-- Razlog, kretanjem kroz klasterovani indeks, lako mogu doci do zeljene n-torke gde se nalazi ona koja mi treba

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

SELECT BusinessEntityID
FROM [dbo].[Person]
WHERE BusinessEntityID < 1000

-- Bez indeksa: 0.17
-- Sa klasterovanim (PK) indeksom: 0.003

-- Ovde kako menjamo vrednost menja se i resenje koje uzima QueryOptimizer,
-- pa nekad ide preko klasterovanog, nekad preko ncl. ( 1000 | 10000 )

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

------------------ GROUP BY -------------------

SELECT FirstName, COUNT(*)
FROM [dbo].[Person]
GROUP BY FirstName

-- Bez indeksa: 0.37
    -- TableScan -> Hash Match -> ComputeScalar
    -- za Hash Match pretpostavlja da ce raditi brze od sortiranja

-- Sa PK: 0.37
    -- Sve isto, samo umesto tableScan imamo Clustered Index Scan

-- Sa NCL: 0.08
    -- Index Scan -> Stream Aggregate -> Compute scalar

    -- Nemamo nikakvu hash mapu iz razloga sto nam neklasterovani indeks
    -- poseduje sortiranje po FirstName-u i zato je potrebno samo da prodjemo
    -- kroz isti i prebrojimo, ne pristupamo tabeli.

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

SELECT firstname, lastname, count(*)
FROM [dbo].[Person]
GROUP BY firstname, lastname

-- Bez indeksa/sa klasterovanim: 1.51
    -- Table scan/clustered index scan: 0.17
    -- -> Sortiranje tabele 1.32
    -- -> Agregacija (COUNT) i compute

-- Sa neklasterovanim: 1.51
    -- U indeksu se nalazi kopija pa imamo sortirano po firstname,
        -- ali moramo pristupiti tabeli zbog lastname
        -- sto znaci da nemam korist od indeksa i radim isto kao bez njih

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

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

SELECT *
FROM [dbo].[Person]
ORDER BY FirstName

-- Bez indeksa i sa klasterovanim: 1.49
        -- Table scan / Clustered index scan
        -- Sort

-- Isto vazi i sa neklasterovanim indeksom posto radim sa celom tabelom
    -- vise mi se isplati da ne koristim indeks

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

SELECT firstName
FROM [dbo].[Person]
ORDER BY FirstName

-- Bez indeksa i sa klasterovanim: 1.49
    -- Table Scan / Clustered indexscan
    -- Sort

-- Sa neklasterovanim
    -- imam indeks koji je sortiran po firstName-u sto mi odgovara
    -- a posto vracam samo firstName, ovde ce mi indeks pomoci
    -- nemam operaciju sort

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

SELECT businessEntityID, firstName
FROM [dbo].[Person]
ORDER BY FirstName

-- Bez indeksa / sa klasterovanim: 1.49
    -- Table / Clustered index scan
    -- Sort

-- Sa neklasterovanim: 0.07
    -- Isto kao iznad
    -- Radice brze iz razloga sto se i businessEntityID nalazi u indeksu
    -- Ne pristupam tabeli

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

SELECT businessEntityID, firstName, lastname
FROM [dbo].[Person]
ORDER BY FirstName

-- U ovoj situaciji mi indeksi ne pomazu: 1.49
    -- Razlog je jer se lastname ne nalazi u neklasterovanom indeksu i onda se prolazi kroz celu tabelu.

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

ALTER TABLE [dbo].[Person]
DROP CONSTRAINT PK_ID

SELECT businessEntityID, firstName
FROM [dbo].[Person]
ORDER BY FirstName

-- Uklonivsi PK iz tabele, gubi se i klasterovani indeks,
    -- sledi da businessentityID vise nije deo indeksa
    -- i mora se pristupati tabeli za isti, zato se ni ne koristi indeks
    -- i ponasanje je isto kao u primeru iznad

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

SELECT BusinessEntityID
FROM [dbo].[Person]
ORDER BY BusinessEntityID

-- Bez klasterovanog: 1.49
    -- Table Scan -> Sort

-- Sa klasterovanim: 0.17
    -- Tabela je vec sortirana, samo citanje

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

SELECT *
FROM [dbo].[Person]
ORDER BY BusinessEntityID

-- Bez klasterovanog: 1.49 -> Table scan -> sort
-- Sa klasterovanim: 0.17
    -- Tabela je vec sortirana po PK, tj po BusinessEntityID-u
        -- imamo samo citanje

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

SELECT TOP 5 *
FROM [dbo].[Person]
ORDER BY FirstName

-- Sortiranje po firstName-u koji je deo neklasterovanog indeksa

-- Bez ncl: 1.49
    -- Mora sortiranje posto posto FirstName nije deo indeksa
    -- TableScan -> Sort

-- Sa ncl: 0.02
    -- Nije potreban sort jer vec je sortirano po FirstName-u,
    -- potrebno je samo spojiti sa klasterovanim indeksom kako bi se vratili svi atributi

-- NCL je koristan sve dok je broj n-torki koje vracamo znacajno manji od tabele
-----------------------------------------------

SELECT TOP 5000 *
FROM [dbo].[Person]
ORDER BY FirstName

-- Bez ncl: 1.49
-- Sa ncl: 1.45

-- Ovde jos uvek imamo vecu korist upotrebom indeksa,
-- ali za veci select, nece se koristiti indeks

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

SELECT TOP 6000 *
FROM [dbo].[Person]
ORDER BY FirstName

-- Bez ncl: 1.49
-- Sa ncl: 1.49

-- Presli smo granicu gde je indeks koristan,
    -- zato sada imamo table scan i sort iste.

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

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

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

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

SELECT BusinessEntityID, FirstName
FROM Person

-- Bez ncl: 0.17
-- Sa ncl: 0.10
    -- Ne ulazim u tabelu, zato je brze

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

SELECT BusinessEntityID, FirstName
FROM Person
ORDER BY FirstName

-- Bez ncl: 1.49
    -- Table scan -> Sort
-- Sa ncl: 0.10
    -- Ne ulazim u tabelu
    -- Ne sortiram
    -- Samo citam

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

SELECT BusinessEntityID, FirstName
FROM [dbo].[Person]
ORDER BY FirstName

-- Bez ncl: 1.49
    -- Table scan -> Sort
-- Sa ncl: 0.07
    -- Ne ulazim u tabelu
    -- Ne sortiram
    -- Samo citam
-- Sa kompozitnim ncl: 0.10
    -- Isto kao i sa obicnim ncl, samo sto je ovde indeks veci
    -- zauzima vise memorije pa je sporij

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

SELECT BusinessEntityID, FirstName, LastName
FROM [dbo].[Person]
ORDER BY FirstName

-- Bez indeksa: 1.49
-- Sa ncl: 1.49
-- Sa kompozitnim ncl: 0.10
    -- Samo citanje iz indeksa iz razloga sto isti poseduje sve atribute
    -- i sortirano je kako treba

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

SELECT BusinessEntityID, FirstName --, LastName
FROM [dbo].[Person]
ORDER BY LastName

-- Bez indeksa: 1.49
-- Sa ncl: 1.49
-- Sa kompozitnim ncl:
    -- Brze iz razloga sto indeks poseduje sve kolone,
    -- sada su sortirane po firstname-u
    -- pa je potrebno sortirati ih po lastname-u
    -- ali i dalje je brze jer je indeks brzi

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

SELECT BusinessEntityID, FirstName --, LastName
FROM [dbo].[Person]
ORDER BY FirstName, LastName

-- Bez indeksa: 1.49
-- Sa ncl: 1.49
-- Sa kompozitnim ncl: 0.10

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

SELECT FirstName, Lastname, COUNT(*)
FROM [dbo].[Person]
GROUP BY FirstName, LastName

-- Bez indeksa: 1.51
-- Sa ncl: 1.51
-- Sa kompozitnim ncl: 0.12
    -- Nemamo sortiranje, sve je u indeksu i sortirano onako kako nam odgovara

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

SELECT LastName, COUNT(*)
FROM [dbo].[Person]
GROUP BY LastName

-- Bez indeksa: 0.38
-- Sa ncl: 0.38
-- Sa kompozitnim: 0.31
    -- Brze jer radi nad indeksom koji ima manje atributa, plus je u memoriji
    -- Razlika je samo u prvoj opraciji, umesto table scan-a imamo IndexScan koji je manji.
-- Svuda radi HashMatch umesto sortiranja jer je brze po proceni Query optimizera.

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

-------------- 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

-- 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

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

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

-- Dodaju se kolone koje nisu deo kljuca pretrage.

-- Kreiranje neklasterovanog indeksa sa include-om
CREATE NONCLUSTERED INDEX idx_first_incl
ON person(firstname) INCLUDE (lastname)

-- 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.

-- Razlika se vidi obicno kod vecih indeksa.

-- 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 su obicno primarni kljucevi, ali to ne mora biti slucaj

-- Ukoliko sami zelimo da postavimo klasterovani indeks, moramo prvo ukloniti postojeci

ALTER TABLE person
ADD CONSTRAINT PK_id
PRIMARY KEY (BusinessEntityID)

ALTER TABLE person
DROP CONSTRAINT PK_id

-- Kreiranje klasterovanog indeksa
CREATE CLUSTERED INDEX cl_last
ON person(lastname)

DROP INDEX cl_last ON person

-- Tabela sada zauzima vise memorije
    -- Kada je klasterovani kljuc PK, uz to i auto-increment
    -- racuna se da nece biti INSERT operacija koje ubacuju vrste u sredinu tabele

    -- Kada klasterovani kljuc nije PK, a nije ni UNIQUE,
    -- moguce je ubacivati nove vrste u svaki deo tabele,
    -- zato se memorijskoj stranici ostavlja dodatan prazan prostor za buduce nove vrste.
    -- Zato i sama tabela zauzima vise stranica

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

SELECT *
FROM person
WHERE lastname LIKE 'Adams'

-- Sa klasterovanim starim: 0.17
-- Sa cl_last: 0.003
    -- Klasterovani indeks predstavlja sortiranu tabelu po tom indeksu
    -- usteda memorije u odnosu na neklasterovani indeks

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

-- Koriscenje INCLUDE naredbe sa klasterovanim indeksom nema smisla
-- Moguce je napraviti klasterovani kompozitni indeks

CREATE CLUSTERED INDEX cl_last_first
ON person(lastname, firstname)

DROP INDEX cl_last_first ON Person

-- data 1664 KB, INDEX 32 KB
-- indeks duplo vise zauzima, jer je duplo veci

SELECT *
FROM [dbo].[Person]
ORDER BY lastname, firstname

-- Bez indeksa: 1.49
        --> table scan
        --> sort
-- Sa cl_last_first: 0.178
        --> Samo citanje

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

SELECT lastname, firstname, COUNT(*)
FROM [dbo].[Person]
GROUP BY lastname, FirstName

-- Bez indeksa: 1.52
        -- table scan
        -- sort
        -- agregacija i racunanje

-- Sa indeksom: 0.2
        -- citanje indeksa (table scan)
        -- agregacija i racunanje, nemamo sortiranje

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

-------- PRAVLJENJE NEKLASTEROVANO PK ---------

ALTER TABLE person
ADD CONSTRAINT PK_id
PRIMARY KEY NONCLUSTERED (BusinessEntityID)

ALTER TABLE person
DROP CONSTRAINT PK_id

-----------------------------------------------
-- Indeksi mogu i da uspore izvrsavanje nekih upita (INSERT, UPDATE, DELETE)
------------------- INSERT --------------------

INSERT INTO person(BusinessEntityID, PersonType, FirstName, LastName, ModifiedDate) VALUES (30000, 'EM', 'Pera', 'Peric', '2015-12-3');

-- Bez indeksa: 0.01
    -- Table insert: 0.01 - ubacivanje nove vrste u tabelu
    -- Assert - provera da li se narusava neko ogranicenje
-- Klasterovani indeks: 0.01
    -- Clustered Index Insert: 0.01 - ubacivanje nove vrste u tabelu
    -- Assert - provera ogranicenja

-- Sa ncl_first: 0.02
    -- Clustered Index Insert: 0.02
        -- duplo vise iz razloga sto osim u tabelu, ubacuje se i u ncl indeks

-- Sa ncl_first i kompozitnim: 0.03
    -- Clustered Index Insert: 0.03
        -- isto obrazlozenje, imamo jedan indeks vise za insert




-- Klasterovani indeks ne usporava INSERT naredbu
-- Neklasterovani indeksi usporavaju naredbe modifikacije jer se svaka promena mora odraziti i u indeksima


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

UPDATE Person
SET lastname = 'Jovanovic'
WHERE firstname = 'Pera'

-- Bez indeksa: 0.1862
    -- Table scan -> Compute -> Update
-- Sa klasterovanim: 0.1862
    -- Isto sve
-- Neklasterovani ncl_last: 0.2
    -- Update traje duplo duze jer treba azurirati vrstu na 2 mesta

-- UPDATE je moguce ubrzati jer ima WHERE deo
    CREATE NONCLUSTERED INDEX idx_first
    ON person(firstname)
-- 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