Materialized Views

[former_member203645]

Hi all,

We are using sybase iq  15 ,

Is it possible to create Materialized view , right now we have views in sybase iq and they are really slow .

Please confirm will the Materialized view have better performance compare to Views .

If YES , I tried creating but it is throwing erro.

The materialized view 'TESTMV (GHT)' could not be created in the database.

Syntax error near 'DIMREP' -- Materialized view definition must not use the following construct: 'Remote object'

[Sybase][ODBC Driver][Sybase IQ]Syntax error near 'DIMREPPRODUCT' -- Materialized view definition must not use the following construct: 'Remote object'

SQLCODE: -1031

SQLSTATE: HY000

SQL Statement: CREATE MATERIALIZED VIEW "GHT"."TESTMV"( /* view_column_name, ... */ )

IN "IQ_USER_MAIN" AS

select code from ght.dimrepproduct

Message was edited by: RUC .

[saroj_bagai · Accepted solution]

IQ doesn't support materilaized views. Materialized views  are supported by SA, you can try to create in system (catalog db), but we don't recommend using catalog db for  user tables and views

[c_baker]

Materialized views are not supported in IQ 15.

What you should probably do is turn on index advisor to understand where the slowness might be occurring in the underlying query and implement additional indexes as required.

If the view remains slow after indexing, then this could be a case where implement the view as an aggregate table might be practical.

Chris

[Former Member]

If you are using Sybase IQ 15.x you can use "join index".  I think join indexes were dropped on IQ 16.  Be sure to use HG index on the join columns on the base tables.

Hope this help.