How to set IQ FP index default created as FLAT FP?

[former_member232292]

Dear Experts,

As we know -- In IQ 15.x -- if we turn off the global option "minize storage" -- Then the following created FP indexes will be created default as FLAT FP. Yet, I found in IQ16.x -- even I turned it off -- the FP index still created as bitmap FP not FLAT one-- so it leads to some table join incorrect resultset...

Would you please tell me how to set the options so that the FP indexes can be created as FLAT default on IQ16.x?

Thanks a lot

Regards

Eisen

[RobertWaywell]

If you are getting incorrect results, then you need to open a Support issue to diagnose the problem. The fact that that FP index was created using a bitmap structure would not change the values in the index and should have no impact on the correctness of the result set.

What exact version of SAP IQ 16.x are you using?

[former_member232292 · Accepted solution]

Dear Robert,

Thanks for your comments -- Yes, customer had raised lots of incident on the incorrect join result since 15.2 to 16.0... Yet -- so many years passed, no version can fix that. The current situation is --

On 15.x -- if turn off "Hash join", the merge-join and nl-join will return correct result with Nbit FP

On 16.x -- even the most recent 16.1 sp4 -- both hash-join and merge-join will return incorrect result with Nbit FP -- only NL-join can return correct result.

And -- if we recreated the table with FLAT FP -- then no matter 15.x or 16.x, hash-join, merge-join and NL-join all return correct result ...

So I need a workaround to auto create column ans FLAT FP before this issue fixed... 🙂

Regards

Eisen

[markmumy]

Eisen,

There should never be a reason to turn off the optimized FPs. They provide so much benefit that it just makes sense to keep them on. In fact, we even document that you should not disable the n-bits. If you have issues with query results, that is likely a bug and should be addressed via a support case.

You can turn off the optimized n-bit by using "IQ UNIQUE(0)" when creating the table, on a per column basis.

https://help.sap.com/viewer/6754c6c35da941ba96ae9cfd3485176b/16.1.5.0/en-US/96524712b1ce4c2e9b3db303...

Using IQ UNIQUE(0) will disables all FP or NBit optimizations and force usage of a flat FP. Do not use this setting.

Mark

[former_member232292]

Dear Mark,

Thanks. I know use "unique" in "create table " statement can create FLAT FP... But that needs customer to recoding nearly all their codes -- that's horrible. So currently -- customer still keep working on IQ 15.x and turn off that "minize storage"... 😞

Regards

Eisen