W Oracle 26ai została wprowadzona nowa klauzula dla funkcji agregujących - FILTER. Filter umożliwia wyliczanie agregatów w jednym zapytaniu dla różnych warunków filtracji.
Plan dla zapytania z klauzulą FILTER.
Plany wykonań wyglądają identycznie. Z jednym drobnym szczegółem w sekcji Projection: dla zapytania z klauzulą CASE pojawia się adnotacja
Zobaczmy jak to działa.
Dane testowe
Tworzymy prostą tabelkę T i ładujemy do niej trochę danych, 100 000 000 wierszy.
drop table if exists t; create table t as with gen_rows as ( select mod(rownum, 30) as col from dual connect by level <= 1000000), q as ( select mod(rownum, 30) as col from dual connect by level <= 100) select rownum id, gen_rows.col col from gen_rows, q;
Zapytanie w starym stylu - CASE
Policzymy teraz ilość wierszy gdzie col = 10 oraz jednocześnie ilość wierszy gdzie col = 20.
W wersjach Oracle starszych niż 26ai trzeba było w tym celu wykorzystać klauzulę CASE
select count(*), count( case when col = 10 then 1 else null end ) cnt_10, count( case when col = 20 then 1 else null end) cnt_20 from t; COUNT(*) CNT_10 CNT_20 ---------- ---------- ---------- 100000000 3333400 3333300
Wykorzystanie klauzuli FILTER
W wersji 26ai dostajemy nową funkcjonalność, klauzulę FILTER, upraszczającą nieco tę konstrukcję.
select count(*), count(*) filter (where col = 10) cnt_10, count(*) filter (where col = 20) cnt_20 from t; COUNT(*) CNT_10 CNT_20 ---------- ---------- ---------- 100000000 3333400 3333300
Wygląda nieźle. A co z wydajnością?
Z moich testów na 100 000 000 wierszy wychodzi, że zapytanie z klauzulą CASE jest dwa razy szybsze! (oczywiście na innych danych wyniki mogą być nieco inne).
Średnie czasy zapytania z klauzulą FILTER w moich testach to 6-8 sekund, zaś zapytania z klauzulą CASE - 3-4 sekundy.
Ale dlaczego? Czy to nie powinno być to samo zapytanie?
Sprawdźmy, czy transformacja FILTER faktycznie zamienia zapytania na klauzulę CASE.
begin SYS.DBMS_SQLDIAG.DUMP_TRACE(p_sql_id => '89r73qnvn5rjh', p_child_number => 0, p_component => 'Compiler', p_file_id => 'trace_filter'); end; /
Query after transformation ***************************** SELECT COUNT(*) "COUNT(*)", COUNT(CASE WHEN "EMP"."DEPARTMENT_ID"=10 THEN 1 ELSE NULL END ) "CNT_10", COUNT(CASE WHEN "EMP"."DEPARTMENT_ID"=20 THEN 1 ELSE NULL END ) "CNT_20" FROM "HR"."EMP" "EMP";
Nope. Zapytania są identyczne.
Porównanie planów wykonania
Może Optymalizator wygenerował inne plany wykonania?
Plan dla zapytania z CASE.
--------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | --------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | | | 52852 (100)| | | 1 | SORT AGGREGATE | | 1 | 3 | | | | 2 | TABLE ACCESS FULL| T | 100M| 286M| 52852 (1)| 00:00:03 | --------------------------------------------------------------------------- Column Projection Information (identified by operation id): ----------------------------------------------------------- 1 - (#keys=0) COUNT(CASE "COL" WHEN 20 THEN 1 ELSE NULL END )[22], COUNT(CASE "COL" WHEN 10 THEN 1 ELSE NULL END )[22], COUNT(*)[22] 2 - (rowset=256) "COL"[NUMBER,22]
Plan dla zapytania z klauzulą FILTER.
--------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | --------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | | | 52852 (100)| | | 1 | SORT AGGREGATE | | 1 | 3 | | | | 2 | TABLE ACCESS FULL| T | 100M| 286M| 52852 (1)| 00:00:03 | --------------------------------------------------------------------------- Column Projection Information (identified by operation id): ----------------------------------------------------------- 1 - (#keys=0) COUNT(CASE "COL" WHEN 20 THEN 1 ELSE NULL END )[22], COUNT(CASE "COL" WHEN 10 THEN 1 ELSE NULL END )[22], COUNT(*)[22] 2 - "COL"[NUMBER,22]
(rowset=256) przy operacji TABLE ACCESS FULL. W przypadku klauzuli FILTER ten parametr znika. Co to oznacza?
Zakładam, że wiąże się to z tym, czy da się utworzyć tablicę wierszy do przekazania do węzła nadrzędnego (na potrzeby przetwarzania wektorowego), zamiast przekazywać dane wiersz po wierszu. Ten drugi sposób jest, co oczywiste, wolniejszy.
Wygląda na to, że w przypadku użycia klauzuli FILTER wiersze przekazywane są wiersz po wierszu, co przy dużych tabelach może mieć znaczny wpływ na wydajność.
Błąd?
Dzięki pomocy Nigel Bayliss został zgłoszony błąd:
Bug 40060415 - QUERY WITH FILTER CLAUSE SLOWER THAN CASE STATEMENT EQUIVALENT - NOT USING ROWSETS
Także ostrożnie z tą klauzulą na dużych ilościach danych :)
Komentarze
Prześlij komentarz