zad2 ####################################################### set autotrace traceonly explain select nazwa, count(*) from zesp natural join prac group by nazwa order by count(*) desc; 0 SELECT STATEMENT Optimizer=CHOOSE 1 0 SORT (ORDER BY) 2 1 SORT (GROUP BY) 3 2 MERGE JOIN 4 3 SORT (JOIN) 5 4 &nbs p; TABLE ACCESS (FULL) OF 'PRAC' 6 3 SORT (JOIN) 7 6 &nbs p; TABLE ACCESS (FULL) OF 'ZESP' SORT (JOIN) - bo będzie to przydatne później, przy grupowaniu merge join - skoro już jest podortowane to grzech nie skorzystać 2 sortowania na potrzeby group by i order by zad4 ####################################################### select rowid, nazwisko, plec, placa from prac where id_prac = 900; 0 SELECT STATEMENT Optimizer=CHOOSE 1 0 TABLE ACCESS (FULL) OF 'PRAC' metoda full scan, ponieważ nie mamy założonego żadnego indeksu zad5 ####################################################### select rowid, nazwisko, plec, placa from prac where rowid='ABMYnCAASAAAEp8AC8'; 0 SELECT STATEMENT Optimizer=CHOOSE 1 0 TABLE ACCESS (BY USER ROWID) OF 'PRAC' metoda dostępu BY USER ROWID ponieważ użytkownik jawnie podał adres wiersza, więc można z niego skorzystać zad6 ####################################################### set autotrace off; select * from user_indexes; na relacji PRAC nie ma żadnych indeksów zad7 ####################################################### create index prac_idx on prac(id_prac); set autotrace traceonly explain select rowid, nazwisko, plec, placa from prac where id_prac = 900; 0 SELECT STATEMENT Optimizer=CHOOSE 1 0 TABLE ACCESS (BY INDEX ROWID) OF 'PRAC' 2 1 INDEX (RANGE SCAN) OF 'PRAC_IDX' (NON-UNIQUE) dostęp do tabeli za pomocą indeksu RANGE SCAN - ponieważ indeks jest nieunikalny zad8 ####################################################### set autotrace off; drop index prac_idx; alter table prac add constraint prac_pk PRIMARY KEY(id_prac); zad9 ####################################################### select * from user_indexes; na kluczu głównym zbudowany został indeks zad10###################################################### set autotrace traceonly explain select rowid, nazwisko, plec, placa from prac where id_prac = 900; 0 SELECT STATEMENT Optimizer=CHOOSE 1 0 TABLE ACCESS (BY INDEX ROWID) OF 'PRAC' 2 1 INDEX (UNIQUE SCAN) OF 'PRAC_PK' (UNIQUE) wykorzystano indeks założony na kluczu głównym. Indek jest unikalny więc dostęp typu UNIQUE SCAN zad11###################################################### create index prac_nazwisko_idx on prac(nazwisko); set autotrace traceonly explain select * from prac where nazwisko='Prac155' 0 SELECT STATEMENT Optimizer=CHOOSE 1 0 TABLE ACCESS (BY INDEX ROWID) OF 'PRAC' 2 1 INDEX (RANGE SCAN) OF 'PRAC_NAZW ISKO_IDX' (NON-UNIQUE) indeks na nazwisku nie jest unikalny, więc najpierw RANGE SCAN a indeksie aby wyznaczyć właściwe row id, a następnie dobieramy się do tabeli za pomocą tegoż row id. set autotrace traceonly explain select * from prac where nazwisko like 'Prac155%'; 0 SELECT STATEMENT Optimizer=CHOOSE 1 0 TABLE ACCESS (BY INDEX ROWID) OF 'PRAC' 2 1 INDEX (RANGE SCAN) OF 'PRAC_NAZW ISKO_IDX' (NON-UNIQUE) analogicznie, początek każdego nazwiska musi być taki sam jak przykład wyżej więc ic wyszukanie też się odbywa tak samo select * from prac where nazwisko like '%Prac155%'; 0 SELECT STATEMENT Optimizer=CHOOSE 1 0 TABLE ACCESS (FULL) OF 'PRAC' tutaj początek nazwiska nie jest znany, więc trzeba przejrzeć całą tabelę, w celu znalzienia właściwego wzorca select * from prac where nazwisko like 'Prac155%' or nazwisko like 'Prac255%'; 0 SELECT STATEMENT Optimizer=CHOOSE 1 0 CONCATENATION 2 1 TABLE ACCESS (BY INDEX ROWID) OF 'PRAC' 3 2 INDEX (RANGE SCAN) OF 'PRAC_NAZWISKO_IDX' (NON-UNIQUE) 4 1 TABLE ACCESS (BY INDEX ROWID) OF 'PRAC' 5 4 INDEX (RANGE SCAN) OF 'PRAC_NAZWISKO_IDX' (NON-UNIQUE) jak w przypadku pierwszym, RANGE SCAN wyznacza row id dla Prac155% i Prac255%, następnie mamy dostęp do tabeli dla każdego z nich i wykonanie operacji konkatenacji obu tabel. zad12###################################################### drop index prac_nazwisko_idx; create index prac_nazw_placa_idx on prac(nazwisko, placa); select count(*) from prac where nazwisko like 'Prac1%'; 0 SELECT STATEMENT Optimizer=CHOOSE 1 0 SORT (AGGREGATE) 2 1 INDEX (RANGE SCAN) OF 'PRAC_NAZW _PLACA_IDX' (NON-UNIQUE) mamy indek skonkatenowany - najpierw przeszukiwany jest pod kątem nazwisko, później placy. w zapytaniu warunek jest na nazwisku, wiec druga część indeksu jest zwyczajnie olewana, RANGE SCAN - bo indeks nie jest unikalny select count(*) from prac where nazwisko like 'Prac1%' and placa > 100; 0 SELECT STATEMENT Optimizer=CHOOSE 1 0 SORT (AGGREGATE) 2 1 INDEX (RANGE SCAN) OF 'PRAC_NAZW _PLACA_IDX' (NON-UNIQUE) wykorzystanie właściwości indeksu, wybranie konkretnych nazwisk, a spośród tych wybieramy pracowników o placy > 1000 select count(*) from prac where placa > 100; 0 SELECT STATEMENT Optimizer=CHOOSE 1 0 SORT (AGGREGATE) 2 1 TABLE ACCESS (FULL) OF 'PRAC' ideks nic nie daje, bo najpierw organizuje pod kątem nazwiska, później płacy, zatem mamy do wyboru biegać po indeksie i dobierać się do tabeli lub odrazu dobierać się do tabeli. Druga możliwość wydaje sie być sensowniejsza :] zad13###################################################### create index prac_plec_idx on prac(plec); select count(*) from prac where plec='M' and id_prac between 100 and 110; 0 SELECT STATEMENT Optimizer=CHOOSE 1 0 SORT (AGGREGATE) 2 1 TABLE ACCESS (BY INDEX ROWID) OF 'PRAC' 3 2 INDEX (RANGE SCAN) OF 'PRAC_PLEC_IDX' (NON-UNIQUE) jak ławo zauważyć nie zebrano jeszcze statystyk :D zatem optymalizator nie wie że indeks na płci, ma stosunkową kiepską selektywność i korzysta z niego zad14###################################################### zad15###################################################### select table_name, last_analyzed, num_rows from user_tables where table_name='PRAC'; select column_name, num_distinct, low_value, high_value, num_buckets from user_tab_columns where table_name = 'PRAC'; select index_name, last_analyzed, num_rows from user_indexes where table_name='PRAC'; select * from user_tab_histograms where table_name='PRAC'; zad16###################################################### analyze table prac compute statistics; zad17###################################################### zad18###################################################### zad19###################################################### select count(*) from prac where plec='M' and id_prac between 100 and 110; 0 SELECT STATEMENT Optimizer=CHOOSE (Cost=3 Card=1 Bytes=4) 1 0 SORT (AGGREGATE) 2 1 TABLE ACCESS (BY INDEX ROWID) OF 'PRAC' (Cost=3 Card=6 Bytes=24) 3 2 INDEX (RANGE SCAN) OF 'PRAC_PK' (UNIQUE) (Cost=2 Card=12) Jak widać optymalizator zmądrzał. Po obliczeniu statystyk wie że płeć ma małą selektywość więc z niej nie korzysta zad20###################################################### select count(*) from prac; 0 SELECT STATEMENT Optimizer=CHOOSE (Cost=4 Card=1) 1 0 SORT (AGGREGATE) 2 1 INDEX (FAST FULL SCAN) OF 'PRAC_ PK' (UNIQUE) (Cost=4 Card=10000) nie ma dostępu do tabeli. wszystkie informacje są dostępne w indeksie założonym na kluczu głównym zad21###################################################### create index prac_czy_etat_idx on prac(czy_etat); analyze table prac compute statistics for all indexes; select count(*) from prac where czy_etat='T' and plec='K'; 0 SELECT STATEMENT Optimizer=CHOOSE (Cost=6 Card=1 Bytes=2) 1 0 SORT (AGGREGATE) 2 1 TABLE ACCESS (FULL) OF 'PRAC' (C ost=6 Card=2500 Bytes=5000) zad22###################################################### drop index PRAC_PLEC_IDX; drop index PRAC_CZY_ETAT_IDX; select count(*) from prac where czy_etat='T' and plec='K'; 0 SELECT STATEMENT Optimizer=CHOOSE (Cost=6 Card=1 Bytes=2) 1 0 SORT (AGGREGATE) 2 1 TABLE ACCESS (FULL) OF 'PRAC' (C ost=6 Card=2500 Bytes=5000) nie ma różnicy. oba indeksy mają małą selektywność, w dodatku mamy do wykonania operację and, wiec szybciej jest wykonać skan tabeli niż biegać po indeksach. zad 22 to potwierdza zad23###################################################### create bitmap index prac_plec_bmp_idx on prac(plec) compute statistics; create bitmap index prac_czy_etat_bmp_idx on prac(czy_etat) compute statistics; zad24###################################################### select /*+ RULE*/ count(*) from prac where czy_etat='T' and plec='K'; 0 SELECT STATEMENT Optimizer=HINT: RULE 1 0 SORT (AGGREGATE) 2 1 TABLE ACCESS (FULL) OF 'PRAC' zad25###################################################### select count(*) from prac where czy_etat='T' and plec='K'; 0 SELECT STATEMENT Optimizer=CHOOSE (Cost=2 Card=1 Bytes=2) 1 0 SORT (AGGREGATE) 2 1 BITMAP CONVERSION (COUNT) 3 2 BITMAP AND 4 3 BITM AP INDEX (SINGLE VALUE) OF 'PRAC_CZY_ETAT_BMP_IDX' 5 3 BITM AP INDEX (SINGLE VALUE) OF 'PRAC_PLEC_BMP_IDX' zad26###################################################### create index prac_placa_idx on prac(placa) compute statistics; zad27###################################################### select nazwisko from prac where placa < 2; 0 SELECT STATEMENT Optimizer=CHOOSE (Cost=5 Card=20 Bytes=200) 1 0 INDEX (FAST FULL SCAN) OF 'PRAC_NAZW_PLACA_IDX ' (NON-UNIQUE) (Cost=5 Card=20 Bytes=200) select nazwisko from prac where ROUND(placa) < 2; 0 SELECT STATEMENT Optimizer=CHOOSE (Cost=6 Card=500 Bytes=500 0) 1 0 TABLE ACCESS (FULL) OF 'PRAC' (Cost=6 Card=500 Bytes=5000) drugie nie korzysta z indeksu ponieważ indeks był założony na atrybucie placa a nie wyrażeniu zad28###################################################### zad29###################################################### niech ktos to zrobi,mi sie chwilowo nie chce :] zad30###################################################### dostępne indeksy PRAC_CZY_ETAT_BMP_IDX PRAC_NAZW_PLACA_IDX PRAC_PK PRAC_PLACA_IDX PRAC_PLEC_BMP_IDX select * from prac order by id_prac; 0 SELECT STATEMENT Optimizer=CHOOSE (Cost=56 Card=10000 Bytes= 200000) 1 0 SORT (ORDER BY) (Cost=56 Card=10000 Bytes=2000 00) 2 1 TABLE ACCESS (FULL) OF 'PRAC' (C ost=6 Card=10000 Bytes=200000) select * from prac order by id_prac desc; 0 SELECT STATEMENT Optimizer=CHOOSE (Cost=56 Card=10000 Bytes= 200000) 1 0 SORT (ORDER BY) (Cost=56 Card=10000 Bytes=2000 00) 2 1 TABLE ACCESS (FULL) OF 'PRAC' (C ost=6 Card=10000 Bytes=200000) select * from prac order by nazwisko; 0 SELECT STATEMENT Optimizer=CHOOSE (Cost=56 Card=10000 Bytes= 200000) 1 0 SORT (ORDER BY) (Cost=56 Card=10000 Bytes=2000 00) 2 1 TABLE ACCESS (FULL) OF 'PRAC' (C ost=6 Card=10000 Bytes=200000) select distinct nazwisko from prac; 0 SELECT STATEMENT Optimizer=CHOOSE (Cost=36 Card=10000 Bytes= 80000) 1 0 SORT (UNIQUE) (Cost=36 Card=10000 Bytes=80000) 2 1 TABLE ACCESS (FULL) OF 'PRAC' (C ost=6 Card=10000 Bytes=80000) select nazwisko from prac group by nazwisko; 0 SELECT STATEMENT Optimizer=CHOOSE (Cost=36 Card=10000 Bytes= 80000) 1 0 SORT (GROUP BY) (Cost=36 Card=10000 Bytes=8000 0) 2 1 TABLE ACCESS (FULL) OF 'PRAC' (C ost=6 Card=10000 Bytes=80000) we wszystkich przypadkach okazało się, że korzystniej jest wykonać TABLE ACCESS (FULL) niż biegać po indeksach pamiętamy, że liście indeksów tworzą listę dwukierunkową i stąd bierzemy row id. zad31###################################################### zad32########### Tabele IOT # Klastry ####################### zad33########### Tabele partycjonowane ####################### zad34###################################################### zad35###################################################### zad36###################################################### alter session set optimizer_goal = rule; set autotrace traceonly explain statistics select count(*) from zesp natural join prac; 0 SELECT STATEMENT Optimizer=RULE 1 0 SORT (AGGREGATE) 2 1 MERGE JOIN 3 2 SORT (JOIN) 4 3 TABL E ACCESS (FULL) OF 'PRAC' 5 2 SORT (JOIN) 6 5 TABL E ACCESS (FULL) OF 'ZESP' select count(*) from prac natural join zesp; 0 SELECT STATEMENT Optimizer=RULE 1 0 SORT (AGGREGATE) 2 1 MERGE JOIN 3 2 SORT (JOIN) 4 3 TABL E ACCESS (FULL) OF 'ZESP' 5 2 SORT (JOIN) 6 5 TABL E ACCESS (FULL) OF 'PRAC' nie ma indeksów na atrybucie połączeniowym więc NL odpadł w przedbiegach. ponieważ ten optymalizator nie zna HJ więc wykorzystał MJ zad37###################################################### create index zesp_id_zesp_idx on zesp(id_zesp); select count(*) from zesp natural join prac; 0 SELECT STATEMENT Optimizer=RULE 1 0 SORT (AGGREGATE) 2 1 NESTED LOOPS 3 2 TABLE ACCESS (FULL ) OF 'PRAC' 4 2 INDEX (RANGE SCAN) OF 'ZESP_ID_ZESP_IDX' (NON-UNIQUE) select count(*) from prac natural join zesp; 0 SELECT STATEMENT Optimizer=RULE 1 0 SORT (AGGREGATE) 2 1 NESTED LOOPS 3 2 TABLE ACCESS (FULL ) OF 'PRAC' 4 2 INDEX (RANGE SCAN) OF 'ZESP_ID_ZESP_IDX' (NON-UNIQUE) widac, ze dodanie indeksu na atrybucie polaczeniowym spowodowało, że relacje zesp (ta z indeksem) stała się relacją wewnętrzną w NL. widać, że jest to niezależne od kolejności tabel w zapytaniu. zad38###################################################### create index prac_id_zesp_idx on prac(id_zesp); select count(*) from zesp natural join prac; 0 SELECT STATEMENT Optimizer=RULE 1 0 SORT (AGGREGATE) 2 1 NESTED LOOPS 3 2 TABLE ACCESS (FULL ) OF 'PRAC' 4 2 INDEX (RANGE SCAN) OF 'ZESP_ID_ZESP_IDX' (NON-UNIQUE) select count(*) from prac natural join zesp; 0 SELECT STATEMENT Optimizer=RULE 1 0 SORT (AGGREGATE) 2 1 NESTED LOOPS 3 2 TABLE ACCESS (FULL ) OF 'ZESP' 4 2 INDEX (RANGE SCAN) OF 'PRAC_ID_ZESP_IDX' (NON-UNIQUE) po założeniu indeksu na atr. połączeniowym w tabeli prac optymalizator przestał mieszac w kolejności tabel i stosuje takią jaką podał użytkownik zad39###################################################### alter session set optimizer_goal = choose; select count(*) from zesp natural join prac; 0 SELECT STATEMENT Optimizer=CHOOSE (Cost=1 Card=1 Bytes=15) 1 0 SORT (AGGREGATE) 2 1 NESTED LOOPS (Cost=1 Card=818000 Bytes=12270000) 3 2 INDEX (FULL SCAN) OF 'ZESP_ID_ZESP_IDX' (NON-UNIQUE) (Cost=1 Card=409 Bytes=53 17) 4 2 INDEX (RANGE SCAN) OF 'PRAC_ID_ZESP_IDX' (NON-UNIQUE) select count(*) from prac natural join zesp; 0 SELECT STATEMENT Optimizer=CHOOSE (Cost=1 Card=1 Bytes=15) 1 0 SORT (AGGREGATE) 2 1 NESTED LOOPS (Cost=1 Card=818000 Bytes=12270000) 3 2 INDEX (FULL SCAN) OF 'ZESP_ID_ZESP_IDX' (NON-UNIQUE) (Cost=1 Card=409 Bytes=53 17) 4 2 INDEX (RANGE SCAN) OF 'PRAC_ID_ZESP_IDX' (NON-UNIQUE) są indeksy, więc zostały wykorzystane FULL SCAN zawsze na mniejszej tabeli, niezależnie on kolejności podanej przez użytkownika brak fizycznego dostępu do tabeli, wszystkie obliczenia na poziomie indeksów zad40###################################################### drop index PRAC_CZY_ETAT_BMP_IDX; drop index PRAC_ID_ZESP_IDX; drop index PRAC_NAZW_PLACA_IDX; drop index PRAC_PLACA_IDX ; drop index PRAC_PLEC_BMP_IDX; drop index ZESP_ID_ZESP_IDX; select nazwa, count(*) from prac natural join zesp group by nazwa; 0 SELECT STATEMENT Optimizer=CHOOSE (Cost=3014 Card=818000 Byt es=22086000) 1 0 SORT (GROUP BY) (Cost=3014 Card=818000 Bytes=2 2086000) 2 1 HASH JOIN (Cost=9 Card=818000 By tes=22086000) 3 2 TABLE ACCESS (FULL ) OF 'ZESP' (Cost=2 Card=409 Bytes=10225) 4 2 TABLE ACCESS (FULL ) OF 'PRAC' (Cost=6 Card=10000 Bytes=20000) nie mamy już indeksów na id_zesp, a opymalizator choose nigdy nie wybierze MJ więc obserwujemy wykorzystanie HJ. obserwujemy pełen dostęp do obu tabel + sortowanie na potreby group by zad41###################################################### zad42###################################################### zad43###################################################### zad44###################################################### zad45###################################################### zad46###################################################### zad47###################################################### zad48###################################################### zad49###################################################### zad50######################################################