Are materialized views available with IQ16

[Former Member]

I had some issues creating a materialized view like this:

CREATE MATERIALIZED VIEW "DBA"."myView"
IN "IQ_SYSTEM_MAIN" AS
dateceiling(hh, timestampstart) as timestampstart_day,
dateceiling(dd, timestampstart) as timestampstart_hour,
datefloor(hh, timestampend) as timestampend_hour,
datefloor(dd, timestampend) as timestampend_day 
from myTable;

Part of error message is

 Materialized view definition must not use the following construct: 'Remote object'

Having a look onto SCN I found, that IQ15 was not supporting m. View. But on documentation for IQ16 I found several references. E.g. at SyBooks Online

Is IQ16 supporting materilized views and if so, what's wrong at my example SQL?

[former_member182090 · Accepted solution]

IQ does not support materialized views (MVs) - that is, not against IQ tables. You can only create MVs against SQL Anywhere tables in IQ.

The reason IQ does not support MVs on IQ tables is that you don't need them: MVs are an optimization for row-oriented databases. As IQ is column-oriented, the type of performance bottleneck that is addressed by MVs doesn't really apply: because IQ has a different storage and processing model, MVs are not needed (the same applies to SAP HANA BTW, which is also a column-oriented DBMS).

If you need the view name and column names of an MV in IQ, create it as a regular view instead.

[Former Member · Accepted solution]

I need  them in combination with RLV to speed up my already existing views

[saroj_bagai · Accepted solution]

it is documented in the docs, that materialized views are only supported on catalog tables(SA)

SyBooks Online

Creating a Materialized View

Define a SQL query, using the Create Materialized View wizard, to create a
new materialized view in the system store (IQ catalog store).

[Former Member · Accepted solution]

What have you done to tune your views and queries so far? I mean no offense but in most cases that I've found, materialized views tend to be less of performance bonus and more of the design issue.  YMMV however

[markmumy · Accepted solution]

I am quite interested in that answer.  We've not had access to MVs in Sybase until they were introduced in ASE and SA just recently.  I am curious to hear why they are needed, or not, from an EDW, data mart, ODS, big data, etc perspective.  My take was that we could get pretty near to MV performance in IQ with some simple tuning, in most cases, so having them was never really pushed.  And as Jason mentions, they tend to be a design aid/crutch that may add overhead and maintenance that could be unnecessary.

Mark

[Former Member · Accepted solution]

in the Oracle world, MV are often used when you have multiple layers of views.  Views on views on views and so on. 

From the front end perspective, a view and a table are effectively the same thing (some resource that contains data) although we know differently. There are numerous classes and tutorials that use views and tables interchangeably so I don't believe it is the developers at fault..  they often don't know better. 

All that being said, I'm not sure that MV are even relevant in respect to data warehouses in general let alone IQ.  If I'm wrong, please let me know with some sort of case example.

[Former Member · Accepted solution]

Well, in my case it would solve a task of serveral recomputing of values -- see my orignal example, but a little more complex. Of course, I could put that into a view or completly recompute on executing query. But I thought I would make more sense to store the data for easier and faster access.

I see that most (most likely every piece) of them could be done during ETL, but in my current scenario most of the ETL will be replace by continiuous flow via RLV on one hand and a hard to change application interface sending data on the other side.

[markmumy]

No. These are only available on catalog tables.

[Former Member]

So maybe a bug inside documentation?

[former_member182090]

As far as I know, CREATE MATERIALIZED VIEW is not listed in the IQ reference manual. Typically for these SA-only cases, if such a command is mentioned in the IQ documentation it will mention that it can be used  on catalog tables (=SA tables = "IN SYSTEM" tables).

[Former Member]

It's in the SAP Control Center documentation.

[saroj_bagai]

SAP control Center documention  does mention that :

Creating a Materialized View

Define a SQL query, using the Create Materialized View wizard, to create a
new materialized view in the system store (IQ catalog store).

[Former Member]

I never found SCC useful beyond consuming massive amounts of resources.  I'm looking forward to the rewrite.