tehnike indeksiranja u Oracle

Ljudi,

Radim neki specijalisticki Oracle tehnike indeksiranja sve tipove indeksiranja sam obradio teorijski itd.
Medjutim, sad treba neki prakticni primjer, moja ideja je bila da napravim ogromnu tabelu, napunim je podacima i pokazem da je pretrazivanje ne indeksirane tabele mnogo sporije u odnosu na tabelu nad kojom je kreirana neka vrsta indeksa.

Koristeci Oracle 11g i sql developer napravio sam tabelu sa slucajnim stringom duzine 1000, slucajnim brojem duzine 100, slucajnim datumom, sa 20000 redova

kad stavim select * from test_table where slucajni_string like '%abcd%' pronadje mi podatke za otpirlike isto vrijeme (0.3sec), kao kad kreiram indeks nad slucajnim stringom, vjerovatno oracle ima neki optimizer itd.

Dajte neku ideju kako da pokazem kroz nekoliko primjera da je indeksiranje zaista efikasno

Ne znam za Oracle, ali MySQL za upite SELECT * FROM tabela WHERE polje LILE "%nesto%" ne koristi indexe, vec mora da radi full table scan - index ide po celom polju, a ne i za pattern matching za LIKE.

Potpis

Please do not feed the Trolls!

Blasphemy? How can I blaspheme? I'm a god!'

Ako bi umesto select * from koristio select kolona from tabela where kolona = 'ABC'; trebalo bi da trazi po indeksu.

Probaj da napravis 2-3 tabele, napunis podacima i da ih u selectu join-ujes. Kolone koje su ti u uslovu spajanja neka u jednom primeru budu indeksirane a u drugom ne. Tu bi trebalo da vidis korist od indexa.

Potpis

I've seen things you people wouldn't believe. Attack ships on fire off the shoulder of Orion. I watched C-beams glitter in the dark near the Tannhauser gate. All those moments will be lost in time, like tears in rain. Time to die.

20 000 slogova za testiranje sa indexima je ništa.

napuni ti fino tablicu sa 20 milijuna slogova pa onda testiraj.

Da, al mi oracle database express R2 (http://www.oracle.com/technetw...ss-edition/overview/index.html) ne dozvoljava da unesem u jednoj tabeli preko 17883 redova...

Stavi neku vrednost na mali broj redova, pa onda uradi upit trazeci to bez i sa indeksom.

Ovo nije tacno, nema ogranicenja koliko tabela ima redova, ogranicenja su druge vrste. Kad sam probao 10XE u jednu tabelu sam ubacio skoro milion redova, loaderom iz text fajla, za dva minuta je ubacio.

Gore si vec napisao da si formirao tabelu sa 20000 redova, kako sad ne radi?

Ne znam da li je test sa LIKE bas reprezentativan, u stvari koliko je upit iz jedne tabele reprezentativan. Problem tj usporenje nastaje prilkom povezivanja dve tabele, prakticno sva polja u WHERE uslovi bi trebala da su indeksirana. U WHERE uslovu mora da si polja iz raznih tabela, probaj vise od dve, sa indeksima i bez. Mora da se oseti razlika.



_{[Ovu poruku je menjao dragancesu dana 22.05.2012. u 11:31 GMT+1]}

Potpis

Pomozite Micro$oftu u borbi protiv piraterije, poklonite prijatelju Linux

1.registriraj se na oracle.com, registracija je besplatna.
2.skini enterprise edition, svi oracle produkti su besplatni za učenje i razvoj.

XE 11g ima ogranicenja na CPU(koristi jedan, ma koliko da ih ima komp), na RAM(maximalno koristi 1GB), i na podatke(maximalno 11GB podataka sveukupno).
Ne postoji ogranicenje na broj redova u tabeli.

Potpis

I've seen things you people wouldn't believe. Attack ships on fire off the shoulder of Orion. I watched C-beams glitter in the dark near the Tannhauser gate. All those moments will be lost in time, like tears in rain. Time to die.

Sve ovo sto ste pomenuli mi je poznato.

evo npr kad kreiram tabelu na ovaj nacin

CREATE TABLE Table1 AS
SELECT rownum id
FROM all_objects
WHERE rownum < 100000;

napuni se tabela sa 17883 reda

Napravio sam tri tabele i kad pretrazujem povezanim upitom primjetni su rezultati... Dok za upit like se ne pretrazuje indeks vec full scan tabele

Probaj

select count(*) from all_objects

i dobićeš 17883

To što sistemski view all_objects skoro prazne Oracle XE baze ima 17883 redova, ne znači da je to maksimalan broj redova koji ti možeš da imaš u tvojo bazi.

Uradi sledeće:



i dobićeš tabelu sa milion redova.

Moram da reagujem na ovo:


Mislim da nisi baš dobro odradio. Evo mali test za proveru znanja:

Imaš tabelu test, koja ima neka polja, a među njima i polje text varchar2(20) i indeks nad poljem text test_ind1. Dalje pretpostavimo da se u polju text nalaze stringovi koji se sastoje samo od velikih slova. Odgovori na sledeća pitanja:

1 select * from test where text>"A" neće koristiti indeks. Zašto?
2 select * from test where text>"X" će koristiti indeks. Zašto?
3 select * from test where test<"C" će korisiti indeks. Zašto?
4 select * from test where test between "A" and "C" će korisitit indeks. Zašto?
5 select text from test where text like "A%" neće koristiti tabelu nego samo indeks. Zašto?
6 select * from test where text like "%A%" neće koristiti indeks. Zašto?
7 select * from test where text like "%X" neće koristiti indeks. Zašto?

haha odlicno, ovo ce mi koristiti
E ovako, tacnije tema mi je 'tehnike indeksiranja', na prvih 30 stranica sam teorijski objasnio samo kakvi tipovi indeksa postoje - primarni, clustered, sekundarni, sekundarni sa dodatnm nivoima redierkcije, b+-stabla itd.
Znaci Oracla doticao nisam, e sad mi je ideja da obradim malo Oracle..

Od ovih pitanja, da ne bih ispao glup u drustvu i lupao, pokusacu da odg samo na br 4. i 5.
4. Logicno je da se samo pretazi indeks tabela koja ce da pokaze gdje se nalaze rezultati i ucitace cijeli redovi tabele
5. Dovoljno je da se pretrazi indeks tabela i iscitaju polja koja pocinju sa A

Odgovori:

1. Uz pretpostavku da su u pitanju slučajni stringovi u polju text i da Oracle ima ažurnu statistiku tabele i indeksa, optimizator će provaliti da upit vraća kao rezultat 96% redova iz tabele. Zato će odlučiti da ne koristi indeks, jer će morati da pročita oko 90% indeksa i 96% tabele da bi vratio rezultat. Umesto toga će se uraditi FULL TABLE SCAN koji će imati samo 4% overheada, umesto da ima 90% overhead zbog indeksa.

2. Upit vraća sve stringove koji počinju na Y i Z što je oko 8% slogova, pa će Oracle odlučiti da koristi index da bi našao prvi element veći od X, a zatim vratiti i sve ostale krećući se s leva u desno (po rastućem redosledu) po listi listova indeksa

3. Indeks je organizovan kao B* stablo čiji su listovi članovi dvostruko povezane liste. Upit vraća oko 8% slogova baze, pa će preko indeksa da se nađe prvi element koji je manji od "C", a dalje će se proći kroz indeks u smeru s desna na levo (što znači od većih ka manjim vrednostima).

4. BETWEEN je odličan način da se iskoristi indeks. Nađe se prvi element koji zadovoljava uslov, onda se ide po listi u desno. Rezultat je oko 12% slogova baze - slogovi čije polje text počinje sa A, B ili C

5. Kada lista polja u select i where uslovu čini jedan indeks, a where je tako napisan da indeks može da se koristi, Oracle neće čitati tabelu, nego samo index, jer su indeksi manji od tabele (polja indeksa su podskup polja tabele), a svi potrebni podaci se već nalaze u indeksu.

6. Indeks može da se koristi samo kada je uslov pretrage PREFIKS indeksiranih polja. Dakle LIKE 'A%', LIKE 'ABC%' itd može da se nađe po indeksu. Međutim LIKE '%A%' kaže samo da je negde u polju slovo A, što znači da bi trebalo da se pročita ceo indeks, pa na osnovu indeksa nađu ostali slogovi, što može potencijalno da znači čitanje celog indeksa i cele tabele. Oracle bira da onda samo uradi čitanje tabele. NIJEDNA BAZA NA SVETU NEĆE KORISTITI INDEKS ZA UPITE TIPA LIKE '%nesto%'. Isti je slučaj i ako je indeks sastavljen iz više polja, recimo A,B,C,D. Indeks se koristi ako je u where uslovu A, A i B, A i B i C, A i B i C i D, A i C, A i D, A i C i D, ali neće u slučaju kada je A izostavljeno.

7. Isto kao 6.