ENG
Null - jedni go lubią inni próbują konsekwentnie unikać. Jednak chciał czy nie chciał - null jest stałym elementem bazodanowego krajobrazu.
Null - jedni go lubią inni próbują konsekwentnie unikać. Jednak chciał czy nie chciał - null jest stałym elementem bazodanowego krajobrazu.
Czy null sprawia problemy? A i owszem, potrafi wyskoczyć jak filip z konopi i nieco namieszać. Ale traktowany odpowiednio pozwoli się zaprząc do każdej roboty! Nie trzeba się go bać, wystarczy go nieco oswoić.
Jednym z częstszych problemów jest brak możliwości indeksowania nulla. Czasem byśmy chcieli wyszukać rekordy z polem nulowym. Oczywiście można, niestety czeka nas wtedy nieco dłuższe oczekiwanie - Oracle w poszukiwaniu nulla przeszuka wszystkie rekordy tabeli (full scan).
Przyjrzyjmy się nulowi na przykładzie schematu HR .
W tabeli EMPLOYEES mamy kolumnę MANAGER_ID wskazująca na ID managera. Prezes rzecz jasna nie ma przełożonego - więc kolumnę tę ma pustą (null).
Załóżmy na kolumnie MANGER_ID index (index ten domyślnie jest utworzony w schemacie HR).
create index EMP_MANAGER_IX on employees( manager_id);
Sprawdźmy, czy wyszukiwanie po indexie zadziała dla zaindeksowanej wartości:
EXPLAIN PLAN FOR select * from employees where manager_id = 148;
select * from table(dbms_xplan.display);
------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 6 | 456 | 2 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID BATCHED| EMPLOYEES | 6 | 456 | 2 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | EMP_MANAGER_IX | 6 | | 1 (0)| 00:00:01 |
------------------------------------------------------------------------------------------------------
Konkretna wartość kolumny MANAGER_ID została wyszukana po założonym indexie.
Co jeśli będziemy chcieli wyszukać prezesa, który kolumnę MANAGER_ID ma null?
EXPLAIN PLAN FOR select * from employees where manager_id is null;
select * from table(dbms_xplan.display);
-------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 76 | 3 (0)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| EMPLOYEES | 1 | 76 | 3 (0)| 00:00:01 |
-------------------------------------------------------------------------------
Niestety - wartości null nie są indeksowane, więc każde wyszukanie nulla będzie kończyło się przeszukaniem całej tabeli - full scan.
Niemniej nie rozdzierajcie szat i nie usuwajcie każdego nulla ze swojego systemu! To że coś jest niemożliwe nie znaczy, że się nie da! Da się, oczywiście, i to na kilka sposobów!
Sposób 1
Podczas tworzenia indexu - jako dodatkową indeksowaną wartość należy dodać stałą. Stała ta zagwarantuje, że zaindeksowane będą wszystkie wiersze w tabeli, również te, które są nulowe.
create index mgr_id_1_idx on employees( manager_id, 1);
EXPLAIN PLAN FOR select * from employees where manager_id is null;
select * from table(dbms_xplan.display);
----------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 76 | 2 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID BATCHED| EMPLOYEES | 1 | 76 | 2 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | MGR_ID_1_IDX | 1 | | 1 (0)| 00:00:01 |
----------------------------------------------------------------------------------------------------
No i proszę, zmusiliśmy bazę do użycia indeksu przy wyszukiwaniu nulla! Koniec z full scanem!
Sposób 2
Do zaindeksowania wartości null można również wykorzystać index funkcyjny i po prostu podmienić null na konkretną wartość.
UWAGA! Podczas wyszukiwania należy użyć dokładnie funkcji użytej do tworzenia indeksu.
create index mgr_id_nvl_idx on employees (nvl2(manager_id, 1, 0)) ;
EXPLAIN PLAN FOR select * from employees where manager_id is null;
select * from table(dbms_xplan.display);
-------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 76 | 3 (0)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| EMPLOYEES | 1 | 76 | 3 (0)| 00:00:01 |
-------------------------------------------------------------------------------
EXPLAIN PLAN FOR select * from employees where (nvl2(manager_id, 1, 0)) = 0;
select * from table(dbms_xplan.display);
------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 76 | 2 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID BATCHED| EMPLOYEES | 1 | 76 | 2 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | MGR_ID_NVL_IDX | 1 | | 1 (0)| 00:00:01 |
------------------------------------------------------------------------------------------------------
Voila - null pokonany :)
Sposób 3
Sposób trzeci choć działa, nie do każdego systemu będzie odpowiedni. Gdyż albowiem sposób trzeci to użycie indeksu bitmapowego. Indeks bitmapowy jest mniej wybredny niż B*Tree i indeksuje wszystko jak leci, w tym nulle. Także tutaj możemy go dla przykładu zastosować, ale w praktyce na takiej kolumnie byśmy takiego indeksu nie zakładali. Dlaczego? A to już materiał na całkiem nowy post :)
create bitmap index mgr_id_btm_idx on employees (manager_id);
EXPLAIN PLAN FOR select * from employees where manager_id is null;
select * from table(dbms_xplan.display);
------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 76 | 1 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID BATCHED| EMPLOYEES | 1 | 76 | 1 (0)| 00:00:01 |
| 2 | BITMAP CONVERSION TO ROWIDS | | | | | |
|* 3 | BITMAP INDEX SINGLE VALUE | MGR_ID_BTM_IDX | | | | |
Także i tutaj widzimy wykorzystanie indeksu do wyszukiwania.
Null owiany jest złą sławą - że kłopotliwy, że niewydajny. Jedna we wprawnych rękach programisty będzie zachowywał się potulnie i nie będzie sprawiał żadnych problemów!
Komentarze
Prześlij komentarz