.jpg)
Schemat: HR
Wersja bazy danych: Oracle AI Database 26ai Free Release 23.26.1.0.0 - Develop, Learn, and Run for Free
Przygotowywałam ostatnio prezentację "Why the f*** Oracle does not use my index! [*fabulous]. Pracowałam nad skryptami DEMO i w pewnym momencie - wkurzyłam się - zapytanie, które miało wykorzystać ścieżkę dostępu INDEX SCAN - uparcie robiło FULL TABLE SCAN. W sumie fajny case, taki pasujący do tematu - ale to ja miałam zaskakiwać słuchaczy wnikliwymi przypadkami a nie przypadki zaskakują mnie!
Po kolei:
Utworzyłam index I na kolumnie JOB_TITLE w tabeli JOBS.
create index IF NOT EXISTS i on jobs(job_title); Index I created.
Jednak moje zapytanie wybiera FULL TABLE SCAN.
select job_id,
job_title
from jobs
where job_title = 'Programmer';
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 27 | 3 (0)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| JOBS | 1 | 27 | 3 (0)| 00:00:01 |
--------------------------------------------------------------------------
Naliczmy może statystyki, to nie zaszkodzi.
exec dbms_stats.GATHER_TABLE_STATS('HR', 'JOBS'); PL/SQL procedure successfully completed.
Nope.
select job_id,
job_title
from jobs
where job_title = 'Programmer';
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 27 | 3 (0)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| JOBS | 1 | 27 | 3 (0)| 00:00:01 |
--------------------------------------------------------------------------
Może clustering factor namieszał? Zerknijmy.
select clustering_factor from user_indexes where index_name = 'I'; CLUSTERING_FACTOR ----------------- 7
Clustering Factor niski. To nie to.
Żyjemy w dobie AI prawda? Zapytajmy Agenta!
Dlaczego zapytanie
select job_id,
job_title
from jobs
where job_title = 'Programmer';
nie korzysta z indeksu - przeanalizuje skrypt tworzący indeks oraz bazę danych db26_hr
Zapytanie nie korzysta z indeksu, ponieważ:
Tabela JOBS ma tylko 19 wierszy (jest bardzo mała).
1. Oracle optymalizator uznaje, że pełny skan tabeli (full table scan) jest szybszy niż użycie indeksu przy tak małej liczbie rekordów.
2. Brak widocznych indeksów na kolumnie JOB_TITLE w metadanych (user_ind_columns zwraca pusty wynik).
3. Indeks o nazwie I lub TEST_IDX_JOB_TITLE nie jest widoczny w user_indexes – prawdopodobnie nie został utworzony lub jest niewidoczny dla optymalizatora.
Podsumowanie: Oracle nie używa indeksu, bo tabela jest bardzo mała i/lub indeks nie istnieje lub nie jest widoczny w metadanych. To typowe zachowanie optymalizatora dla małych tabel.
Spójrzcie na punkt 2. BRAK widocznych indeksów? Jak to nie ma indeksu? Przecież utworzyłam indeks i nawet dostałam komunikat Index I created. Do tego przecież indeks sprawdziliśmy Clustering Factor tego indeksu!
Zerknijmy sami.
select * from user_ind_columns where table_name = 'JOBS' and column_name = 'JOB_TITLE'; no rows selected
Czyli jest ale nie ma.
Zapytajmy jeszcze raz AI.
1. nie wykonało się skutecznie. Najbardziej prawdopodobne przyczyny:Składnia IF NOT EXISTS nie została rozpoznana przez parser SQL (mimo wersji 23c, może być wyłączona lub wymaga innych ustawień).
2. Indeks o nazwie I był już kiedyś utworzony i usunięty, a jego metadane nie zostały poprawnie odświeżone.
3. Wystąpił błąd podczas tworzenia indeksu, ale nie został zgłoszony w sesji (np. brak commit, problem z uprawnieniami, konflikt nazw).
4. Indeks został utworzony w innym schemacie lub na innej tabeli.
Podsumowanie: Oracle nie utworzył indeksu I, bo polecenie nie zostało poprawnie wykonane lub nie jest obsługiwane w Twoim środowisku. Użyj klasycznej składni bez IF NOT EXISTS i sprawdź, czy indeks się pojawi.
Punkty 1-3 raczej mnie nie przekonały, ale już punkt 4 - może coś w tym jest. Sprawdźmy.
select table_name,
clustering_factor
from user_indexes
where index_name = 'I';
TABLE_NAME CLUSTERING_FACTOR
------------------------
EMPLOYEES 7
I bingo!
Indeks nie został utworzony na tabeli JOBS gdyż indeks o nazwie I już istniał, tylko na innej tabeli!
Składnia IF[NOT] EXIST nie sprawdza "poprawności" polecenia. Jeśli indeks o danej nazwie już istnieje, nawet jeśli to jest inna tabela - polecenie zakończy się sukcesem. I będziemy zadowoleni, że mamy indeks na tabeli JOBS - a nasze zapytania będą wykonywać FULL TABLE SCAN.
Funkcjonalność jest bardzo przydatna, ale bardziej niż kiedykolwiek musimy uważać na stosowanie Dobrych Praktyk, w szczególności dbać o prawidłowe nazewnictwo obiektów. Jeśli przyjmiemy standard nazewniczy i będziemy tworzyć nazwy indeksów o stałej strukturze - unikniemy tego typu pułapek.
Jednym z bardziej popularnych standardów jest zawieranie w nazwie indeksu - nazwy lub aliasu tabeli oraz nazw lub aliasów kolumn wchodzących w skład indeksu. Na przykład nasz indeks mógłby się nazywać: JOB_TITLE_IDX. Dzięki takiej strukturze - tego typu pomyłki jak w naszym przykładzie zostałyby wyeliminowane.
create index IF NOT EXISTS job_title_idx on jobs(job_title);
Index I created.
select job_id,
job_title
from jobs
where job_title = 'Programmer';
--------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 27 | 2 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID BATCHED| JOBS | 1 | 27 | 2 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | I | 1 | | 1 (0)| 00:00:01 |
--------------------------------------------------------------------------------------------
I w końcu sukces - oczekiwany INDEX SCAN!
Komentarze
Prześlij komentarz