Oracle 26ai introduced a new clause for aggregate functions - FILTER. Filter allows aggregates to be calculated in a single query for different filtering conditions.
Let's see how it works.
Test data
We create a simple table T and load some data into it, 100,000,000 rows.
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;
The old-style query - CASE
Let's now count the number of rows where col = 10 and at the same time the number of rows where col = 20.
In Oracle versions older than 26ai, we had to use the CASE clause for this.
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
Using the FILTER clause
In version 26ai, we get a new feature, the FILTER clause, which simplifies this construction somewhat.
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
Looks good. But what about performance?
In my tests on 100,000,000 rows, the query using the CASE clause turns out to be twice as fast! (Of course, results may be slightly different with other data.)
The average execution time for the query using the FILTER clause in my tests is 6-8 seconds, while the query using the CASE clause takes 3-4 seconds.
But why? Shouldn't these be the same query?
Let's check whether the FILTER transformation actually converts the queries to a CASE clause.
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. The queries are identical.
Comparing the execution plans
Maybe the Optimizer generated different execution plans?
Plan for the query using 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 for the query using the FILTER clause.
--------------------------------------------------------------------------- | 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("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) appears next to the TABLE ACCESS FULL operation. For the FILTER clause, this parameter is missing. What does it mean?
I assume this is related to whether a row array can be created and passed to the parent node (for vector processing), instead of passing the data row by row. The latter approach is, obviously, slower.
It looks like when the FILTER clause is used, rows are passed one by one, which can have a significant impact on performance with large tables.
A bug?
With the help of Nigel Bayliss, a bug was reported:
Bug 40060415 - QUERY WITH FILTER CLAUSE SLOWER THAN CASE STATEMENT EQUIVALENT - NOT USING ROWSETS
So be careful with this clause when working with large amounts of data :)
Komentarze
Prześlij komentarz