Sybase Database - Sybase and materialized views

Author

Sybase and materialized views

supergel

2005-12-10, 8:23 pm

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.

DA Morgan

2005-12-10, 8:23 pm

supergel wrote:
> 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
damorgan@x.washington.edu
(replace x with u to respond)

julien Touche

2005-12-11, 11:23 am

supergel wrote on 10/12/2005 22:32:
> 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.

maybe something like proxy tables ?
http://www.sybase.com/detail?id=1029418

Regards,

Julien

Carl Kayser

2005-12-12, 11:23 am

"DA Morgan" <damorgan@psoug.org> wrote in message
news:1134252669.433523@jetspin.drizzle.com...
> supergel wrote:
>
> 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
> 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". 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, 3:23 am

On Mon, 12 Dec 2005, kayser_c@bls.gov wrote:
>
> "DA Morgan" <damorgan@psoug.org> wrote in message
> news:1134252669.433523@jetspin.drizzle.com...
>
> (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 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, 11:23 am

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:
> On Mon, 12 Dec 2005, kayser_c@bls.gov wrote:
>
> Its a reasonable way to look at it, for understanding them.
>
>
> Oracle uses its own logging mechanism on the tables being queried to
> know how to update the table.
>
>
> It can be tuned quite a bit by a DBA that knows what she is doing.
>
>
> 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

Galen Boyer

2005-12-14, 8:23 pm

On 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 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? ...

> regards,
> sybdba

--
Galen Boyer

Mike Epprecht \(SQL MVP\)

2005-12-15, 3:23 am

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

"Galen Boyer" <galen_boyer@yahoo.com> wrote in message
news:ud5jzotc4.fsf@rcn.com...
> On 14 Dec 2005, sybdba@yahoo.com wrote:
>
> I guess you could. I'll take your work on it.
>
>
> 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? ...
>
>
>
> --
> Galen Boyer

Carl Kayser

2005-12-16, 8:23 pm

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

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

> supergel wrote:
>
> 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
> damorgan@x.washington.edu
> (replace x with u to respond)

Kristian Damm Jensen

2005-12-21, 11:23 am

"Galen Boyer" <galen_boyer@yahoo.com> wrote in message
news:ud5jzotc4.fsf@rcn.com...
> On 14 Dec 2005, sybdba@yahoo.com wrote:
>
> I guess you could. I'll take your work on it.
>
>
> 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? ...

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-21, 8:23 pm

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

--
Galen Boyer

Kristian Damm Jensen

2005-12-22, 8:23 pm

"Galen Boyer" <galen_boyer@yahoo.com> wrote in message
news:u1x05q18g.fsf@rcn.com...
> On Wed, 21 Dec 2005, kristiandamm@yahoo.dk wrote:
>
> 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

Sponsored Links