Enabling Double Column support and query performance improvement.

Question

Hi,

In my environment, dimension and fact tables are indexed on column_IDs or code values (and I do not have much control over the physical design).

Considering ad-hoc analysis, I would like to get an idea with regards to enabling Double Column support and its impact on query performance improvement. OBI EE Help does not directly mention query performance improvement as one of the usage. I did a small prototype in development where explain plan showed differences in values (Double Column supported SQL query showed less cost) however, the execution time was not that significant, considering the amount of data in this environment. (Running this in Live, is a little possibility as I need to prove 'good' improvements in development first after enabling the support-feature.) One of the pre-fat environments showed similar pattern, when the cost values were quite different, but the execution time was more or less the same.

Therefore, in general, I would like to know whether or not enabling Double Column support is considered one of the good practices if language-independent filtering and modelling spatial columns is not a need.

Thank you.

Regards,

Manoj.

Manoj Dixit · Answer

Thanks Christian for giving this insight.

Regards,

Manoj.

[Deleted User] · Answer

Good answer fro Robin as usual I'd just like to add one thing:

Be careful! Using Descriptor ID functionality will break certain advanced front end functionality like nested FILTER USING or AGGREGATE AT in OBIPS column formulas.

rmoff · Answer

As a 'rule of thumb', queries that hit predicates on their indexed key column rather than full text description are going to be better. Or rather, wouldn't expect it to be worse.

If you're not testing with decent volumes of data, then the elapsed duration you see may not be that different. If you're expected to prove benefit before the code change is accepted, you need to have a proper environment on which you can prove it