Semantic view Case statement over base table current_ind

Database
Enthusiast

Semantic view Case statement over base table current_ind

Hi all,

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:

With Case statement:

REPLACE VIEW databasename.viewname1

AS

LOCK ROW FOR ACCESS

SELECT MPLH.MATL_PURCHASING_LOC_HIST_KEY,

MPL.MATERIAL_COLLECTION_KEY,

MPL.PURCHASING_LOC_KEY,

COCS.OAS_CONTROL_SEGMENT_NAME CURR_OAS_CONTROL_SEGMENT_NAME,

COBCS.OAS_BM_CONTROL_SEGMENT_NAME CURR_OAS_BM_CNTRL_SEGMENT_NAME,

CASE WHEN MPLH.CURRENT_IND = 'Y' THEN

'Current'

ELSE

'Non-Current'

END AS CURRENT_IND,

Without CASE statement:

REPLACE VIEW databasename.viewname2

AS

LOCK ROW FOR ACCESS

SELECT MPLH.MATL_PURCHASING_LOC_HIST_KEY,

MPL.MATERIAL_COLLECTION_KEY,

MPL.PURCHASING_LOC_KEY,

COCS.OAS_CONTROL_SEGMENT_NAME CURR_OAS_CONTROL_SEGMENT_NAME,

COBCS.OAS_BM_CONTROL_SEGMENT_NAME CURR_OAS_BM_CNTRL_SEGMENT_NAME,

MPLH.CURRENT_IND,

The queries that I run against both views are as follows:

sel * from databasename.viewname1

where current_ind='Y';

sel * from databasename.viewname2

where current_ind='current';

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? 

Thanks,

Joe