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

For those not familiar with 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.

"DA Morgan" <damorgan@psoug.orgwrote in message
news:1134252669.433523@jetspin.drizzle.com
supergel wrote:
>Hi
>I'm new to Sybase from Does Sybase have anything equivalent to
>materialzed views? If not want is the best practice for creating views
>with
>huge amounts of data? than indexing the tables.
>Thanks for help.
>
For those not familiar with 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.

Mon, 12 Dec 2005, kayser_c@bls.gov wrote:

"DA Morgan" <damorgan@psoug.orgwrote in message
news:1134252669.433523@jetspin.drizzle.com
>supergel wrote:
Hi I'm new to Sybase from Does Sybase have anything
equivalent to materialzed views? If not want is the best practice
for creating views with huge amounts of data? than indexing
the tables. Thanks for help.
>>
>For those not familiar with 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
>damorgan@x.washington.edu
>(replace x with u to respond)
>
(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".

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

Apparently materialized views are more efficient than this
approach?

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 LTP
(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 designate a table as a materialized "view"?

There are two uses for these very nifty features. 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 "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), IMH, 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? , suppose your query that
is taking a long time is something like:

SELECT code, count(*)
FRM some_table
GRUP 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(*)
FRM some_table
GRUP BY code;

Then, with query_rewrite enabled, the next time the code in the app
runs, 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", will keep code_grouper_byer up-do-date as well.
The complexity of the view's code can be pretty hairy, and
usually can handle it. I think the limitation it has is when you use
analytics within the view.

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

Galen Boyer wrote:
Mon, 12 Dec 2005, kayser_c@bls.gov wrote:

"DA Morgan" <damorgan@psoug.orgwrote in message
news:1134252669.433523@jetspin.drizzle.com
>supergel wrote:
Hi I'm new to Sybase from Does Sybase have anything
equivalent to materialzed views? If not want is the best practice
for creating views with huge amounts of data? than indexing
the tables. Thanks for help.
>>
>For those not familiar with 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
>damorgan@x.washington.edu
>(replace x with u to respond)
>
(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".

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

Apparently materialized views are more efficient than this
approach?

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 LTP
(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 designate a table as a materialized "view"?

There are two uses for these very nifty features. 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 "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), IMH, 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? , suppose your query that
is taking a long time is something like:

SELECT code, count(*)
FRM some_table
GRUP 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(*)
FRM some_table
GRUP BY code;

Then, with query_rewrite enabled, the next time the code in the app
runs, 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", will keep code_grouper_byer up-do-date as well.
The complexity of the view's code can be pretty hairy, and
usually can handle it. I think the limitation it has is when you use
analytics within the view.

14 Dec 2005, sybdba@yahoo.com wrote:
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.

so what is the big deal?

Well, in , Materialized Views are used by many people when the
need fits their environments. That is quite a bit different than
someone saying, "you CULD 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?

regards,
sybdba

Hi

The plain fact is that Sybase does not support it.

DB2, 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

"Galen Boyer" <galen_boyer@yahoo.comwrote in message
news:ud5jzotc4.fsf@rcn.com
14 Dec 2005, sybdba@yahoo.com wrote:
>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.
>
>so what is the big deal?
>
Well, in , Materialized Views are used by many people when the
need fits their environments. That is quite a bit different than
someone saying, "you CULD 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?
>
>
>regards,
>sybdba
>

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

"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."

"DA Morgan" <damorgan@psoug.orgwrote in message
news:1134252669.433523@jetspin.drizzle.com

supergel wrote:
>Hi
>I'm new to Sybase from Does Sybase have anything equivalent to
>materialzed views? If not want is the best practice for creating views
>with
>huge amounts of data? than indexing the tables.
>Thanks for help.
>
For those not familiar with 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.

Wed, 21 Dec 2005, kristiandamm@yahoo.dk wrote:

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 for Materialized Views.