Hi All,

I have a requirement of creating a view which is having joins on 9 tables. This view is used by Cognos team to fetch data for reporting.

For reporting purpose, they need mostly counts based on some where clauses.For one of those where clauses the query on view is taking too long. This where clause contains a derived column. Even if I create index on the base column, the query dosen't speeds up.

The query which they are firing on view is:

select count(*) from test_view where col2='Approved';

This col2 is derived as follows:

Coalesce((CASE col1 When 'Y' Then 'Approved' When 'N' Then 'Rejected' Else NULL End),'Pending') col2

Please guide as to how can i increase the query efficiency.

Thanks in adv

shalini11, First, this has nothing to do with your performance issue but I would remove the COALESCE and replace NULL with 'Pending':

CASE col1 WHEN 'Y' THEN 'Approved' WHEN 'N' THEN 'Rejected' ELSE 'Pending' END AS col2

There is just extra work by having the CASE set a NULL and then using a COALESCE to replace the NULL with a value when the CASE could set the value in the first place.

Second, as you found out, creating an Index on the base column but using a derived (or converted) value from that column does not use the index (because the derived value is not indexed).

I don't know if this will help or not but you can return both the base value (col1) and the derived value (col2) in the View and then change the query on the View to use the base value (col1) in the WHERE clause and the derived value (col2) in the SELECT clause. This should allow the Index to be use by the WHERE clause but still display the user friendly text string for the report. (But if all that is being done is COUNT(*) you don't really need the derived/converted value although it may be needed by other queries.)