Sybase and materialized views

Post by supergel
Hi
I'm new to Sybase from Oracle. Does Sybase have anything equivalent to
materialzed views? If not want is the best practice for creating views with
huge amounts of data? Other than indexing the tables.
Thanks for help.

For those not familiar with Oracle materialized views ... they are
tables (not views) that refresh themselves based on a SQL statement
based on criteria supplied during their creation. Multiple materialized
views can be made to refresh as part of a single transaction so as to
guarantee their data consistency.

I too am looking for the answer.

--
Daniel A. Morgan
http://www.psoug.org
***@x.washington.edu
(replace x with u to respond)

Carl Kayser

2005-12-12 15:33:03 UTC

(1) It doesn't seem to exist in Sybase, at least with that name. (ASE 15.0
does have materialized [deterministic | computed] columns, but this seems
different from your description.)

(2) I imagine that the equivalent functionality could be done with triggers
on the base table(s) that would update the aggregate/dimension/whatever
"materialized view". Apparently Oracle materialized views are more
efficient than this approach?

(3) I would think that this capability is not best suited for OLTP (too
frequent activity) environments. And static tables can be created/updated
in batch DSS environments.

(4) Why does Oracle designate a table as a materialized "view"?

Galen Boyer

2005-12-14 04:06:03 UTC

Hi I'm new to Sybase from Oracle. Does Sybase have anything
equivalent to materialzed views? If not want is the best practice
for creating views with huge amounts of data? Other than indexing
the tables. Thanks for help.

For those not familiar with Oracle materialized views ... they are
tables (not views) that refresh themselves based on a SQL statement
based on criteria supplied during their creation. Multiple
materialized views can be made to refresh as part of a single
transaction so as to guarantee their data consistency.
I too am looking for the answer.
--
Daniel A. Morgan
http://www.psoug.org
(replace x with u to respond)

Its a reasonable way to look at it, for understanding them.

Apparently Oracle materialized views are more efficient than this
approach?

Oracle uses its own logging mechanism on the tables being queried to
know how to update the table.

(3) I would think that this capability is not best suited for OLTP
(too frequent activity) environments.

It can be tuned quite a bit by a DBA that knows what she is doing.

And static tables can be created/updated in batch DSS environments.
(4) Why does Oracle designate a table as a materialized "view"?

There are two uses for these very nifty features. One is really a
replacment for homegrown batch processes which build reporting
tables/marts/dws. Instead of putting the insert/update/refresh logic in
place with code, UNIX scripts, cron, ... jobs, just code the SQL that
gets the data needed, slap "create materialized view" on top of it and
then set the parameters of the materialized view to let Oracle "batch"
it up. You can then, either query the view directly, or use them to
insert to the final tables.

But, the much more interesting usage (even though the previous one is
quite nice), IMHO, is that these things can become very sophisticated
indexes for an already working application. It involves setting the
instance parameter, query_rewrite to true (I think thats the parm and
setting). Without changing anything about an application, one can speed
up retrieval by adding an index, correct? Okay, suppose your query that
is taking a long time is something like:

SELECT code, count(*)
FROM some_table
GROUP BY code;

This is sitting somewhere in your application and cannot be touched, and
is taking too long. Well,

CREATE materialized view code_grouper_byer
AS
SELECT code, count(*)
FROM some_table
GROUP BY code;

Then, with query_rewrite enabled, the next time the code in the app
runs, Oracle will know by its meta data that it already has the answer
stored by the materialized view and "rewrite" the query to access the
materialized view. As data changes happen to the underlying
"some_table", Oracle will keep code_grouper_byer up-do-date as well.
The complexity of the view's code can be pretty hairy, and Oracle
usually can handle it. I think the limitation it has is when you use
analytics within the view.

--
Galen Boyer

sybdba

2005-12-14 17:12:36 UTC

Since this work seems to be dependent on committed work on tables, the
equivalent could be done in ASE by using ASE Replicator (or full blown
Rep Server).
You can write all the procedural logic you want and update any tables
you want, using this event based mechanism, so what is the big deal?

regards,
sybdba

Hi I'm new to Sybase from Oracle. Does Sybase have anything
equivalent to materialzed views? If not want is the best practice
for creating views with huge amounts of data? Other than indexing
the tables. Thanks for help.

For those not familiar with Oracle materialized views ... they are
tables (not views) that refresh themselves based on a SQL statement
based on criteria supplied during their creation. Multiple
materialized views can be made to refresh as part of a single
transaction so as to guarantee their data consistency.
I too am looking for the answer.
--
Daniel A. Morgan
http://www.psoug.org
(replace x with u to respond)

Its a reasonable way to look at it, for understanding them.

Apparently Oracle materialized views are more efficient than this
approach?

Oracle uses its own logging mechanism on the tables being queried to
know how to update the table.

(3) I would think that this capability is not best suited for OLTP
(too frequent activity) environments.

It can be tuned quite a bit by a DBA that knows what she is doing.

And static tables can be created/updated in batch DSS environments.
(4) Why does Oracle designate a table as a materialized "view"?

There are two uses for these very nifty features. One is really a
replacment for homegrown batch processes which build reporting
tables/marts/dws. Instead of putting the insert/update/refresh logic in
place with code, UNIX scripts, cron, ... jobs, just code the SQL that
gets the data needed, slap "create materialized view" on top of it and
then set the parameters of the materialized view to let Oracle "batch"
it up. You can then, either query the view directly, or use them to
insert to the final tables.
But, the much more interesting usage (even though the previous one is
quite nice), IMHO, is that these things can become very sophisticated
indexes for an already working application. It involves setting the
instance parameter, query_rewrite to true (I think thats the parm and
setting). Without changing anything about an application, one can speed
up retrieval by adding an index, correct? Okay, suppose your query that
SELECT code, count(*)
FROM some_table
GROUP BY code;
This is sitting somewhere in your application and cannot be touched, and
is taking too long. Well,
CREATE materialized view code_grouper_byer
AS
SELECT code, count(*)
FROM some_table
GROUP BY code;
Then, with query_rewrite enabled, the next time the code in the app
runs, Oracle will know by its meta data that it already has the answer
stored by the materialized view and "rewrite" the query to access the
materialized view. As data changes happen to the underlying
"some_table", Oracle will keep code_grouper_byer up-do-date as well.
The complexity of the view's code can be pretty hairy, and Oracle
usually can handle it. I think the limitation it has is when you use
analytics within the view.
--
Galen Boyer

Galen Boyer

2005-12-15 01:48:02 UTC

Post by sybdba
Since this work seems to be dependent on committed work on tables, the
equivalent could be done in ASE by using ASE Replicator (or full blown
Rep Server).
You can write all the procedural logic you want and update any tables
you want, using this event based mechanism,

I guess you could. I'll take your work on it.

Post by sybdba
so what is the big deal?

Well, in Oracle, Materialized Views are used by many people when the
need fits their environments. That is quite a bit different than
someone saying, "you COULD do such and such". So the question is, do
you do what you are proposing? Is it something that is easy to do?
Have you proven it over and over and never even had to debug it? ...

Post by sybdba
regards,
sybdba

--
Galen Boyer

Mike Epprecht (SQL MVP)

2005-12-15 07:11:22 UTC

Hi

The plain fact is that Sybase does not support it.

DB2, Oracle and Microsoft SQL Server do. We have a system that needed it due
to performance requirements so they moved off Sybase as the Sybase option
was too complicated and too much work to implement by hand. It must either
work out the box or it is not a supportable solution.

Regards

Mike

I guess you could. I'll take your work on it.

Post by sybdba
so what is the big deal?

Post by sybdba
regards,
sybdba

--
Galen Boyer

Kristian Damm Jensen

2005-12-21 15:31:33 UTC

I guess you could. I'll take your work on it.

Post by sybdba
so what is the big deal?

As I have implemented a materialised view using triggers only a few month
ago, I have had some thoughts on this.

First, we decided on a materialised view because the SQL-command for the
normal view would have become quite complex and we feared poor performance.

Secondly, maintaining the materialised view as quite simple, because there
was a one-to-one correspondence between rows in the view and row in *one*
other table. Furthermore the only update ever necessary on the view would be
on a status column matching exactly an equivalent column in the other table.

If on the other hand, the view is based on a number of other tables, each of
may have data inserted and updated independently, I would be more wary of
such an undertaking. The complexity of the maintenance could be great
indeed, even without considering problems with multithreaded updating:
syncronisation, locking, etc.

Regards,
Kristian Damm Jensen

Galen Boyer

2005-12-22 00:02:02 UTC

Post by Kristian Damm Jensen
If on the other hand, the view is based on a number of other tables,
each of may have data inserted and updated independently, I would be
more wary of such an undertaking. The complexity of the maintenance
could be great indeed, even without considering problems with
multithreaded updating: syncronisation, locking, etc.

Multiple tables aren't an issue with Oracle for Materialized Views.

--
Galen Boyer

Kristian Damm Jensen

2005-12-22 19:49:18 UTC

Multiple tables aren't an issue with Oracle for Materialized Views.

Meaning - I suppose - that Oracle has no problems materializing a view,
regardless of the complexity and number of tables in the defining select.

I know that.

I was simply presenting som considerations on how to implement something
like materailized views in Sybase ASE.

Regards,
Kristian Damm Jensen

Carl Kayser

2005-12-16 19:25:18 UTC

Apparently Sybase ASA (versus ASE) will have it next year:

http://www.sdtimes.com/article/special-20051215-01.html
"In August, Sybase demonstrated Jasper, the code name for the next edition
of the SQL Anywhere mobile data management and synchronization solution.
Jasper includes new failover capabilities through server mirroring,
materialized views to make it easier for developers to specify and store
precomputed query results, and additional monitoring tools to identify
bottlenecks."

julien Touche

2005-12-11 17:08:36 UTC