👿 NULL-e: małe, podstępne diabełki. Na pewno znasz je DOBRZE? 👿

w dniu 




Schemat: HR
Wersja: Oracle Database 23ai Free Release 23.0.0.0.0 - Develop, Learn, and Run for Free


Na linked in opublikowałam jakiś czas temu quiz z pytaniem:

👿 NULLs: the sneaky little devil. Do you know them WELL? 👿

You create a B-tree index on a nullable column. Will rows with NULL values be indexed?


A poniżej odpowiedzi:

0%
83%
17%


A jaka jest prawidłowa odpowiedź? 
Otóż WSZYSTKIE odpowiedzi są (w jakimś sensie) - prawidłowe. Tak jak jedna osoba skomentowała quiz - TO ZALEŻY :)

Zacznijmy od najbardziej popularnej odpowiedzi czyli - NULL się nie liczy.
Null w bazie danych ORACLE traktowany jest w sposób specjalny, bardziej jako NIE WIEM niż NIC. Z tego też powodu wiersze mające w indeksowanej za pomocą indeksu B-Tree kolumny - NULL, nie znajdą się w tym indeksie. Baza by wyszukać taki wiersze będzie musiała wykonać FULL TABLE SCAN, nawet w przypadku wysokiej selektywności kolumny.


Spójrzmy na poniższy przykład:
create table emp as
select  * from employees;

select employee_id, manager_id
from emp where manager_id is null;

EMPLOYEE_ID MANAGER_ID
----------- ----------
        100

Zapytanie zwraca tylko jeden wiersz. Wydaje się więc być dobrym kandydatem na założenie indeksu b-tree. 
create index i on emp(manager_id);

select employee_id, manager_id 
from emp where manager_id = 124;

--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |     1 |     6 |     3   (0)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| EMP  |     1 |     6 |     3   (0)| 00:00:01 |
-----------------------------

select employee_id, manager_id
from emp where manager_id is null;
--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |     2 |    12 |     3   (0)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| EMP  |     2 |    12 |     3   (0)| 00:00:01 |
--------------------------------------------------------------------------
Założyliśmy indeks na kolumnie MANAGER_ID.
Zapytanie dla MANAGER_ID = 124 faktycznie korzysta bez problemu z indeksu.
Jednak zapytanie dla MANAGE_ID IS NULL - musi już wykonać pełen przegląd tabeli. 
Wiersz z wartością NULL nie załapał się do indeksu.

Ale czy NULLe mogą się jednak jakoś wślizgnąć do indeksu B-Tree?
A oczywiście, to sprytne bestie. I to mają na to kilka sposobów.
Jeśli mamy taką sytuację, że kolumna jest selektywna i chcielibyśmy móc wyszukiwać wiersze NULL z wykorzystaniem indeksu - jest na to kilka sposobów:

Indeks złożony

Możemy wykorzystać INDEKS złożony. Jako drugą kolumnę wybieramy kolumnę NOT NULL albo - PIEROGI (lub inny dowolny literał).

drop index i;
create index i on emp(manager_id, 'Pierogi');

select employee_id, manager_id 
from emp where manager_id is null;

--------------------------------------------------------------------------------------------
| Id  | Operation                           | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                    |      |     2 |    12 |     2   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID BATCHED| EMP  |     2 |    12 |     2   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN                  | I    |     2 |       |     1   (0)| 00:00:01 |
--------------------------------------------------------------------------------------------

Jeśli ZESTAW kolumn w indeksie złożonym nie będzie NULL - czyli jeśli chociaż jedna kolumna będzie miała wartość - wiersz ten znajdzie się w naszym indeksie. Ale o co chodzi z pierogami? Tutaj jako drugą kolumnę zaindeksowaliśmy LITERAŁ, coś, co zawsze ma wartość. Wymusi to zaindeksowanie tego wiersza przez Oracle. 
select 'Pierogi', manager_id
from emp;

PIEROGI MANAGER_ID
------- ----------
Pierogi           
Pierogi        100
Pierogi        100
Pierogi        102
Pierogi        103
Pierogi        103


FBI

Function Based Index
Kolejnym rozwiązaniem może być zastosowanie INDEKSU B-TREE FUNKCYJNEGO czyli założonego na funkcji. Możemy wykorzystać dowolną funkcję obsługującą NULL - na przykład NVL. 
drop index i;

select nvl(manager_id, 0) , manager_id 
from emp 
where manager_id is null;

NVL(MANAGER_ID,0) MANAGER_ID
----------------- ----------
                0           

create index i on emp(nvl(manager_id, 0));

select employee_id, manager_id
from emp 
where manager_id is null;
--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |     1 |     8 |     3   (0)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| EMP  |     1 |     8 |     3   (0)| 00:00:01 |
--------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------

select employee_id, manager_id
from emp 
where nvl(manager_id, 0) = 0 ;
--------------------------------------------------------------------------------------------
| Id  | Operation                           | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                    |      |     1 |    21 |     2   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID BATCHED| EMP  |     1 |    21 |     2   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN                  | I    |     1 |       |     1   (0)| 00:00:01 |
--------------------------------------------------------------------------------------------

Trzeba jednak zwrócić uwagę, że w warunku WHERE musimy podać całe wyrażenie użyte do indeksu. Jeśli wyszukamy WHERE MANAGER_ID IS NULL - Oracle w dalszym ciągu będzie wyszukiwał za pomocą Full Table Scan. Warto o tym pamiętać.

MSSQL

Dociekliwe osoby zauważyły zapewne, że w pytaniu nie napisałam O JAKĄ BAZĘ CHODZI. Większość założyła (i słusznie), że chodzi o bazę danych Oracle.
Ale co jeśli podobne pytanie padłoby w kontekście bazy danych MSSQL Server?
Można by powiedzieć - baza to baza. Ale jednak bazy różnią się w szczegółach.
W bazie MSSQL - w indeksach B-TREE jak najbardziej znajdziemy wiersze z wartością NULL!

https://sqlfiddle.com/sql-server/online-compiler?id=0ff73921-82e1-42c1-89b3-3ab4eb53effc
Create table emp 
(emp_id numeric,
 last_name varchar(100),
 manager_id numeric);
 
GO
INSERT INTO emp (emp_id, last_name, manager_id)
SELECT TOP (100000)
       ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) + 5,
       'Test',
       2
FROM sys.objects a
CROSS JOIN sys.objects b;
GO

-- create index
CREATE INDEX IX_emp_manager_id ON emp(manager_id);
GO

UPDATE STATISTICS emp;
GO

SET SHOWPLAN_ALL ON; 
GO

-- query null
SELECT * FROM emp   WHERE manager_id IS NULL ; 
GO

SET SHOWPLAN_ALL OFF;
GO

Plan wykonania: 
	--Nes	ed Loops(Inner Join, OUTER REFERENCES:([Bmk1000])) 1 2 1 Nested Loops Inner Join OUTER REFERENCES:([Bmk1000]) NULL 1.0 0.0 4.1799999E-6 79 6.5703802E-3 [db_44b4usptx_44bbwzjnr].[dbo].[emp].[emp_id], [db_44b4usptx_44bbwzjnr].[dbo].[emp].[last_name], [db_44b4usptx_44bbwzjnr].[dbo].[emp].[manager_id] NULL PLAN_ROW 0 1.0
|	-Index Seek(OBJECT:([db_44b4usptx_44bbwzjnr].[dbo].[emp].[IX_emp_manager_id]), SEEK:([db_44b4usptx_44bbwzjnr].[dbo].[emp].[manager_id]=NULL) ORDERED FORWARD) 1 3 2 Index Seek Index Seek OBJECT:([db_44b4usptx_44bbwzjnr].[dbo].[emp].[IX_emp_manager_id]), SEEK:([db_44b4usptx_44bbwzjnr].[dbo].[emp].[manager_id]=NULL) ORDERED FORWARD [Bmk1000], [db_44b4usptx_44bbwzjnr].[dbo].[emp].[manager_id] 1.0 0.003125 0.0001581 24 0.0032831 [Bmk1000], [db_44b4usptx_44bbwzjnr].[dbo].[emp].[manager_id] NULL PLAN_ROW 0 1.0
|	-RID Lookup(OBJECT:([db_44b4usptx_44bbwzjnr].[dbo].[emp]), SEEK:([Bmk1000]=[Bmk1000]) LOOKUP ORDERED FORWARD) 1 5 2 RID Lookup RID Lookup OBJECT:([db_44b4usptx_44bbwzjnr].[dbo].[emp]), SEEK:([Bmk1000]=[Bmk1000]) LOOKUP ORDERED FORWARD [db_44b4usptx_44bbwzjnr].[dbo].[emp].[emp_id], [db_44b4usptx_44bbwzjnr].[dbo].[emp].[last_name] 1.0 0.003125 0.0001581 70 0.0032831 [db_44b4usptx_44bbwzjnr].[dbo].[emp].[emp_id], [db_44b4usptx_44bbwzjnr].[dbo].[emp].[last_name] NULL PLAN_ROW 0 1.0

Jak widzicie - w MSSQL nic nie trzeba robić, by wymusić indeksowanie wiersza z wartością NULL. To po prostu tutaj działa :)

Komentarze

Prześlij komentarz