OBIEE 11g Using FILTER Function Instead of CASE Statements
It
is well-known that CASE statements are causing low query performance and an
alternative performance-friendly function is FILTER, can be used instead. In
the Expression Builder, this function can be found under Functions >
Display Functions > Filter. Here is an example of how to use it:
Assuming there are two Logical Columns derived from the following expressions:
- Southern
Region Units:
CASE WHEN Paint.Markets.Region = ‘SOUTHERN REGION’ THEN Paint. SalesFacts.Units ELSE 0 END - Western
Region Units:
CASE WHEN Paint.Markets.Region = ‘WESTERN REGION’ THEN Paint. SalesFacts.Units ELSE 0 END
An equivalent FILTER expression:
- Southern
Region Units:
FILTER(Paint. SalesFacts.Units USING Paint.Markets.Region = ‘SOUTHERN REGION’) - Western
Region Units:
FILTER(Paint. SalesFacts.Units USING Paint.Markets.Region = ‘WESTERN REGION’)
CASE generated query (assuming all columns come from the
same table):
SELECT Year,
SUM(CASE WHEN Region = ‘SOUTHERN REGION’ THEN Units ELSE 0),
SUM(CASE WHEN product = ‘WESTERN REGION’ THEN Units ELSE 0)
FROM physical_table
GROUP BY year
SUM(CASE WHEN Region = ‘SOUTHERN REGION’ THEN Units ELSE 0),
SUM(CASE WHEN product = ‘WESTERN REGION’ THEN Units ELSE 0)
FROM physical_table
GROUP BY year
FILTER generated query:
SELECT Year,
SUM(CASE WHEN Region = ‘SOUTHERN REGION’ THEN Units),
SUM(CASE WHEN product = ‘WESTERN REGION’ THEN Units)
FROM physical_table
WHERE Region = ‘SOUTHERN REGION’ OR Region = ‘WESTERN REGION’
GROUP BY year
SUM(CASE WHEN Region = ‘SOUTHERN REGION’ THEN Units),
SUM(CASE WHEN product = ‘WESTERN REGION’ THEN Units)
FROM physical_table
WHERE Region = ‘SOUTHERN REGION’ OR Region = ‘WESTERN REGION’
GROUP BY year
The
major difference is in FILTER query's WHERE clause, which gets executed
first at the database level; where for CASE query, it executes line-by-line,
which impacts the performance.
No comments:
Post a Comment