Semantic view Case statement over base table current_ind
We have a history table in our base layer that is defined with a current_ind = Y/N. A view has been created for the business in the semantic layer that redefines the current_ind in a case statement as 'current' or 'not current'. Current_ind statistics in the base table are current. I wanted to see if the optimizer loses stats as a result of the case statement, so I created another semantic layer view that removes the case statement. Instead of the case statement the view simply exposes current_ind. As I expected, dbql shows an explain that is much worse when the view with the case statement is run compared to the view exposing just current_ind. To illustrate, here are the pertinent parts of the two views:
The queries that I run against both views are as follows:
sel * from databasename.viewname1
sel * from databasename.viewname2
The second query runs twice as long and the DBQL entries show how much more resources are used when executing the view with the CASE statement. I won't list the explain plan here as it is quite long. Has anyone else run into this type of a scenario where BO wants to present a more descriptive filter but in doing so causes a query to be executed that cannot use the stats on the underlying table? I wonder if it would be possible to use the view without the case statement, let BO filter on current_ind = 'current' or 'not current', but set current_ind=Y/N in the query that executes on the database?