Korišćenje indexa kod bitwise operacija

[ vujkev @ 23.09.2016. 15:09 ] @

Primera radi imamo tabelu

Code (tsql):

create table foo (

    id int,

    Status tinyint,

    ...

i nad kolonom Status kreiran index.

Kolona status predstavlja jedan ili više mogućih statusa

enum Statusi : byte
{
Status1 = 0x01,
Status2 = 0x02,
Status3 = 0x04,
Status4 = 0x08
}

Ukoliko napišem upit

Code (tsql):

Select * from foo where Status & 1 = 1

SQL neće koristiti kreiran index nad kolonom Status jer mora za svaki red da odradi bitwise operaciju.
Ukoliko upit napišem kao

Code (tsql):

Select * from foo where Status = 1

index se naravno koristi. Na žalost ako neki red ima Status = Status4|Status1 gornji upit neće vratiti taj red.

Jedno moguće rešenje je da se statusi prebace u posebnu tabelu i da se svaki mogući status snima kao poseban red

Code (tsql):

create table fooStatus(

    fooID int,

    Status tinyInt

U ovom slučaju statusi mogu da budu brojevi 1, 2, 3, 4 ... jer se neće koristiti bitwise operacije, ali ukoliko foo tabela ima 5.000.000 redova ova tabela može imati do 50.000.000 (ako kažemo da postoji max 10 moguća statusa). Da li će ovoliki index imati uticaj na performanse izvršavanja upita?

Drugo rešenje je da se koristi IN prilikom selecta

Code (tsql):

Select * from foo where Status IN (1, 3, 5, 7, 9, 11, 13, 15) -- ako ima max 4 statusa i tražimo Status & 1 = 1

 

U ovom slučaju problem je ako postoji npr 10 mogućih statusa. IN clause u ovom slučaju može biti poprilično dugačka. Da li će ovo imati uticaj na performanse izvršavanja upita?

Postoji li još neko rešenje ovog problema i kako ga rešavate u vašim aplikacijama?

Poz.

[ dusans @ 23.09.2016. 15:32 ] @

Možeš da probaš ovako - napravi jednu tabelu gde je svaki flag mapiran na sve brojeve koji ga sadrže.
Evo ti primer za tabelu sa 3 flag-a [1, 2, 4] (za 10 flagova bi imala 1024 redova pa je malo dugačko da pišem):

Code:

flag     num
1         1
1         3
1         5
1         7
2         2
2         3
2         6
2         7
4         4
4         5
4         6
4         7

Na osnovu ovakve tabele, uradi join sa flags.num ka foo.status, tipa:

Code (sql):

SELECT foo.* 

FROM flags

INNER JOIN foo ON foo.STATUS = flags.num

WHERE flags.flag = 1

Pa posle možeš i da indeksiraš obe kolone u flags tabeli i šta ti je već volja...

[ djoka_l @ 23.09.2016. 16:39 ] @

Problem je pogrešno postavljen, a imao sam prilike da vidim slična rešenja.
Recimo, ljudi ponekad vole da stave nešto kao

STATUS='1, 2, 3, 8, 12'

pa onda listu statusa pretvaraju u tabelu pa rade join.

Za tvoj problem, rešenje je jednostavno, umesto da stavljaš binarni zapis statusa (nekog registra) napravi onoliko polja koliko ima statusa i neka svako polje ima samo vrednosti 1 ili 0.

Moguće je da bi ovakav pristup rešio tvoj problem. Naravno, morao bi da prilikom upisa u tabelu bitove statusa upišeš u odgovoarajuća polja.

[ vujkev @ 23.09.2016. 16:46 ] @

Nešto mi ne deluje da bi po jedna kolona za svaki status bilo dobro rešenje zbog:
- Svaku kolonu bi trebao da indexiram
- ako bi tražio red sa kombinacijom statusa (npr Statu4|Status1) to bi bilo ....
- za svaki novi status treba da pravim novu kolonu
- ... sigurno ima još neka mana

Rešenje koje je predložio dusans mi je za sad najpraktičnije. Ima li bolje?

Poz.

[ djoka_l @ 23.09.2016. 17:28 ] @

Ima bolje rešenje, pređi na Oracle i koristi bitmap indeks.
I dalje je tvoje rešenje užasno za SQL Server. Čikam te da napišeš upit za primer koji si naveo u poslednjem postu koristeći dušanovo rešenje (dušanovo rešenje, kao takvo, nije loše, loš je tvoj koncept).

Uzgred, čikam te da upotrebiš bitmap indeks u transakcionoj bazi pa da ubiješ performanse kad zaključaš ogromnu količinu slogova za jedan insert. Bitmap indeks se koristi isključivo u data warehouse bazama.

[ vujkev @ 23.09.2016. 17:43 ] @

Kakav bi koncept ti napravio na MS SQL bazi (oracle nije u opciji). Postojeću bazu ne mogu da menjam, ali da znam za ubuduće koje je bolje rešenje.

[ djoka_l @ 23.09.2016. 20:40 ] @

Ne mogu da ti dam koncept rešenja, zato što ne znam koji je tvoj problem.
Rekao si da ćeš imati 5 miliona slogova. Rekao si da će ti trebati upiti tipa status1 | status2. Neko normalno razmatranje, ako postoji 10 statusa je da će ti takav upit vrati ti bar 20% slogova (ako su statusi uniformno raspoređeni). Štaviše, ako u statusu1 i statusu2 ima više od jedne jedinice, tj u njihovom OR više od dve jedinice, tada je realno očekivati da će ti upit vratiti n*10% gde je n broj bitova sa vrednošću 1. Tu ti ni jedan indeks ne pomaže.

Problem jednostavno nije za relacionu bazu podataka, ako jeste, onda se pomiri sa činjenicom da je FULL TABLE SCAN gotovo neumitan rezultat upita.
U bilo kojoj relacionoj bazi je svako polje koje ima malu kardinalnost u odnosu na broj slogova LOŠ kandidat za ključ (indeks).

Why low cardinality indexes negatively impact performance:
https://www.ibm.com/developerw...y/techarticle/dm-1309cardinal/